Using Excel to Calculate an Average Based on Criteria
You can use Excel to calculate an average. Picture this: You need to calculate an average from a list of numbers, using only numbers in the list that match a condition. In other words, calculate the average using numbers if they match a given criterion. Excel is full of surprises, and for this trick, you can pull out a great function named AVERAGEIF.
The following figure shows a list of employees, along with the years when they were hired, how many accounts they’ve opened so far in 2016, and (for some) a message saying that they’re doing a great job.
At the top of the worksheet is the average number of accounts opened per employee in 2015. Below is the expected number of accounts to open in 2016. The average of 44 for 2015 is just a static number. 2015 is over, and this is the final average. It’s so — last year! What about 2016? This is where it gets interesting.
To calculate the expected number of accounts to open in 2016, a calculation is used to derive the number as an average based on the number of accounts that have been opened so far for the year. This is a technique management might use to increase competition among employees. In other words, instead of just fixing an expected number, such as 48 (which would be 10 percent growth over 2015), the expected number of accounts to open is based on how employees have been performing so far for the year.
In cell C4, the AVERAGEIF function is used. This is how it is entered:
=AVERAGEIF(C8:C16,">" & C3)
What this does is take the average of opened accounts from the list of accounts per employee, using only the numbers that are greater than the 44 opened in 2015. This is determined by the second argument in the function (“>” & C3), which states to determine the average using only values that are greater than 44, found in cell C3.
For example, Julie so far has opened only 40 accounts, so her count is not used in the average. On the other hand, Yolanda has opened 48 accounts, so her count is used in calculating the average.
When you average only the numbers greater than 2015’s 44, a higher number is returned for the expected openings for 2016. The calculated value for 2016 as of this point is 54. Next to each employee’s name, the message Great Job! appears if that employee is beating the average. Only those who have opened at least 54 accounts receive the message.
How? By using the IF function! In each cell where the message appears the IF function, compares whether the number of accounts the employee has opened so far is equal to or greater than the average shown in cell C4. Here is the formula in cell D4 showing that Harrison is doing a great job:
=IF(C8>=$C$4, "Great Job!", ")
The IF function tests a condition. If the condition is true, the first answer is displayed. If the condition is not true, the second answer is displayed.
So far, so good! At this company, the aggressive salespeople are rewarded, and the laggards are not given any praise.
It’s time to level the playing field. One thing not considered in this example is year of hire. Some employees were hired in 2016, so in fairness, the number of accounts opened so far in 2016 should not be compared with the high average.
The following figure shows an update of the worksheet. The single IF function is replaced with a set of nested IF functions: The outer IF takes into account whether the year of hire is 2016 (the actual condition test is to see if the year of hire is greater than 2015); and, if so, the inner IF function tests whether the employee’s number of opened accounts for the year so far is equal or greater than half of the overall average.
Now three possible outcomes are possible for the message:
Great Job! Keep cracking! The year is not over yet! No message
The Keep cracking! message appears for those hired in 2016 who have opened a number of accounts equal to or greater than half the average — it’s a rewarding message for those who are new and doing a good job. The rest of the employees are either superstars or in need of some help!
The formula in Cell D8 (and for each employee row in column D) is structured like this:
=IF((A8>$A$3), IF(C8>=($C$4/2), "Great Job!","Keep cracking! The year is not over yet!"),IF(C8>=$C$4,"Great Job!","))