Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel cahrt do not want plotting of empty cells
Hi,
I have a chart I am trying to create, however, i not want to plot the empty ranges. I have used the NA() option in my formulas so that it does not get plotetd on my graphs but it is not working. How can I fix this error? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel cahrt do not want plotting of empty cells
Hi,
What exactly does 'not working' mean? What type of chart? The use of NA() in a line chart will only suppress the displaying of a data marker. The line will be interpolated between real data points. http://www.andypope.info/charts/brokenlines.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info wrote in message ... Hi, I have a chart I am trying to create, however, i not want to plot the empty ranges. I have used the NA() option in my formulas so that it does not get plotetd on my graphs but it is not working. How can I fix this error? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel cahrt do not want plotting of empty cells
Hi Andy,
I have sometimes felt that this should be labelled as a bug in Excel. Column charts correctly leave gaps, but line and scatter charts do not. As you say, only the markers are not plotted, but the lines are still there. A possible workaround (which works only in Excel 2007) is to use a 3d line chart with angles and perspective set to zero. This correctly leaves a gap in the middle, and looks pretty close to a 2d line chart. Ed Ferrero www.edferrero.com Hi, What exactly does 'not working' mean? What type of chart? The use of NA() in a line chart will only suppress the displaying of a data marker. The line will be interpolated between real data points. http://www.andypope.info/charts/brokenlines.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel cahrt do not want plotting of empty cells
On Jul 11, 8:44*pm, "Ed Ferrero" wrote:
Hi Andy, I have sometimes felt that this should be labelled as a bug in Excel. Column charts correctly leave gaps, but line and scatter charts do not. As you say, only the markers are not plotted, but the lines are still there. A possible workaround (which works only in Excel 2007) is to use a 3d line chart with angles and perspective set to zero. This correctly leaves a gap in the middle, and looks pretty close to a 2d line chart. Ed Ferrerowww.edferrero.com Hi, What exactly does 'not working' mean? What type of chart? The use of NA() in a line chart will only suppress the displaying of a data marker. The line will be interpolated between real data points. http://www.andypope.info/charts/brokenlines.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel- Hide quoted text - - Show quoted text - Hi Andy, I am trying to create a column graph. I have made my formula to return NA() rather then a "" value if the result is 0. However, even though I am using NA() which supposedly shoul not plot on a graph the data range is still plotting as 0. Would you be able to help me with this, as I am trying to create a dynamic set of charts and it is difficult to always manually select the correct data ranges. Thanks for your help!! |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel cahrt do not want plotting of empty cells
Hi,
Sounds like you want the data point removed completely if it is NA. As I said the NA only suppresses the data marker. Easiest way to remove the data point from the chart is to hide the row. You can use auto filter to do this. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info wrote in message ... On Jul 11, 8:44 pm, "Ed Ferrero" wrote: Hi Andy, I have sometimes felt that this should be labelled as a bug in Excel. Column charts correctly leave gaps, but line and scatter charts do not. As you say, only the markers are not plotted, but the lines are still there. A possible workaround (which works only in Excel 2007) is to use a 3d line chart with angles and perspective set to zero. This correctly leaves a gap in the middle, and looks pretty close to a 2d line chart. Ed Ferrerowww.edferrero.com Hi, What exactly does 'not working' mean? What type of chart? The use of NA() in a line chart will only suppress the displaying of a data marker. The line will be interpolated between real data points. http://www.andypope.info/charts/brokenlines.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel- Hide quoted text - - Show quoted text - Hi Andy, I am trying to create a column graph. I have made my formula to return NA() rather then a "" value if the result is 0. However, even though I am using NA() which supposedly shoul not plot on a graph the data range is still plotting as 0. Would you be able to help me with this, as I am trying to create a dynamic set of charts and it is difficult to always manually select the correct data ranges. Thanks for your help!! |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel cahrt do not want plotting of empty cells
On Jul 14, 5:34*pm, "Andy Pope" wrote:
Hi, Sounds like you want the data point removed completely if it is NA. As I said the NA only suppresses the data marker. Easiest way to remove the data point from the chart is to hide the row. You can use auto filter to do this. Cheers Andy -- Andy Pope, Microsoft MVP - wrote in message ... On Jul 11, 8:44 pm, "Ed Ferrero" wrote: Hi Andy, I have sometimes felt that this should be labelled as a bug in Excel. Column charts correctly leave gaps, but line and scatter charts do not. As you say, only the markers are not plotted, but the lines are still there. A possible workaround (which works only in Excel 2007) is to use a 3d line chart with angles and perspective set to zero. This correctly leaves a gap in the middle, and looks pretty close to a 2d line chart. Ed Ferrerowww.edferrero.com Hi, What exactly does 'not working' mean? What type of chart? The use of NA() in a line chart will only suppress the displaying of a data marker. The line will be interpolated between real data points. http://www.andypope.info/charts/brokenlines.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel- Hide quoted text - - Show quoted text - Hi Andy, I am trying to create a column graph. I have made my formula to return NA() rather then a "" value if the result is 0. However, even though I am using NA() which supposedly shoul not plot on a graph the data range is still plotting as 0. Would you be able to help me with this, as I am trying to create a dynamic set of charts and it is difficult to always manually select the correct data ranges. Thanks for your help!!- Hide quoted text - - Show quoted text - Hi Andy, Thanks for your reply. Is there any other way of doing this? With the way the table is set up, is that the data point might be NA in some columns but is a value in another. For example: Class 1 Class 2 Class 3 John 2 1 NA Michelle 3 NA 6 Lisa NA 6 5 I have 3 charts, one for Class 1 one for Class 2 and one for Class 3. For example for class 1 John would be plotted with 2, and Michelle with 3 and I would not want a data point at all for Lisa. I can't exactly hide Lisa's row as she is participating in Class 2 and Class 3 that need to be plotted on their respective graphs. It would be difficult to separate this table to separate tables as in reality this table is huge and it would not be practical to have 3 tables rather then 1. Do you know another solution to this as hiding rows will not work?? Thanks again for all of your help!! |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Excel cahrt do not want plotting of empty cells
You would need to re-create the tables using formula such that the NA entries where excluded or outputted at the end. Then use dynamic names for the data source of each chart. I do something similar, although only for 1 chart in this example. http://www.andypope.info/charts/piezeros.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info wrote in message ... On Jul 14, 5:34 pm, "Andy Pope" wrote: Hi, Sounds like you want the data point removed completely if it is NA. As I said the NA only suppresses the data marker. Easiest way to remove the data point from the chart is to hide the row. You can use auto filter to do this. Cheers Andy -- Andy Pope, Microsoft MVP - wrote in message ... On Jul 11, 8:44 pm, "Ed Ferrero" wrote: Hi Andy, I have sometimes felt that this should be labelled as a bug in Excel. Column charts correctly leave gaps, but line and scatter charts do not. As you say, only the markers are not plotted, but the lines are still there. A possible workaround (which works only in Excel 2007) is to use a 3d line chart with angles and perspective set to zero. This correctly leaves a gap in the middle, and looks pretty close to a 2d line chart. Ed Ferrerowww.edferrero.com Hi, What exactly does 'not working' mean? What type of chart? The use of NA() in a line chart will only suppress the displaying of a data marker. The line will be interpolated between real data points. http://www.andypope.info/charts/brokenlines.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel- Hide quoted text - - Show quoted text - Hi Andy, I am trying to create a column graph. I have made my formula to return NA() rather then a "" value if the result is 0. However, even though I am using NA() which supposedly shoul not plot on a graph the data range is still plotting as 0. Would you be able to help me with this, as I am trying to create a dynamic set of charts and it is difficult to always manually select the correct data ranges. Thanks for your help!!- Hide quoted text - - Show quoted text - Hi Andy, Thanks for your reply. Is there any other way of doing this? With the way the table is set up, is that the data point might be NA in some columns but is a value in another. For example: Class 1 Class 2 Class 3 John 2 1 NA Michelle 3 NA 6 Lisa NA 6 5 I have 3 charts, one for Class 1 one for Class 2 and one for Class 3. For example for class 1 John would be plotted with 2, and Michelle with 3 and I would not want a data point at all for Lisa. I can't exactly hide Lisa's row as she is participating in Class 2 and Class 3 that need to be plotted on their respective graphs. It would be difficult to separate this table to separate tables as in reality this table is huge and it would not be practical to have 3 tables rather then 1. Do you know another solution to this as hiding rows will not work?? Thanks again for all of your help!! |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Plot only data desired
One way to achieve this result is to use filters.
After you have the table created as you have shown, and you have the charts created that use it, turn on filters. Filter, for example, the Class 1 column to exclude $N/A. The chart will then not include Lisa. Clear filters and filter Class 2 and the chart will not include Michelle. You could use a simple macro to set and clear the filters for the number of columns and copy/paste the resulting charts to PPT if that is what you need. roniael wrote: Excel cahrt do not want plotting of empty cells 17-Jul-08 On Jul 14, 5:34=A0pm, "Andy Pope" wrote: ou ..com wrote in message ine gap a Hi Andy, Thanks for your reply. Is there any other way of doing this? With the way the table is set up, is that the data point might be NA in some columns but is a value in another. For example: Class 1 Class 2 Class 3 John 2 1 NA Michelle 3 NA 6 Lisa NA 6 5 I have 3 charts, one for Class 1 one for Class 2 and one for Class 3. For example for class 1 John would be plotted with 2, and Michelle with 3 and I would not want a data point at all for Lisa. I can't exactly hide Lisa's row as she is participating in Class 2 and Class 3 that need to be plotted on their respective graphs. It would be difficult to separate this table to separate tables as in reality this table is huge and it would not be practical to have 3 tables rather then 1. Do you know another solution to this as hiding rows will not work?? Thanks again for all of your help!! Previous Posts In This Thread: On Friday, July 11, 2008 3:37 AM Andy Pope wrote: Hi,What exactly does 'not working' mean? What type of chart? Hi, What exactly does 'not working' mean? What type of chart? The use of NA() in a line chart will only suppress the displaying of a data marker. The line will be interpolated between real data points. http://www.andypope.info/charts/brokenlines.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info wrote in message ... On Friday, July 11, 2008 6:44 AM Ed Ferrero wrote: Hi Andy,I have sometimes felt that this should be labelled as a bug in Excel. Hi Andy, I have sometimes felt that this should be labelled as a bug in Excel. Column charts correctly leave gaps, but line and scatter charts do not. As you say, only the markers are not plotted, but the lines are still there. A possible workaround (which works only in Excel 2007) is to use a 3d line chart with angles and perspective set to zero. This correctly leaves a gap in the middle, and looks pretty close to a 2d line chart. Ed Ferrero www.edferrero.com On Monday, July 14, 2008 3:34 AM Andy Pope wrote: Hi,Sounds like you want the data point removed completely if it is NA. Hi, Sounds like you want the data point removed completely if it is NA. As I said the NA only suppresses the data marker. Easiest way to remove the data point from the chart is to hide the row. You can use auto filter to do this. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info wrote in message ... On Jul 11, 8:44 pm, "Ed Ferrero" wrote: Hi Andy, I am trying to create a column graph. I have made my formula to return NA() rather then a "" value if the result is 0. However, even though I am using NA() which supposedly shoul not plot on a graph the data range is still plotting as 0. Would you be able to help me with this, as I am trying to create a dynamic set of charts and it is difficult to always manually select the correct data ranges. Thanks for your help!! On Monday, July 14, 2008 5:28 AM roniael wrote: Excel cahrt do not want plotting of empty cells Hi, I have a chart I am trying to create, however, i not want to plot the empty ranges. I have used the NA() option in my formulas so that it does not get plotetd on my graphs but it is not working. How can I fix this error? On Monday, July 14, 2008 5:28 AM roniael wrote: Excel cahrt do not want plotting of empty cells On Jul 11, 8:44=A0pm, "Ed Ferrero" wrote: umn ay, e p Hi Andy, I am trying to create a column graph. I have made my formula to return NA() rather then a "" value if the result is 0. However, even though I am using NA() which supposedly shoul not plot on a graph the data range is still plotting as 0. Would you be able to help me with this, as I am trying to create a dynamic set of charts and it is difficult to always manually select the correct data ranges. Thanks for your help!! On Thursday, July 17, 2008 7:22 AM Andy Pope wrote: You would need to re-create the tables using formula such that the NAentries You would need to re-create the tables using formula such that the NA entries where excluded or outputted at the end. Then use dynamic names for the data source of each chart. I do something similar, although only for 1 chart in this example. http://www.andypope.info/charts/piezeros.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info wrote in message ... On Jul 14, 5:34 pm, "Andy Pope" wrote: Hi Andy, Thanks for your reply. Is there any other way of doing this? With the way the table is set up, is that the data point might be NA in some columns but is a value in another. For example: Class 1 Class 2 Class 3 John 2 1 NA Michelle 3 NA 6 Lisa NA 6 5 I have 3 charts, one for Class 1 one for Class 2 and one for Class 3. For example for class 1 John would be plotted with 2, and Michelle with 3 and I would not want a data point at all for Lisa. I can't exactly hide Lisa's row as she is participating in Class 2 and Class 3 that need to be plotted on their respective graphs. It would be difficult to separate this table to separate tables as in reality this table is huge and it would not be practical to have 3 tables rather then 1. Do you know another solution to this as hiding rows will not work?? Thanks again for all of your help!! On Thursday, July 17, 2008 10:35 PM roniael wrote: Excel cahrt do not want plotting of empty cells On Jul 14, 5:34=A0pm, "Andy Pope" wrote: ou ..com wrote in message ine gap a Hi Andy, Thanks for your reply. Is there any other way of doing this? With the way the table is set up, is that the data point might be NA in some columns but is a value in another. For example: Class 1 Class 2 Class 3 John 2 1 NA Michelle 3 NA 6 Lisa NA 6 5 I have 3 charts, one for Class 1 one for Class 2 and one for Class 3. For example for class 1 John would be plotted with 2, and Michelle with 3 and I would not want a data point at all for Lisa. I can't exactly hide Lisa's row as she is participating in Class 2 and Class 3 that need to be plotted on their respective graphs. It would be difficult to separate this table to separate tables as in reality this table is huge and it would not be practical to have 3 tables rather then 1. Do you know another solution to this as hiding rows will not work?? Thanks again for all of your help!! EggHeadCafe - Software Developer Portal of Choice Migration 2003-2007 Project Server details http://www.eggheadcafe.com/tutorials...007-proje.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste cahrt to new sheet-update data automatically? | Charts and Charting in Excel | |||
Maximum no of cahrt sheets? | Charts and Charting in Excel | |||
Can I avoid plotting 'non-empty' cells | Charts and Charting in Excel | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) |