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!. I'd like these values to show up as blanks in my graphs, but they're showing up as zeroes. I've also tried the formula =IFERROR(AVERAGE(B3:Q3),"") to change the #DIV/0! cells to blanks, but it makes no difference in the chart. Additionally, I've gone into "Select Data" and made sure that "Show empty cells as: Gaps" is selected. Why are my blanks still showing up as zeroes? I'm using Excel on Mac.
Thanks
11 Replies
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
