There’s average and there’s average. OK that’s a bit cryptic so let me explain. Take the following spreadsheet that I’m using to keep track of my preparation for a marathon (it’s a demo file – I’m not really running a marathon!!)

For any given week, I want to know the average number of miles that I have run per day and for demo purposes I have done this in 3 ways:

B24 contains a function =SUM(B15:B21) which calculates the total number of miles run.

B25 contains a function =COUNT(B15:B21) which calculates the number of days that I have run.

B27 contains a formula =B24/B25 which calculates the average miles run per day. The result is incorrect because it is dividing 66 (total) by 7 (number of days). It should be dividing by 6 because I did not run on Thursday. The COUNT function counts the number of cells in a range that contain a numeric value (whether that value is positive, negative or zero).

B28 contains a function =AVERAGE(B15:B21) which calulates the average of the values in B15:B21. The AVERAGE function is an alternative to =SUM(range) / COUNT(range) therefore in this case, the result is also incorrect.

To calculate the correct average we can either leave B18 (Thursday) blank and use either of the above formulas, or use the AVERAGEIF function which calculates the average of the values in a range based on a criteria.

The syntax for the AVERAGEIF function is =AVERAGEIF(range,criteria)

The function in B29 is =AVERAGEIF(B15:B21,"<>0") which calculates the average of the values in the range B15:B21 but only includes cells where the value does not equal zero.

Download the sample file here – it’s a zip file containing XLS and XLSX versions.