Getting a line chart to NOT plot a zero value
I have a chart that is updated each month with new production figures and a
line chart set up to automatically pull the data from the chart and plot it; however, on the months that haven't occurred yet, the line chart is plotting those months as "zero". I've tried setting the "plot empty cells as: not plotted" in tools/options/chart, but that doesn't work. Apparently, because there is a formula in the cell for the future months, it is plotting the zero... What am I missing?! Thanks a ton!! |
Formulas must return something. "" (which they presumably now return
for future months) is not equivalent to an empty cell. All text plots as zero. Either delete the formulas until they are needed (they could be restored automatically by a worksheet change event) or use #N/A instead of "" for future months. Jerry sheryl wrote: I have a chart that is updated each month with new production figures and a line chart set up to automatically pull the data from the chart and plot it; however, on the months that haven't occurred yet, the line chart is plotting those months as "zero". I've tried setting the "plot empty cells as: not plotted" in tools/options/chart, but that doesn't work. Apparently, because there is a formula in the cell for the future months, it is plotting the zero... What am I missing?! Thanks a ton!! |
Thanks, Jerry!
I ended up just moving the future formulas out of the chart data range and I'll move them back in each month. "Jerry W. Lewis" wrote: Formulas must return something. "" (which they presumably now return for future months) is not equivalent to an empty cell. All text plots as zero. Either delete the formulas until they are needed (they could be restored automatically by a worksheet change event) or use #N/A instead of "" for future months. Jerry sheryl wrote: I have a chart that is updated each month with new production figures and a line chart set up to automatically pull the data from the chart and plot it; however, on the months that haven't occurred yet, the line chart is plotting those months as "zero". I've tried setting the "plot empty cells as: not plotted" in tools/options/chart, but that doesn't work. Apparently, because there is a formula in the cell for the future months, it is plotting the zero... What am I missing?! Thanks a ton!! |
Getting a line chart to NOT plot a zero value
#N/A works great... thanks
"sheryl" wrote: Thanks, Jerry! I ended up just moving the future formulas out of the chart data range and I'll move them back in each month. "Jerry W. Lewis" wrote: Formulas must return something. "" (which they presumably now return for future months) is not equivalent to an empty cell. All text plots as zero. Either delete the formulas until they are needed (they could be restored automatically by a worksheet change event) or use #N/A instead of "" for future months. Jerry sheryl wrote: I have a chart that is updated each month with new production figures and a line chart set up to automatically pull the data from the chart and plot it; however, on the months that haven't occurred yet, the line chart is plotting those months as "zero". I've tried setting the "plot empty cells as: not plotted" in tools/options/chart, but that doesn't work. Apparently, because there is a formula in the cell for the future months, it is plotting the zero... What am I missing?! Thanks a ton!! |
All times are GMT +1. The time now is 02:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com