Blank cells show up as zeros in chart

[Emilia480]

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

[#2]

@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.

[#3]

@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?

[#4]

@Emilia480 , try to change semicolons on commas within the formula

[#5]

@Sergei Baklan @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.

[#6]

@Emilia480 

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

[#7]

@Sergei Baklan 

I followed the instruction but the empty cells still showed up as data point. (The column of data was copied and pasted as values to ensure no formula is in there). I found out that I have to press "delete" on the already emptied cells to prevent the point from showing up in the chart...  feels like Excel does not treat an emptied cell truly emptied until delete is done on the cell? Thanks for the help.

[#8]

@GaryChan 

Perhaps you copy/pasted as values cells with empty texts (=""). Even empty text is not visible and have zero length it's still the text. Thus the cell is not blank. If so - yes, you need to delete such empty texts.

[#9]

A formula that looks like =IF(something,"") that returns "" is NOT an empty cell, and will be plotted as a zero value. Change it to =IF(something,NA()), and you will get #N/A in the cell, and #N/A can be plotted as if it is blank.

[#10]

Thanks! It works. I wonder why "empty text" is not treated as empty. Maybe it has something to do with data type definition for that particular cell?

[#11]

@GaryChan "" is not an empty cell, it contains "", which is a short piece of text. Maybe it's not logical, but it's how Excel works.

[#12]

@GaryChan 

I had the same problem with apparently blank cells plotting at zero on my graphs. I used the solution shown here [ rather than using '' in the IF statement, I used NA() ], and the problem seems to have been resolved. Thanks.