Conditional format na()

Question

Hi,

In my data I have formula =IF(SUM(H3:H9)=0,NA(),SUM(H3:H9)) which returns #N/A when the sum = zero.  I'm doing this as I have line chart from this data (x 12 months) and don't want to see the line for the months with no data.

My problem is that the totals in my tables are full of #N/A's which don't look so good.  I'm trying to conditinal format the cell with the formula in, so that when #N/A appears, the font is white. I've tried typing the formula in conditional format; typed #N/A in conditional format but I can still see the 'error'.

Can someone help me conditional format this cell so that the #N/A is 'invisable' when there is no data to sum (therefore no line on my chart) but when the value of the formula is greater than zero, then the conditinal format is not used.

Hope that makes sense.

TIA,

Pete

Answer 1

Hi,

Say this formula is In A1 and is returning NA().

Select A1; you can then drag to select other cells, then

Home tab | Conditional Formatting | New Rule | use a formula to decide which cells to format and enter this formula

=ISNA(A1)

Click the 'Format' button | Font tab and in the colour dropdown select white and OK out.

Answer 2

Hi,

Select the range of data and go to Home > Conditional formatting > New Rule > Format only cells that Contain > Errors.  Click on Format and under Font, select White.  Click on OK/Apply.

Hope this helps.

Answer 3

Hi,

 

Say this formula is In A1 and is returning NA().

 

Select A1; you can then drag to select other cells, then

 

Home tab | Conditional Formatting | New Rule | use a formula to decide which cells to format and enter this formula

 

 

=ISNA(A1)

 

 

Click the 'Format' button | Font tab and in the colour dropdown select white and OK out.

Great stuff, works perfectly - thanks for the prompt reply Mike.

ATB,

Pete