AVERAGE Function in Excel

The AVERAGE function which was introduced in Excel 2003, is one of the most popular and widely used functions in Excel to calculate the average of a group of numbers. To summarize the data in a business, analyse the grades or compare statistical values, the AVERAGE function is a quick and efficient way to find the central value in your data.

Why Should You Use the AVERAGE Function?

The AVERAGE function is a built-in feature in Excel that is designed to simplify the task of finding the mean value in a dataset by quickly calculating the sum of all the numerical values and then the count of those values will be divided with the sum of all values. It is a perfect function for financial analysis, statistical reports and comparing the values across data sets.

• Functionality: To calculate the mean of selected numeric values from a range or group of cells.
• Outcome: Produces a single numeric value, which is the average of all specified values.

Syntax of the AVERAGE Function

=AVERAGE(number1, [number2], …)

• number1 (mandatory): The first value or a range of cells to calculate the average.
• number2, … (optional): additional numbers or ranges you wish to include to calculate the average and can use up to a maximum of 255 arguments.

How to Use AVERAGE in Excel?

The AVERAGE function is easy to use by simply selecting the range or values you want to calculate and the function will sum all the numerical values and then divide by the count of numerical values which gives the mean.

Example: To find the average sales from a range from A1 to A10, you can use the following formula:
=AVERAGE(A1:A10)
This formula will return the mean of all values from the specified range.

=AVERAGE(A1:A10) 'Returns average of all numerical values from A1 to A10
=AVERAGE(B2:B11) 'Finding Average from a Range
=AVERAGE(B2:B5, C2:C5) 'Calculating the Average Across Multiple Ranges

=AVERAGEIF(B2:B10, ">50") 'Average of values from B2 to B10, where the values are greater than 50

=ROUND(AVERAGE(A1:A10), 2) 'Using AVERAGE in Combination with Other Functions

Key Features of the AVERAGE Function

• Numeric Data Only: The AVERAGE function calculates the mean for cells that contain numeric values only and automatically ignores text and blank cells.
• Multiple Ranges: You can specify multiple ranges at once or also individual cells by separating them with commas.
• Dynamic Updates: If any values are added or removed from the selected range, the AVERAGE function updates the result automatically for you.

Step-by-Step Examples

Example 1: Finding Average from a Range

Let’s suppose you have a list of test scores from range B2 to B11 and you want to find the average of the class.

1. Select the cell to get the result (e.g., B12).
2. Enter the following formula:
   =AVERAGE(B2:B11)
3. Press Enter.

Result: This will return the average score for all the entries from B2 to B11.

Example 2: Calculating the Average Across Multiple Ranges

Let’s suppose you have a list of test scores from range B2 to B11 and you want to find the average of the class.

1. Select the cell to get the result (e.g., B12).
2. Enter the following formula:
   =AVERAGE(B2:B11)
3. Press Enter.

Result: This will return the average score for all the entries from B2 to B11.

Example 3: Ignoring non-numeric data

If a range you want to find average has mixed data types such as numbers, texts, and blank cells, the AVERAGE function automatically excludes non-numeric cells. For example:

1. Select the cell to get the result  (e.g., C12).
2. Enter this formula:
   =AVERAGE(C1:C10)
3. Press Enter.

Result: This formula will calculate the average for numeric values from the selected range ignoring text and blanks.

Key Variations of the AVERAGE Function

Excel provides additional functions for different types of averages. Such as:

• AVERAGEA: Treats TRUE as 1 and FALSE as 0 and also numbers that are stored in text form, counting all non-empty cells.
• AVERAGEIF: Calculates the average of cells only when a specific condition passed is met.
• AVERAGEIFS: Calculates the average of cells only if one or more conditions passed are met.

Example: To find the average of values from B2 to B10, where the values are greater than 50, you can use the AVERAGEIF.

=AVERAGEIF(B2:B10, ">50")

This will calculate the average for the values in the range if they are greater than 50.

Using AVERAGE in Combination with Other Functions

The AVERAGE function can be used with other functions for more complex calculations in Excel such as IF, SUM, or ROUND to apply additional conditions or formatting.

Example: You want to find the average from A1 to A10 and also want to round the result to two decimal places:

=ROUND(AVERAGE(A1:A10), 2)

This formula will first calculate the average of the values in the selected range and then the output is rounded to two decimals.

Key Insights About AVERAGE

• The AVERAGE function only calculates the average for numeric values and ignores any text, logical values, and empty cells.
• Use AVERAGEA if you need logical values or text numbers to be included in your output.
• AVERAGEIF and AVERAGEIFS allow you to calculate the average based on specific criteria.

Conclusion

The AVERAGE function is an important tool for anyone who is working with numeric data in Excel. Whether you need a simple average for the entries or are handling large datasets, mastering this function will save you time and ensure accuracy in your reports and analysis. Keep in mind the different AVERAGE variations like AVERAGEA and AVERAGEIF to cover all your data counting needs.