OK ladies, I know Excel is below most of you

bomberesque · Post by **bomberesque** » October 11th, 2007, 11:21

but I have a question anyway

- I am plotting a graph
- that graph uses a list of dates (DATE) and values (PROGRESS) for its source data, showing progress on a project, thus the PROGRESS values for future dates are blank
- The PROGRESS source data list inturn gets its data from querying an input table. Thus it is populated with formulae.
- A simple formula in this box will return a 0 instead of null value from future dates in the input table and so the graph shows my progress to date and a line down to zero tomorrow
- An IF statement in the PROGRESS list cells can return "" to that cell in the event of a zero value on the input table, but the graph still reads and plots it as a zero.
- Entering the formula ="" in the PROGRESS cell also horses teh graph to read and plot the value as zero instead of null value, even though the cell itself erads blank

Any idea how I can get teh cell to return a true NULL value?

Post by **fabyak** » October 11th, 2007, 12:13

Cunning use of the if statement?
i.e. If value for said statement is 0, set output to null, otherwise show the result of the fomula

bomberesque · Post by **bomberesque** » October 11th, 2007, 12:20

That was my thought too so I tried it..

=if(CELL1=0,"",CELL1)

does return a blank if Cell1 = 0, but the graph reads it as zero still

I'm looking for what to swap "" for to get it to return a null value that teh graph will recognise as such.

Actually, this is probably more a bug in the graphing software than a problem with the formula.

MIkkyo · Post by **MIkkyo** » October 11th, 2007, 12:22

bomberesque wrote:That was my thought too so I tried it..
.

Actually, this is probably more a bug in the graphing software than a problem with the formula.

if its got a name for the thing with a null value it'll try to plot it

bomberesque · Post by **bomberesque** » October 11th, 2007, 12:25

MIkkyo wrote:
if its got a name for the thing with a null value it'll try to plot it

yeah, but if the cell is "truly" blank (like, I delete all contents) then it doesn't plot anything, which is what I want.

However, if there's a formula in the cell that's returning "" it plots the value as zero, so the chart isn't reading "" as a null value, which is not what I want and therefore pants

Post by **fabyak** » October 11th, 2007, 12:32

<s>options/ charts/ plot empty cells as: not plotted?</s>
What if you... *wanders off to think*

MIkkyo · Post by **MIkkyo** » October 11th, 2007, 12:35

bomberesque wrote:
yeah, but if the cell is "truly" blank (like, I delete all contents) then it doesn't plot anything, which is what I want.

However, if there's a formula in the cell that's returning "" it plots the value as zero, so the chart isn't reading "" as a null value, which is not what I want and therefore pants

hmmmmm, thats a bugger. when do you need it for? I'm about to go back to Uni then I got a job interview, but if you can send me a copy of it I can have a look. Extra set of eyes 9/10 helps.

bomberesque · Post by **bomberesque** » October 11th, 2007, 12:38

already done, actually I guess that is default, coz I didn't know it was there

although ta fro pointing out that dialog, I was also wondering how to stop it wiping data plots when I hide the table. Why is that dialog there and not in chart options? Bloody silly

the trouble is, the chart is reading "" not as blank but as text, I think, and so plotting a zero value. Does teh same if I enter text in a cell, just plots zero

Post by **fabyak** » October 11th, 2007, 12:39

Use the if statement, and get the value you want to ignore returned as #N/A

bomberesque · Post by **bomberesque** » October 11th, 2007, 12:42

not super urgent at all.

It's actually just a way to make the daily updating much more automatic. As such another method would be writing a VBA script to fill the cells from teh input table for me, but I'm likely to have to leave the chart to someone else who doesn't know VBA and if they have to bugger around with it for some reason, they'll have a problem.

pm me your email and I'll send you a demonstration of the principle (the actual file is about 200 graphs, or at least will be once complete, you don't want all that)

bomberesque · Post by **bomberesque** » October 11th, 2007, 12:44

fabyak wrote:Use the if statement, and get the value you want to ignore returned as #N/A

You win :D

thanks fellas!

MIkkyo · Post by **MIkkyo** » October 11th, 2007, 12:45

fabyak wrote:Use the if statement, and get the value you want to ignore returned as #N/A

Thats the Buisness

Post by **fabyak** » October 11th, 2007, 12:45

bomberesque · Post by **bomberesque** » October 11th, 2007, 12:55

it's still a bit of a dirty workround though...

returning the text string "#N/A" doesn't seem to be enough, it needs to return the error code #N/A which requires you to ask it to look up a piece of data it's not going to find in a dummy list somewhere

so;

if(A1=0,Match("x",AA1),A1)

will return the value of A1 if it's non zero or teh error code #N/A if it is zero and cell AA1 does not equal "x"

Also, makes my data table look all ugly coz of all teh #N/A s everywhere

Still, it does work, which is the point afiac

Thanks Mr Yak

/edit or you set up a cell elsewhere which returns the error code #N/A then refer to that so,

if(A1=0,AB1,A1) will return #N/A

where
A1=0
AB1=Match("x",AA1)
and AA1="Mr Yak is teh Haxxorz"

or without the separate Hax cell

if(A1=0,Match("x",A1),A1)

laks Mr Yak Haxxor easter egg though

Post by **FatherJack** » October 11th, 2007, 13:03

Excel from Excel Saga