Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Blanks chart as zeros
Hi All......
I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#2
|
|||
|
|||
In your formula, instead of "", put NA().
"CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#3
|
|||
|
|||
Thanks for the quick reply Barb, but that don't do it for me........all that
does is put a #N/A in my data column (which comfounds my other formulas) and then the chart just connects the dots of the cells before and after it, giving a continuous line..I want a break in the line like you get with a "real" empty cell. I forgot to mention I'm using XL97, is 2000 any easier in this regard? Any other options? Thanks again, Vaya con Dios, Chuck, CABGx3 "Barb R." wrote: In your formula, instead of "", put NA(). "CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#4
|
|||
|
|||
Apparently my reply was too quick as I didn't catch everything that you were
looking for. Could you modify the other formulas to include ISNA in them so that you can deal with NA in this case? "CLR" wrote: Thanks for the quick reply Barb, but that don't do it for me........all that does is put a #N/A in my data column (which comfounds my other formulas) and then the chart just connects the dots of the cells before and after it, giving a continuous line..I want a break in the line like you get with a "real" empty cell. I forgot to mention I'm using XL97, is 2000 any easier in this regard? Any other options? Thanks again, Vaya con Dios, Chuck, CABGx3 "Barb R." wrote: In your formula, instead of "", put NA(). "CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#5
|
|||
|
|||
Yes Ma'am, of course I could do that if indeed the N/A thing actually gave me
the "space" I'm looking for, but it doesn't,(at least on my XL97 setup, maybe it's corrupt?).......the chart just connects the dots from the previous to the next cells like there was no data point there at all and as tho it's value was just halfway between the two.....I want a gap. I've discovered I can use "xxx" to fill in for the "" and then copy paste-special-values and replace "xxx" with "nothing" and eventually I get it to where I want, but such a pain.....I guess I could do a macro to do the whole thing, but is that the only way? Thanks again, Chuck, CABGx3 "Barb R." wrote: Apparently my reply was too quick as I didn't catch everything that you were looking for. Could you modify the other formulas to include ISNA in them so that you can deal with NA in this case? "CLR" wrote: Thanks for the quick reply Barb, but that don't do it for me........all that does is put a #N/A in my data column (which comfounds my other formulas) and then the chart just connects the dots of the cells before and after it, giving a continuous line..I want a break in the line like you get with a "real" empty cell. I forgot to mention I'm using XL97, is 2000 any easier in this regard? Any other options? Thanks again, Vaya con Dios, Chuck, CABGx3 "Barb R." wrote: In your formula, instead of "", put NA(). "CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#6
|
|||
|
|||
CLR,
These three resources should help: From Tushar Mehta: (1) http://www.tushar-mehta.com/excel/so...discontinuity/ (2) http://groups-beta.google.com/group/...cca276cbd4877b From Andy Pope: (3) http://www.andypope.info/charts/brokenlines.htm ---- Regards, John Mansfield http://www.pdbook.com "CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#7
|
|||
|
|||
Thanks for the reply John, but none of those work for me...........I
couldn't get either the first or third ones to work at all and I percieve that I would have to put code in every chart sheet for the second method to work and I have too many to do that.......... I do appreciate you trying tho........thanks again, Vaya con Dios, Chuck, CABGx3 "John Mansfield" wrote in message ... CLR, These three resources should help: From Tushar Mehta: (1) http://www.tushar-mehta.com/excel/so...discontinuity/ (2) http://groups-beta.google.com/group/...rting/msg/31cc a276cbd4877b From Andy Pope: (3) http://www.andypope.info/charts/brokenlines.htm ---- Regards, John Mansfield http://www.pdbook.com "CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#8
|
|||
|
|||
Nothing behaves exactly like a empty cell, except an empty cell. As you
have discovered, text (even "") plots as zeros, #N/A doesn't plot, but it also doesn't break joining lines. If you also don't want to kludge a broken line a la Tushar Mehta and Andy Pope, then your only choice is to delete the formulas so that the cells will be truly empty. If the status of these cells will be changing, then you could write a macro tied to a change event that would delete or replace the formulas as necessary. Jerry CLR wrote: Yes Ma'am, of course I could do that if indeed the N/A thing actually gave me the "space" I'm looking for, but it doesn't,(at least on my XL97 setup, maybe it's corrupt?).......the chart just connects the dots from the previous to the next cells like there was no data point there at all and as tho it's value was just halfway between the two.....I want a gap. I've discovered I can use "xxx" to fill in for the "" and then copy paste-special-values and replace "xxx" with "nothing" and eventually I get it to where I want, but such a pain.....I guess I could do a macro to do the whole thing, but is that the only way? Thanks again, Chuck, CABGx3 "Barb R." wrote: Apparently my reply was too quick as I didn't catch everything that you were looking for. Could you modify the other formulas to include ISNA in them so that you can deal with NA in this case? "CLR" wrote: Thanks for the quick reply Barb, but that don't do it for me........all that does is put a #N/A in my data column (which comfounds my other formulas) and then the chart just connects the dots of the cells before and after it, giving a continuous line..I want a break in the line like you get with a "real" empty cell. I forgot to mention I'm using XL97, is 2000 any easier in this regard? Any other options? Thanks again, Vaya con Dios, Chuck, CABGx3 "Barb R." wrote: In your formula, instead of "", put NA(). "CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#9
|
|||
|
|||
Hi Chuck -
You don't need to put the code into every chart sheet's code module. You could create a class module, define a variable with events to represent the chart, and put the code into the class module. Then use a Worksheet_SheetActivate event to instantiate the newly activated chart sheet as an instance of this class. I recently wrote an article about chart events which might help: http://www.computorcompanion.com/LPMArticle.asp?ID=221 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ CLR wrote: Thanks for the reply John, but none of those work for me...........I couldn't get either the first or third ones to work at all and I percieve that I would have to put code in every chart sheet for the second method to work and I have too many to do that.......... I do appreciate you trying tho........thanks again, Vaya con Dios, Chuck, CABGx3 "John Mansfield" wrote in message ... CLR, These three resources should help: From Tushar Mehta: (1) http://www.tushar-mehta.com/excel/so...discontinuity/ (2) http://groups-beta.google.com/group/...rting/msg/31cc a276cbd4877b From Andy Pope: (3) http://www.andypope.info/charts/brokenlines.htm ---- Regards, John Mansfield http://www.pdbook.com "CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#10
|
|||
|
|||
Thanks Jerry.........you have very nicely collected and expressed my
thoughts and experiences. I talked with Tushar and he said the Add-in does not now work with line charts, (since XL2k), so thats out. The task I have at hand is to generate about 30 charts for Machine Efficiency.....line charts done weekly, above and below Standard efficiency, and I believe they only have to exist long enough to print them out, so a changing of the Data Source should be quite acceptable......I'll just whip up a macro to empty out the cells and print the chart and then delete it so values can't be changed and someone wondering why the chart don't respond.........I've done it on samples and it seems to work just fine. Thanks very much for your response. Vaya con Dios, Chuck, CABGx3 "Jerry W. Lewis" wrote in message ... Nothing behaves exactly like a empty cell, except an empty cell. As you have discovered, text (even "") plots as zeros, #N/A doesn't plot, but it also doesn't break joining lines. If you also don't want to kludge a broken line a la Tushar Mehta and Andy Pope, then your only choice is to delete the formulas so that the cells will be truly empty. If the status of these cells will be changing, then you could write a macro tied to a change event that would delete or replace the formulas as necessary. Jerry CLR wrote: Yes Ma'am, of course I could do that if indeed the N/A thing actually gave me the "space" I'm looking for, but it doesn't,(at least on my XL97 setup, maybe it's corrupt?).......the chart just connects the dots from the previous to the next cells like there was no data point there at all and as tho it's value was just halfway between the two.....I want a gap. I've discovered I can use "xxx" to fill in for the "" and then copy paste-special-values and replace "xxx" with "nothing" and eventually I get it to where I want, but such a pain.....I guess I could do a macro to do the whole thing, but is that the only way? Thanks again, Chuck, CABGx3 "Barb R." wrote: Apparently my reply was too quick as I didn't catch everything that you were looking for. Could you modify the other formulas to include ISNA in them so that you can deal with NA in this case? "CLR" wrote: Thanks for the quick reply Barb, but that don't do it for me........all that does is put a #N/A in my data column (which comfounds my other formulas) and then the chart just connects the dots of the cells before and after it, giving a continuous line..I want a break in the line like you get with a "real" empty cell. I forgot to mention I'm using XL97, is 2000 any easier in this regard? Any other options? Thanks again, Vaya con Dios, Chuck, CABGx3 "Barb R." wrote: In your formula, instead of "", put NA(). "CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
#11
|
|||
|
|||
Thanks Jon but that's a bit over my head. I've no experience with Class
Modules, and need to get this thing working soon, so I will probably go with the "delete the formulas" method Jerry mentioned........I do appreciate your response tho, I go to your page frequently and have learned a LOT there........I think I'm going to be trying a Speedometer Chart soon........I'll also check out the article you mentioned........... Thanks again, Vaya con Dios, Chuck, CABGx3 "Jon Peltier" wrote in message ... Hi Chuck - You don't need to put the code into every chart sheet's code module. You could create a class module, define a variable with events to represent the chart, and put the code into the class module. Then use a Worksheet_SheetActivate event to instantiate the newly activated chart sheet as an instance of this class. I recently wrote an article about chart events which might help: http://www.computorcompanion.com/LPMArticle.asp?ID=221 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ CLR wrote: Thanks for the reply John, but none of those work for me...........I couldn't get either the first or third ones to work at all and I percieve that I would have to put code in every chart sheet for the second method to work and I have too many to do that.......... I do appreciate you trying tho........thanks again, Vaya con Dios, Chuck, CABGx3 "John Mansfield" wrote in message ... CLR, These three resources should help: From Tushar Mehta: (1) http://www.tushar-mehta.com/excel/so...discontinuity/ (2) http://groups-beta.google.com/group/...rting/msg/31cc a276cbd4877b From Andy Pope: (3) http://www.andypope.info/charts/brokenlines.htm ---- Regards, John Mansfield http://www.pdbook.com "CLR" wrote: Hi All...... I have a Line Chart wherein all cells in my source data that are blank, ( as result of a formula resulting in ""), all plot as ZERO, and I would like them to just be nothing, not charted.......I do not want them interpolated and the one before and the one after just joined......I do not want NA in the cells because I use them for other formulas.........I go to Tools Options Chart tab, and no matter which option I select under "Plot empty cells as", I STILL get them plotted as ZERO's...... The only way I've been able to get them to plot correctly is to actually copy a "real" blank cell over to it......is this the only way, please? TIA Vaya con Dios, Chuck, CABGx3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart Help Please | Excel Discussion (Misc queries) | |||
Timeline Chart? | Charts and Charting in Excel | |||
Problem with xlusrgal.xls file | Charts and Charting in Excel | |||
Impedding/Overlaying Charts | Charts and Charting in Excel | |||
pivot table multi line chart | Charts and Charting in Excel |