stopping a graph from starting from zero on calculations. 1

[jillseurre]

stopping a graph from starting or ending @ zero on cells with calculations in.

I have a graph as follows

A1.A100 = the x axis range of dates
B1.B100 = the Y values

now if I only populate B1.B50
The graph produced stops at B50 but the X axis carries on,
so when I add B51 the graph area stays the same but it adds an additional Y value.. NO RETURN TO ZERO
PERFECT, exactly what I want, so far so good

Now I start to add another axis C1.C100 and I fill this with a calculation
in C1 I put =IF(B1="",some calc which makes a number,"&quot and copy to C100

Now when I graph it at C51 it will do the calc, But on the graph instead of starting at the value in C51 it starts C50 from 0 and I get a line up the graph.

Then for D1.D100 I want to do the opposite
=IF(B1="","",some calc which makes a number)
Then the graph when it reaches D51 will go down to zero.

how do I stop this zeroing from happening on cells with calcualtions. In the if statement instead of putting "" can I put something else which the graph will not see as a zero ???

 

[xlbo]

the problem is that your formula cells are not empty.
Instead of:
=IF(B1="",some calc which makes a number,"&quot
use
=IF(B1="",some calc which makes a number,#N/A)

charts do not plot #N/A errors but DO plot "" (zero length strings)

If this makes your data set look bad, you can then apply conditional formatting to make the cell text white if the formula evaluates to #N/A

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jillseurre]

thanks, worked a treat, + not concerned about data set...

 

[SkipVought]

Hi,

Do you REALLY want to show the Axes BEYOND the range of your data?

Why COMPRESS your series displays like this?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com