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

 

[KenWright]

Cheers Guys - I can't believe I have only ever tried this with a stacked line graph and not the others, and yet the only ones it bombs with are the stacked line graphs - Aaaagggghhhh (Dohhhhhhhhh).

Thanks again.

Ken...............

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

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

 

[norty303]

You learn something new every day Ken...

Well in my case it's lots of things and remembering them on subsequent days becomes the issue....

Cheers

 

[megpow]

I'm posting on this thread because I think I have a similar question.. kind of..

I have a combination chart in Excel ..
The base of the chart is a stacked bar that shows forecasted monthly figures for every month, Jan thru Dec. Combined on the same axes is a stacked line chart showing actual figures. If we only have actual data for part of the year on the stacked line but forecasted data for the entire year, how do we make the null values not appear on the stacked line? I've already gone through the tools>options>chart>plot empty cells as.. and it doesn't work. I tell it not to plot empty values, but it keeps plooting them as zeros. Am I going crazy? help!

 

[xlbo]

That's because you have "" not an empty cell
change it to #N/A as per the many posts above

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

 

[megpow]

Sorry, I forgot to mention that I had already tried the NA() or #N/A .. it didn't fix the problem. They still show up on the chart. When I scroll over the graphed nulls, it says "value: #N/A" but is still graphed as a zero

The null values aren't charted on a different line graph (not stacked) that I have on the same worksheet, referencing the same data-- but for some reason it graphs the nulls on my combo chart with a stacked line ...

 

[xlbo]

See KenWrights post on 7th Jan in this thread - it tends to help if you read the whole thread 1st before asking a question

#N/A doesn't work with stacked lines - your only option if you don't want them graphed is to delete the cell contents so it really is an empty cell

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

 

[CandyS]

Here's another good resource I found for creating dynamic charts:

http://www.tushar-mehta.com/index.h...xcel/newsgroC:\PROGRA~1\INTERN~1\iexplore.exe

Candy

 

[megpow]

geoff : I've already tried that also (clearing the contents of the cells).. I have read the whole thread (a few times) and I am really frustrated! I've tried dynamic ranges, the NA function, clearing the contents of the cells, changing chart options, etc...

<sigh> Thanks for the suggestion CandyS. I installed the Add-in, but it doesn't seem to work in my situation. When I go back and check on the series range for the line that should not be plotting empty cells, it still says that the data range includes those empty cells...

Perhaps the dynamic range isn't working correctly because I have a stacked bar combined with the stacked line, and the stacked bar chart (forecasted) includes data for the whole year, while the stacked line (actual) only includes data for part of the year..?

 

[xlbo]

Have you 1 dyna range or 2 ??
I'd suggest creating a seperate dyna range for actual and forecast and insert them as series

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