|
||||||||||||||||||||
|
|||||||||||||||||||||
|
|||||||
 |
|
|
Thread Tools | Search this Thread | Display Modes |
|
#1
05-29-2005, 07:05 PM
|
|||
|
|||
|
#N/A Values : Returned by Formulas vs Entered Manually
Hello;
When some cells of the data series have #N/A values returned by formulas, the corresponding chart fails. But if the #N/A values are manually entered into those same cells, the chart works fine !! To my understanding, Excel Charts treat cells with #N/A values as empty cells, so one may select the relevant chart option to "leave gaps", which is perfect. With this apparent different interpretation (by Excel Chart) of the same #N/A values in the data series, how can I make the #N/A values returned by formulas acceptable by the chart ?? Thank you for your help. 
|
|
#2
05-29-2005, 09:05 PM
|
|||
|
|||
|
Re: #N/A Values : Returned by Formulas vs Entered Manually
The #N/A should be the result of the function NA() and not just typing
the literal "#N/A". Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY Scatter chart but rather as a 'interpolate across the #N/A.' -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>, monir@discussions.microsoft.com says... > Hello; > > When some cells of the data series have #N/A values returned by formulas, > the corresponding chart fails. > But if the #N/A values are manually entered into those same cells, the chart > works fine !! > To my understanding, Excel Charts treat cells with #N/A values as empty > cells, so one may select the relevant chart option to "leave gaps", which is > perfect. > > With this apparent different interpretation (by Excel Chart) of the same > #N/A values in the data series, how can I make the #N/A values returned by > formulas acceptable by the chart ?? > > Thank you for your help. >
|
|
#3
05-29-2005, 11:05 PM
|
|||
|
|||
|
Re: #N/A Values : Returned by Formulas vs Entered Manually
Tushar;
Apsolutely correct ! By having the formula returning the result of the function NA() instead of returning the string "#N/A", the line chart problem disappeared ! Thank you once again for your help. "Tushar Mehta" wrote: > The #N/A should be the result of the function NA() and not just typing > the literal "#N/A". > > Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY > Scatter chart but rather as a 'interpolate across the #N/A.' > > -- > Regards, > > Tushar Mehta > www.tushar-mehta.com > Excel, PowerPoint, and VBA add-ins, tutorials > Custom MS Office productivity solutions > > In article <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>, > monir@discussions.microsoft.com says... > > Hello; > > > > When some cells of the data series have #N/A values returned by formulas, > > the corresponding chart fails. > > But if the #N/A values are manually entered into those same cells, the chart > > works fine !! > > To my understanding, Excel Charts treat cells with #N/A values as empty > > cells, so one may select the relevant chart option to "leave gaps", which is > > perfect. > > > > With this apparent different interpretation (by Excel Chart) of the same > > #N/A values in the data series, how can I make the #N/A values returned by > > formulas acceptable by the chart ?? > > > > Thank you for your help. > > >
|
|
#4
07-03-2005, 04:05 PM
|
|||
|
|||
|
Re: #N/A Values : Returned by Formulas vs Entered Manually
I too wish to stop a chart line. I am using the formula
=IF(AC90=0,#N/A,AC90*2) in one column. The chart line comes from a second column of figures =IF(AC90=#N/A,"",1). This gives me #N/A and doesn't stop the chart line. Any suggestions would be greatly appreciated. Roger "monir" <monir@discussions.microsoft.com> wrote in message news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com... > Tushar; > > Apsolutely correct ! By having the formula returning the result of the > function NA() instead of returning the string "#N/A", the line chart > problem > disappeared ! > > Thank you once again for your help. > > > > "Tushar Mehta" wrote: > >> The #N/A should be the result of the function NA() and not just typing >> the literal "#N/A". >> >> Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY >> Scatter chart but rather as a 'interpolate across the #N/A.' >> >> -- >> Regards, >> >> Tushar Mehta >> www.tushar-mehta.com >> Excel, PowerPoint, and VBA add-ins, tutorials >> Custom MS Office productivity solutions >> >> In article <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>, >> monir@discussions.microsoft.com says... >> > Hello; >> > >> > When some cells of the data series have #N/A values returned by >> > formulas, >> > the corresponding chart fails. >> > But if the #N/A values are manually entered into those same cells, the >> > chart >> > works fine !! >> > To my understanding, Excel Charts treat cells with #N/A values as empty >> > cells, so one may select the relevant chart option to "leave gaps", >> > which is >> > perfect. >> > >> > With this apparent different interpretation (by Excel Chart) of the >> > same >> > #N/A values in the data series, how can I make the #N/A values returned >> > by >> > formulas acceptable by the chart ?? >> > >> > Thank you for your help. >> > >>
|
|
#5
07-03-2005, 11:05 PM
|
|||
|
|||
|
Re: #N/A Values : Returned by Formulas vs Entered Manually
Hi Roger -
To return #N/A in a formula, use NA() in the formula: =IF(AC90=0,NA(),AC90*2) - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Roger wrote: > I too wish to stop a chart line. I am using the formula > =IF(AC90=0,#N/A,AC90*2) in one column. The chart line comes from a second > column of figures =IF(AC90=#N/A,"",1). This gives me #N/A and doesn't stop > the chart line. Any suggestions would be greatly appreciated. > > Roger > > > > "monir" <monir@discussions.microsoft.com> wrote in message > news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com... > >>Tushar; >> >>Apsolutely correct ! By having the formula returning the result of the >>function NA() instead of returning the string "#N/A", the line chart >>problem >>disappeared ! >> >>Thank you once again for your help. >> >> >> >>"Tushar Mehta" wrote: >> >> >>>The #N/A should be the result of the function NA() and not just typing >>>the literal "#N/A". >>> >>>Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY >>>Scatter chart but rather as a 'interpolate across the #N/A.' >>> >>>-- >>>Regards, >>> >>>Tushar Mehta >>>www.tushar-mehta.com >>>Excel, PowerPoint, and VBA add-ins, tutorials >>>Custom MS Office productivity solutions >>> >>>In article <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>, >>>monir@discussions.microsoft.com says... >>> >>>>Hello; >>>> >>>>When some cells of the data series have #N/A values returned by >>>>formulas, >>>>the corresponding chart fails. >>>>But if the #N/A values are manually entered into those same cells, the >>>>chart >>>>works fine !! >>>>To my understanding, Excel Charts treat cells with #N/A values as empty >>>>cells, so one may select the relevant chart option to "leave gaps", >>>>which is >>>>perfect. >>>> >>>>With this apparent different interpretation (by Excel Chart) of the >>>>same >>>>#N/A values in the data series, how can I make the #N/A values returned >>>>by >>>>formulas acceptable by the chart ?? >>>> >>>>Thank you for your help. >>>> >>> > >
|
|
#6
07-04-2005, 02:05 PM
|
|||
|
|||
|
Re: #N/A Values : Returned by Formulas vs Entered Manually
Hi Jon, good to hear from you and thanks for your reply. I actually looked
at your website prior to sending the previous request. I appear to have sent the wrong information for my request so here are the actual formulae and details. This first formula gives different results depending on whether there is a number or a blank in H column =IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7). I would prefer not to adjust this if it is possible as it affects several other columns as well. The problem column formula is =IF(ISERROR(BN86),"",IF(ROW() > Selection!$D$8,"",(BN86-BN$7)/BN$7)) This is the column where I want the chart line stopped if there is no numerical value from the previous formula. Roger "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message news:%23a88hsDgFHA.1284@TK2MSFTNGP14.phx.gbl... > Hi Roger - > > To return #N/A in a formula, use NA() in the formula: > > =IF(AC90=0,NA(),AC90*2) > > - Jon > ------- > Jon Peltier, Microsoft Excel MVP > Peltier Technical Services > Tutorials and Custom Solutions > http://PeltierTech.com/ > _______ > > > Roger wrote: > >> I too wish to stop a chart line. I am using the formula >> =IF(AC90=0,#N/A,AC90*2) in one column. The chart line comes from a >> second column of figures =IF(AC90=#N/A,"",1). This gives me #N/A and >> doesn't stop the chart line. Any suggestions would be greatly >> appreciated. >> >> Roger >> >> >> >> "monir" <monir@discussions.microsoft.com> wrote in message >> news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com... >> >>>Tushar; >>> >>>Apsolutely correct ! By having the formula returning the result of the >>>function NA() instead of returning the string "#N/A", the line chart >>>problem >>>disappeared ! >>> >>>Thank you once again for your help. >>> >>> >>> >>>"Tushar Mehta" wrote: >>> >>> >>>>The #N/A should be the result of the function NA() and not just typing >>>>the literal "#N/A". >>>> >>>>Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY >>>>Scatter chart but rather as a 'interpolate across the #N/A.' >>>> >>>>-- >>>>Regards, >>>> >>>>Tushar Mehta >>>>www.tushar-mehta.com >>>>Excel, PowerPoint, and VBA add-ins, tutorials >>>>Custom MS Office productivity solutions >>>> >>>>In article <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>, >>>>monir@discussions.microsoft.com says... >>>> >>>>>Hello; >>>>> >>>>>When some cells of the data series have #N/A values returned by >>>>>formulas, >>>>>the corresponding chart fails. >>>>>But if the #N/A values are manually entered into those same cells, the >>>>>chart >>>>>works fine !! >>>>>To my understanding, Excel Charts treat cells with #N/A values as empty >>>>>cells, so one may select the relevant chart option to "leave gaps", >>>>>which is >>>>>perfect. >>>>> >>>>>With this apparent different interpretation (by Excel Chart) of the >>>>>same >>>>>#N/A values in the data series, how can I make the #N/A values returned >>>>>by >>>>>formulas acceptable by the chart ?? >>>>> >>>>>Thank you for your help. >>>>> >>>> >>
|
|
#7
07-05-2005, 04:05 PM
|
|||
|
|||
|
Re: #N/A Values : Returned by Formulas vs Entered Manually
A zero or blank in H86 gives the same answer in this formula:
=IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7) but if the blank is not a blank after all but another formula returning "", you can use this: =IF(OR(H86=0,LEN(H86)=0),NA(),H86*SUM(J$7:J86)/J$7) Note the use of NA() instead of #N/A. It probably doesn't matter in this case, but it's sharper to use NA(). In the second formula, I'd replace both "" with NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Roger wrote: > Hi Jon, good to hear from you and thanks for your reply. I actually looked > at your website prior to sending the previous request. I appear to have > sent the wrong information for my request so here are the actual formulae > and details. This first formula gives different results depending on > whether there is a number or a blank in H column > =IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7). I would prefer not to adjust this if > it is possible as it affects several other columns as well. > > > > The problem column formula is > > =IF(ISERROR(BN86),"",IF(ROW() > Selection!$D$8,"",(BN86-BN$7)/BN$7)) > > This is the column where I want the chart line stopped if there is no > numerical value from the previous formula. > > > > Roger > > > > "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message > news:%23a88hsDgFHA.1284@TK2MSFTNGP14.phx.gbl... > >>Hi Roger - >> >>To return #N/A in a formula, use NA() in the formula: >> >> =IF(AC90=0,NA(),AC90*2) >> >>- Jon >>------- >>Jon Peltier, Microsoft Excel MVP >>Peltier Technical Services >>Tutorials and Custom Solutions >>http://PeltierTech.com/ >>_______ >> >> >>Roger wrote: >> >> >>>I too wish to stop a chart line. I am using the formula >>>=IF(AC90=0,#N/A,AC90*2) in one column. The chart line comes from a >>>second column of figures =IF(AC90=#N/A,"",1). This gives me #N/A and >>>doesn't stop the chart line. Any suggestions would be greatly >>>appreciated. >>> >>>Roger >>> >>> >>> >>>"monir" <monir@discussions.microsoft.com> wrote in message >>>news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com... >>> >>> >>>>Tushar; >>>> >>>>Apsolutely correct ! By having the formula returning the result of the >>>>function NA() instead of returning the string "#N/A", the line chart >>>>problem >>>>disappeared ! >>>> >>>>Thank you once again for your help. >>>> >>>> >>>> >>>>"Tushar Mehta" wrote: >>>> >>>> >>>> >>>>>The #N/A should be the result of the function NA() and not just typing >>>>>the literal "#N/A". >>>>> >>>>>Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a XY >>>>>Scatter chart but rather as a 'interpolate across the #N/A.' >>>>> >>>>>-- >>>>>Regards, >>>>> >>>>>Tushar Mehta >>>>>www.tushar-mehta.com >>>>>Excel, PowerPoint, and VBA add-ins, tutorials >>>>>Custom MS Office productivity solutions >>>>> >>>>>In article <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>, >>>>>monir@discussions.microsoft.com says... >>>>> >>>>> >>>>>>Hello; >>>>>> >>>>>>When some cells of the data series have #N/A values returned by >>>>>>formulas, >>>>>>the corresponding chart fails. >>>>>>But if the #N/A values are manually entered into those same cells, the >>>>>>chart >>>>>>works fine !! >>>>>>To my understanding, Excel Charts treat cells with #N/A values as empty >>>>>>cells, so one may select the relevant chart option to "leave gaps", >>>>>>which is >>>>>>perfect. >>>>>> >>>>>>With this apparent different interpretation (by Excel Chart) of the >>>>>>same >>>>>>#N/A values in the data series, how can I make the #N/A values returned >>>>>>by >>>>>>formulas acceptable by the chart ?? >>>>>> >>>>>>Thank you for your help. >>>>>> >>>>> >
|
|
#8
07-06-2005, 09:05 PM
|
|||
|
|||
|
Re: #N/A Values : Returned by Formulas vs Entered Manually
Hi Jon, thanks so much for your solution to my second formula. It stops the
chart lines exactly as requested. There is one problem it creates which is not too important and that is a calculation to determine the highest and lowest number in the above column which has many scenarios using essentially the same formula except for the column designation. I could solve this by puting my old formula into a second column for each section but this would make the file much larger. If you have a better way of solving this it would be much appreciated. Thanks again, Roger "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message news:usqXnaYgFHA.3316@TK2MSFTNGP14.phx.gbl... >A zero or blank in H86 gives the same answer in this formula: > > =IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7) > > but if the blank is not a blank after all but another formula returning > "", you can use this: > > =IF(OR(H86=0,LEN(H86)=0),NA(),H86*SUM(J$7:J86)/J$7) > > Note the use of NA() instead of #N/A. It probably doesn't matter in this > case, but it's sharper to use NA(). > > In the second formula, I'd replace both "" with NA(). > > - Jon > ------- > Jon Peltier, Microsoft Excel MVP > Peltier Technical Services > Tutorials and Custom Solutions > http://PeltierTech.com/ > _______ > > Roger wrote: >> Hi Jon, good to hear from you and thanks for your reply. I actually >> looked at your website prior to sending the previous request. I appear >> to have sent the wrong information for my request so here are the actual >> formulae and details. This first formula gives different results >> depending on whether there is a number or a blank in H column >> =IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7). I would prefer not to adjust this >> if it is possible as it affects several other columns as well. >> >> >> >> The problem column formula is >> >> =IF(ISERROR(BN86),"",IF(ROW() > Selection!$D$8,"",(BN86-BN$7)/BN$7)) >> >> This is the column where I want the chart line stopped if there is no >> numerical value from the previous formula. >> >> >> >> Roger >> >> >> >> "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message >> news:%23a88hsDgFHA.1284@TK2MSFTNGP14.phx.gbl... >> >>>Hi Roger - >>> >>>To return #N/A in a formula, use NA() in the formula: >>> >>> =IF(AC90=0,NA(),AC90*2) >>> >>>- Jon >>>------- >>>Jon Peltier, Microsoft Excel MVP >>>Peltier Technical Services >>>Tutorials and Custom Solutions >>>http://PeltierTech.com/ >>>_______ >>> >>> >>>Roger wrote: >>> >>> >>>>I too wish to stop a chart line. I am using the formula >>>>=IF(AC90=0,#N/A,AC90*2) in one column. The chart line comes from a >>>>second column of figures =IF(AC90=#N/A,"",1). This gives me #N/A and >>>>doesn't stop the chart line. Any suggestions would be greatly >>>>appreciated. >>>> >>>>Roger >>>> >>>> >>>> >>>>"monir" <monir@discussions.microsoft.com> wrote in message >>>>news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com... >>>> >>>> >>>>>Tushar; >>>>> >>>>>Apsolutely correct ! By having the formula returning the result of the >>>>>function NA() instead of returning the string "#N/A", the line chart >>>>>problem >>>>>disappeared ! >>>>> >>>>>Thank you once again for your help. >>>>> >>>>> >>>>> >>>>>"Tushar Mehta" wrote: >>>>> >>>>> >>>>> >>>>>>The #N/A should be the result of the function NA() and not just typing >>>>>>the literal "#N/A". >>>>>> >>>>>>Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a >>>>>>XY >>>>>>Scatter chart but rather as a 'interpolate across the #N/A.' >>>>>> >>>>>>-- >>>>>>Regards, >>>>>> >>>>>>Tushar Mehta >>>>>>www.tushar-mehta.com >>>>>>Excel, PowerPoint, and VBA add-ins, tutorials >>>>>>Custom MS Office productivity solutions >>>>>> >>>>>>In article <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>, >>>>>>monir@discussions.microsoft.com says... >>>>>> >>>>>> >>>>>>>Hello; >>>>>>> >>>>>>>When some cells of the data series have #N/A values returned by >>>>>>>formulas, >>>>>>>the corresponding chart fails. >>>>>>>But if the #N/A values are manually entered into those same cells, >>>>>>>the chart >>>>>>>works fine !! >>>>>>>To my understanding, Excel Charts treat cells with #N/A values as >>>>>>>empty >>>>>>>cells, so one may select the relevant chart option to "leave gaps", >>>>>>>which is >>>>>>>perfect. >>>>>>> >>>>>>>With this apparent different interpretation (by Excel Chart) of the >>>>>>>same >>>>>>>#N/A values in the data series, how can I make the #N/A values >>>>>>>returned by >>>>>>>formulas acceptable by the chart ?? >>>>>>> >>>>>>>Thank you for your help. >>>>>>> >>>>>> >>
|
|
#9
07-06-2005, 10:05 PM
|
|||
|
|||
|
Re: #N/A Values : Returned by Formulas vs Entered Manually
Roger -
You mean the formula craps out because of the error? Try this. If I have 20 values, including some errors, this fails: =MAX(A1:A20) But I can write a formula that effectively ignores the errors: {=MAX(IF(NOT(ISERROR(A1:A20)),A1:A20,-1E+300))} This is an array formula, so don't type the {curly braces}. Hold down CTRL-SHIFT while pressing Enter, and if it's done properly, Excel drawns them for you. Basically the formula takes the maximum of either the value, if there's no error, or -1E300, which is a very negative number. Your MAX is not likely to be anywhere near this number. A matching array formula for minimum: {=MIN(IF(NOT(ISERROR(A1:A20)),A1:A20,1E+300))} - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Roger wrote: > Hi Jon, thanks so much for your solution to my second formula. It stops the > chart lines exactly as requested. There is one problem it creates which is > not too important and that is a calculation to determine the highest and > lowest number in the above column which has many scenarios using essentially > the same formula except for the column designation. I could solve this by > puting my old formula into a second column for each section but this would > make the file much larger. If you have a better way of solving this it > would be much appreciated. > > Thanks again, > > Roger > > > > "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message > news:usqXnaYgFHA.3316@TK2MSFTNGP14.phx.gbl... > >>A zero or blank in H86 gives the same answer in this formula: >> >> =IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7) >> >>but if the blank is not a blank after all but another formula returning >>"", you can use this: >> >> =IF(OR(H86=0,LEN(H86)=0),NA(),H86*SUM(J$7:J86)/J$7) >> >>Note the use of NA() instead of #N/A. It probably doesn't matter in this >>case, but it's sharper to use NA(). >> >>In the second formula, I'd replace both "" with NA(). >> >>- Jon >>------- >>Jon Peltier, Microsoft Excel MVP >>Peltier Technical Services >>Tutorials and Custom Solutions >>http://PeltierTech.com/ >>_______ >> >>Roger wrote: >> >>>Hi Jon, good to hear from you and thanks for your reply. I actually >>>looked at your website prior to sending the previous request. I appear >>>to have sent the wrong information for my request so here are the actual >>>formulae and details. This first formula gives different results >>>depending on whether there is a number or a blank in H column >>>=IF(H86=0,#N/A,H86*SUM(J$7:J86)/J$7). I would prefer not to adjust this >>>if it is possible as it affects several other columns as well. >>> >>> >>> >>>The problem column formula is >>> >>>=IF(ISERROR(BN86),"",IF(ROW() > Selection!$D$8,"",(BN86-BN$7)/BN$7)) >>> >>>This is the column where I want the chart line stopped if there is no >>>numerical value from the previous formula. >>> >>> >>> >>>Roger >>> >>> >>> >>>"Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message >>>news:%23a88hsDgFHA.1284@TK2MSFTNGP14.phx.gbl... >>> >>> >>>>Hi Roger - >>>> >>>>To return #N/A in a formula, use NA() in the formula: >>>> >>>> =IF(AC90=0,NA(),AC90*2) >>>> >>>>- Jon >>>>------- >>>>Jon Peltier, Microsoft Excel MVP >>>>Peltier Technical Services >>>>Tutorials and Custom Solutions >>>>http://PeltierTech.com/ >>>>_______ >>>> >>>> >>>>Roger wrote: >>>> >>>> >>>> >>>>>I too wish to stop a chart line. I am using the formula >>>>>=IF(AC90=0,#N/A,AC90*2) in one column. The chart line comes from a >>>>>second column of figures =IF(AC90=#N/A,"",1). This gives me #N/A and >>>>>doesn't stop the chart line. Any suggestions would be greatly >>>>>appreciated. >>>>> >>>>>Roger >>>>> >>>>> >>>>> >>>>>"monir" <monir@discussions.microsoft.com> wrote in message >>>>>news:8252DE7F-65B3-4D3E-8074-3045D15899CD@microsoft.com... >>>>> >>>>> >>>>> >>>>>>Tushar; >>>>>> >>>>>>Apsolutely correct ! By having the formula returning the result of the >>>>>>function NA() instead of returning the string "#N/A", the line chart >>>>>>problem >>>>>>disappeared ! >>>>>> >>>>>>Thank you once again for your help. >>>>>> >>>>>> >>>>>> >>>>>>"Tushar Mehta" wrote: >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>>The #N/A should be the result of the function NA() and not just typing >>>>>>>the literal "#N/A". >>>>>>> >>>>>>>Also, a #N/A is *not* treated as 'leave a gap' for a line chart or a >>>>>>>XY >>>>>>>Scatter chart but rather as a 'interpolate across the #N/A.' >>>>>>> >>>>>>>-- >>>>>>>Regards, >>>>>>> >>>>>>>Tushar Mehta >>>>>>>www.tushar-mehta.com >>>>>>>Excel, PowerPoint, and VBA add-ins, tutorials >>>>>>>Custom MS Office productivity solutions >>>>>>> >>>>>>>In article <FB2D921C-14A0-42B2-84DD-EBAF458A2EDE@microsoft.com>, >>>>>>>monir@discussions.microsoft.com says... >>>>>>> >>>>>>> >>>>>>> >>>>>>>>Hello; >>>>>>>> >>>>>>>>When some cells of the data series have #N/A values returned by >>>>>>>>formulas, >>>>>>>>the corresponding chart fails. >>>>>>>>But if the #N/A values are manually entered into those same cells, >>>>>>>>the chart >>>>>>>>works fine !! >>>>>>>>To my understanding, Excel Charts treat cells with #N/A values as >>>>>>>>empty >>>>>>>>cells, so one may select the relevant chart option to "leave gaps", >>>>>>>>which is >>>>>>>>perfect. >>>>>>>> >>>>>>>>With this apparent different interpretation (by Excel Chart) of the >>>>>>>>same >>>>>>>>#N/A values in the data series, how can I make the #N/A values >>>>>>>>returned by >>>>>>>>formulas acceptable by the chart ?? >>>>>>>> >>>>>>>>Thank you for your help. >>>>>>>> >>>>>>> > >
|
|
| Bookmarks |
New topics in Excel Charting
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
