Forum Discussion
Charts acting like blank cells are zeros - NB the cells use a formula which returns the blank value
Hi Richard,
Right click on your chart, Select Data in the menu, Hidden and Empty Cells button at bottom left, select Gaps
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!
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
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
Richard,
To average you may use AGGREGATE function https://support.office.com/en-us/article/AGGREGATE-function-43b9278e-6aa7-4f17-92b6-e19993fa26df like
=AGGREGATE(1,6,<your range>)
where first parameter 1 means average, second (6) to ignore errors within the range.
Conditional formatting:
Apply the rule as on screenshot
instead 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)