Google Sheets offers several powerful functions to calculate averages, depending on your data and criteria. Whether you need a simple mean, include non-numeric values, or apply conditions, AVERAGE, AVERAGEA, AVERAGEIF, and AVERAGEIFS help you analyze data effectively.
In this guide, we’ll explore these functions with syntax, examples, and real-world applications.
1. AVERAGE Function (Basic Mean Calculation)
What Does AVERAGE Do?
The AVERAGE function calculates the arithmetic mean of a numeric range.
Syntax:
=AVERAGE(range)
Example:
To find the average of numbers in A1 to A10:
=AVERAGE(A1:A10)
✅ Result: Returns the mean of the numeric values.
Use Case: Ideal for calculating average sales, test scores, or any dataset with purely numerical values.
2. AVERAGEA Function (Includes Non-Numeric Values)
What Does AVERAGEA Do?
The AVERAGEA function calculates the average but also considers text and logical values (TRUE = 1, FALSE = 0).
Syntax:
=AVERAGEA(range)
Example:
If a dataset contains numbers and text:
=AVERAGEA(A1:A10)
✅ Result: Non-numeric values (TRUE, FALSE, or text) impact the average calculation.
Use Case: Useful when working with mixed datasets containing numbers and logical values.
3. AVERAGEIF Function (Conditional Average)
What Does AVERAGEIF Do?
The AVERAGEIF function calculates the average of numbers that meet a specified condition.
Syntax:
=AVERAGEIF(range, criteria, [average_range])
Example:
To find the average sales (column B) for “Electronics” in column A:
=AVERAGEIF(A1:A100, "Electronics", B1:B100)
✅ Result: Returns the average of sales where the category is “Electronics”.
Use Case: Ideal for filtering average sales by category, performance metrics by department, etc.
4. AVERAGEIFS Function (Multiple Condition Average)
What Does AVERAGEIFS Do?
The AVERAGEIFS function calculates the average of numbers that meet multiple conditions.
Syntax:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
Example:
To find the average salary (column C) for employees in the “IT” department (column A) with more than 5 years of experience (column B):
=AVERAGEIFS(C2:C100, A2:A100, "IT", B2:B100, ">5")
✅ Result: Returns the average salary of IT employees with more than 5 years of experience.
Use Case: Ideal for advanced filtering in HR, finance, and sales reports.
Common Issues & Solutions
🚨 Problem: AVERAGE function isn’t returning expected results. ✅ Solution: Ensure there are no text values in the numeric range.
🚨 Problem: AVERAGEA includes non-numeric values, affecting results. ✅ Solution: Use AVERAGE instead if you want only numeric values.
🚨 Problem: AVERAGEIF or AVERAGEIFS is returning 0. ✅ Solution: Ensure the criteria match the dataset correctly and check for blank cells.
Final Thoughts: When to Use Each Function
| Function | Purpose |
|---|---|
AVERAGE | Calculates the mean of numeric values |
AVERAGEA | Includes text and logical values in the average |
AVERAGEIF | Averages values based on one condition |
AVERAGEIFS | Averages values based on multiple conditions |
🚀 Next Steps:
Explore SUMIFS, COUNTIFS, and MAXIFS to level up your data analysis!
📢 Have questions? Drop them in the comments!