|
dont show zero vaules in a chart
I have a chart that is tracking sales for the month, (by day) the chart has
Zero values due to an "IF" statment that has a false or the reference cell is not yet filled in resulting in a "zero value" the chart has a trend line that is tending based on all the remaining zero values . I have changed the Chart options to not display zeros, but this doesnt solve it. |
dont show zero vaules in a chart
Hi,
In your formula use the function NA() instead of False. Cheers Andy Donald Watkins wrote: I have a chart that is tracking sales for the month, (by day) the chart has Zero values due to an "IF" statment that has a false or the reference cell is not yet filled in resulting in a "zero value" the chart has a trend line that is tending based on all the remaining zero values . I have changed the Chart options to not display zeros, but this doesnt solve it. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
dont show zero vaules in a chart
I'm having a similar problem, using a pie-in-pie chart on Excel 2003 (SP3).
I'm using NA() - this returns the #NA error BUT when I go to tools options to set the chart preference to ignore empty, the option is grayed out (not functional). Is this due to security settings (I am on a workplace computer), or is there something else I can check? "Andy Pope" wrote: Hi, In your formula use the function NA() instead of False. Cheers Andy Donald Watkins wrote: I have a chart that is tracking sales for the month, (by day) the chart has Zero values due to an "IF" statment that has a false or the reference cell is not yet filled in resulting in a "zero value" the chart has a trend line that is tending based on all the remaining zero values . I have changed the Chart options to not display zeros, but this doesnt solve it. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
dont show zero vaules in a chart
The Ignore Empty Cells setting:
- Applies to Line and XY charts for Interpolate/Blank/Zero - Applies to Column/Bar charts for Blank/Zero - Applies to Area charts for Interpolate/Zero - Does not apply at all to Pie charts - Only applies to empty cells, not to cells containing "" or NA() NA() results in interpolated lines in Line and XY charts NA() is treated as zero in other charts "" and other text and errors are treated as zero in all charts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Lori H." wrote in message ... I'm having a similar problem, using a pie-in-pie chart on Excel 2003 (SP3). I'm using NA() - this returns the #NA error BUT when I go to tools options to set the chart preference to ignore empty, the option is grayed out (not functional). Is this due to security settings (I am on a workplace computer), or is there something else I can check? "Andy Pope" wrote: Hi, In your formula use the function NA() instead of False. Cheers Andy Donald Watkins wrote: I have a chart that is tracking sales for the month, (by day) the chart has Zero values due to an "IF" statment that has a false or the reference cell is not yet filled in resulting in a "zero value" the chart has a trend line that is tending based on all the remaining zero values . I have changed the Chart options to not display zeros, but this doesnt solve it. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
dont show zero vaules in a chart
So, is there a way to NOT show 0 values in a pie chart?
I want to data labels (other than 0), but I don't want to delete the labels for the 0 values because I want the chart to update when the data changes (so in the future if one of the 0s becomes 0, the data label should return). "Jon Peltier" wrote: The Ignore Empty Cells setting: - Applies to Line and XY charts for Interpolate/Blank/Zero - Applies to Column/Bar charts for Blank/Zero - Applies to Area charts for Interpolate/Zero - Does not apply at all to Pie charts - Only applies to empty cells, not to cells containing "" or NA() NA() results in interpolated lines in Line and XY charts NA() is treated as zero in other charts "" and other text and errors are treated as zero in all charts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Lori H." wrote in message ... I'm having a similar problem, using a pie-in-pie chart on Excel 2003 (SP3). I'm using NA() - this returns the #NA error BUT when I go to tools options to set the chart preference to ignore empty, the option is grayed out (not functional). Is this due to security settings (I am on a workplace computer), or is there something else I can check? "Andy Pope" wrote: Hi, In your formula use the function NA() instead of False. Cheers Andy Donald Watkins wrote: I have a chart that is tracking sales for the month, (by day) the chart has Zero values due to an "IF" statment that has a false or the reference cell is not yet filled in resulting in a "zero value" the chart has a trend line that is tending based on all the remaining zero values . I have changed the Chart options to not display zeros, but this doesnt solve it. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
dont show zero vaules in a chart
If all you're showing is a value, use a number format that suppresses zeros,
something like 0;;; 0.0%;;; $#,##0;;; or whatever. Read about number formats he http://peltiertech.com/Excel/NumberFormats.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Lori H." wrote in message ... So, is there a way to NOT show 0 values in a pie chart? I want to data labels (other than 0), but I don't want to delete the labels for the 0 values because I want the chart to update when the data changes (so in the future if one of the 0s becomes 0, the data label should return). "Jon Peltier" wrote: The Ignore Empty Cells setting: - Applies to Line and XY charts for Interpolate/Blank/Zero - Applies to Column/Bar charts for Blank/Zero - Applies to Area charts for Interpolate/Zero - Does not apply at all to Pie charts - Only applies to empty cells, not to cells containing "" or NA() NA() results in interpolated lines in Line and XY charts NA() is treated as zero in other charts "" and other text and errors are treated as zero in all charts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Lori H." wrote in message ... I'm having a similar problem, using a pie-in-pie chart on Excel 2003 (SP3). I'm using NA() - this returns the #NA error BUT when I go to tools options to set the chart preference to ignore empty, the option is grayed out (not functional). Is this due to security settings (I am on a workplace computer), or is there something else I can check? "Andy Pope" wrote: Hi, In your formula use the function NA() instead of False. Cheers Andy Donald Watkins wrote: I have a chart that is tracking sales for the month, (by day) the chart has Zero values due to an "IF" statment that has a false or the reference cell is not yet filled in resulting in a "zero value" the chart has a trend line that is tending based on all the remaining zero values . I have changed the Chart options to not display zeros, but this doesnt solve it. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
dont show zero vaules in a chart
Hello,
I have a similar problem about displaying zeroes, although mine is not to display the category label when the value is zero but I don't want to use filter to hide zeroes in the worksheet. Please help. Thanks Jake "Jon Peltier" wrote: If all you're showing is a value, use a number format that suppresses zeros, something like 0;;; 0.0%;;; $#,##0;;; or whatever. Read about number formats he http://peltiertech.com/Excel/NumberFormats.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Lori H." wrote in message ... So, is there a way to NOT show 0 values in a pie chart? I want to data labels (other than 0), but I don't want to delete the labels for the 0 values because I want the chart to update when the data changes (so in the future if one of the 0s becomes 0, the data label should return). "Jon Peltier" wrote: The Ignore Empty Cells setting: - Applies to Line and XY charts for Interpolate/Blank/Zero - Applies to Column/Bar charts for Blank/Zero - Applies to Area charts for Interpolate/Zero - Does not apply at all to Pie charts - Only applies to empty cells, not to cells containing "" or NA() NA() results in interpolated lines in Line and XY charts NA() is treated as zero in other charts "" and other text and errors are treated as zero in all charts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Lori H." wrote in message ... I'm having a similar problem, using a pie-in-pie chart on Excel 2003 (SP3). I'm using NA() - this returns the #NA error BUT when I go to tools options to set the chart preference to ignore empty, the option is grayed out (not functional). Is this due to security settings (I am on a workplace computer), or is there something else I can check? "Andy Pope" wrote: Hi, In your formula use the function NA() instead of False. Cheers Andy Donald Watkins wrote: I have a chart that is tracking sales for the month, (by day) the chart has Zero values due to an "IF" statment that has a false or the reference cell is not yet filled in resulting in a "zero value" the chart has a trend line that is tending based on all the remaining zero values . I have changed the Chart options to not display zeros, but this doesnt solve it. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
dont show zero vaules in a chart
There are VBA ways:
search Google Groups for my name and 'hide zeros' or 'don't include zeros' or similar terms and formulaic ways: check out Chip Pearson's site, http://cpearson.com, and look for his techniques for extracting values according to certain rules Choose your poison. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jake" wrote in message ... Hello, I have a similar problem about displaying zeroes, although mine is not to display the category label when the value is zero but I don't want to use filter to hide zeroes in the worksheet. Please help. Thanks Jake "Jon Peltier" wrote: If all you're showing is a value, use a number format that suppresses zeros, something like 0;;; 0.0%;;; $#,##0;;; or whatever. Read about number formats he http://peltiertech.com/Excel/NumberFormats.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Lori H." wrote in message ... So, is there a way to NOT show 0 values in a pie chart? I want to data labels (other than 0), but I don't want to delete the labels for the 0 values because I want the chart to update when the data changes (so in the future if one of the 0s becomes 0, the data label should return). "Jon Peltier" wrote: The Ignore Empty Cells setting: - Applies to Line and XY charts for Interpolate/Blank/Zero - Applies to Column/Bar charts for Blank/Zero - Applies to Area charts for Interpolate/Zero - Does not apply at all to Pie charts - Only applies to empty cells, not to cells containing "" or NA() NA() results in interpolated lines in Line and XY charts NA() is treated as zero in other charts "" and other text and errors are treated as zero in all charts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Lori H." wrote in message ... I'm having a similar problem, using a pie-in-pie chart on Excel 2003 (SP3). I'm using NA() - this returns the #NA error BUT when I go to tools options to set the chart preference to ignore empty, the option is grayed out (not functional). Is this due to security settings (I am on a workplace computer), or is there something else I can check? "Andy Pope" wrote: Hi, In your formula use the function NA() instead of False. Cheers Andy Donald Watkins wrote: I have a chart that is tracking sales for the month, (by day) the chart has Zero values due to an "IF" statment that has a false or the reference cell is not yet filled in resulting in a "zero value" the chart has a trend line that is tending based on all the remaining zero values . I have changed the Chart options to not display zeros, but this doesnt solve it. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
| All times are GMT +1. The time now is 05:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com