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

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,

Hi,

Was the chart selected when you did Tools>Options ?

Cheers
Andy

teds wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info

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" wrote:

> Hi,
>
> Was the chart selected when you did Tools>Options ?
>
> Cheers
> Andy
>
> teds wrote:
> > 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, Microsoft MVP - Excel
> http://www.andypope.info
>

Assuming formula is in C7 the conditional formatting would be,

FormulaIs: =ISNA(C7)

Cheers
Andy

teds wrote:
> 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" wrote:
>
>
>>Hi,
>>
>>Was the chart selected when you did Tools>Options ?
>>
>>Cheers
>>Andy
>>
>>teds wrote:
>>
>>>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, Microsoft MVP - Excel
>>http://www.andypope.info
>>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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" wrote:

> Assuming formula is in C7 the conditional formatting would be,
>
> FormulaIs: =ISNA(C7)
>
> Cheers
> Andy
>
> teds wrote:
> > 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" wrote:
> >
> >
> >>Hi,
> >>
> >>Was the chart selected when you did Tools>Options ?
> >>
> >>Cheers
> >>Andy
> >>
> >>teds wrote:
> >>
> >>>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, Microsoft MVP - Excel
> >>http://www.andypope.info
> >>
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>

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)

teds wrote:
> 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" wrote:
>
>
>>Assuming formula is in C7 the conditional formatting would be,
>>
>>FormulaIs: =ISNA(C7)
>>
>>Cheers
>>Andy
>>
>>teds wrote:
>>
>>>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" wrote:
>>>
>>>
>>>
>>>>Hi,
>>>>
>>>>Was the chart selected when you did Tools>Options ?
>>>>
>>>>Cheers
>>>>Andy
>>>>
>>>>teds wrote:
>>>>
>>>>
>>>>>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, Microsoft MVP - Excel
>>>>http://www.andypope.info
>>>>
>>
>>--
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I apologize, but in my attempt to simplify, I have caused confusion.
In E54 I have =IF(SUM(B54:D54)=0,#N/A,SUM(B54:D54)), 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" wrote:

> 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)
>
> teds wrote:
> > 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" wrote:
> >
> >
> >>Assuming formula is in C7 the conditional formatting would be,
> >>
> >>FormulaIs: =ISNA(C7)
> >>
> >>Cheers
> >>Andy
> >>
> >>teds wrote:
> >>
> >>>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" wrote:
> >>>
> >>>
> >>>
> >>>>Hi,
> >>>>
> >>>>Was the chart selected when you did Tools>Options ?
> >>>>
> >>>>Cheers
> >>>>Andy
> >>>>
> >>>>teds wrote:
> >>>>
> >>>>
> >>>>>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, Microsoft MVP - Excel
> >>>>http://www.andypope.info
> >>>>
> >>
> >>--
> >>
> >>Andy Pope, Microsoft MVP - Excel
> >>http://www.andypope.info
> >>
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>

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

teds wrote:
> I apologize, but in my attempt to simplify, I have caused confusion.
> In E54 I have =IF(SUM(B54:D54)=0,#N/A,SUM(B54:D54)), 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" wrote:
>
>
>>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)
>>
>>teds wrote:
>>
>>>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" wrote:
>>>
>>>
>>>
>>>>Assuming formula is in C7 the conditional formatting would be,
>>>>
>>>>FormulaIs: =ISNA(C7)
>>>>
>>>>Cheers
>>>>Andy
>>>>
>>>>teds wrote:
>>>>
>>>>
>>>>>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" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Hi,
>>>>>>
>>>>>>Was the chart selected when you did Tools>Options ?
>>>>>>
>>>>>>Cheers
>>>>>>Andy
>>>>>>
>>>>>>teds wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>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, Microsoft MVP - Excel
>>>>>>http://www.andypope.info
>>>>>>
>>>>
>>>>--
>>>>
>>>>Andy Pope, Microsoft MVP - Excel
>>>>http://www.andypope.info
>>>>
>>
>>--
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

That was problem. Thanks again.

"Andy Pope" wrote:

> 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
>
> teds wrote:
> > I apologize, but in my attempt to simplify, I have caused confusion.
> > In E54 I have =IF(SUM(B54:D54)=0,#N/A,SUM(B54:D54)), 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" wrote:
> >
> >
> >>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)
> >>
> >>teds wrote:
> >>
> >>>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" wrote:
> >>>
> >>>
> >>>
> >>>>Assuming formula is in C7 the conditional formatting would be,
> >>>>
> >>>>FormulaIs: =ISNA(C7)
> >>>>
> >>>>Cheers
> >>>>Andy
> >>>>
> >>>>teds wrote:
> >>>>
> >>>>
> >>>>>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" wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Hi,
> >>>>>>
> >>>>>>Was the chart selected when you did Tools>Options ?
> >>>>>>
> >>>>>>Cheers
> >>>>>>Andy
> >>>>>>
> >>>>>>teds wrote:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>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, Microsoft MVP - Excel
> >>>>>>http://www.andypope.info
> >>>>>>
> >>>>
> >>>>--
> >>>>
> >>>>Andy Pope, Microsoft MVP - Excel
> >>>>http://www.andypope.info
> >>>>
> >>
> >>--
> >>
> >>Andy Pope, Microsoft MVP - Excel
> >>http://www.andypope.info
> >>
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>

No worries, glad we got it sorted.

Cheers
Andy

teds wrote:
> That was problem. Thanks again.
>
> "Andy Pope" wrote:
>
>
>>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
>>
>>teds wrote:
>>
>>>I apologize, but in my attempt to simplify, I have caused confusion.
>>>In E54 I have =IF(SUM(B54:D54)=0,#N/A,SUM(B54:D54)), 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" wrote:
>>>
>>>
>>>
>>>>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)
>>>>
>>>>teds wrote:
>>>>
>>>>
>>>>>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" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Assuming formula is in C7 the conditional formatting would be,
>>>>>>
>>>>>>FormulaIs: =ISNA(C7)
>>>>>>
>>>>>>Cheers
>>>>>>Andy
>>>>>>
>>>>>>teds wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>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" wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>Hi,
>>>>>>>>
>>>>>>>>Was the chart selected when you did Tools>Options ?
>>>>>>>>
>>>>>>>>Cheers
>>>>>>>>Andy
>>>>>>>>
>>>>>>>>teds wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>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, Microsoft MVP - Excel
>>>>>>>>http://www.andypope.info
>>>>>>>>
>>>>>>
>>>>>>--
>>>>>>
>>>>>>Andy Pope, Microsoft MVP - Excel
>>>>>>http://www.andypope.info
>>>>>>
>>>>
>>>>--
>>>>
>>>>Andy Pope, Microsoft MVP - Excel
>>>>http://www.andypope.info
>>>>
>>
>>--
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info