Negative or zero values cannot be plotted
Rebecca
Office XP w/patches and the error message still comes up
when plotting.
Negative or zero values cannot be plotted correctly on
log charts. Only positive values can be interpreted on a
logarithmic scale.
Tushar Mehta
are plotting points that are ill-defined for a log chart. LOG(0) and
LOG(<0) do not return real values and cannot be plotted. Either don't
plot cells with values <=0 or use a formula to translate them into NA
(). Something like IF(<real-cell> <= 0,NA((),<real-cell>)
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--
In <000f01c2add8$02ac4260$d7f82ecf@TK2MSFTNGXA14>, Rebecca
<Rebecca....@Shawgrp.com> wrote
Thomas Bartkus
news:000f01c2add8$02ac4260$d7f82ecf@TK2MSFTNGXA14...
As Tushar has aptly pointed out, your complaint is with math, not Excel.
Log values for numbers =< zero do not exist and thus, there is no way they
can be "plotted correctly" by Excel.
If whatever it is you are doing produces some values at zero and below - a
log plot for these is probably bad math. But if you insist on doing so, it
is incumbant upon you to filter out such values before plotting.
--
Thomas Bartkus
www.BartkusConsulting.com
Postman
| As Tushar has aptly pointed out, your complaint is with math, not Excel.
| Log values for numbers =< zero do not exist and thus, there is no way they
| can be "plotted correctly" by Excel.
That's a lame excuse - Excel lets you do a lot of things that are
mathematically nonsensical.
This is one annoying feature you would think they would have corrected since
version 4.0!!!
| If whatever it is you are doing produces some values at zero and below - a
| log plot for these is probably bad math. But if you insist on doing so,
it
| is incumbant upon you to filter out such values before plotting.
And it would be nice if the program would let you.
One reason for zeros - missing data or integer sampling.
Of course I *KNOW* the reason that you cannot represent 0 (or -ve) on a log
scale - but it is still a standard way to plot spectrometry data. All it
needs is a tiny little check-box in an options menu to enable us to
specifically disable this alert. Sheesh, if it did it once it wouldn't be
so bad ;o(
Oh, this bug BUGs me in case it didn't show ...
P
Jon Peltier
workaround?
Assume your X and Y data are in columns A and B. You need some
non-error-producing X and Y data in columns C and D, or wherever you have
some space. If the first X value is in A2, enter this formula in C2:
=IF(ISNUMBER(A2),IF(A2<=0,NA(),A2),NA())
This leaves a loggable value alone, and converts text, blanks, zeros, and
negatives to #N/A, which your chart will happily ignore. Drag this right to
fill D2 and then drag C2:D2 down as far as needed to fill the data range.
Double clicking the little black square in the bottom right of a selection
will fill it down as far as the column to the left of the selection is not
blank (two tips in one!). Now simply construct the chart from the data in
C:D.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <av7scb$1tk$1...@news7.svr.pol.co.uk>, Postman said...
Ambrose Drauphlin
My cure was to install XL 97 in a different directory, upgraded it with the
latest Service packs for Y2K compatibility and am doing 100's of semi-log plots
of production vs. date semilog plots without a hitch. XL 97 and newer versions
of XL can quite peacefully co-exist on the same computer together (thank god). I
jumped through hoops trying to get XL 2000 and XL XP to handle certain quite
legitimate date vs. value semilog plots, and inspite of a great deal of advice
from the denizens of this conference and some pretty exotic VB routines, just
could not get a satisfactory solution with newer versions of XL that worked
anywhere near as well as just doing what ever log plots I have to do with XL 97
with updated Y2K service packs.
Ambrose Drauphlin
> So do you want to stop complaining and fix it? Or at least install a
> workaround?
> Assume your X and Y data are in columns A and B. You need some
> non-error-producing X and Y data in columns C and D, or wherever you have
> some space. If the first X value is in A2, enter this formula in C2:
> =IF(ISNUMBER(A2),IF(A2<=0,NA(),A2),NA())
When one is working with more sophisticated semilog plots using dates, this can
turn out to be a -real- cumbersome piece of crap workaround. Microsoft needs to
get thier head out of thier asses and let the people who are using thier product
decide if they need to keep seeing MS's inane error messages about 0 values and
log plots by allowing us to disable those ridiculous messages. It could easily
be worked around in XL 97, but not XL 2000. I truly though that by the time
microsoft had come out with XL XP, they would of seen the light and allowed
thier users to just disable the damned message that isn't even applicable to a
lot of log plots thier XL user have to do every day.
Jon Peltier
In article <OZUrowGtCHA.1776@TK2MSFTNGP09>, Ambrose Drauphlin said...
Martin Brown
Tushar Mehta wrote:
> While there are some who will disagree, that message indicates that you
> are plotting points that are ill-defined for a log chart. LOG(0) and
> LOG(<0) do not return real values and cannot be plotted. Either don't
> plot cells with values <=0 or use a formula to translate them into NA
> (). Something like IF(<real-cell> <= 0,NA((),<real-cell>)
It is still a pretty infuriating error message that would be so easy to fix
at source.
The method I prefer is to have my own log/lin display toggle macro which
takes the elementary precaution of checking and fixing up
ActiveChart.Axes(xlValue).MinimumScale <= 0 before enabling Log mode.
This method works OK in both XL97 and XL2k.
Plenty of bulk scientific data from counting systems need log display but
with log(0) quietly ignored.
Regards,
Martin Brown
Tushar Mehta
It's possible that MS could create a more sophisticated mechanism for
handling how it plots non-real data. However, if it silently ignored
log(<=0) errors, there would be a whole bunch of people complaining
about that! And, if it made it an option, there would be issues about
whether it is a one-time warning, machine-based, workbook based, user
based, etc.
This is *not* targeted at you.
The workaround for the occasional zero or no data yet scenarios is so
trivial that the handful of people who complain about it seem more
interested in complaining about the 'problem' than in solving it.
* Use a named range that excludes data not yet available
* Leave cells that don't contain data empty
* Use NA()
* Do the log() mapping in the worksheet and plot the transformed data.
* If XL doesn't match their sophisticated capabilities, they should use
a program that matches their requirements.
--
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel
In article <3E180C1C...@pandora.be>, martin...@pandora.be
says...
Thomas Bartkus
news:av7scb$1tk$1...@news7.svr.pol.co.uk...
> Thomas defended an indefensible annoyance with Excel's charting ...
>
>
> | As Tushar has aptly pointed out, your complaint is with math, not Excel.
> | Log values for numbers =< zero do not exist and thus, there is no way
they
> | can be "plotted correctly" by Excel.
>
> That's a lame excuse - Excel lets you do a lot of things that are
> mathematically nonsensical.
Too true! So why complain when they get it right?
> This is one annoying feature you would think they would have corrected
since
> version 4.0!!!
>
> | If whatever it is you are doing produces some values at zero and below -
a
> | log plot for these is probably bad math. But if you insist on doing so,
> it
> | is incumbant upon you to filter out such values before plotting.
>
> And it would be nice if the program would let you.
> One reason for zeros - missing data or integer sampling.
> Of course I *KNOW* the reason that you cannot represent 0 (or -ve) on a
log
> scale - but it is still a standard way to plot spectrometry data. All it
> needs is a tiny little check-box in an options menu to enable us to
> specifically disable this alert. Sheesh, if it did it once it wouldn't
be
> so bad ;o(
I really don't know why this is so inflammatory. There are 2 problems here.
One is the math, the other is the way Excel handles the problem of ones
attempt at bad math. Remember the original post!
>>Negative or zero values cannot be plotted correctly on
>>log charts. Only positive values can be interpreted on a
>>logarithmic scale.
The second sentence is a mathematical fact that has nothing to do with
Excel. I'm glad you understand the problem but the original poster clearly
did not.
> Of course I *KNOW* the reason that you cannot represent 0 (or -ve) on
a log
> scale - but it is still a standard way to plot spectrometry data.
Where might that be? And how on earth did these math challenged
spectrometer operators do it manually on semi-log paper before they had
Excel? The answer is that there was still no option to plot zero values.
There are no zero values! Just a detection limit below which you can not
measure. The only way to plot these was at the lower detection limit - a
distinctly non-zero value. This is the way you should handle it in Excel.
It will work fine in Excel the same way as when we had to do it with pencil
and paper.
Old enough to remember!
--
Thomas Bartkus
www.BartkusConsulting.com
Martin Brown
Tushar Mehta wrote:
> Hi Martin,
>
> It's possible that MS could create a more sophisticated mechanism for
> handling how it plots non-real data. However, if it silently ignored
> log(<=0) errors, there would be a whole bunch of people complaining
> about that!
I doubt it. But even if there was a potential conflict it is not insurmountable.
Most of the people that are inconvenienced by this are process monitoring guys in
industry facing routine analysis of chunks of several thousand measurements by
20-100 channels of data over a wide dynamic range.
I get asked to provide fixups for this "helpful" feature often enough.
> And, if it made it an option, there would be issues about
> whether it is a one-time warning, machine-based, workbook based, user based,
> etc.
All they need to do is add a "don't show this warning again" check box. Their
plotting code can already quite happily cope with ignoring any data values that
it cannot plot on a log scale.
> This is *not* targeted at you.
>
> The workaround for the occasional zero or no data yet scenarios is so
> trivial that the handful of people who complain about it seem more
> interested in complaining about the 'problem' than in solving it.
>
> * Use a named range that excludes data not yet available
> * Leave cells that don't contain data empty
> * Use NA()
> * Do the log() mapping in the worksheet and plot the transformed data.
Your workarounds become extremely cumbersome on bulk data.
Forcing exact "0" to empty cell by susbstitution is about the fastest of these
options.
XL is rather patchy about when it issues this warning message.
> * If XL doesn't match their sophisticated capabilities, they should use
> a program that matches their requirements.
Granted there are better packages for visualising bulk scientific data like IDL
for instance, but at around 10x the price per seat and a steep learning curve it
is never going to have much impact outside well funded research environments. The
guys in routine QC work are stuck with commonly supported applications like XL
and get nagged hourly by this message. I can well understand why they get bitter
and twisted about it.
It is also not strictly true to say that negative values cannot be plotted on a
log scale.
It is only trying to plot zero on a log scale that is seriously problematic.
1000
100
10
1
0.1
[discontinuity]
-0.1
-1
-10
-100
-1000
It probably would not be what anyone wanted, but it is perfectly possible to plot
(give or take + i.pi ).
The most common case where log plots get used for a quick look see is bulk
positive integer data typically in the range 0...10^9 from scientific instruments
or process monitoring kit.
Regards,
Martin Brown
> In article <3E180C1C...@pandora.be>, martin...@pandora.be
> says...
> >
> >
> > Tushar Mehta wrote:
> >
> > > While there are some who will disagree, that message indicates that you
> > > are plotting points that are ill-defined for a log chart. LOG(0) and
> > > LOG(<0) do not return real values and cannot be plotted.
>