Chart - stopping line from plotting when there is no data in cell

[Max Velocity]

Hello,

I have pre-prepared a 'Line - Column on 2 axis chart' on a monthl
basis for 1 year. My data consists of revenues and profits as column
and margin as line.

The data is retrieved from a databse into excel, if there is no dat
yet in the database for that month a zero is placed in the cell.

My columns only plot themselves up to the month that I have data. M
problem is that my margin line plots its way to December at zero valu
even when there is no data (for example - Feb - Dec). I want to sto
this - so if I only have data for Jan and Feb there is only two plo
marks for margin line.

At the moment i'm using an if function to try and tell the margin lin
not to plot but it doesn't work.

IF(C$13=0,"",C13/C12)
Is there a formula that makes the cell look empty?

Thank

 

[JanetW]

I had posted a similar question on this forum a while back. The onl
solution that was offered was to use the NA() option to get the line t
not plot for those cells. Unfortunately, and this is one of my bigges
pet peeves about Excel, a cell with a formula is considered to b
non-blank, even if the formula returns a blank ( "" ). Go figure!

And I agree with you that using conditional formatting is less tha
ideal...when you print on a low end laser printer, the N/A shows u
again.

My solution was to use the NA() option in my column of data. The
create a new column that returned the value of the original column i
it wasn't N/A, and "" if it was N/A. You graph your original column
but then hide it, leaving only your "for display only" column visible

 

[Mathew]

Hi Max,
Refer to my post of yesterday, <Autoupdate Charts> I had a similar problem with autoupdated charts,and got exactly the help i
required.
Follow the thread, it should be useful to you.


http://groups.google.com/[email protected]&rnum=1

Mathew



Hello,

I have pre-prepared a 'Line - Column on 2 axis chart' on a monthly
basis for 1 year. My data consists of revenues and profits as columns
and margin as line.

The data is retrieved from a databse into excel, if there is no data
yet in the database for that month a zero is placed in the cell.

My columns only plot themselves up to the month that I have data. My
problem is that my margin line plots its way to December at zero value
even when there is no data (for example - Feb - Dec). I want to stop
this - so if I only have data for Jan and Feb there is only two plot
marks for margin line.

At the moment i'm using an if function to try and tell the margin line
not to plot but it doesn't work.

IF(C$13=0,"",C13/C12)
Is there a formula that makes the cell look empty?

Thanks


---

 

[Max Velocity]

I agree Janet, its a solution but a little unsatisfactory. However I
cannot beleive that a program as well thought out as excel does not
take this problem into account. Anyone got any ideas?

 

[Jon Peltier]

Janet -

You can hide the #N/A errors with conditional formatting, as shown by
Debra Dalgleish:

http://contextures.com/xlCondFormat03.html#Errors

- Jon