Hello Excellers and welcome back another #FormulaFriday #Excel blog post in my 2020 Excel series. I will share a solution to a question asked recently. How to find how many numbers in your list are greater than the average. This is a useful formula solution using the COUNTIF Function. It can be used to answer a number of questions such as
HOW MANY?
- students scored higher than average
- employees are paid higher than the average
- sales reps made sales higher than the average
The COUNTIF Syntax.
=COUNTIF(range, criteria)
Where
range – is the range of cells that you want to count based on the criteria
criteria – is the criteria used to determine which cells to count
An Example Using COUNTIF Function.
Let’s take an example and find out how many employees are paid higher than the average salary.
First, let’s calculate the average salary. Easy. See below.
We have used a value in another cell as part of the criteria, so we have used the ampersand (&) character to concatenate the cell value and the greater than symbol. As we are using a text string ( the greater than symbol) we must use the double quotation marks with it.
Now we have the average salary we can calculate using the COUNTIF Function how many employees earn more than the average.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below
