Excel and Google Sheets how-to
Fix #DIV/0! in an average
An average of nothing is a division by zero, so AVERAGE shows #DIV/0! until the first number arrives. One IF fixes it.
| A | B | C | |
|---|---|---|---|
| 1 | Day | Week 1 | Week 2 |
| 2 | Mon | 120 | |
| 3 | Tue | 0 | |
| 4 | Wed | 90 | |
| 5 | Thu | ||
| 6 | Fri | ||
| 7 | Average | 70 |
Sample data. Daily sales; Week 2 has no numbers yet. The formula in B7 is copied to C7, which stays blank instead of showing #DIV/0!.
Why it happens
AVERAGE adds the numbers and divides by how many there are. With no numbers yet, that is a division by zero, so Week 2 shows #DIV/0!:
=AVERAGE(C2:C6)Empty cells, and cells whose formula returns "" (empty text), are skipped, so a column of blanks has nothing to divide by. Week 1 works: (120 + 0 + 90) / 3 = 70.
The fix: check for numbers first
Count the numbers before averaging, and show a blank when there are none:
=IF(COUNT(B2:B6)=0,"",AVERAGE(B2:B6))B7 still shows 70, and copied to C7 it shows an empty cell instead of the error. COUNT counts only cells that hold numbers, so empty cells and "" are not counted.
Show 0 instead of a blank
Swap the "" for a 0:
=IF(COUNT(C2:C6)=0,0,AVERAGE(C2:C6))Week 2 now shows 0. A blank is usually clearer, because a 0 looks like a real result.
Why not just IFERROR?
This hides the error too:
=IFERROR(AVERAGE(C2:C6),"")It works, but it hides every error, including a #N/A or #VALUE! from a broken cell inside the range, so a real problem goes unnoticed. The IF and COUNT version hides only the "no numbers yet" case and still shows #N/A when a cell in the range has one.
Leave the zeros out
If a 0 means "nothing recorded" rather than a real zero, average only the cells that are not 0:
=AVERAGEIF(B2:B6,"<>0")Week 1 becomes (120 + 90) / 2 = 105. AVERAGEIF shows #DIV/0! when nothing is left to average, so on an empty week wrap it:
=IFERROR(AVERAGEIF(C2:C6,"<>0"),"")Here IFERROR is the simple guard, since the only cells it can meet are your own numbers.
The same error in any division
Any formula that divides by an empty or zero cell shows #DIV/0!. With a total in D2 and a count in E2, check the count first:
=IF(E2=0,"",D2/E2)With 50 in D2 and nothing or 0 in E2, the cell stays blank; with 4 in E2 it shows 12.5. An empty cell counts as 0 in this test. IF, COUNT, AVERAGE, AVERAGEIF and IFERROR work in every Excel since 2007 and in Google Sheets.
Templates with the math already done
Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.
- Teacher GradebookScores in, overall percent, letter grade and missing work out, for up to 40 students.
- Sales Tracker and Dashboard 2027Log every sale and see revenue, top products and top customers on a dashboard.
- Worked Hours and Pay TrackerA timesheet for hourly workers that checks every paystub.
More how-tos
- Find a price by its code with XLOOKUPType a product code, get its price back from your price list. One formula, no helper columns.
- Total each category with SUMIFSAdd up all the Fuel, all the Office, all the Meals: one formula per category, and more rules when you need them.
- Check two conditions at once with IF and ANDFree shipping only when the order is 50 or more and ships in Canada: one IF, one AND, done.
- Add leading zeros to numbersType 00021 and Excel or Google Sheets shows 21, because to them it is just the number 21. Here is how to get the zeros back, and keep them.
- Count rows that meet two conditions with COUNTIFSHow many of Ana's deals were 500 or more? COUNTIFS answers it in one cell, and takes as many rules as you need.
- Calculate percentage changeSales went from 400 to 500. That is a 25% increase, and one short formula works it out for every row.
- Free pricing calculatorsPrice a job from your real costs and margin. No sign up.