INDIRECT function returns a zero instead of a blank

[Paul]

Hi,

I'm using the INDIRECT function to allow me to plot a series that
spans several worksheets, as suggested here:

http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html

My problem is that whereas some values are actually zero, others are
blank; INDIRECT displays both as zero values. Is there a way to
prevent this from happening?

 

[Pete_UK]

You could have something like:

=IF(your_formula="","",your_formula)

Hope this helps.

Pete

 

[Paul]

That's perfect - thank you!

Just for the record the final formula was:

=IF(INDIRECT(cellref)="","",INDIRECT(cellref))

 

[Paul]

Ok, not quite perfect (yet!). Although the cells appear blank, the
chart seems to see them as zero values. My setting in
Tools>Options>Chart is to "Plot empty cells as"..."Interpolated". I
would like this to remain this way so that lines join up entered
values and simply bypass those that are missing. This normally works
and, indeed, neighbouring cells which are 'really' blank (completely
empty rather than containing the formula that enters "") are bypassed
as they should be.

Is there perhaps another value, other than "", that will give a
properly blank cell? Or is there some way of formatting the cells that
will help?

 

[Pete_UK]

The cell is not empty as it contains a formula. You might like to try
this variation:

=IF(INDIRECT(cellref)="",NA(),INDIRECT(cellref))

which will return #N/A, and this should be ignored by the graph.

Hope this helps.

Pete

 

[Dave Peterson]

Maybe you could try:

=IF(INDIRECT(cellref)="",na(),INDIRECT(cellref))

And then use format|Conditional formatting to hide that error (to make it look
pretty).

 

[Paul]

Wow, we're really getting there!

The na() worked like a charm - the chart skips the cells displaying #N/
A.

The conditional formatting is certainly not vital but would be good.
Everyone likes a pretty spreadsheet, no? I can't figure out what to
set as the condition though! I've tried "Cell Value Is"..."equal
to"..."#N/A" but that didn't seem to have an effect.

 

[Paul]

I've actually now spotted a strange phenomenon:

Within the same series, one empty cell read by INDIRECT and displayed
as #N/A is ignored/bypassed by the chart whilst another value in that
same series (also read by INDIRECT and displayed as #N/A) is charted
as zero! Interestingly it is the same x-axis value for every series on
a particular chart. I'm sure I've checked the formulae and I can't see
any difference (plus they were cut-n-pasted, anyway).

Any ideas?!?

 

[Dave Peterson]

Select the range to apply format|conditional formatting
With C2 (say), try this:

Format|conditional formatting
formula is:
=isna(c2)

and use a white font on a white fill (or something to make the cell look empty).

I don't have a guess about the other charting problem, though.

 

[Paul]

Perfect. Thank you! (And the other charting problem was a mistake on
my part - I removed the post but looks like you got it anway).

Thank you for all your time,

Paul

 

[Dave Peterson]

Once you post a message, it's released to the wild. You can't stop it from
spreading over the internets. <vbg>

Glad you found the problem.

 

[$10 Hampster Sandwich]

Here's how to get rid of the "zeros"


=IF(INDIRECT("Global!A"&ROW(A3))="","",INDIRECT("Global!A"&ROW(A3)))


Basically, if the cell you're referencing to is blank, then it will display a blank cell. If the cell has something in it, then it will display whatever it is in it.


The thing I like about this is that the formula WON"T follow the referanced cell if you delete it or move it. It simply will display whatever is in cell "A3" no matter what. Add a row, delete a row, it will always display A3



Global = sheet name
the &ROW(A3) I had to use in order to "drag" my formula