If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Blank instead of ""
Hi,
In a chart I have a formula that either returns a number or "" in a cell. I then have a dynamic range set which plots the values on a graph. The problem is that the chart will plot the ""s as zeros as it sees something in the cell. Is there a way I can have a true blank cell result coming from a formula? If not how I can avoid the dynamic range seeing this as a zero? Thanks LiAD |
#2
|
|||
|
|||
Blank instead of ""
Replace the blank by #N/A
So let's say your formula is =IF(this-test, B2,"") then use =IF(this-test,B2,NA()) When the test fails, this displays #N/A which the chart engine ignores If this looks odd in a print out, use a conditional format such as =ISNA(C2) and make the font the same as the cell background - make it invisible on the screen and then printed. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "LiAD" wrote in message ... Hi, In a chart I have a formula that either returns a number or "" in a cell. I then have a dynamic range set which plots the values on a graph. The problem is that the chart will plot the ""s as zeros as it sees something in the cell. Is there a way I can have a true blank cell result coming from a formula? If not how I can avoid the dynamic range seeing this as a zero? Thanks LiAD |
#3
|
|||
|
|||
Blank instead of ""
Hi,
Thanks for the suggestion. I'm afraid the chart engine (or at least mine anyway) doesn't ignore it - it plots nothing on the graph but shows a lot of empty space which increases/decreases as i add/delete N/A's. If i try this technique on a chart without dynamic ranges it doesnt work either. Any ideas why this would be different to your result? Thanks "Bernard Liengme" wrote: Replace the blank by #N/A So let's say your formula is =IF(this-test, B2,"") then use =IF(this-test,B2,NA()) When the test fails, this displays #N/A which the chart engine ignores If this looks odd in a print out, use a conditional format such as =ISNA(C2) and make the font the same as the cell background - make it invisible on the screen and then printed. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "LiAD" wrote in message ... Hi, In a chart I have a formula that either returns a number or "" in a cell. I then have a dynamic range set which plots the values on a graph. The problem is that the chart will plot the ""s as zeros as it sees something in the cell. Is there a way I can have a true blank cell result coming from a formula? If not how I can avoid the dynamic range seeing this as a zero? Thanks LiAD . |
#4
|
|||
|
|||
Blank instead of ""
What type of chart are you using? Jon Peltier provides several examples of
how you can handle gaps, and how different chart types vary at: http://peltiertech.com/WordPress/min...g-empty-cells/ -- Best Regards, Luke M "LiAD" wrote in message ... Hi, Thanks for the suggestion. I'm afraid the chart engine (or at least mine anyway) doesn't ignore it - it plots nothing on the graph but shows a lot of empty space which increases/decreases as i add/delete N/A's. If i try this technique on a chart without dynamic ranges it doesnt work either. Any ideas why this would be different to your result? Thanks "Bernard Liengme" wrote: Replace the blank by #N/A So let's say your formula is =IF(this-test, B2,"") then use =IF(this-test,B2,NA()) When the test fails, this displays #N/A which the chart engine ignores If this looks odd in a print out, use a conditional format such as =ISNA(C2) and make the font the same as the cell background - make it invisible on the screen and then printed. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "LiAD" wrote in message ... Hi, In a chart I have a formula that either returns a number or "" in a cell. I then have a dynamic range set which plots the values on a graph. The problem is that the chart will plot the ""s as zeros as it sees something in the cell. Is there a way I can have a true blank cell result coming from a formula? If not how I can avoid the dynamic range seeing this as a zero? Thanks LiAD . |
#5
|
|||
|
|||
Blank instead of ""
Thanks - looing through JPs stuff I can't find a solution - it applies to
blank cells in the middle of data not at the end. My blanks at the end are necessary as the user selects a product then the data and graph update automatically. As the amount of data changes I need a formula that returns either data or "" (or something else that the graph cannot plot). I'm using a line chart. I have a list of data which contains a string of NA#s at the end. When I plot it the graph doesnt plot the NA# but it shifts the line to the left as if it was plotted. What I would like is the last point of the graph to be to the very right of the graph to use all the space. If I replace the NA() with "" i get the same result. If I delete the cell completely the graph moves to the right as it should. I'm using dynamic ranges. Thanks LiAD "Luke M" wrote: What type of chart are you using? Jon Peltier provides several examples of how you can handle gaps, and how different chart types vary at: http://peltiertech.com/WordPress/min...g-empty-cells/ -- Best Regards, Luke M "LiAD" wrote in message ... Hi, Thanks for the suggestion. I'm afraid the chart engine (or at least mine anyway) doesn't ignore it - it plots nothing on the graph but shows a lot of empty space which increases/decreases as i add/delete N/A's. If i try this technique on a chart without dynamic ranges it doesnt work either. Any ideas why this would be different to your result? Thanks "Bernard Liengme" wrote: Replace the blank by #N/A So let's say your formula is =IF(this-test, B2,"") then use =IF(this-test,B2,NA()) When the test fails, this displays #N/A which the chart engine ignores If this looks odd in a print out, use a conditional format such as =ISNA(C2) and make the font the same as the cell background - make it invisible on the screen and then printed. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "LiAD" wrote in message ... Hi, In a chart I have a formula that either returns a number or "" in a cell. I then have a dynamic range set which plots the values on a graph. The problem is that the chart will plot the ""s as zeros as it sees something in the cell. Is there a way I can have a true blank cell result coming from a formula? If not how I can avoid the dynamic range seeing this as a zero? Thanks LiAD . . |
#6
|
|||
|
|||
Blank instead of ""
You need to define your chart data dynamically:
Dynamic Charts » Peltier Tech Blog http://peltiertech.com/WordPress/dynamic-charts/ Dynamic Chart Review » Peltier Tech Blog http://peltiertech.com/WordPress/dynamic-chart-review/ Dynamic and Interactive Charts http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3/30/2010 2:41 AM, LiAD wrote: Thanks - looing through JPs stuff I can't find a solution - it applies to blank cells in the middle of data not at the end. My blanks at the end are necessary as the user selects a product then the data and graph update automatically. As the amount of data changes I need a formula that returns either data or "" (or something else that the graph cannot plot). I'm using a line chart. I have a list of data which contains a string of NA#s at the end. When I plot it the graph doesnt plot the NA# but it shifts the line to the left as if it was plotted. What I would like is the last point of the graph to be to the very right of the graph to use all the space. If I replace the NA() with "" i get the same result. If I delete the cell completely the graph moves to the right as it should. I'm using dynamic ranges. Thanks LiAD "Luke M" wrote: What type of chart are you using? Jon Peltier provides several examples of how you can handle gaps, and how different chart types vary at: http://peltiertech.com/WordPress/min...g-empty-cells/ -- Best Regards, Luke M wrote in message ... Hi, Thanks for the suggestion. I'm afraid the chart engine (or at least mine anyway) doesn't ignore it - it plots nothing on the graph but shows a lot of empty space which increases/decreases as i add/delete N/A's. If i try this technique on a chart without dynamic ranges it doesnt work either. Any ideas why this would be different to your result? Thanks "Bernard Liengme" wrote: Replace the blank by #N/A So let's say your formula is =IF(this-test, B2,"") then use =IF(this-test,B2,NA()) When the test fails, this displays #N/A which the chart engine ignores If this looks odd in a print out, use a conditional format such as =ISNA(C2) and make the font the same as the cell background - make it invisible on the screen and then printed. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme wrote in message ... Hi, In a chart I have a formula that either returns a number or "" in a cell. I then have a dynamic range set which plots the values on a graph. The problem is that the chart will plot the ""s as zeros as it sees something in the cell. Is there a way I can have a true blank cell result coming from a formula? If not how I can avoid the dynamic range seeing this as a zero? Thanks LiAD . . |
Thread Tools | |
Display Modes | |
|
|