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:
IF(B2:B8<>0,B2:B8)- we use IF to test what cells are not zero and then for the TRUE part of the IF statement (after the comma) we say what range to use that contains the non zero cells. Note that it will only count the non zero cells in the range since this is the TRUE part of the IF test.
=AVERAGE(IF(B2:B8<>0,B2:B8))- we take the average of the non zero cells
{=AVERAGE(IF(B2:B8<>0,B2:B8))}- we enter the formula by clicking Ctrl, Enter and Shift at the same time, so Excel knows it is working with an array (otherwise we would get an error)
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:
SUM(B2:B8)- we add up the sales
COUNTIF(B2:B8,"<>0")- we count the non zero using COUNTIF, which has two parts. First, what is the range. Second, what is the criteria (make sure you use quotes)
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.