✨ Introduce
📌 Definition: The AVERAGE function in Microsoft Excel calculates the arithmetic mean of a range of values.
📌 Purpose: The AVERAGE function determines the average value of a set of numbers.
📌 Syntax & Arguments:
AVERAGE(number1, [number2], [...]) 📌 Return Value: The AVERAGE function returns the arithmetic mean of the provided values.
📌 Remarks: The AVERAGE function ignores empty cells, text, and logical values in the range.
✨ Examples
Example 1: Average Monthly Sales Consider a business with monthly sales data. Calculate the average monthly sales.
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | $500 |
| 3 | Feb | $800 |
| 4 | Mar | $1200 |
- Formula (B5):
=AVERAGE(B2:B4) - Result: The average monthly sales will be calculated as $833.33.
Example 2: Average Quantity Sold Consider the quantity of a product sold on different days. Find the average quantity sold.
| A | B | |
|---|---|---|
| 1 | Day | Qty |
| 2 | 1 | 10 |
| 3 | 2 | 15 |
| 4 | 3 | 12 |
- Formula (B5):
=AVERAGE(B2:B4) - Result: The average quantity sold will be calculated as 12.33.
Example 3: Average Response Time Calculate the average response time of a customer support team for different support tickets.
| A | B | |
|---|---|---|
| 1 | Ticket# | Time |
| 2 | T001 | 5:23 |
| 3 | T002 | 8:45 |
| 4 | T003 | 7:12 |
- Formula (B5):
=AVERAGE(TIMEVALUE(B2:B4)) - Result: The average response time will be calculated as 7:06.
Example 4: Average Test Scores Calculate the average test scores for students in different sections.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Section | Score |
| 2 | John | Section1 | 85 |
| 3 | John | Section2 | 90 |
| 4 | John | Section3 | 80 |
| 5 | Maria | Section1 | 92 |
| 6 | Maria | Section2 | 88 |
| 7 | Maria | Section3 | 85 |
- Formula (C8):
=AVERAGE(IF($B$2:$B$7="Section1",$C$2:$C$7)) - Result: We use an array formula with the IF function to calculate the average score for Section 1 as 88.5.
Example 5: Average Revenue Growth Calculate the average revenue growth rate over five years.
| A | B | |
|---|---|---|
| 1 | Year | Revenue |
| 2 | 2017 | $100,000 |
| 3 | 2018 | $120,000 |
| 4 | 2019 | $150,000 |
| 5 | 2020 | $180,000 |
| 6 | 2021 | $200,000 |
- Formula (B7):
=(B6-B2)/B2/(COUNT(B2:B6)-1) - Result: The average revenue growth rate will be calculated as 20%.
Example 6: Average Project Completion Time Calculate the average time to complete different projects.
| A | B | |
|---|---|---|
| 1 | Project | Duration |
| 2 | Project1 | 5 days |
| 3 | Project2 | 7 days |
| 4 | Project3 | 4 days |
| 5 | Project4 | 6 days |
- Formula (B6):
=AVERAGE(VALUE(SUBSTITUTE(B2:B5," days",""))) - Result: The average project completion time will be calculated as 5.5 days.
Example 7: Calculate the average production cost per unit for different products.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Qty | Cost |
| 2 | Product1 | 100 | $200 |
| 3 | Product2 | 50 | $150 |
| 4 | Product3 | 75 | $180 |
| 5 | Product4 | 200 | $300 |
- Formula (C6):
=AVERAGE($C$2:$C$5/$B$2:$B$5) - Result: The average production cost per unit will be $2.18.
💡 Tips and Tricks:
- Ensure that the range of values provided to the AVERAGE function includes only numeric data.
- The AVERAGE function ignores empty cells, text, and logical values in the range.
- Combine the AVERAGE function.