Forum Discussion

Copper Contributor

Oct 24, 2017

Charts acting like blank cells are zeros - NB the cells use a formula which returns the blank value

Hi, I have a column, C, of the form: C6=IF(OR(A6="",B6=""),"",A6-B6) where A and B are columns with mostly numerical values but occasionally blanks. The formula, rather than a simple C=A-B, ensur...

Charts & Visualizing Data

Diamond Contributor

Oct 24, 2017

Hi Richard,

Right click on your chart, Select Data in the menu, Hidden and Empty Cells button at bottom left, select Gaps

Richard Cooke
Copper Contributor
Oct 24, 2017
Thank you Sergei - I tried that. However it does not work, as it somehow interprets a blank cell caused by a formula as different to a truly blank cell. It treats the blank-cell-caused-by-a-formula as a zero ...it's most irritating!
- SergeiBaklan
  Diamond Contributor
  Oct 24, 2017
  Hi Richard,
  
  In your formula you may use NA() instead of empty string
  =IF(OR(A3="",B3=""),NA(),A3-B3)
  when
  To hide #N/A you may apply conditional formatting with rule like
  =IFNA(C3,"na")="na"
  to format the cell as white on white
  And in latest Excel you may select to show #N/A as empty cell, when
  - Richard Cooke
    Copper Contributor
    Oct 24, 2017
    Great thanks Sergei
    
    I am now using the function of the form:
    =IF(OR(A3="",B3=""),NA(),A3-B3)
    and this does indeed return #N/A when either, or both, of the two input cells is blank.
    The chart now works, since it treats #N/A as a blank or gap in the chart. However, I have a table of Average values and these Average values now contain #N/A for any Averages that contain any #N/A cells. How do i correct this bit? (I did not fully understand how to apply the conditional formatting you mentioned, specifically where to enter this:
    =IFNA(C3,"na")="na"
    thanks again for your help, Richard

Resources