Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Avoid plotting unwanted values
Hi
I'm using Excel to plot math graphs, specifically numerical integrations. Thanks to an earlier post from Jon Peltier I've found that it's better to use the XY scatter chart rather than a simple line graph for this purpose - although you lose the area fill-in feature, but that's a cosmetic problem! Now can anyone help me to find a way of preventing Excel from plotting unwanted values? The point here is that the table of X and Y value is set up as 10000 row columns, but only part of this is used depending on the limits of the integral and/or the size of the iterations, both of which are adjustable by formula. So part of the X scale is often out of range, or not required. There is an option to leave blank cells which are empty, but since my cells all have a formula in them, even when it's set by formula to zero or "", Excel plots them as unwanted points on my graph! Another way of solving this would be if I could use a formula to set the Data Range, rather than just just fix it as $B$10:$C$10000. The only way I've found to do this so far is to manually change the Data Range each time I change the integral limits. As this needs to often, it's a real drag! Any advice greatly appreciated. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Avoid plotting unwanted values
One option might be to use the combination of the Excel functions OFFSET and
COUNTA within named ranges as chart sources. Doing so will allow your chart to dynamically update. http://pubs.logicalexpressions.com/p...cle.asp?ID=518 Also, for certain conditions you can build "=NA()" into your data source where you don't want that data to appear on your chart. -- John Mansfield cellmatrix.net "Jim" wrote: Hi I'm using Excel to plot math graphs, specifically numerical integrations. Thanks to an earlier post from Jon Peltier I've found that it's better to use the XY scatter chart rather than a simple line graph for this purpose - although you lose the area fill-in feature, but that's a cosmetic problem! Now can anyone help me to find a way of preventing Excel from plotting unwanted values? The point here is that the table of X and Y value is set up as 10000 row columns, but only part of this is used depending on the limits of the integral and/or the size of the iterations, both of which are adjustable by formula. So part of the X scale is often out of range, or not required. There is an option to leave blank cells which are empty, but since my cells all have a formula in them, even when it's set by formula to zero or "", Excel plots them as unwanted points on my graph! Another way of solving this would be if I could use a formula to set the Data Range, rather than just just fix it as $B$10:$C$10000. The only way I've found to do this so far is to manually change the Data Range each time I change the integral limits. As this needs to often, it's a real drag! Any advice greatly appreciated. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Avoid plotting unwanted values
Thanks for that John,
I've tried the N/A() suggestion before, and again now, and it doesn't work for me. My chart still reads the N/A() points as zeros and plots them. I've set the 'Plot empty cells as (Not plotted, leave gaps)' in the Chart Options. Am I missing something? As for your first suggestion, I'm studying your article with much interest. I think it will do what I want and opens up other possibilities. Many thanks! Jim "John Mansfield" wrote: One option might be to use the combination of the Excel functions OFFSET and COUNTA within named ranges as chart sources. Doing so will allow your chart to dynamically update. http://pubs.logicalexpressions.com/p...cle.asp?ID=518 Also, for certain conditions you can build "=NA()" into your data source where you don't want that data to appear on your chart. -- John Mansfield cellmatrix.net "Jim" wrote: Hi I'm using Excel to plot math graphs, specifically numerical integrations. Thanks to an earlier post from Jon Peltier I've found that it's better to use the XY scatter chart rather than a simple line graph for this purpose - although you lose the area fill-in feature, but that's a cosmetic problem! Now can anyone help me to find a way of preventing Excel from plotting unwanted values? The point here is that the table of X and Y value is set up as 10000 row columns, but only part of this is used depending on the limits of the integral and/or the size of the iterations, both of which are adjustable by formula. So part of the X scale is often out of range, or not required. There is an option to leave blank cells which are empty, but since my cells all have a formula in them, even when it's set by formula to zero or "", Excel plots them as unwanted points on my graph! Another way of solving this would be if I could use a formula to set the Data Range, rather than just just fix it as $B$10:$C$10000. The only way I've found to do this so far is to manually change the Data Range each time I change the integral limits. As this needs to often, it's a real drag! Any advice greatly appreciated. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Avoid plotting unwanted values
Ah yes of course, NA() not N/A(), silly of me! This works now and answers my
question. I really appreciate your input.Now I've got an answer which works and an interesting idea to follow up. many thanks again! Jim "John Mansfield" wrote: One option might be to use the combination of the Excel functions OFFSET and COUNTA within named ranges as chart sources. Doing so will allow your chart to dynamically update. http://pubs.logicalexpressions.com/p...cle.asp?ID=518 Also, for certain conditions you can build "=NA()" into your data source where you don't want that data to appear on your chart. -- John Mansfield cellmatrix.net "Jim" wrote: Hi I'm using Excel to plot math graphs, specifically numerical integrations. Thanks to an earlier post from Jon Peltier I've found that it's better to use the XY scatter chart rather than a simple line graph for this purpose - although you lose the area fill-in feature, but that's a cosmetic problem! Now can anyone help me to find a way of preventing Excel from plotting unwanted values? The point here is that the table of X and Y value is set up as 10000 row columns, but only part of this is used depending on the limits of the integral and/or the size of the iterations, both of which are adjustable by formula. So part of the X scale is often out of range, or not required. There is an option to leave blank cells which are empty, but since my cells all have a formula in them, even when it's set by formula to zero or "", Excel plots them as unwanted points on my graph! Another way of solving this would be if I could use a formula to set the Data Range, rather than just just fix it as $B$10:$C$10000. The only way I've found to do this so far is to manually change the Data Range each time I change the integral limits. As this needs to often, it's a real drag! Any advice greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoid plotting refferanced blanks as zeros in scatter plot graphs | Charts and Charting in Excel | |||
Avoid 0 values in a graph? | Charts and Charting in Excel | |||
Plotting 0 Values | Charts and Charting in Excel | |||
Can I avoid plotting 'non-empty' cells | Charts and Charting in Excel | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |