Blank instead of ""
in [Charts]
|
From: LiAD on 29 Mar 2010 06:43 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
From: Bernard Liengme on 29 Mar 2010 08:22 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" <LiAD(a)discussions.microsoft.com> wrote in message news:FB1B18B9-BDF6-4B19-884F-9F9C58215D3B(a)microsoft.com... > 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
From: LiAD on 29 Mar 2010 10:57 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" <LiAD(a)discussions.microsoft.com> wrote in message > news:FB1B18B9-BDF6-4B19-884F-9F9C58215D3B(a)microsoft.com... > > 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 > > . >
From: Luke M on 29 Mar 2010 12:21 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/mind-the-gap-charting-empty-cells/ -- Best Regards, Luke M "LiAD" <LiAD(a)discussions.microsoft.com> wrote in message news:25ACFA09-ED43-4C91-98B1-C6FC9D025CBF(a)microsoft.com... > 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" <LiAD(a)discussions.microsoft.com> wrote in message >> news:FB1B18B9-BDF6-4B19-884F-9F9C58215D3B(a)microsoft.com... >> > 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 >> >> . >>
From: LiAD on 30 Mar 2010 02:41
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/mind-the-gap-charting-empty-cells/ > > -- > Best Regards, > > Luke M > "LiAD" <LiAD(a)discussions.microsoft.com> wrote in message > news:25ACFA09-ED43-4C91-98B1-C6FC9D025CBF(a)microsoft.com... > > 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" <LiAD(a)discussions.microsoft.com> wrote in message > >> news:FB1B18B9-BDF6-4B19-884F-9F9C58215D3B(a)microsoft.com... > >> > 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 > >> > >> . > >> > > > . > |