|
Chart data with zeroes
I have used an IF formula to replace blanks, zeroes etc. with #N/A as I
thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting |
Chart data with zeroes
Hi,
The use of NA() within a line chart will only stop the data marker being plotted. The line will be interpolated between valid data points. The only way to truly break the line is to have empty data cells. You could use Autofilter to hide rows containing #N/A. And if the chart has the option to Plot visible cells only the information will not be displayed. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I have used an IF formula to replace blanks, zeroes etc. with #N/A as I thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting |
Chart data with zeroes
Thanks Andy,
I tried replacing "#N/A" in the IF formula with "" but this still plots 'zeroes'. My problem is that this is to appear in a template where there will be data within the chart source data but not necessarily in every cell. I am trying to avoid the user having to do anything to produce the chart and cannot think of a way round this. "Andy Pope" wrote: Hi, The use of NA() within a line chart will only stop the data marker being plotted. The line will be interpolated between valid data points. The only way to truly break the line is to have empty data cells. You could use Autofilter to hide rows containing #N/A. And if the chart has the option to Plot visible cells only the information will not be displayed. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I have used an IF formula to replace blanks, zeroes etc. with #N/A as I thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting |
Chart data with zeroes
The formula replacement would be
=IF(<test,<value,NA() ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... Thanks Andy, I tried replacing "#N/A" in the IF formula with "" but this still plots 'zeroes'. My problem is that this is to appear in a template where there will be data within the chart source data but not necessarily in every cell. I am trying to avoid the user having to do anything to produce the chart and cannot think of a way round this. "Andy Pope" wrote: Hi, The use of NA() within a line chart will only stop the data marker being plotted. The line will be interpolated between valid data points. The only way to truly break the line is to have empty data cells. You could use Autofilter to hide rows containing #N/A. And if the chart has the option to Plot visible cells only the information will not be displayed. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I have used an IF formula to replace blanks, zeroes etc. with #N/A as I thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting |
Chart data with zeroes
I may have misunderstood. Same result #N/A appears on the x axis and point at zero. "Andy Pope" wrote: The formula replacement would be =IF(<test,<value,NA() ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... Thanks Andy, I tried replacing "#N/A" in the IF formula with "" but this still plots 'zeroes'. My problem is that this is to appear in a template where there will be data within the chart source data but not necessarily in every cell. I am trying to avoid the user having to do anything to produce the chart and cannot think of a way round this. "Andy Pope" wrote: Hi, The use of NA() within a line chart will only stop the data marker being plotted. The line will be interpolated between valid data points. The only way to truly break the line is to have empty data cells. You could use Autofilter to hide rows containing #N/A. And if the chart has the option to Plot visible cells only the information will not be displayed. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I have used an IF formula to replace blanks, zeroes etc. with #N/A as I thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting |
Chart data with zeroes
I doubt you miss-understood :)
Even using NA() in the formula you will not get a different result. The Category label will still appear as #N/A. Only way to get rid of the category is to exclude it from the data series. Either by not selecting that cell or hiding the row. The line will still appear with no break in it. The data marker, if present, will not be displayed. The data label will not be shown if Value is the item being displayed. If you are displaying Category then the #N/A will appear. There is not a formula based way of removing data points from a chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I may have misunderstood. Same result #N/A appears on the x axis and point at zero. "Andy Pope" wrote: The formula replacement would be =IF(<test,<value,NA() ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... Thanks Andy, I tried replacing "#N/A" in the IF formula with "" but this still plots 'zeroes'. My problem is that this is to appear in a template where there will be data within the chart source data but not necessarily in every cell. I am trying to avoid the user having to do anything to produce the chart and cannot think of a way round this. "Andy Pope" wrote: Hi, The use of NA() within a line chart will only stop the data marker being plotted. The line will be interpolated between valid data points. The only way to truly break the line is to have empty data cells. You could use Autofilter to hide rows containing #N/A. And if the chart has the option to Plot visible cells only the information will not be displayed. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I have used an IF formula to replace blanks, zeroes etc. with #N/A as I thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting |
Chart data with zeroes
Thanks Andy, I'm going to have to work my way round this by getting the user
to paste values into another area where it is already set up as the source data. This way at least I will get truly blank cells for the chart to ignore (I hope!). Your help has been appreciated. Forums are the best part of the web. "Andy Pope" wrote: I doubt you miss-understood :) Even using NA() in the formula you will not get a different result. The Category label will still appear as #N/A. Only way to get rid of the category is to exclude it from the data series. Either by not selecting that cell or hiding the row. The line will still appear with no break in it. The data marker, if present, will not be displayed. The data label will not be shown if Value is the item being displayed. If you are displaying Category then the #N/A will appear. There is not a formula based way of removing data points from a chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I may have misunderstood. Same result #N/A appears on the x axis and point at zero. "Andy Pope" wrote: The formula replacement would be =IF(<test,<value,NA() ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... Thanks Andy, I tried replacing "#N/A" in the IF formula with "" but this still plots 'zeroes'. My problem is that this is to appear in a template where there will be data within the chart source data but not necessarily in every cell. I am trying to avoid the user having to do anything to produce the chart and cannot think of a way round this. "Andy Pope" wrote: Hi, The use of NA() within a line chart will only stop the data marker being plotted. The line will be interpolated between valid data points. The only way to truly break the line is to have empty data cells. You could use Autofilter to hide rows containing #N/A. And if the chart has the option to Plot visible cells only the information will not be displayed. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I have used an IF formula to replace blanks, zeroes etc. with #N/A as I thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting |
Chart data with zeroes
"Code Numpty" wrote: Thanks Andy, I'm going to have to work my way round this by getting the user to paste values into another area where it is already set up as the source data. This way at least I will get truly blank cells for the chart to ignore (I hope!). Your help has been appreciated. Forums are the best part of the web. "Andy Pope" wrote: I doubt you miss-understood :) Even using NA() in the formula you will not get a different result. The Category label will still appear as #N/A. Only way to get rid of the category is to exclude it from the data series. Either by not selecting that cell or hiding the row. The line will still appear with no break in it. The data marker, if present, will not be displayed. The data label will not be shown if Value is the item being displayed. If you are displaying Category then the #N/A will appear. There is not a formula based way of removing data points from a chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I may have misunderstood. Same result #N/A appears on the x axis and point at zero. "Andy Pope" wrote: The formula replacement would be =IF(<test,<value,NA() ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... Thanks Andy, I tried replacing "#N/A" in the IF formula with "" but this still plots 'zeroes'. My problem is that this is to appear in a template where there will be data within the chart source data but not necessarily in every cell. I am trying to avoid the user having to do anything to produce the chart and cannot think of a way round this. "Andy Pope" wrote: Hi, The use of NA() within a line chart will only stop the data marker being plotted. The line will be interpolated between valid data points. The only way to truly break the line is to have empty data cells. You could use Autofilter to hide rows containing #N/A. And if the chart has the option to Plot visible cells only the information will not be displayed. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I have used an IF formula to replace blanks, zeroes etc. with #N/A as I thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting I don't know if this is the same problem you're having but mine is that I am trying to create a chart from data that is in the form of a formula, some of those cells have a formula that returns a blank cell, "". I want my line chart to stop if the cell is blank, instead it is showing as a zero value. Any ideas on how to do this. |
Chart data with zeroes
Hi,
It probably is. You need to do this revision to your formula structure. =IF(<test,<value,NA() ) NA() instead of "". But as previously stated this will not cause a break in the line only stop the data marker appearing and interpolation of the line between valid points. Cheers Andy Beth@ngc wrote: "Code Numpty" wrote: Thanks Andy, I'm going to have to work my way round this by getting the user to paste values into another area where it is already set up as the source data. This way at least I will get truly blank cells for the chart to ignore (I hope!). Your help has been appreciated. Forums are the best part of the web. "Andy Pope" wrote: I doubt you miss-understood :) Even using NA() in the formula you will not get a different result. The Category label will still appear as #N/A. Only way to get rid of the category is to exclude it from the data series. Either by not selecting that cell or hiding the row. The line will still appear with no break in it. The data marker, if present, will not be displayed. The data label will not be shown if Value is the item being displayed. If you are displaying Category then the #N/A will appear. There is not a formula based way of removing data points from a chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I may have misunderstood. Same result #N/A appears on the x axis and point at zero. "Andy Pope" wrote: The formula replacement would be =IF(<test,<value,NA() ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... Thanks Andy, I tried replacing "#N/A" in the IF formula with "" but this still plots 'zeroes'. My problem is that this is to appear in a template where there will be data within the chart source data but not necessarily in every cell. I am trying to avoid the user having to do anything to produce the chart and cannot think of a way round this. "Andy Pope" wrote: Hi, The use of NA() within a line chart will only stop the data marker being plotted. The line will be interpolated between valid data points. The only way to truly break the line is to have empty data cells. You could use Autofilter to hide rows containing #N/A. And if the chart has the option to Plot visible cells only the information will not be displayed. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Code Numpty" wrote in message ... I have used an IF formula to replace blanks, zeroes etc. with #N/A as I thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting I don't know if this is the same problem you're having but mine is that I am trying to create a chart from data that is in the form of a formula, some of those cells have a formula that returns a blank cell, "". I want my line chart to stop if the cell is blank, instead it is showing as a zero value. Any ideas on how to do this. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
| All times are GMT +1. The time now is 04:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com