1. Education
You can opt-out at any time. Please refer to our privacy policy for contact information.

How to Use the AVERAGE Function in Excel

By

Statistics is a subject with a multitude of formulas. Many of these are relatively easy to use for small data sets. They can even be used in calculations by hand. For data sets with more than a handful of values, these formulas become very cumbersome and unwieldy for hand calculations. While it is important to understand the concepts behind the formulas, the use of statistical software becomes indispensable for quick and accurate calculations.

There are a number of statistical software packages. One that is commonly used for calculations is Microsoft Excel. There are a variety of different formulas that are programmed into Excel. Some of these deal with very basic descriptive statistics.

AVERAGE

One of the most basic calculations in descriptive statistics is the mean or average. This measurement of central tendency shows up throughout statistics. This is a relatively easy calculation to perform by hand. Excel eliminates some of the tedium of the calculation. Excel also includes some variations on this function.

To determine the mean of a list of numbers, type these numbers into cells in the spreadsheet. Then in an empty cell type what is in the quotation marks "=AVERAGE( ". At this point we can type the location of the cells where the data is and then close the parentheses with " ) ". This can be done by use of the following syntax. If our data is located in cells A2 to A10, then (omitting the quotation marks) "=AVERAGE(A2:A10)" will obtain the mean of the entries in cells A2 to A10.

Rather than typing the cells where our data is located, we can use a different method. This involves typing the first half of the formula "=AVERAGE( ", and clicking on the first cell where the data is located. A colored box will appear around the cell that we have selected. We then drag the mouse until we have selected all of the cells that contain our data. We finish this by closing the parentheses.

Considerations About AVERAGE

One important thing to realize about AVERAGE is how Excel deals with empty cells. Depending upon which option has been chosen, Excel with either ignore any empty cells in the range of selected cells or it will consider these cells to be zero. The default is to not include empty cells in the calculation.

Another consideration with the AVERAGE function in Excel is to recognize its limitations. This function will only find the mean of a total of 256 data values.

AVERAGEA

A function in Excel that is closely related to the AVERAGE function is called AVERAGEA. The AVERAGEA Excel function works much like AVERAGE, with the exception that a text representation of a number can be entered as an argument in AVERAGEA. Also logical terms of TRUE and FALSE can be entered into AVERAGEA. The function AVERAGEA recognizes FALSE as having the value zero and TRUE as having the value one.

AVERAGEIF

The AVERAGEIF function is another variation on AVERAGE. AVERAGEIF will calculate a mean for a selected range of cells, subject to some condition. The arguments for AVERAGEIF are as follows: AVERAGEIF(range of cells, condition). The condition is typed inside quotation marks such as “< 10” can include:

  • Statements about order such as less than a particular number. For less than 10 we would write "< 10".
  • Statements about order such as greater than a particular number. For greater than 10 we would write "> 10".
  • Statements about order such as equal to a particular number. For equal to 10 we would write "= 10".
  • The wildcard character * can be used in the statement of the condition for any number of characters.
  • The wildcard character ? can be used in the statement of the condition for a single character.

Even for something as simple as a mean, Excel is quite versatile with a variety of options. With a little bit of practice, anyone can use this software to calculate the mean of a set of data.

©2014 About.com. All rights reserved.