Excel Average formula explained

So you have an array that you have to average.
It is very simple with Excel Average formula.
The Syntax is =AVERAGE(A1:A10)
Where A1:A10 is whatever you want to average.

Excel average


You can also select individual cells, by holding down the Ctrl key and selecting the cells you want to average.

So we know how to use the average formula. Great!

There is only one issue with the average formula.
It counts the zeros in the range as a non empty cell.
Unfortunately this skews our result.
Below I highlighted you the Zeros in red.

Average formula vs averageif formula

The solution for excel average with zeros:

The Excel AVERAGEIF formula

As you can see we can exclude the zeros with the AVERAGEIF formula.

The Syntax is =AVERAGEIF(range, criteria, average range)
So as you can see above the right hand side formula excludes the zeroes. Hence the result is different.

The AVERAGEIF formula is very similar to the COUNTIF formula

We made a facebook page for ExcelAngel.
Check out ExcelAngel on Facebook!

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]