Re: Zero values cause errors on log scale

From: Martin Brown (|||newspam|||_at_nezumi.demon.co.uk)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 13:31:46 +0100

In message <40F48AD0.4040806@no_e-mail.com>, Jerry W. Lewis
<post_a_reply@no_e-mail.com> writes
> log(0) = -infinity
>
>If these zero values are "real" then a log scale is not appropriate,
>and the warning is telling you something important.

That isn't necessarily true though. A lot of scientific data for example
ion counting systems can include 0 counts, 1 count and a dynamic range
of up to 10^9. A logarithmic scale is the only way to sensibly display
the resulting huge range of faint signals and very high peaks.

> If these zero values are unfortunate place holders for "unknown",
>"na", etc., then instead of plotting the data as received from SQL,
>plot an intermediate formula like
> =IF(obs=0,#N/A,obs)

The log(0) issue is largely irrelevant for many graph display purposes.
Defining (incorrectly) Log(0) = 0 is often good enough as a work around.
The Excel error message is infuriating because it cannot be disabled on
a per plot basis.

The simplest way round this mess is to select the raw data range and
execute a search and replace to put empty cells in place of all
precisely zero values. This is much the fastest way for large arrays of
raw data.

Log(empty_cell) is curiously not an error

Or in this particular case tweak the SQL code to export a different
representation of zero (e.g. empty_cell) that does not generate the
error message.

>Scott Doyle wrote:
>
>> I deal with a lot of plots with "real" data from an SQL database,
>>and must plot with log scales. The "real" data includes 0 values.
>>Is there a way to turn off the repeated, and annoying, warning
>>"Negative or zero values cnnot be plotted on log charts"? Resizing
>>windows is painful with a plot on screen when this message pops up
>>evry time I start to drag the cursor a few pixels.
>

I find it a bit odd that Excel in Office XP doesn't give the same log(0)
error message the first time that you switch a graph containing negative
or zero values into log mode.

Regards, 

-- 
Martin Brown

Relevant Pages

  • Re: Zero values cause errors on log scale
    ... >>If these zero values are "real" then a log scale is not appropriate, ... > The Excel error message is infuriating because it cannot be disabled on ... > a per plot basis. ... > Martin Brown ...
    (microsoft.public.excel.charting)
  • Re: 3d trajectory problem
    ... I get an error message concerning pm3d. ... no warning appears and rotation works fine. ... That's why you got a warning and a plot, ...
    (comp.graphics.apps.gnuplot)
  • Re: Zero values cause errors on log scale
    ... If these zero values are "real" then a log scale is not appropriate, ... the warning is telling you something important. ... If these zero values ... plotting the data as received from SQL, ...
    (microsoft.public.excel.charting)
  • Re: 3d trajectory problem
    ... I get an error message concerning pm3d. ... no warning appears and rotation works fine. ... That's why you got a warning and a plot, ...
    (comp.graphics.apps.gnuplot)
  • Re: SP2 Wont Install
    ... in the partial log posted here neither FATAL or WARNING appear. ... 0xf00d from the log to an 0x80242008 error code. ... On the Edit menu, click Find, and then search for the specific error message. ... Additionally, if you do not know which error occurred on the Windows update Web site or you want to find more information about a failure by Automatic Updates, search for the following key words: ...
    (microsoft.public.windowsupdate)