How to count blank cells as zero when using AVERAGE in Excel?
When I average a range of data which includes some blank cells, it will auto calculate the data ignoring empty cells by using =AVERAGE(range). But in some cases, you may want to count the blank cells as zero when average the range of data as below screenshot shown, how can you solve it in Excel?
Select Nonblank Cells（quickly select non-blank cells from a selection or whole sheet.）
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
To count the blank cells as zero when averaging a range of data, you can do as below:
Select a blank cell that will place the result, type this formula =AVERAGE(0+A1:A10), then press Shift + Ctrl + Enter key to get to correct result. See screenshot:
1. Using =SUM(A1:A10)/10 and pressing the Enter key also can help you to count blanks as zeros when averaging.
2. In above formulas, A1:A10 is the range you want to calculate the average.