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
Arul Tresoldi's avatar
Arul Tresoldi
Iron Contributor
Aug 15, 2019

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.

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

Resources