Want to calculate the average of the lowest N numbers in a list or column in Google Sheets?
Good news—Google Sheets makes this task simple using a combination of the SORTN and AVERAGE functions. In this tutorial, you’ll learn how to dynamically average the lowest 3, 5, or any number of values from a range using a formula. Whether you’re working with scores, prices, or other numerical data, this method works seamlessly.
But before jumping into formulas, there are two key factors to consider when calculating the average of the lowest N numbers in Google Sheets:
1. Include or Exclude Zeros
Depending on your data, zero values might represent actual values, placeholders, or even missing data.
- Include 0s: If zeros are valid entries (like scores or costs), they should be included in the average.
- Exclude 0s: If zeros are used to represent missing or irrelevant data, it’s best to filter them out.
Real-life example: In a student grade sheet, a zero might indicate a missed test. Including it in the average could unfairly lower the student’s score.
2. Lowest N Distinct Values vs. N Lowest Values
Next, decide whether to:
- Average the lowest N values (allowing duplicates), or
- Average the N lowest unique values (ignoring duplicates)
Real-life example: If you’re analyzing the cheapest product prices, and several products have the same low price, you might want to consider only distinct price points to avoid duplication bias.
Example Data
Let’s use this list in column A (A1:A10):
| A (Values) |
| 0 |
| 15 |
| 20 |
| 0 |
| 10 |
| 10 |
| 5 |
| 25 |
| 30 |
| 5 |
Formulas to Average the Lowest N Numbers in Google Sheets
1. Average of Lowest 3 Values (including zeros)
=AVERAGE(SORTN(A1:A10, 3))Result: Averages 0, 0, 5 → 1.67
Here, SORTN sorts the values and picks the lowest 3. AVERAGE then computes the mean.
2. Average of Lowest 3 Values (excluding zeros)
=AVERAGE(SORTN(FILTER(A1:A10, A1:A10), 3))Result: Averages 5, 5, 10 → 6.67
FILTER removes zeros, and the rest works the same as above.
3. Average of Lowest 3 Distinct Values (including zeros)
=AVERAGE(SORTN(A1:A10, 3, 2))
Result: Averages 0, 5, 10 → 5
The display_ties_mode set to 2 in SORTN ensures that only distinct values are considered.
4. Average of Lowest 3 Distinct Values (excluding zeros)
=AVERAGE(SORTN(FILTER(A1:A10, A1:A10), 3, 2))Result: Averages 5, 10, 15 → 10
This combines FILTER for maximum control.
Bonus: Conditional Average of the Lowest N Numbers
In real-world datasets, you might want to average the lowest values for a specific group—like test scores in one class or prices for a specific product type. This is where a conditional average of the lowest N numbers becomes useful.
Let’s say you have this table:
| A (Group) | B (Score) |
| A | 15 |
| B | 20 |
| A | 5 |
| A | 10 |
| B | 25 |
| A | 30 |
| B | 0 |
| A | 10 |
| B | 5 |
| A | 0 |
Goal: Average the 3 Lowest Scores for Group A
➕ Including Zeros
Use this inside SORTN:
FILTER(B1:B10, A1:A10="A")Full formula:
=AVERAGE(SORTN(FILTER(B1:B, A1:A="A"), 3))➖ Excluding Zeros
Use this instead:
FILTER(B1:B10, A1:A10="A", B1:B10)Full formula:
=AVERAGE(SORTN(FILTER(B1:B10, A1:A10="A", B1:B10), 3))This gives you a conditional average based on both value and group.
To return only distinct values, simply specify display_ties_mode as 2 in these formulas.
