Negative or zero values cannot be plotted correctly on log charts

[NTE]

Excel 2002 SP3.

This maybe a stupid question (I'm not a statistician or mathematician).

I have data that looks like this:

Na 0.032617
Cl 0.0265
Ca 0.000524
K 0.005523
Mg 0.0000823
PO4 0.00031
HCO3 0.001655

I am plotting it with categorical values on the xaxis and the numerical
value on the y axis. I am using a logarithmic scale for the y axis.

I am getting the "Negative or zero values cannot be plotted correctly
on log charts. Only positive values can be interpreted on a
logarithmic scale" message, which I see from the newsgroup is common if
there are zeros in the data. However, the data I am using does not
appear to be zero to me. All the numbers are positive, even if they
are small.

So why am I getting this message?

Thank you.
Nicole

 

[James Silverton]

NTE wrote on 7 Dec 2005 07:06:06 -0800:

N> This maybe a stupid question (I'm not a statistician or
N> mathematician).

N> I have data that looks like this:

N> Na 0.032617
N> Cl 0.0265
N> Ca 0.000524
N> K 0.005523
N> Mg 0.0000823
N> PO4 0.00031
N> HCO3 0.001655

N> I am plotting it with categorical values on the xaxis and
N> the numerical value on the y axis. I am using a logarithmic
N> scale for the y axis.

N> I am getting the "Negative or zero values cannot be plotted
N> correctly on log charts. Only positive values can be
N> interpreted on a logarithmic scale" message, which I see
N> from the newsgroup is common if there are zeros in the data.
N> However, the data I am using does not appear to be zero to
N> me. All the numbers are positive, even if they are small.

You rightly point out that negative numbers cannot be plotted
(since their logs are imaginary numbers ) and I also wonder what
is going on! I took your data and plotted it as a standard line
chart in Excel 2002. The result was not too bad just like that.
I switched the y-axis scale to logarithmic and that worked too.
Do you have some sort of formatting problem?

James Silverton.
Potomac, Maryland.

 

[NTE]

I'm not sure what you mean by formatting problem. There are no
formulas hidden in the columns or anything like that--the data is as I
included.

And the points do seem to plot ok, I just don't understand why I'm
getting the message. Those values are medians for certain
electrolytes, and I am trying also to plot error bars using min/max for
each category. I can get a plus error bar but not a minus, and I was
wondering if the error about negative or zero values was impacting
that.

Thank you for your input.

Nicole

 

[James Silverton]

NTE wrote on 7 Dec 2005 07:59:25 -0800:

N> And the points do seem to plot ok, I just don't understand
N> why I'm getting the message. Those values are medians for
N> certain electrolytes, and I am trying also to plot error
N> bars using min/max for each category. I can get a plus
N> error bar but not a minus, and I was wondering if the error
N> about negative or zero values was impacting that.

N> Thank you for your input.

I don't know for sure but I think it is possible. I must say
I've never tried to plot error bars on a log chart and haven't
got the time to experiment just now.

Good luck!

James Silverton.

 

[Jon Peltier]

Any blank cells in the data range?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[NTE]

No. The data is as shown above, no empty cells. Thank you.

 

[vandenberg p]

: No. The data is as shown above, no empty cells. Thank you.

I think it is clear that the problem is caused by your error bars.
You say you do not get lower bars, this is because the lower
confidence interval covers zero so they are all negative.

Error would be computed as Y+/- = X +/- error.
Since X is very small it is easy to see how Y could be negative.
Probably the only error bars you could plot would be % choice on
the menu. This would keep the numbers positive since
the computation would be Y+/- = X * (1+/%). (Assuming you
keep % less than 100%)

The other choices are all absolute numbers which could
easily result in negative numbers for the lower limit.

Pieter Vandenberg