Contents

1 Excel Function Details:
2 Where To Find The AVERAGE Function
3 Using AVERAGE With Other Data Types:
4 Simple Example
5 Advanced Example
6 Troubleshooting & Errors
7 What Is The AVERAGE Function Useful For?
8 Similar Useful Functions
9 Conclusion

The AVERAGE function in Excel calculates the mean value of a group of numbers, balancing out the extremes.

It’s like finding the middle ground in a set of data points.

Excel Function Details:

Available in:	All versions of Microsoft Excel
User Level:	Beginners
Inputs:	“Number1”: The initial number or range for averaging.
Inputs:	“Number2, …”: Additional numbers or ranges, if any.
Output:	Computes the mean of the numbers.
Wildcards:	Not supported by AVERAGE.
Case Sensitive:	Not case-sensitive.

Where To Find The AVERAGE Function

To find the AVERAGE function, go to the top of the ribbon on your screen.

Click Formulas > AutoSum, AVERAGE.

Using AVERAGE With Other Data Types:

Average function in excel working with other data types

Dates: Excel treats dates as serial numbers, so averaging dates gives you a midpoint date
Currencies: Treated as numbers and included in the average.
Times: Times also work with the average function!
Percentages: Percentages can also be used effectively.
Text: Ignored by AVERAGE. You cannot use average for text.

Simple Example

Imagine you spent £200, £150, and £250 on groceries over the past three months.

To find your average spending, type these amounts into cells A2, A3, and A4.

In cell C2, enter the formula

=AVERAGE(A2:A4)

The result in A4 will show your average grocery spending!

Example of the average function working in excel

If your data isn’t formatted perfectly, the function will not work as intended. A great tip is to use the TRIM Function to clean your data to avoid this.

Advanced Example

Let’s say you have a list of weekly sales figures for the quarter and want to find the average sales for weeks with sales over £500 to focus on high-performing weeks.

Your sales data is in cells B1 through B12. In cell C1, enter the formula

=AVERAGEIF(A2:A8, “>500”)

This formula will calculate the average sales for all weeks where the sales figure is greater than £500, giving you insight into your best sales weeks!

2nd Example of the average function working in excel

The AVERAGE function can also be used to create heat maps, a colour-coded visualisation of data.

💡 Trainer Insight:

One way to make your use of the AVERAGE function stand out is to compare your averages against dynamic benchmarks – like industry standards, previous months, or team targets. You can do this by using conditional formatting or helper formulas.

On our Excel courses, we often teach students to layer moving averages or weighted averages into dashboards, so the average isn’t just a number – it becomes a benchmark tool for spotting trends, anomalies, and even forecasting.

Troubleshooting & Errors

Error 1: #DIV/0! Error

Cause: No numbers to average; range is empty or in an unsupported data type.

Solution: Ensure the range includes numeric values.

Error 2: #VALUE! Error

Cause: Non-numeric characters in AVERAGE range.

Solution: Remove or convert text to numbers in the range.

Error 3: #NAME? Error

Cause: Typo in formula, like a misspelled function name.

Solution: Check and correct spelling in the formula.

What Is The AVERAGE Function Useful For?

The AVERAGE function is a tool that simplifies several routine tasks in Excel:

1. Data Cleaning:	AVERAGE is great for identifying outliers or errors in a dataset by comparing the average of a data range against individual entries.
2. Consistency Checks:	By calculating averages across different data sets, you can perform consistency checks to ensure data integrity across reports.
3. Data Analysis	You can combine the AVERAGE function with the COUNT function for data analysis and to and make powerful observations.

Similar Useful Functions

Other functions similar to the average function that are easy for you to include:

MEDIAN: Finds the midpoint of your data
AVERAGEIF: Averages your dataset if a condition is met
MODE: Calculates the most frequently occurring value in your data

Conclusion

The AVERAGE function is a staple in Excel’s suite of tools, offering a straightforward way to analyse sets of numbers.

It’s essential for anyone looking to quickly find the central tendency of data, from simple personal tasks to complex business analyses.

For a business focused example, see how AVERAGE can make a moving average forecast here.