Making formula result #NA invisible?

  • Thread starter Thread starter Phil C
  • Start date Start date
P

Phil C

Hi All

I want to make my #NA values invisible in a table, from which a chart is
produced. I have to use NA(), not "", or else the chart doesn't plot
correctly. The error values are produced (when a referenced cell is blank)
by a formula of the form: =IF(B40="",NA(),24*60*(B40-$C$14))

Diane posted essentially the same problem on 7 August and got a 'workaround'
suggestion from DVT as follows:

"You could set your formula to return a #NA, then use conditional formatting
to make the text "invisible" in that case. The conditional formatting would
go something like this: Select cell A1, Format | Conditional Formatting |
Formula is | =NA(A1). Then set the font color to the same as the background
color (i.e. white). You can copy that cell and Paste Special | Formats".

I have tried this but it doesn't seem to work. The text in the cell stays
black when I have formatted it as brown (IF condition as above). I have
input the suggestion literally. If I type NA( in the dialogue box then
click on the cell in question, followed by ) , then OK, Excel puts
="NA($C$40)" in the box. C40 is the cell in question. Have I misinterpreted
the suggestion, or is it just wrong? I have tried vaious things using Cell
Value Is (e.g. equal to..."#NA"), but to no avail.


Thanks in advance, Phil
 
If you want to format cell C5, in the Conditional Formatting dialog
box, set the first drop down to 'Formula Is' In the text box enter
=ISNA(C5). Note the presence of the equal sign and the *absence* of
any $ sign.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top