Averages

You may mostly use the SUM function, but Excel also is pretty handy at calculating averages using AVERAGE.

The brief exercises are in the Average Exercise file.  The results are in the Average Result file.

Here we have a list of sales for a coffeeshop.

The average function works much like sum.  The formula for the average would be:   =AVERAGE(B2:B7)

You could also put in the calculation by clicking the down arrow in the sum icon on the toolbar (this might be something only available in Excel 2002 or other more recent versions).

But now the owner has decided to add web surfing.  There are no sales yet, but he has inserted a line on the sales sheet for this new line.  Will the blank information affect the average?

As you can see blanks do not affect the average.  You do not need to modify your formula.  But what if the new category showed zero rather than blank?

Zeros are a different matter.  They get counted.  You may want that.  But what if you don't or if you want to show the average also without the zeros?  There are a couple different ways to do it.

One is to use an array, which is a range of cells.  We perform a test on the range of cells, ignoring the blank cells.  The formula is:

{=AVERAGE(IF(B2:B8<>0,B2:B8))}

As a good formula building practice, let's look at it from the inside out:

There is another way we could do it.  This is a longer formula:

=SUM(B2:B8)/COUNTIF(B2:B8,"<>0")

The formula has two parts:

There is a third way.  We don't like this as well since this formula would have to be manually adjusted for any changes.  With a formula like this, you are asking for errors if you know there will be changes down the road.

For this way, you select the average function, then while holding down the Ctrl key, select the range you want to average.  We skip over the blank cell.

While we showed these techniques to take care of average problems with zeros, you could also use these solutions in other ways.  For example, you could use them to calculate the average sale for all lines with sales above $250,000 in the above example.  In that case, we would change the test from "<>0" to ">250000".

=AVERAGE(IF(B2:B8>250000,B2:B8))

Averages are a powerful feature in Excel. Know how to handle zeros or use special conditions to get even more out of your average calculations.

Return To Excel Tips Index