How to Use AVERAGE in Excel
Returns the arithmetic mean of a range while automatically ignoring blanks and text.
What is AVERAGE in Microsoft Excel?
AVERAGE adds the numeric values in a range and divides by how many numbers it found. Blank cells and text are skipped, not treated as zero, which is exactly what you want for survey scores, ratings and monthly averages.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| number1 | Required | The first number, cell or range to average. |
| number2 | Optional | Further numbers or ranges, up to 255 arguments in total. |
When to Use AVERAGE (Real-World Business Scenarios)
- 1
Calculating average sales per month, average marks per student or average handling time.
- 2
Reporting a mean rating from a feedback column that contains some blank responses.
- 3
Comparing a team average against a target in a dashboard KPI card.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Employee | Rating |
|---|---|
| Rahul | 4 |
| Pooja | 5 |
| Aman |
How it works: Adds 4 and 5, then divides by 2 because the blank cell is ignored. The result is 4.5, not 3, which is what would happen if the blank were counted as zero.
Common Errors & How to Fix Them
Why it happens: The range contains no numeric values at all, so Excel cannot divide by a count of zero.
Fix: Wrap it: =IFERROR(AVERAGE(B2:B4),0) or check whether the column was imported as text.
Why it happens: Real zeros are included in the calculation, or text-formatted numbers were ignored while zeros were kept.
Fix: Decide whether zero means 'zero' or 'missing'. Use AVERAGEIF(range,"<>0") for the second case.
Try AVERAGE Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (AVERAGE)
What does the AVERAGE formula do in Excel?
AVERAGE adds the numeric values in a range and divides by how many numbers it found. Blank cells and text are skipped, not treated as zero, which is exactly what you want for survey scores, ratings and monthly averages.
What is the syntax for AVERAGE?
What are common mistakes when using AVERAGE?
#DIV/0!: The range contains no numeric values at all, so Excel cannot divide by a count of zero. (Wrap it: =IFERROR(AVERAGE(B2:B4),0) or check whether the column was imported as text.) Average looks too low: Real zeros are included in the calculation, or text-formatted numbers were ignored while zeros were kept. (Decide whether zero means 'zero' or 'missing'. Use AVERAGEIF(range,"<>0") for the second case.)
Related Excel Formulas
Keep building your formula toolkit with these math & stats functions.
- SUMIFSSums numerical values across multiple conditions (e.g. Sales in North Region for Q1).
- COUNTIFSCounts the number of rows that satisfy multiple simultaneous criteria.
- SUMAdds numbers across cells, ranges and entire columns — the first formula most people ever type in Excel.
- SUMIFAdds only the numbers that match one condition — the fastest way to total a region, product or status without a Pivot Table.
- COUNTIFCounts how many cells match a condition — duplicates, blanks, specific text, or values above a limit.
- MAXReturns the largest number in a range — highest sale, top score, latest date or most recent invoice.
Master AVERAGE in Basic Excel
Learn AVERAGE along with our formula library, practice files, and verifiable certificates in our complete curriculum.