Excel Averageif & Averageifs are a very useful function in case you want to quickly change the conditions of what range you are looking for an average. Instead of changing the range each time, you can apply a value to help easily change the criteria.
Excel Averageif & Averageifs example
In order to demonstrate the capabilities of this function I will use a US cities and states database I downloaded from the internet. The database has ~900 rows. This is a small scale print screen of the database :
In column N you can see an example of Averageif. Based on the US database above, I wanted to see what the average population is for each state. Averageif only allows one condition to be checked for the averaging, Avergaifs allow you to define many conditions. In column Q you can see an example of Averageifs with many conditions – not only am I averaging the population by state,I am also including cities with a name shorter than 10 characters and the population is more than 300K.
Excel Averageif, Averageifs code breakdown for easy copying
Column P = Averageif($C:$C,$J14,$F:$F)
Column Q = Averageifs($F:$F,$C:$C,$J14,$H:$H,”<10″,$F:$F,”>300000)
Detailed explanation of the function Averageif
1st argument – Range where the criteria will be checked
2nd argument – Value to be checked in the range
3rd argument – Range to be averaged for each row where there is a match
Detailed explanation of the function Averageifs
1st argument – Range to be averaged for each row where there is a match
2nd argument – Range where the criteria will be checked
3rd argument -Value to be checked in the range
Each new argument couple will be the range and value based on the 2nd and 3rd example.
Averageif – for simple averages, Averageifs for complicated averages these formulas are a must of any Excel user. Once you start using them, you’ll never look back! Here are some more functions below.