Negative or zero values cannot be plotted correctly on log charts

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

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.

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

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.

Any blank cells in the data range?

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


"NTE" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

NTE <[email protected]> wrote:
: 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

I am having the exact same problem.

And this time there are no error bars to talk of...

Data is as follows:

x y1 y2
1.00E-06 9.856777879 1886
1.00E-05 9.770482746 1885
1.00E-04 9.177803095 1877
1.00E-03 6.154087305 1730
1.00E-02 0.770268024 1591
2.00E-02 0.231368807 1588
5.00E-02 0.039476744 1587
1.00E-01 0.009966827 1587


I can plot y1 and y2 against x on a regular plot.
But as soon as I try to turn the x-axis logarithmic I receive errors about negative and zero numbers, but there aren't any! The plot then looks very distorted for y1, y2 looks OK.

If I plot x against y1 separately the logarithmic graph is produced fine, no problems. The logarithmic graph is fine also for x against y2.

Why am I receiving this error, which is distorting the plot, when I plot y1 and y2 against a logarithmic x-axis?

The data is exactly as above, no empty cells and no zeros.

Has anyone had any more experience of this lately?

Many thanks for any advice...
HVSmith