Written and maintained by CASRAI Editorial Board
Last updated
To calculate averages in Google Sheets, type =AVERAGE(A2:A100) into a cell: Google describes the function as returning “the numerical average value in a dataset, ignoring text.” The same page defines the result as the sum of the values divided by the count of values, which is the arithmetic mean. Four related functions (AVERAGEA, AVERAGEIF, AVERAGEIFS and MEDIAN) change what gets counted, and choosing between them matters more than the formula itself.
What it is used for in research
Researchers use a sheet average as a quick descriptive statistic: the mean of replicate measurements, of scores in one study group, or of a column of survey responses. The function answers “what is the mean of these numbers?” and nothing more. It does not tell you whether the mean is the right summary for your data. When a few extreme values dominate a column, Google’s own AVERAGE help page points to MEDIAN as the alternative, and our guide to calculating the median explains the same idea for spreadsheets. For data that grow multiplicatively (growth rates, ratios), see the geometric mean.
Install or access
Google Sheets runs in a web browser, so there is nothing to install: the averaging functions described here are built-in spreadsheet functions documented in Google’s Docs Editors Help, and they are available in any sheet. Every formula below is typed into a cell and begins with an equals sign.
Step-by-step: how to calculate the mean in Google Sheets
How to find the mean of a column or range
- Click an empty cell where the result should appear.
- Type
=AVERAGE(A2:A100), replacing the range with your own data range. - Press Enter. The cell shows the sum of the numeric values divided by how many there are.
The same steps answer “how to calculate mean on Google Sheets” and “how to find mean in Google Sheets”: in Sheets the mean is the AVERAGE function. The syntax is AVERAGE(value1, [value2, ...]).
How to average non-adjacent ranges and single numbers
AVERAGE accepts several arguments, and each can be a range or a number. Google’s examples are AVERAGE(A2:A100,B2:B100,4,26) and AVERAGE(1,2,3,4,5,C6:C20). Use this form when your replicates sit in separate columns.
How to average only the rows that meet a condition (AVERAGEIF)
Use AVERAGEIF(criteria_range, criterion, [average_range]), which Google describes as returning “the average of a range depending on criteria.” If average_range is left out, Sheets averages the criteria range itself. Google’s examples:
AVERAGEIF(A1:A10, ">20", B1:B10)averages the B values whose A value is above 20.AVERAGEIF(A1:A10, "<10")averages the A values below 10.AVERAGEIF(A1:A10, "Paid", B1:B10)averages the B values on rows where A reads Paid.AVERAGEIF(A1:A10, "<"&B1)compares against the value in another cell.
How to average with several conditions (AVERAGEIFS)
Use AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]). Google’s examples are AVERAGEIFS(A1:A10, B1:B10, ">20", C1:C10, "<30") and AVERAGEIFS(C1:C100, E1:E100, "Yes"). The same task in Excel is covered in our AVERAGEIFS guide and our AVERAGEIF guide.
Mind the argument order: in AVERAGEIF the range to average comes last and is optional, while in AVERAGEIFS it comes first and is required.
Output and how to read it
AVERAGE or AVERAGEA: how text cells change the answer
AVERAGE skips text in its value arguments. AVERAGEA does the opposite: Google states that “any text encountered in the value arguments will be set to 0 and included in calculation.” Here is a worked example with five cells, A1:A5, holding 10, 20, the text “n/a”, 30 and 40:
| Formula | Arithmetic | Result |
|---|---|---|
=AVERAGE(A1:A5) |
(10 + 20 + 30 + 40) / 4 | 25 |
=AVERAGEA(A1:A5) |
(10 + 20 + 0 + 30 + 40) / 5 | 20 |
The two results differ by 5 because AVERAGEA counts the text cell as a zero. If a text entry means “missing”, AVERAGE is usually what you want; if it genuinely means zero, AVERAGEA.
Criteria forms for AVERAGEIF and AVERAGEIFS
| Criterion | Meaning (from Google’s help page) |
|---|---|
"text", 1, "=text" |
Equal to that text or number |
">1", ">=1" |
Greater than; greater than or equal to |
"<1", "<=1" |
Less than; less than or equal to |
"<>1", "<>text" |
Not equal to |
Report the mean together with the number of observations behind it, and look at the distribution before trusting it. A histogram in Google Sheets shows quickly whether one number is doing most of the work.
Troubleshooting
- The mean looks too high or too low. Check whether you used AVERAGE or AVERAGEA. Text cells are ignored by the first and counted as 0 by the second.
- A conditional average returns the wrong rows. Check the argument order: AVERAGEIF ends with the range to average, AVERAGEIFS starts with it.
- A comparison against another cell fails. Join the operator and the cell reference with an ampersand, as in
"<"&B1, rather than typing both inside one quoted string. - Extreme values distort the result. Compare with
MEDIAN(value1, [value2, ...]), the alternative named on Google’s AVERAGE page.
Free alternatives: other averaging functions in Sheets
Every function below is part of Google Sheets itself, so no extra tool is needed. The syntax comes from Google’s function list.
| Function | Syntax | What it does |
|---|---|---|
| AVERAGE | AVERAGE(value1, [value2, ...]) |
Mean of the numbers, ignoring text |
| AVERAGEA | AVERAGEA(value1, [value2, ...]) |
Mean with text counted as 0 |
| AVERAGEIF | AVERAGEIF(criteria_range, criterion, [average_range]) |
Mean of a range that meets one criterion |
| AVERAGEIFS | AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
Mean of a range that meets several criteria |
| MEDIAN | MEDIAN(value1, [value2, ...]) |
Middle value, the alternative Google names for AVERAGE |
FAQ
How do I calculate averages in Google Sheets?
Type =AVERAGE(, add the range or values to average, close the bracket and press Enter. For example, =AVERAGE(A2:A100) returns the mean of the numbers in that range.
What is the Google Sheets average function?
It is AVERAGE, with the syntax AVERAGE(value1, [value2, ...]). It returns the sum of the values divided by their count and ignores text.
How do I calculate the mean in Google Sheets?
The mean is what AVERAGE returns, so =AVERAGE(range) is the formula for it. Use AVERAGEIF or AVERAGEIFS when only some rows should count.
What is the difference between AVERAGE and AVERAGEA?
AVERAGE ignores text; AVERAGEA sets text to 0 and includes it. With the values 10, 20, “n/a”, 30, 40 the results are 25 and 20.
How do I average only certain rows?
Use AVERAGEIF for one condition and AVERAGEIFS for several, for instance =AVERAGEIFS(C1:C100, E1:E100, "Yes").
Sources
- Google Docs Editors Help, AVERAGE: https://support.google.com/docs/answer/3093615 (checked 2026-10-29)
- Google Docs Editors Help, AVERAGEA: https://support.google.com/docs/answer/3093617 (checked 2026-10-29)
- Google Docs Editors Help, AVERAGEIF: https://support.google.com/docs/answer/3256529 (checked 2026-10-29)
- Google Docs Editors Help, AVERAGEIFS: https://support.google.com/docs/answer/3256534 (checked 2026-10-29)
- Google Docs Editors Help, function list: https://support.google.com/docs/table/25273 (checked 2026-10-29)
