Forum Discussion
Blank cells show up as zeros in chart
Emilia480 to ignore what's happening, use this:
=IF(COUNTA(B3:Q3)=0;"";AVERAGE(B3:Q3))
This way, IF there are no value (and so the average of no values will export #DIV/0 error) then leave blank (""); else (so if there are some values, positive or negative), then apply AVERAGE.
You can do the same with SUM instead of COUNTA, but that means that a row with no values and a row with some value that summed are 0 are going to be counted as the same.
With COUNTA, a row with no values will be empty ("") and a row with value that sums 0 shows average 0 as it should be.
Arul Tresoldi thanks for you help. However, when I tried to use that formula, it says its not a valid formula. Any ideas why it's not working?
Emilia480 , try to change semicolons on commas within the formula
- SergeiBaklan Arul Tresoldi, I replaced the semicolons with commas, which allowed the formula to go through. Thanks for the suggestion. But the blank cells still show up as zeros in my chart. Not sure what I'm missing.
For your formula you may return #N/A if error, like
=IFERROR(<your formula>,NA())and check Show #N/A as empty cells together with Gaps for empty cells
Not to show errors in the table you may apply conditional formatting rule and format font to same color as background (white)
Please find this sample in attached file
