Introduction
The AVERAGE function in Excel calculates the average of a group of numbers. It is useful for calculating average sales, employee salaries, student marks, monthly expenses, attendance, and business performance.
In this tutorial, you will learn the AVERAGE Function in Excel Tutorial through practical examples, starting with basic calculations and progressing to company reports, filtered data, Excel Tables, and performance analysis. Each question introduces a different concept so you can build your Excel skills without repeating the same type of problem.
AVERAGE Function Syntax
=AVERAGE(number1, [number2], ...)How it works:
number1: The first number, cell, or range you want to average.number2: Additional numbers, cells, or ranges. This argument is optional.
Example:
=AVERAGE(B2:B6)This formula calculates the average of the numeric values in cells B2 through B6.
Question 1: Calculate Average Monthly Sales
Problem Statement:
A company wants to calculate its average daily sales to understand its regular sales performance.
Data:
| Day | Sales (₹) |
|---|---|
| Monday | 18,000 |
| Tuesday | 22,000 |
| Wednesday | 20,000 |
| Thursday | 25,000 |
| Friday | 15,000 |
Excel Formula:
=AVERAGE(B2:B6)Expected Result: ₹20,000
Student Task:
Add Saturday’s sales of ₹30,000 in cell B7. Update the formula to include Saturday and calculate the new average.
Expected Result: ₹21,666.67
Question 2: Calculate the Average of Selected Cells
Problem Statement:
A sales manager wants to calculate the average sales for Monday, Wednesday, and Friday only. Tuesday and Thursday should not be included.
Data:
| Day | Sales (₹) |
|---|---|
| Monday | 12,000 |
| Tuesday | 15,000 |
| Wednesday | 18,000 |
| Thursday | 21,000 |
| Friday | 24,000 |
Excel Formula:
=AVERAGE(B2,B4,B6)Expected Result: ₹18,000
Student Task:
Calculate the average sales for Tuesday and Thursday only.
Expected Formula:
=AVERAGE(B3,B5)Expected Result: ₹18,000
Question 3: Understand How Blank Cells and Zero Affect the Average
Problem Statement:
An employee enters daily sales into Excel. Some days have no recorded sales, while another day has confirmed sales of zero.
Data:
| Day | Sales (₹) |
|---|---|
| Monday | 10,000 |
| Tuesday | 15,000 |
| Wednesday | (Blank) |
| Thursday | 0 |
| Friday | 25,000 |
Excel Formula:
=AVERAGE(B2:B6)Expected Result: ₹12,500
Why? Excel ignores the blank cell, but it includes the numeric zero in the calculation. The total is ₹50,000 across four numeric entries.
If Thursday’s zero is replaced with a blank, the average becomes ₹16,666.67 because only three numeric entries remain.
Student Task:
Change Friday’s sales to ₹30,000. Calculate the average while keeping Thursday’s zero and Wednesday’s blank unchanged.
Expected Result: ₹13,750
Question 4: Calculate Average Marks When a Cell Contains Text
Problem Statement:
A training institute wants to calculate students’ average marks. One student is marked as “Absent” instead of receiving a numeric score.
Data:
| Student | Marks |
|---|---|
| Rahul | 70 |
| Priya | 80 |
| Aman | Absent |
| Neha | 90 |
| Karan | 60 |
Excel Formula:
=AVERAGE(B2:B6)Expected Result: 75
Why? Excel ignores text entered directly in a referenced range when calculating the average. The four numeric marks total 300, so the average is 75.
Important: A numeric zero is included in the average, but text such as Absent is ignored.
Student Task:
Change Karan’s marks from 60 to 100. Calculate the new average.
Expected Result: 85
Question 5: Calculate the Average Across Multiple Ranges
Problem Statement:
A company records sales for two groups of employees in different parts of a worksheet. The manager wants one average covering both groups.
Data:
| Group A Sales (₹) | Group B Sales (₹) | |
|---|---|---|
| 40,000 | 70,000 | |
| 50,000 | 80,000 | |
| 60,000 | 90,000 |
Assume Group A values are in B2:B4 and Group B values are in D2:D4.
Excel Formula:
=AVERAGE(B2:B4,D2:D4)Expected Result: ₹65,000
Student Task:
Calculate the average using only the first two sales values from each group.
Expected Formula:
=AVERAGE(B2:B3,D2:D3)Expected Result: ₹57,500
Question 6: Calculate Average Employee Salary
Problem Statement:
The HR department wants to know the average monthly salary of four employees.
Data:
| Employee | Monthly Salary (₹) |
|---|---|
| Amit | 45,000 |
| Priya | 50,000 |
| Rahul | 55,000 |
| Neha | 60,000 |
Excel Formula:
=AVERAGE(B2:B5)Expected Result: ₹52,500
Student Task:
Add a fifth employee with a monthly salary of ₹70,000. Calculate the updated average salary.
Expected Formula:
=AVERAGE(B2:B6)Expected Result: ₹56,000
Business Use: HR teams can use averages to summarize salary data. However, the average alone does not show the full salary distribution or differences between employees.
Question 7: Calculate Average Target Achievement Percentage
Problem Statement:
A sales manager wants to compare the target achievement percentages of three employees.
Data:
| Employee | Sales Target (₹) | Actual Sales (₹) | Achievement |
|---|---|---|---|
| Amit | 100,000 | 80,000 | 80% |
| Priya | 200,000 | 180,000 | 90% |
| Rahul | 300,000 | 270,000 | 90% |
Assume targets are in column B and actual sales are in column C.
Step 1: Calculate Each Employee’s Achievement
In cell D2, enter:
=C2/B2Format the cell as Percentage, then copy the formula down to D4.
Step 2: Calculate the Average Achievement
=AVERAGE(D2:D4)Expected Result: 86.67%
Important Business Concept: The simple average of the three achievement percentages is 86.67%. However, total actual sales are ₹530,000 against combined targets of ₹600,000, giving overall target achievement of 88.33%.
To calculate overall achievement, use:
=SUM(C2:C4)/SUM(B2:B4)Student Task:
Change Priya’s actual sales from ₹180,000 to ₹160,000. Calculate both the average individual achievement and the overall achievement.
Expected Results:
- Average individual achievement: 83.33%
- Overall achievement: 85%
Question 8: Calculate the Average of Visible Rows After Filtering
Problem Statement:
A company stores sales data for multiple regions. After applying a filter, the manager wants to calculate the average sales of only the visible region.
Data:
| Region | Sales (₹) |
|---|---|
| North | 100 |
| East | 200 |
| North | 300 |
| West | 400 |
| North | 700 |
Formula for the Average of All Rows:
=AVERAGE(B2:B6)Expected Result: ₹340
The AVERAGE function includes the values in the filtered-out rows as well.
Formula for the Average of Visible Rows:
=SUBTOTAL(1,B2:B6)After filtering the Region column to show only North, the expected result is ₹366.67.
Student Task:
Filter the table to show only East and West. Use the SUBTOTAL formula to calculate the average of the visible sales.
Expected Result: ₹300
Question 9: Calculate the Average Using an Excel Table
Problem Statement:
A company maintains a sales table that receives new records regularly. The manager wants a formula that automatically includes new table rows.
Data:
| Product | Sales (₹) |
|---|---|
| Keyboard | 10,000 |
| Mouse | 15,000 |
| Monitor | 20,000 |
Assume the data has been converted into an Excel Table named SalesTable, with the sales column named Sales.
Excel Formula:
=AVERAGE(SalesTable[Sales])Expected Result: ₹15,000
Student Task:
Add a new row to the table for a printer with sales of ₹25,000. Check the average again.
Expected Result: ₹17,500
Tip: Excel Tables can expand when you add new records, making structured references useful for regularly updated reports.
Question 10: Calculate Average Sales Across Multiple Worksheets
Problem Statement:
A company stores sales data for January and February on separate worksheets. The manager wants to calculate the average of all six sales entries.
January Worksheet:
| Employee | Sales (₹) |
|---|---|
| Amit | 20,000 |
| Priya | 30,000 |
| Rahul | 40,000 |
February Worksheet:
| Employee | Sales (₹) |
|---|---|
| Amit | 30,000 |
| Priya | 40,000 |
| Rahul | 50,000 |
Assume the sales values are in B2:B4 on both worksheets.
Excel Formula:
=AVERAGE(January:February!B2:B4)Expected Result: ₹35,000
This formula uses a 3-D reference to average the corresponding cell range across worksheets from January through February. The worksheets must be arranged in the correct order, and the reference includes worksheets between the two named sheets.
Student Task:
Add a March worksheet between January and February, containing sales of ₹40,000, ₹50,000, and ₹60,000 in cells B2. Check the updated average.
Expected Result: ₹41,666.67
Student Challenge: Build an Employee Performance Summary
A company wants to review employee performance using monthly sales data.
Data:
| Employee | January (₹) | February (₹) | March (₹) |
|---|---|---|---|
| Amit | 20,000 | 25,000 | 30,000 |
| Priya | 30,000 | 35,000 | 40,000 |
| Rahul | 15,000 | 20,000 | 25,000 |
| Neha | 40,000 | 45,000 | 50,000 |
| Karan | 25,000 | 30,000 | 35,000 |
Your Tasks
- Calculate Amit’s average monthly sales.
- Calculate the average sales for all employees in January.
- Calculate Neha’s average monthly sales.
- Calculate the average of all sales values in the table.
- Identify the employee with the highest average monthly sales.
- Add April sales for each employee: ₹35,000, ₹45,000, ₹30,000, ₹55,000, and ₹40,000. Calculate the updated average for all employees.
Answer Key
1. Amit’s average monthly sales
=AVERAGE(B2:D2)Expected result: ₹25,000
2. Average January sales
=AVERAGE(B2:B6)Expected result: ₹26,000
3. Neha’s average monthly sales
=AVERAGE(B5:D5)Expected result: ₹45,000
4. Average of all sales values
=AVERAGE(B2:D6)Expected result: ₹30,333.33
5. Employee with the highest average
Neha, with average monthly sales of ₹45,000.
6. Updated average after adding April sales
If April sales are entered in column E, use:
=AVERAGE(B2:E6)Expected result: ₹33,250
Key Takeaways
- The AVERAGE function calculates the arithmetic mean of numeric values.
- Use
=AVERAGE(B2:B10)to average a continuous range. - Use commas to average selected cells or multiple ranges.
- Blank cells and text in referenced ranges are ignored, but numeric zero is included.
- Use
SUBTOTAL(1,range)when you need the average of visible rows after filtering. - Structured references such as
=AVERAGE(SalesTable[Sales])are useful for expanding Excel Tables. - A simple average of percentages may differ from an overall achievement rate calculated from combined totals.
- Combine AVERAGE in excel with IF to compare individual results against a team average.
Frequently Asked Questions (FAQs)
1. What is the AVERAGE function in Excel?
The AVERAGE function calculates the arithmetic mean of numeric values by adding the numbers and dividing their total by the number of numeric entries.
2. What is the syntax of the AVERAGE function?
The syntax is =AVERAGE(number1, [number2], ...). You can enter numbers, cell references, or ranges.
3. Does AVERAGE ignore blank cells?
Yes. Blank cells in a referenced range are ignored.
4. Does AVERAGE include zero?
Yes. A numeric zero is included in the calculation and affects the result.
5. Does AVERAGE ignore text?
Text entered directly into a referenced range is ignored. However, text values entered directly as arguments in the formula can cause an error.
6. How do I calculate the average of selected cells?
Use commas between the cell references. For example, =AVERAGE(B2,B4,B6) averages only those three cells.
7. How do I calculate the average of visible rows only?
Use =SUBTOTAL(1,B2:B10). With function number 1, SUBTOTAL calculates the average and excludes rows hidden by a filter.
8. Can I calculate an average across multiple worksheets?
Yes. You can use separate worksheet references or a 3-D reference, such as =AVERAGE(January:February!B2:B4), when the worksheets are arranged appropriately.
9. What is the difference between AVERAGE and SUM?
SUM adds numeric values together. AVERAGE calculates the mean of the numeric values.
10. Why does AVERAGE return a #DIV/0! error?
This usually happens when the formula has no numeric values to average, such as when the referenced cells are all blank or contain only text.