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

Question

Hi,&nbsp;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, ensures C is also blank if either of A or B are blank. So my column C now has mostly numerical values but also some blanks. So when i look at Averages in the column the returned value is correct.&nbsp;&nbsp;HOWEVER, when i chart the row, all the blank cells appear as zero values in the chart. If I 'delete' the formula that gives the blank return, and therefore replace it with an empty (ie blank) cell then the chart treats it correctly as a gap.&nbsp;&nbsp;How do i get the chart to treat the blank (resulting from the formula) as a true blank (ie a gap in the chart)? Or do I need to change the formula?&nbsp;

sergeibaklan · Answer

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

richard cooke · Answer

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 · Answer

Hi Richard,&nbsp;In your formula you may use NA() instead of empty string=IF(OR(A3="",B3=""),NA(),A3-B3)whenTo hide #N/A you may apply conditional formatting with rule like=IFNA(C3,"na")="na"to format the cell as white on whiteAnd in latest Excel you may select to show #N/A as empty cell, when&nbsp;

sergeibaklan · Answer

Richard,&nbsp;To average you may use AGGREGATE function&nbsp;https://support.office.com/en-us/article/AGGREGATE-function-43b9278e-6aa7-4f17-92b6-e19993fa26df like=AGGREGATE(1,6,&lt;your range&gt;)where first parameter 1 means average, second (6) to ignore errors within the range.&nbsp;Conditional formatting:Apply the rule as on screenshotinstead of "na" it could be whatever you want which is definitely not in you range. The rule return TRUE if #N/A in the cell and FALSE otherwise.Click Format and apply white font (or the same as your background)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

richard cooke · Answer

Great thanks Sergei&nbsp;I am now using the function of the form:&nbsp;=IF(OR(A3="",B3=""),NA(),A3-B3)and this does indeed return #N/A&nbsp; 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:&nbsp;=IFNA(C3,"na")="na"&nbsp;thanks again for your help, Richard

Forum Discussion

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

Share

Resources