Using Excel’s AVERAGE Function in Excel

Microsoft Excel comes with a great function, AVERAGE, that will quickly average a range of cells that you specify. When you average numbers what you are doing is summing the total numbers and then dividing that sum by the count of numbers.

So let’s assume that your list of numbers are in cells A1 to cells A200. Using Excel’s AVERAGE Function will allow us to quickly find the average for this range. Simply write your AVERAGE function in another cell, B1 for example, and then hit enter.

For this example your AVERAGE Function formula would be written like so:

=AVERAGE(A1:A200)

You now have your average for that range!

But… there is one problem with routinely using the AVERAGE Function. The AVERAGE Function includes any numbers that are zero (0). This can result in results that you may not expect. As mentioned earlier, average is the sum of numbers divided but the count of numbers, which would include the zeros. A simple way around this is to combine to functions together into one formula. SUMPRODUCT and SUM.

So, to recalculate the average excluding zeros would be written like so:

=SUM(A1:A1200)/(SUMPRODUCT((A1:A200<>0)*1))

You can also combine the AVERAGE Function with the SUBTOTAL Function.

I hope you found this post on Excel’s AVERAGE Function useful.

Leave a Reply

Your email address will not be published. Required fields are marked *