Average Function

If you haven't looked at the Sum function yet, we recommend you look at that process first. The Excel way of handling the average function is conceptually identical to the way the sum function is handled.

Also, we use the Average function as the way to illustrate how to enter ANY function available in Excel. You might want to study this tutorial for purposes other than the Average function.

With that out of the way, let's take a look at the average function. First, the average function will compute the arithmetic mean. That is, it will sum the numbers in a range (horizontal or vertical) and divide by the number of numbers in the range.

The actual function will take the form of: =AVERAGE(beginning cell : ending cell)

Beginning	Ending	Function
c5	k5	=average(c5:k5)
b6	b26	=average(b6:b26)
D8	M8	=average(d8:m8)

Note that in each case, the function begins with the = and is followed by the name of the function. The left parenthesis is "pushed" tight against the last character in the function name. [ie. =average( and not =average ( ]

Also note the : is used to mean that Excel is to use all numbers from the beginning cell through the ending cell.

One example of how the finished result might look is provided.

	A	B	C	D	E
1		12
2		34
3		56
4		14
5		7
6
7	Average	24.6	=average(b1:b5)

If the formula entered in cell C7 is entered in cell B7, the result will be the average of the numbers in the range from B1 to B5.

Comment: The average function may be superior to the combination of the Sum function and a division. Suppose there are instances where there are missing numbers. Consider this example. (Note the missing value in Cell B3.)

	A	B	C	D	E
1		12
2		34
3
4		14
5		7
6			=sum(b1:b5)/4
7	Average	16.75	=average(b1:b5)

The two functions would result in the exact same result. If you have a long list of numbers with several blank cells in the list, you must correctly count the number of non-blank cells to determine the correct number to use in the division. Using the Sum function and counting requires accuracy in your counting process. The Average function just averages "whatever numbers" are in the list.

The Average function may not be one of the more popular statistical functions you will use. Consequently, you may forget whether the function begins =average or =avg or =avrg or any possible combinations of letters to match what Excel chooses to name its version of the average function. Excel offers an easy path to insert the average function. This path is important because it illustrates how you may access ALL of the functions installed in your version of Excel. We will examine two very similar ways.

Let's use the same problem. First you select the cell where the average should appear in your spreadsheet. In this case, that is Cell E9.

If you remember the Average function format, you can type it in here. If not, you may use one of the two methods that follow to let Excel insert it for you.

Method 1 - Using the Toolbar

Located on the Toolbar is a symbol of a function. You may hover your mouse over this button and see the tip:

"Paste Function".

Method 2 - Using Insert from the Menu

If you select Insert from the top menu and scroll down to Function, you will move the highlighted blue area to the correct choice.

Note the function symbol to the left of the Function text in the drop-down box. It is identical to the function symbol on the Toolbar used in Method 1.

Using either method to select the Paste Function option will bring up a "function wizard" which will let you be sure you have selected the correct function for your need.

The left window lets you select the type of function you wish to use. Once you select the correct category on the left, the functions in that category will appear in the right window.

While you are here, notice the number of different categories of function. This might be a clue that there are many, many functions available through Excel. Using this Paste Function process, you can access each and every one.

Also, you shouldn't shy away from the functions just because you aren't familiar with each and every type of function. If you don't use Trig in your work, you won't be interested in the Trig functions that are there. If you "shake" at the thought of statistics much beyond the average, you probably will never need the linear regression function. Let those who use those activities have it and just use the functions you need!

Off the soapbox and back to the Average function....

We note that averaging is a statistical function, so we select the Statistical Category.

The statistical functions are displayed alphabetically, and we select the Average function and click OK.

Excel uses what we call a "paste wizard". This lets you look at what is going to happen and make any adjustments if any are needed. This wizard pops up and can be moved on the screen if you need.

When the "Wizard" appears, we will drag it around on the screen so the spreadsheet values are also visible. (This may not be possible with the particular arrangement of your values.) Displayed in the wizard are the beginning and ending cells it predicts you will want to use. Also, Excel provides a short sentence or two which tells you what this function does and what you might expect for a result. If this display is correct, you would click OK.

Excel would then display the final result on your spreadsheet.

Note that the Entry Field contains the exact set of keystrokes you would have typed had you remembered the name of the function!

The process illustrated in this example is probably more important for explaining how to enter ANY function than explaining how to enter the Average function.