Google Sheets
How to Average Recorded Values in Google Sheets Without Counting Blanks as Zero
Published by OneCell
Leave a cell empty when the reading is missing. Enter a numeric zero when you observed zero. AVERAGE skips empty cells and includes numeric zeros. Put the number of readings beside the average so you can see how much of the planned period it covers. Google’s AVERAGE reference
Set up six planned sessions
Imagine a tool-lending desk that tallies returns during six 30-minute sessions in one week. On Tuesday, staff checked and counted zero returns. Wednesday and Friday have no recorded counts.
Create a tab called Returns. Enter Session in A1 and Returns observed in B1. Add the fictional readings below.
Enter Monday in A2. Enter 8 in B2.
Enter Tuesday in A3. Enter 0 in B3.
Enter Wednesday in A4. Leave B4 empty.
Enter Thursday in A5. Enter 4 in B5.
Enter Friday in A6. Leave B6 empty.
Enter Saturday in A7. Enter 12 in B7.
Put the average and counts side by side
Enter Sessions recorded in E1. Put
=COUNT(B2:B7)in E2.Enter Sessions planned in F1. Put 6 in F2.
Enter Average per recorded session in D1. Put
=IF(E2=0, "", AVERAGE(B2:B7))in D2.
COUNT counts the numeric readings, including the Tuesday zero. When E2 is zero, IF makes D2 display blank. Otherwise, D2 averages the recorded values. Expect 6 returns per recorded session in D2, with 4 of 6 sessions recorded in the cells beside it. The four readings total 24, so the average is 24 ÷ 4. Google’s COUNT reference and IF reference
Check the difference between zero and missing
Put a zero in each of the two empty cells, B4 and B6. The average falls to 4 and the recorded count rises to 6. The two extra zeros imply observations the desk never recorded. Clear both cells again.
Suppose staff find a reliable note showing zero returns on Wednesday. Enter 0 in B4 and leave B6 empty. Expect an average of 4.8 across 5 of 6 sessions. The total is still 24, now divided by five recorded sessions.
Clear B2:B7 in the practice sheet. D2 should display blank and E2 should show 0. Then enter one numeric zero in B3. D2 should show 0 and E2 should show 1. Restore the original readings to return to the first result.
Use numeric readings from comparable sessions when applying this to your own log. If the planned period changes, update F2 and adjust the ranges in both formulas to cover the same sessions.