AVERAGE Function Excel – Quick Guide!
Contents
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. |
| “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:
- 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.
Formulas are the building blocks of any Excel pro, and a key focus of our intro to Excel courses.
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!
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!
The AVERAGE function can also be used to create heat maps, a colour-coded visualisation of data.
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.
