A to Z of Excel Functions: AVERAGE

Today’s Excel function is AVERAGE and, you guessed correctly, it’s a function which lets you find out the average of a bunch of values.

I’ll start by explaining how averages work in terms of this function.  To find the arithmetic mean of a group of figures you simply add them up and divide by the number of figures there are.  Let’s say you had these figures:-

1,2,3,4,5

To find the average we add them together, which gives us a total of 15, and then divide by how many items there are in the set, which in this case is 5.

15/5 = 3

So, the average of these values is 3.  Easy to do, but much, much easier when you get Excel to do it.  The AVERAGE function looks like this:-

=AVERAGE(number 1, number 2, ….and so on)

Let’s see it at work using our example above:-

As you can see if you’re manually typing numbers in to average, it’s just a case of typing them into the function separated by commas.  Pressing enter after the closed bracket will return 3, as expected – try it.

You could also do the same, but instead of using manually typed numbers use cell references separated by commas – you can do this by either typing the cell references or by using your mouse to select the cells as you go (Excel will automatically fill in the references as you click):-

Pressing enter again will, again return 3 as the average value.

Even easier!! Rather than individually selecting the values you want to average you can refer to a range of cells and do it that way – you can do this by either typing in the range yourself (in the above case A1:E1), clicking and dragging to select the range you want to average, or by using a named range if you’ve got one set up for your data.  You can see the range method in the screenshot below:-


And again, press enter and your average value will appear in the list!

Simple, isn’t it?

Something worth bearing in mind is that in calculating averages Excel adds together all values and divides the total by the count of cells it finds with values within them.  If you have any blank cells in your range your average may not be as accurate as you might think -it all depends on what you’re doing with your data, but if you’ve got a blank cell which means a zero value it might be worthwhile filling the cell with a zero otherwise that cell wont be counted in the average calculation.  Just a heads up!

Leave a Reply

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