Home |
Search |
Today's Posts |
#1
|
|||
|
|||
#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
|
|||
|
|||
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 , 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
|
|||
|
|||
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" wrote in message ... 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 , 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
|
|||
|
|||
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" wrote in message ... 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 , 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
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 , 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
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 , 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
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 , 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
|
|||
|
|||
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" wrote in message ... 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" wrote in message . .. 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" wrote in message ... 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 , 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sum rounded values from two seperate formulas in Excel? | Excel Worksheet Functions | |||
display values of formulas in Excel | Excel Worksheet Functions | |||
Using SumIF formulas with multiple lookup values | Excel Worksheet Functions | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) |