Google Sheets Average Formula: AVERAGE, AVERAGEIF & More

The core Google Sheets average formula is AVERAGE(range), which returns the arithmetic mean of a set of numbers. You enter a range like A1:A10 or a list of values, and Sheets adds them up and divides by the count of numeric cells. For conditional averages, use AVERAGEIF or AVERAGEIFS.

Formula

In plain terms, the average is the sum of all numeric values divided by the count of numeric values. The AVERAGE function does exactly that:

=AVERAGE(value1, [value2, ...])

• value1 – the first number, cell reference, or range (required).
• value2, … – additional numbers or ranges (optional).

For example, =AVERAGE(B2:B10) sums B2 through B10 and divides by the count of cells that contain numbers. Text, empty cells, and logical values (TRUE/FALSE) are ignored. If you want to include logical values or text representations of numbers, use AVERAGEA instead.

Worked Example

Suppose you ran 10 km in 50 minutes. What is your average pace and average speed?

1. Average pace = total time / distance = 50 min / 10 km = 5.00 min/km.
2. Average speed = distance / (time in hours) = 10 km / (50/60) h = 10 / 0.8333 = 12.00 km/h.

Using the calculator above, enter 50 for finish time and 10 for distance. The average pace shows 5.00 min/km and the average speed shows 12.00 km/h. That matches the manual calculation.

How to Use AVERAGE in Google Sheets

Typing =AVERAGE( in a cell triggers autocomplete. You can select a range with your mouse or type it manually. Press Enter to get the result. For example, if your test scores are in A2:A20, entering =AVERAGE(A2:A20) returns the mean score.

You can also reference individual cells: =AVERAGE(A2, B2, C2) gives the average of those three cells. If you need to average every nth row, you can combine with FILTER or use an array formula, but for most everyday tasks the simple range works.

AVERAGEIF and AVERAGEIFS for Conditional Averages

Sometimes you only want to average numbers that meet a condition. AVERAGEIF does that with a single criterion, and AVERAGEIFS handles multiple criteria.

• =AVERAGEIF(A2:A20, ">50") – average only values greater than 50.
• =AVERAGEIF(B2:B20, "Yes", C2:C20) – average C values where B equals “Yes”.
• =AVERAGEIFS(C2:C20, A2:A20, "North", B2:B20, ">100") – average C where A is “North” and B is greater than 100.

Criteria can be numbers, text, or expressions. Use quotes around text and operators. For example, ">=10" includes 10 and above.

Common Mistakes and Pitfalls

• Including text or blank cells: AVERAGE ignores them, but if you include a cell with an error like #DIV/0!, the whole result becomes an error.
• Mixing units: If you average minutes and seconds together, the result is meaningless. Always convert to the same unit first.
• Using AVERAGE on non-contiguous ranges: You can list multiple ranges separated by commas, but each range must be a valid reference.
• Rounding too early: If you round each value before averaging, the result may differ from the true average. Keep full precision and round only the final answer.

When to Use the Average vs. Median

The average is sensitive to outliers. If you have a dataset with extreme values, the median (middle value) might be more representative. For example, house prices in a city: a few mansions can pull the average up, while the median gives a better sense of a typical home. Use =MEDIAN(range) in Sheets when you suspect outliers are skewing the mean.

Also, the average of percentages is only valid if each percentage is based on the same total. If not, compute the weighted average using =SUMPRODUCT(values, weights)/SUM(weights).

Frequently Asked Questions

Why is my AVERAGE formula returning a #DIV/0! error?

This happens when the range contains no numeric values. AVERAGE divides by the count of numbers, so if that count is zero, you get the error. Check that your range includes numbers and not just text or blank cells. You can wrap with IFERROR to show a custom message.

How do I average only the visible cells after filtering?

AVERAGE ignores hidden rows only if you use the SUBTOTAL function with function number 101. For example, =SUBTOTAL(101, A2:A20) averages only visible cells. The regular AVERAGE includes hidden rows.

Can I exclude zeros from the average?

Yes, use AVERAGEIF with a criterion like ">0". For instance, =AVERAGEIF(A2:A20, ">0") ignores zeros and negative numbers. If you want to exclude only zeros but keep negatives, use "<>0".

What is the difference between AVERAGE and AVERAGEA?

AVERAGE ignores text and logical values. AVERAGEA counts text as 0 and TRUE as 1, FALSE as 0. So if a cell contains “N/A”, AVERAGEA treats it as 0, which can lower the result. Use AVERAGE for numeric data only.

How do I calculate a weighted average in Google Sheets?

Use SUMPRODUCT. For example, if values are in A2:A10 and weights in B2:B10, the weighted average is =SUMPRODUCT(A2:A10, B2:B10)/SUM(B2:B10). This multiplies each value by its weight, sums those products, and divides by the total weight.

Does AVERAGE ignore blank cells automatically?

Yes, blank cells are ignored. Only cells with numbers are counted. If you want to treat blanks as zero, you would need to explicitly include them, for example by using =AVERAGE(IF(ISBLANK(A2:A20),0,A2:A20)) as an array formula, but that is rarely necessary.

Related

• Car Value Estimator: How It Works & How to Use It
• Sum by Month in Excel Columns: 3 Easy Ways
• Mass Calculation: Formula & How to Find It

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2