Stopping a chart when the data runs out
Using Excel 2007, I have a chart set up to display based on a table.
However, the table will have a variable number of entries. The way I have the data defined now includes the whole table which has a maximum of 25 entries. So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. If there are only 20 data points, then only plot 20 points. But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim |
Stopping a chart when the data runs out
You could do it with a dynamic named range. The trick is using the proper
syntax when inputting it into the chart wizard. An example of a chart using dynamic range: http://peltiertech.com/Excel/Charts/...umnChart1.html Another idea, but not sure if it will work in your situation, would be to try and get the cells that you don't want to chart to return NA() instead of "". XL charts will ignore #NA values typically. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tim" wrote: Using Excel 2007, I have a chart set up to display based on a table. However, the table will have a variable number of entries. The way I have the data defined now includes the whole table which has a maximum of 25 entries. So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. If there are only 20 data points, then only plot 20 points. But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim |
Stopping a chart when the data runs out
Tim -
Default to NA() =If(B2<0,B2,NA() which places #N/A into the cells. This looks ugly but is not plotted in a line or XY chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Tim" wrote in message ... Using Excel 2007, I have a chart set up to display based on a table. However, the table will have a variable number of entries. The way I have the data defined now includes the whole table which has a maximum of 25 entries. So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. If there are only 20 data points, then only plot 20 points. But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim |
Stopping a chart when the data runs out
On Mar 20, 4:18*pm, Luke M wrote:
You could do it with a dynamic named range. The trick is using the proper syntax when inputting it into the chart wizard. An example of a chart using dynamic range:http://peltiertech.com/Excel/Charts/...umnChart1.html Another idea, but not sure if it will work in your situation, would be to try and get the cells that you don't want to chart to return NA() instead of "". XL charts will ignore #NA values typically. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tim" wrote: Using Excel 2007, I have a chart set up to display based on a table. However, the table will have a variable number of entries. *The way I have the data defined now includes the whole table which has a maximum of 25 entries. *So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. *What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. *If there are only 20 data points, then only plot 20 points. *But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim- Hide quoted text - - Show quoted text - Luke, Thanks for the tip. I had been trying to create a dynamic chart, but was having trouble using the name I had defined with the chart wizard. But I had not tried to change the series in the formula bar as the article directs. This should work! My only other problem is that my data is in rows, not columns. COUNTA will still work, but there doesn't seem to be a dynamic way to refer to row data as you can with column data without specifying a definite end. That was why I limited myself to 25 entries. Tim |
Stopping a chart when the data runs out
Thanks, Jon. This worked, also, but as you said, the data looked
pretty ugly. But since no one else but me will see the data, it really doesn't matter. Thanks for the tip. Tim On Mar 20, 5:04*pm, "Jon Peltier" wrote: Tim - Default to NA() =If(B2<0,B2,NA() which places #N/A into the cells. This looks ugly but is not plotted in a line or XY chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ "Tim" wrote in message ... Using Excel 2007, I have a chart set up to display based on a table. However, the table will have a variable number of entries. *The way I have the data defined now includes the whole table which has a maximum of 25 entries. *So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. *What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. *If there are only 20 data points, then only plot 20 points. *But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim- Hide quoted text - - Show quoted text - |
Stopping a chart when the data runs out
If you were worried about it looking ugly, you could use conditional
formatting to turn the font white (or your background colour) if the cell value is NA(). -- David Biddulph "Tim" wrote in message ... Thanks, Jon. This worked, also, but as you said, the data looked pretty ugly. But since no one else but me will see the data, it really doesn't matter. Thanks for the tip. Tim On Mar 20, 5:04 pm, "Jon Peltier" wrote: Tim - Default to NA() =If(B2<0,B2,NA() which places #N/A into the cells. This looks ugly but is not plotted in a line or XY chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ "Tim" wrote in message ... Using Excel 2007, I have a chart set up to display based on a table. However, the table will have a variable number of entries. The way I have the data defined now includes the whole table which has a maximum of 25 entries. So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. If there are only 20 data points, then only plot 20 points. But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim- Hide quoted text - - Show quoted text - |
Stopping a chart when the data runs out
You can do dynamic ranges in rows as easily as in columns. Scroll down to
"Series in Rows" in this article: http://peltiertech.com/WordPress/dynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Tim" wrote in message ... On Mar 20, 4:18 pm, Luke M wrote: You could do it with a dynamic named range. The trick is using the proper syntax when inputting it into the chart wizard. An example of a chart using dynamic range:http://peltiertech.com/Excel/Charts/...umnChart1.html Another idea, but not sure if it will work in your situation, would be to try and get the cells that you don't want to chart to return NA() instead of "". XL charts will ignore #NA values typically. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tim" wrote: Using Excel 2007, I have a chart set up to display based on a table. However, the table will have a variable number of entries. The way I have the data defined now includes the whole table which has a maximum of 25 entries. So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. If there are only 20 data points, then only plot 20 points. But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim- Hide quoted text - - Show quoted text - Luke, Thanks for the tip. I had been trying to create a dynamic chart, but was having trouble using the name I had defined with the chart wizard. But I had not tried to change the series in the formula bar as the article directs. This should work! My only other problem is that my data is in rows, not columns. COUNTA will still work, but there doesn't seem to be a dynamic way to refer to row data as you can with column data without specifying a definite end. That was why I limited myself to 25 entries. Tim |
Stopping a chart when the data runs out
Or you could have two sets of formulas. One with ugly #N/A for the chart
source, and one with pretty "" for people to read. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... If you were worried about it looking ugly, you could use conditional formatting to turn the font white (or your background colour) if the cell value is NA(). -- David Biddulph "Tim" wrote in message ... Thanks, Jon. This worked, also, but as you said, the data looked pretty ugly. But since no one else but me will see the data, it really doesn't matter. Thanks for the tip. Tim On Mar 20, 5:04 pm, "Jon Peltier" wrote: Tim - Default to NA() =If(B2<0,B2,NA() which places #N/A into the cells. This looks ugly but is not plotted in a line or XY chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ "Tim" wrote in message ... Using Excel 2007, I have a chart set up to display based on a table. However, the table will have a variable number of entries. The way I have the data defined now includes the whole table which has a maximum of 25 entries. So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. If there are only 20 data points, then only plot 20 points. But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim- Hide quoted text - - Show quoted text - |
Stopping a chart when the data runs out
Excellent! That's exactly what I need to do!
On Mar 23, 6:22*pm, "Jon Peltier" wrote: You can do dynamic ranges in rows as easily as in columns. Scroll down to "Series in Rows" in this article: http://peltiertech.com/WordPress/dynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ "Tim" wrote in message ... On Mar 20, 4:18 pm, Luke M wrote: You could do it with a dynamic named range. The trick is using the proper syntax when inputting it into the chart wizard. An example of a chart using dynamic range:http://peltiertech.com/Excel/Charts/...umnChart1.html Another idea, but not sure if it will work in your situation, would be to try and get the cells that you don't want to chart to return NA() instead of "". XL charts will ignore #NA values typically. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tim" wrote: Using Excel 2007, I have a chart set up to display based on a table. However, the table will have a variable number of entries. The way I have the data defined now includes the whole table which has a maximum of 25 entries. So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. If there are only 20 data points, then only plot 20 points. But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim- Hide quoted text - - Show quoted text - Luke, Thanks for the tip. *I had been trying to create a dynamic chart, but was having trouble using the name I had defined with the chart wizard. *But I had not tried to change the series in the formula bar as the article directs. *This should work! My only other problem is that my data is in rows, not columns. *COUNTA will still work, but there doesn't seem to be a dynamic way to refer to row data as you can with column data without specifying a definite end. *That was why I limited myself to 25 entries. Tim- Hide quoted text - - Show quoted text - |
Stopping a chart when the data runs out
Another happy customer.
- Jon "Tim" wrote in message ... Excellent! That's exactly what I need to do! On Mar 23, 6:22 pm, "Jon Peltier" wrote: You can do dynamic ranges in rows as easily as in columns. Scroll down to "Series in Rows" in this article: http://peltiertech.com/WordPress/dynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ "Tim" wrote in message ... On Mar 20, 4:18 pm, Luke M wrote: You could do it with a dynamic named range. The trick is using the proper syntax when inputting it into the chart wizard. An example of a chart using dynamic range:http://peltiertech.com/Excel/Charts/...umnChart1.html Another idea, but not sure if it will work in your situation, would be to try and get the cells that you don't want to chart to return NA() instead of "". XL charts will ignore #NA values typically. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tim" wrote: Using Excel 2007, I have a chart set up to display based on a table. However, the table will have a variable number of entries. The way I have the data defined now includes the whole table which has a maximum of 25 entries. So if there are only 20 entries, the last 5 default to "", or for practical purposes, 0. If the rest of the table values are in the 70's, and the chart is a control chart, that makes the chart "taller" than it needs to be, and hard to read the part of the chart that contains the real data. What I would like to do is define the data to be plotted dynamically so that the chart ends when the data ends. If there are only 20 data points, then only plot 20 points. But if I add a 21st data point, or subtract one, then the chart would automatically adjust to either include or exclude one more. I've tried defining a name that covers the range, but for the chart wizard doesn't like that. Any help is appreciated. Tim- Hide quoted text - - Show quoted text - Luke, Thanks for the tip. I had been trying to create a dynamic chart, but was having trouble using the name I had defined with the chart wizard. But I had not tried to change the series in the formula bar as the article directs. This should work! My only other problem is that my data is in rows, not columns. COUNTA will still work, but there doesn't seem to be a dynamic way to refer to row data as you can with column data without specifying a definite end. That was why I limited myself to 25 entries. Tim- Hide quoted text - - Show quoted text - |
All times are GMT +1. The time now is 08:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com