Excel: how to indicate null in chart 4

[norty303]

I've got a table showing the projected year ahead with manually input figures on a monthly basis. All of the cells after December are currently blank. I've created a line chart based on this table (actually on a cell with a formula based on other data in the table) and i want to be able to stop the line moving to the base line (zero value).
E.g. for December i want the line to just stop, whereas at the moment it interprets the January value as 0 and drops the line to the baseline for Jan.

I've tried to use an IF statement to say if one of the contributing values to the formula is null then treat the cell as null but it still seems to treat it as 0.

e.g. =IF(G20="","",G20/G21*1000)

or am i misunderstanding the null issue??

Many thanx

 

[ETID]

from menu...
tools>options>chart tab ... select a different option on how to treat empty cells

 

[KenWright]

The other option is to use a dynamic range to determine your data values. This will change automatically as data gets added or removed from the table. See here for instructions on how:-

http://www.peltiertech.com/Excel/Charts/DynamicLast12.html

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[GlennUK]

An easy way to fix this is to use this formula instead ...

=IF(G20="",NA(),G20/G21*1000)

which generates #N/A errors when necessary, which won't be plotted, so that your line won't drop to zero.

Cheers, Glenn.

 

[norty303]

As ever Ken you have provided a very slick solution however in this circumstance GlennUK has the easiest one to implement in my test environment. No doubt I'll use the dynamic range once we decide to roll out our reports once they're proven.

Thanks again

 

[norty303]

As a follow up, and just to make it look pretty, I'd like to set it up so that if the value is #N/A then I'd like to hide that text (or make it white) I've been trying to use conditional formatting for the cells but I'm not sure what to enter into the value cell for it to recognize it. Or is it not this simple??? :-( **shrugs**

 

[xlbo]

Use "Formula Is"
and enter:
=ISNA(b1)
where your data is in B1 and set the font to white

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

 

[norty303]

xlbo: I'm trying this and it is still leaving the #N/A in the cell. I've just tried to change it to red italics just to see if it's doing anything but no joy...

I've also tried using the source cell for the formula as the reference and the cell with the formula itself

 

[norty303]

ok ignore me, i'm being '9am' dumb!!! Caffeine is just getting the cogs working, thanks

 

[KenWright]

Just out of interest then - did you get it to not fall to 0 using the line chart you mentioned at the beginning. I've never had a problem getting it to work using NA() on other charts such as column and bar charts, but a line chart has always been a PITA, hence the dynamic range suggestion.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

Ken - never seen that issue on a line chart - works fine in XP - can't remember for '97 on NT

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

 

[norty303]

We're on win2k and xl2002 and it worked like a dream. Funnily our guru here had always had the same issue with line charts so this solution has been circulated

...and another quicky, how do i get the chart to only display the data range on the y axis where the results are
e.g. all my values are between 60 and 90 so i don't want to see all the white space less than say 50 so the chart is more visible/easier to read. I've done it b4 but i can't find it now for the life of me

 

[xlbo]

Format>Axis - you can set the min and max of the axis in there

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

 

[norty303]

is it possible for it to dynamically recognise the min and max values from the data range so that if i suddenly had a value of 45 it would adjust the graph accordingly without me having to change the axis formats

 

[xlbo]

Record yourself changing the value to get the base code and then use it within the worksheet change event, doing a check to see if the cell changed, was in the correct range.
You will need a named range called "ChartDataRange" which should be set to cover the area where a change in value could affect the axis minimum
summat like this:

Set isect = Application.Intersect(Range(target.address), Range("ChartDataRange&quot)
if isect is nothing then exit sub
ActiveSheet.ChartObjects("ChartName&quot.Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = worksheetfunction.min([ChartDataRange])
End With

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

 

[norty303]

OMG :-o VB!!

Oh well, I'm just getting SQL so it's about time...

Thanx

 

[xlbo]

Sorry - no way I know of without it. Normally you would be ok as the auto scaling should work for you in this way but because youhave #N/A values, the scale is set to start at 0 - Ken's idea of using dynamic ranges would get rid of this problem

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

 

[KenWright]

Geoff/Norty - I'm going nuts here - Any chance you could send me down a line chart example where it doesn't fall to 0 with NA() in it -

I'm running Excel 2000/2002/2003 so this is bugging me now.

ken.wright at ntlworld.com

Cheers
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[norty303]

check ur mail ken..

 

[xlbo]

One from me as well

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