Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I keep zero values from plotting in charts?
I am plotting percentages from a table that will be filled with weekly
values, but not all of the weeks are completed yet, so future weeks are plotting as zero. Cells being graphed contain a A1/B1 formula to get a percentage, resulting in a divide by zero error. I suppressed the error and tried to set the cell as 'blank' by using =IF(ISERROR(A1/B1),,A1/B1). I tried setting the Tools Options Graphs - Plot Empty Cells As: Not Plotted, leave gaps. Problem is, even though the value is 'blank' if error = true, it is no longer a 'blank' cell by Excel's interpretation, so the 'blank' cells are still plotted as zero on the chart. Has anyone else overcome this problem? Thank you very much! - Brian |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I keep zero values from plotting in charts?
Brian -
The cell isn't empty, it contains a formula. And nothing that a formula returns is interpreted as an empty cell. You have two options: 1. Change the formula to =IF(ISERROR(A1/B1),NA(),A1/B1) This leaves an #N/A error in the cell (which conditional formatting will hide) but a line chart or XY chart will interpolate past this point. 2. Construct dynamic ranges and use these in the chart series' source data. This means the chart only has as much data as it can use. Example and links can be found he http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "BrianBrand" wrote in message ... I am plotting percentages from a table that will be filled with weekly values, but not all of the weeks are completed yet, so future weeks are plotting as zero. Cells being graphed contain a A1/B1 formula to get a percentage, resulting in a divide by zero error. I suppressed the error and tried to set the cell as 'blank' by using =IF(ISERROR(A1/B1),,A1/B1). I tried setting the Tools Options Graphs - Plot Empty Cells As: Not Plotted, leave gaps. Problem is, even though the value is 'blank' if error = true, it is no longer a 'blank' cell by Excel's interpretation, so the 'blank' cells are still plotted as zero on the chart. Has anyone else overcome this problem? Thank you very much! - Brian |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I keep zero values from plotting in charts?
Jon
Excel XP 2002... If plot points 1 and 3 have values, and a formula in the data cell for point 2 resolves to NA(), I get a line from point 1 to 3. I want no line between point 1 and 3. The following sounded promising, but "Not Plotted (leave gaps)" and "Interpolated" both act as interpolated. Click the chart. On the Tools menu, click Options, and then click the Chart tab. Under Plot empty cells as, click the option you want. "Jon Peltier" wrote: Brian - The cell isn't empty, it contains a formula. And nothing that a formula returns is interpreted as an empty cell. You have two options: 1. Change the formula to =IF(ISERROR(A1/B1),NA(),A1/B1) This leaves an #N/A error in the cell (which conditional formatting will hide) but a line chart or XY chart will interpolate past this point. 2. Construct dynamic ranges and use these in the chart series' source data. This means the chart only has as much data as it can use. Example and links can be found he http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "BrianBrand" wrote in message ... I am plotting percentages from a table that will be filled with weekly values, but not all of the weeks are completed yet, so future weeks are plotting as zero. Cells being graphed contain a A1/B1 formula to get a percentage, resulting in a divide by zero error. I suppressed the error and tried to set the cell as 'blank' by using =IF(ISERROR(A1/B1),,A1/B1). I tried setting the Tools Options Graphs - Plot Empty Cells As: Not Plotted, leave gaps. Problem is, even though the value is 'blank' if error = true, it is no longer a 'blank' cell by Excel's interpretation, so the 'blank' cells are still plotted as zero on the chart. Has anyone else overcome this problem? Thank you very much! - Brian |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I keep zero values from plotting in charts?
Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps).
should solve it. Doesn't. using Excel XP 2002 "Jon Peltier" wrote: Brian - The cell isn't empty, it contains a formula. And nothing that a formula returns is interpreted as an empty cell. You have two options: 1. Change the formula to =IF(ISERROR(A1/B1),NA(),A1/B1) This leaves an #N/A error in the cell (which conditional formatting will hide) but a line chart or XY chart will interpolate past this point. 2. Construct dynamic ranges and use these in the chart series' source data. This means the chart only has as much data as it can use. Example and links can be found he http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "BrianBrand" wrote in message ... I am plotting percentages from a table that will be filled with weekly values, but not all of the weeks are completed yet, so future weeks are plotting as zero. Cells being graphed contain a A1/B1 formula to get a percentage, resulting in a divide by zero error. I suppressed the error and tried to set the cell as 'blank' by using =IF(ISERROR(A1/B1),,A1/B1). I tried setting the Tools Options Graphs - Plot Empty Cells As: Not Plotted, leave gaps. Problem is, even though the value is 'blank' if error = true, it is no longer a 'blank' cell by Excel's interpretation, so the 'blank' cells are still plotted as zero on the chart. Has anyone else overcome this problem? Thank you very much! - Brian |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I keep zero values from plotting in charts?
This leaves an #N/A error in the cell (which conditional formatting will
hide) but a line chart or XY chart will interpolate past this point. This post solved an almost exactly the same problem I was having. However, the above instruction does not seem to be as easy as it sounds. To get conditional formatting to hide the contents of a cell displaying "#N/A", this is what I did: (it didn't work) Selected the cells clicked conditional formatting in the appropriate boxes, I entered: "Cell value is", "Equal to" "#N/A" (without the quotes) Then I chose formatting with font colour as white so that the white text on white background is invisible. Why doesn't this work? "Jon Peltier" wrote: Brian - The cell isn't empty, it contains a formula. And nothing that a formula returns is interpreted as an empty cell. You have two options: 1. Change the formula to =IF(ISERROR(A1/B1),NA(),A1/B1) This leaves an #N/A error in the cell (which conditional formatting will hide) but a line chart or XY chart will interpolate past this point. 2. Construct dynamic ranges and use these in the chart series' source data. This means the chart only has as much data as it can use. Example and links can be found he http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "BrianBrand" wrote in message ... I am plotting percentages from a table that will be filled with weekly values, but not all of the weeks are completed yet, so future weeks are plotting as zero. Cells being graphed contain a A1/B1 formula to get a percentage, resulting in a divide by zero error. I suppressed the error and tried to set the cell as 'blank' by using =IF(ISERROR(A1/B1),,A1/B1). I tried setting the Tools Options Graphs - Plot Empty Cells As: Not Plotted, leave gaps. Problem is, even though the value is 'blank' if error = true, it is no longer a 'blank' cell by Excel's interpretation, so the 'blank' cells are still plotted as zero on the chart. Has anyone else overcome this problem? Thank you very much! - Brian |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I keep zero values from plotting in charts?
Hi,
Try CF, Formula: =ISNA(B3) changing the cell reference as required. Cheers Andy Frank Winston wrote: This leaves an #N/A error in the cell (which conditional formatting will hide) but a line chart or XY chart will interpolate past this point. This post solved an almost exactly the same problem I was having. However, the above instruction does not seem to be as easy as it sounds. To get conditional formatting to hide the contents of a cell displaying "#N/A", this is what I did: (it didn't work) Selected the cells clicked conditional formatting in the appropriate boxes, I entered: "Cell value is", "Equal to" "#N/A" (without the quotes) Then I chose formatting with font colour as white so that the white text on white background is invisible. Why doesn't this work? "Jon Peltier" wrote: Brian - The cell isn't empty, it contains a formula. And nothing that a formula returns is interpreted as an empty cell. You have two options: 1. Change the formula to =IF(ISERROR(A1/B1),NA(),A1/B1) This leaves an #N/A error in the cell (which conditional formatting will hide) but a line chart or XY chart will interpolate past this point. 2. Construct dynamic ranges and use these in the chart series' source data. This means the chart only has as much data as it can use. Example and links can be found he http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "BrianBrand" wrote in message ... I am plotting percentages from a table that will be filled with weekly values, but not all of the weeks are completed yet, so future weeks are plotting as zero. Cells being graphed contain a A1/B1 formula to get a percentage, resulting in a divide by zero error. I suppressed the error and tried to set the cell as 'blank' by using =IF(ISERROR(A1/B1),,A1/B1). I tried setting the Tools Options Graphs - Plot Empty Cells As: Not Plotted, leave gaps. Problem is, even though the value is 'blank' if error = true, it is no longer a 'blank' cell by Excel's interpretation, so the 'blank' cells are still plotted as zero on the chart. Has anyone else overcome this problem? Thank you very much! - Brian -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I keep zero values from plotting in charts?
Ah yes that works, thatnks! Although it took me a litle while to work out
that once you hve done one cell you can use the format painter brush to do them all the same, Duh! :) Frank "Andy Pope" wrote: Hi, Try CF, Formula: =ISNA(B3) changing the cell reference as required. Cheers Andy Frank Winston wrote: This leaves an #N/A error in the cell (which conditional formatting will hide) but a line chart or XY chart will interpolate past this point. This post solved an almost exactly the same problem I was having. However, the above instruction does not seem to be as easy as it sounds. To get conditional formatting to hide the contents of a cell displaying "#N/A", this is what I did: (it didn't work) Selected the cells clicked conditional formatting in the appropriate boxes, I entered: "Cell value is", "Equal to" "#N/A" (without the quotes) Then I chose formatting with font colour as white so that the white text on white background is invisible. Why doesn't this work? "Jon Peltier" wrote: Brian - The cell isn't empty, it contains a formula. And nothing that a formula returns is interpreted as an empty cell. You have two options: 1. Change the formula to =IF(ISERROR(A1/B1),NA(),A1/B1) This leaves an #N/A error in the cell (which conditional formatting will hide) but a line chart or XY chart will interpolate past this point. 2. Construct dynamic ranges and use these in the chart series' source data. This means the chart only has as much data as it can use. Example and links can be found he http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "BrianBrand" wrote in message ... I am plotting percentages from a table that will be filled with weekly values, but not all of the weeks are completed yet, so future weeks are plotting as zero. Cells being graphed contain a A1/B1 formula to get a percentage, resulting in a divide by zero error. I suppressed the error and tried to set the cell as 'blank' by using =IF(ISERROR(A1/B1),,A1/B1). I tried setting the Tools Options Graphs - Plot Empty Cells As: Not Plotted, leave gaps. Problem is, even though the value is 'blank' if error = true, it is no longer a 'blank' cell by Excel's interpretation, so the 'blank' cells are still plotted as zero on the chart. Has anyone else overcome this problem? Thank you very much! - Brian -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to ignore zero values when plotting a graph | Charts and Charting in Excel | |||
Logarithmic curve formulas in charts and LOGEST fx values? | Excel Worksheet Functions | |||
Displaying null values in charts | Charts and Charting in Excel | |||
Problem with plotting a chart when using arrays as Values and Xvalues | Charts and Charting in Excel | |||
suppress zero values in line charts? | Charts and Charting in Excel |