Forum Discussion

Emilia480's avatar
Emilia480
Copper Contributor
Aug 15, 2019

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...
Charting
excel
Excel on Mac
Formulas and Functions
Emilia480's avatar
Emilia480
Copper Contributor
Aug 15, 2019

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?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 15, 2019

Emilia480 , try to change semicolons on commas within the formula

  • Emilia480's avatar
    Emilia480
    Copper Contributor
    Aug 15, 2019
    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.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 15, 2019

      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

      • GaryChan's avatar
        GaryChan
        Copper Contributor
        Apr 05, 2022

        SergeiBaklan 

        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.

Resources