Leave zeros out of an average
PremiumThe store is closed on Mondays and Sundays and records 0 sales. In E1, find the average sales of the days it was open: AVERAGEIF with the condition ">0".
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Day | Sales | Avg. open day | |||
| 2 | Monday | 0 | ||||
| 3 | Tuesday | 840 | ||||
| 4 | Wednesday | 760 | ||||
| 5 | Thursday | 910 | ||||
| 6 | Friday | 1120 | ||||
| 7 | Saturday | 1370 | ||||
| 8 | Sunday | 0 | ||||
| 9 |
Premium lesson
Unlock AVERAGEIF and AVERAGEIFS and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel assessment (Indeed format)
Why this works
AVERAGE skips blank cells but not zeros, so it would divide 5,000 by 7 days and give 714.29. With ">0" AVERAGEIF only uses the five open days: 5,000 / 5 = 1,000. When the condition is about the numbers themselves, the average range can be left out.
Common mistake: Using AVERAGE on a column with zeros for closed days. Zeros count as values, so the average is too low.
The answer
- E1
=AVERAGEIF(1B2:B8,2">0")
In plain words: Average 1the sales in B2:B8 that are 2greater than 0.