Forum Discussion

Jarod Hart's avatar
Jarod Hart
Copper Contributor
Feb 28, 2018

Line Chart not display cell value if logic formula results in "" (null/blank cell)

Hi,

 

I'm trying to determine if it is possible to have a line chart treat a cell that has a logic formula resulting in a "" value as a blank cell, thus resulting in a gap in the line.

 

For example, I have a logic formula like the following: =if(A1="Yes",200,"")

 

When the formula results in the FALSE result of "" the Excel line chart is displaying a 0 value for the cell, instead of treating it like a blank cell...is there a way to get the chart to treat it like a blank cell?

 

Thank you in advance!

Charts & Visualizing Data
Need Help

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 28, 2018

    Hi Jarod,

     

    You may use #N/A instead of "" like

    =if(A1="Yes",200,NA())

    and in the table you may hide #N/A using conditional formatting with =ISNA(<>) rule.

    If you have calculations on your table like SUM, etc., when use AGGREGATE with ignore error option.

     

    For the chart you shall check Show #N/A as empty cell option. Above for 2016, not sure about earlie versions.