Forum Discussion
Blank cells show up as zeros in chart
Hello, I'm taking simple averages of my data (e.g. =AVERAGE(B3:Q3)) and then plotting them as a line in a scatter plot. However, there are many gaps in my data, so my averages often return #DIV/0...
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.