Blank instead of ""
in [Charts]
|
From: Jon Peltier on 30 Mar 2010 18:51 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/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 >>>> >>>> . >>>> >> >> >> . >> |