AVERAGE Function

Perhaps you’ve mastered the SUM function, and you’re looking for the next challenge, or maybe you just need a refresher on how an Average function works; either way, we’re going to unwrap this essential process right now.

Much like the SUM function, the AVERAGE function is ubiquitous in the spreadsheet world and rightly so. The average function gives us a number representing the central or typical value in a set of data calculated by dividing the sum of a group of values by the number of values in the group.

Thankfully Google Sheets does all the heavy lifting with the AVERAGE function, so we don’t need to jump through too many hoops or even understand its logic.

We will explore this using this basic table below for maximum and minimum temperatures across various regions.

With the least effort, Google Sheets seems to know what I want before I’ve started. I’ve simply typed equals (=) in the cell to the left of the data, and Google is suggesting averaging the first row of data perfect, this is precisely what we need, so just hit tab or enter on your keyboard to accept it.

If you don’t see the AVERAGE function appear with the Google AI, you should start typing the word ‘average’; then, you’ll get at least one option to show up. For example, you can see below just typing ‘av’ was enough to bring up two options; the first being the suggested range - AVERAGE(C3:D3) and the second just shows AVERAGE.

Both options will work, but the first one will get the results faster since you won’t even need to select the range with this one, so just select it and hit enter on your keyboard to accept it.

You’ll also notice that the function gives you the answer before you even accept it (11.85).

If you choose the second option or type it out manually, you’ll need to select the range by clicking on the first cell in the range (C3), holding the click down and dragging across to the last cell in the range (D3). All you need to do now is close the parenthesis ‘)’ to complete the formula and hit enter.

Any function you use will require an opening parenthesis after the function name followed by the range and potentially any other function arguments (these do not apply in this example) and, lastly, a closing parenthesis to complete the formula.

• You’ll start with an equals sign ‘=’

• Then the function name ‘AVERAGE’

• Then opening parenthesis ‘(‘

• Then the range that we want to get an average of ‘C3:D3’

• Finally, the closing parenthesis ‘)’

• The whole thing looks like this: =AVERAGE(C3:D3)

When you hit enter, Google will most likely offer to automatically fill the cells below it with the same formula to save you time, so go ahead and click on the green tick to accept it.

You can reject this suggestion by clicking on the cross or pressing the escape key on your keyboard. It’s a good time saver; I always use this feature.

There you have it, an average temperature for all regions in the table, and you only had to type the formula out once.

How does an average calculation work?

So what’s going on under the hood with this function? As alluded to earlier, the average is the sum of both Max and Min temperatures divided by the total number of temperatures in the set, which in this case is two; we have one maximum value and one minimum value.

We need to include the parenthesis in this formula to ensure the sum or addition part is done first, followed by the divide part. So you can see the answer is 11.85, the same result the average function gave us but more importantly, it is the correct result.

If you omitted the parenthesis, you would get a completely different result, as shown below. The reason is that divide comes before addition when using maths, so without the parenthesis, what’s happening is 7.8 divided by 2 plus 15.9 = 19.8

The lesson here is always to include parenthesis when you have a mixture of mathematical operations since the parenthesis has the highest precedence, followed by multiplication, division, addition and subtraction.

There are a few other beneficial functions in the average function family, for example, AVERAGEIF, AVERAGEIFS and AVERAGE.WEIGHTED, and of course, they all have their specific use cases. Still, since they require thorough explanations respectively, we’ll cover them in separate articles to ensure no stone is left unturned.