How to Do Average in Excel

Calculating the average of a set of data is a basic and essential task in data analysis. Excel offers several ways to quickly and easily calculate the average of a group of numbers across rows, columns, and even multiple worksheets. By using the built-in functions and formulas, you can easily calculate the average of data in Excel without the need for complex mathematical calculations. In this blog post, we’ll explore the different methods to calculate the average in Excel, step-by-step.

Using the AVERAGE Function

The first and easiest way to calculate the average of data in Excel is by using the AVERAGE function. This function returns the average (arithmetic mean) of a set of numbers. Here are the steps to use the AVERAGE function:

1. Select the cell where you want the average to appear.
2. Type =AVERAGE( in the formula bar.
3. Select the range of cells that you want to average, separated by commas or using a colon to indicate a range (e.g., A1:A10).
4. Type ) and press Enter or Return on your keyboard.
5. The result will be displayed in the selected cell.

Example

Suppose we have the following data:

If we want to calculate the average price, we would select an empty cell and enter =AVERAGE(B2:B4). The resulting average would be $15.00.

Using the AutoAverage Feature

Excel also has an AutoAverage feature that automatically calculates the average of a range of cells as you add new data to the range. Here are the steps to use AutoAverage:

1. Select the range of cells where you want the average to appear.
2. Type the first value in the series and press Enter or Return on your keyboard.
3. Type the next value and press Enter or Return.
4. Continue entering values until you have entered all the data.
5. The average will be displayed in the lower-right corner of the selected range.

Example

Suppose we have the following data:

To use AutoAverage, we would select an empty cell below the Price column, enter the first value, press Enter, enter the second value, press Enter, and so on until all the values are entered. The resulting average would be displayed in the lower-right corner of the selected range.

Using the SUM Function to Calculate Average

Although not the conventional way to calculate the average of data, the SUM function can be used to derive the average. Here’s how:

1. Select the cell where you want the average to appear.
2. Type =SUM( in the formula bar.
3. Select the range of cells that you want to average, separated by commas or using a colon to indicate a range (e.g., A1:A10).
4. Type )/COUNT() and press Enter or Return on your keyboard.
5. The result will be displayed in the selected cell.

Example

Suppose we have the following data:

If we want to calculate the average price using the SUM function, we would select an empty cell and enter =SUM(B2:B4)/COUNT(B2:B4). The resulting average would be $15.00.

Handling Errors

When using the AVERAGE function, it’s important to be aware that it can return errors if the selected range includes cells that contain text or are blank. To avoid this, you can use the AVERAGEIF or AVERAGEIFS functions instead. These functions allow you to specify a criteria that determines which cells to include in the average.

Example

Suppose we have the following data:

If we try to calculate the average of the scores using =AVERAGE(B2:B5), we’ll get an error because one cell contains the #N/A error and another is blank. Instead, we can use the AVERAGEIF function to exclude these cells:

=AVERAGEIF(B2:B5, ">0")

This will calculate the average of only the cells that contain numbers greater than 0, giving us a result of 85.

Weighted Averages

In some cases, you may want to calculate a weighted average, where each value in the range is multiplied by a corresponding weight before being averaged. To achieve this, you can use the SUMPRODUCT function:

1. Select the cell where you want the weighted average to appear.
2. Type =SUMPRODUCT( in the formula bar.
3. Select the range of values that you want to average, followed by the range of weights, separated by a comma. For example, A1:A10,B1:B10.
4. Type )/SUM(B1:B10) and press Enter or Return on your keyboard.
5. The result will be displayed in the selected cell.

Example

Suppose we have the following data:

If we want to calculate the weighted average price based on the quantity sold, we would select an empty cell and enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). The resulting weighted average would be $15.54.

Now you can use these different methods to calculate average in Excel to make your mathematical tasks easier and quicker.

FAQ

Here are answers to some frequently asked questions about calculating averages in Excel:

Can you calculate the average of non-contiguous cells?

Yes, you can calculate the average of non-contiguous cells by selecting each range of cells while holding down the Ctrl key, or by selecting each cell individually while holding down the Ctrl key. Then use any of the methods described in this blog post to calculate the average.

Can you ignore zero values when calculating the average?

Yes, you can exclude zero values when calculating the average by using the AVERAGEIF or AVERAGEIFS functions. For example, if you want to calculate the average of a range of cells excluding any cells that contain zero, you can use =AVERAGEIF(range,">0").

Can you calculate the average of only the top or bottom values in a range?

Yes, you can use the AVERAGE and LARGE or SMALL functions to calculate the average of only the top or bottom values in a range. For example, to calculate the average of the top three values in a range, you can use =AVERAGE(LARGE(range,{1,2,3})).

Can you calculate the average of multiple worksheets?

Yes, you can calculate the average of data across multiple worksheets by using the 3D referencing feature in Excel. Simply include the sheet name before the range of cells you want to average, separated by an exclamation mark. For example, to calculate the average of values in cell A1 of Sheet1 and A1 of Sheet2, you can use =AVERAGE(Sheet1!A1, Sheet2!A1).

Why is my AVERAGE function returning an error?

If your AVERAGE function is returning an error, it may be because one or more cells in the selected range are empty or contain text data. To avoid this, you can use the AVERAGEIF or AVERAGEIFS functions to exclude certain cells based on specific criteria.