AVERAGE Function Excel – Quick Guide!

Gif for the average function in Excel working on a small data set

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:

AVERAGE Function Syntax

Available in: All versions of Microsoft Excel
User Level: Beginners
Inputs: “Number1”: The initial number or range for averaging.
“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.

Not the function you’re looking for? Check out our guide to the Sum Function here.

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.

Understanding how formulas work in detail, and what data types they can be used with is key to taking advantage of Excel.

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


The result in A4 will show your average grocery spending!

Example of the average function working in excel


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

You can access another use case for the Average function in this article about creating heat maps.

Looking for more on Excel functions? Check out this guide to the Find Function!

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 can assist in 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. Simplifying Data: Instead of working with a large set of numbers, using the AVERAGE function can simplify the data to a single representative value, making it easier to comprehend and communicate.

You can also use it for Data Analysis alongside the Count Function – more on that here!

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: Calcuates the most freqnetly occuring value in your data


The AVERAGE function is a staple in Excel’s suite of tools, offering a straightforward way to analyze 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.

Where to go next? Check out the Trim Function!


About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.

He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.

He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.