Forum Discussion
Charts acting like blank cells are zeros - NB the cells use a formula which returns the blank value
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)