zero or negative numbers dialog box

[danni]

One of my users posted the following message to our help desk. I really
don't know a lot about Excel, so I was hoping someone could help me
out:

A frustrating feature of Excel:
Excel sends messages that there are zero or negative numbers in
log-log plots of data. However, there aren't any zeros in the data, so
it can't be fixed! Grrr! THis problem seems to happen more often when
there are lots of charts in the worksheet.
Any hints?

Any help that can be provided would be greatly appreciated!

Thanks!
Danni

 

[Tushar Mehta]

Yes, it can be frustrating, though I suspect there are cells that look
empty because a formula evaluates to the empty string "" -- something
like IF(<condition>, <real result>, "")

(Un)fortunately, XL treats zero length strings as zero values for the
purpose of charting.

The two ways around the problem is to (a) replace the "" with NA() or
(b) if the zero-length strings are at the end of the data, plot only
those cells that contain real data. You would have to use a variant of
the technique documented on the 'Dynamic Charts' page of my web site.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2004
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office