Tools/Options/Charts-Active cells is dimmed. Want to select leave

[Guest]

I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in
chart, but still showed zeros. When I went to Tools/Options/Charts, the
Active Cells area was dimmed and I could not select "leave gaps". Any
suggestions?
Thanks,

 

[Andy Pope]

Hi,

Was the chart selected when you did Tools>Options ?

Cheers
Andy

 

[Guest]

Oops. No I didn't have it selected. Thanks. But I am having problems
setting up the Conditional Formating to hide the #N/A. I tried making color
white when cell=#N/A, but didn't work. Could you give me an example?
Thanks again.

 

[Andy Pope]

Assuming formula is in C7 the conditional formatting would be,

FormulaIs: =ISNA(C7)

Cheers
Andy

 

[Guest]

Tried Formula is = isna(e54), and it returned Formula is -= "isna(e54)" .
Then made formating background blue just to see if it was recognizing value,
but did not get blue background. Actual formula in cell was
IF(e54=0,#N/A,e54). What should I try next?
Thanks
teds

 

[Andy Pope]

But if your worksheet formula is testing E54 for zero you need to apply
the formula to the cell that contains IF(e54=0,#N/A,e54) , which I would
assume is E55.
So CF FormulaIs would be =ISNA(E55)

 

[Guest]

I apologize, but in my attempt to simplify, I have caused confusion.
In E54 I have =IF(SUM(B5454)=0,#N/A,SUM(B5454)), which now gives
the answer #N/A.

In CF I entered Formula is = isna(e54), and it returned Formula is -=
"isna(e54)" .

I then formated cell in CF to be light blue color with red font just to see
if it recognized #N/A (goal is to have blank cell & not show zero value on
chart).

When closing CF, I still have #N/A showing, and the cell color is still
white with black font.

I am probably missing something very simple, but don't know what it is.

Thanks again for your help,

 

[Andy Pope]

Ah I think I see the problem.
Make sure you include the ='s when entering the formula in CF. Otherwise
it will end up encased in double-quotes.

Cheers
Andy

 

[Guest]

That was problem. Thanks again.

Ah I think I see the problem.
Make sure you include the ='s when entering the formula in CF. Otherwise
it will end up encased in double-quotes.

Cheers
Andy

 

[Andy Pope]

No worries, glad we got it sorted.

Cheers
Andy

That was problem. Thanks again.

: