Zero values cause errors on log scale

[Scott Doyle]

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.

 

[Jerry W. Lewis]

log(0) = -infinity

If these zero values are "real" then a log scale is not appropriate, and
the warning is telling you something important. 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)

Jerry

 

[Martin Brown]

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.

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,

 

[Jerry W. Lewis]

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.

snip


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.

Provided it is acceptable to equate 0 and 1. For countinuous data
admitting values between 0 and 1, it would be nonsense since it would
not preserve order relationships.

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.

That is essentially the effect of my original suggestion

The chart treats #N/A values as though they were not there. Connecting
lines do not break at #N/A, so if you want connecting lines to break,
then deleting 0's (as you suggested) is the only way.

Jerry

Jerry

 

[Bernard Liengme]

I am not sure what chemical experiment would give ion concentrations in the
range 0 to 10^9.

 

[Tushar Mehta]

Ah, it's a lost cause. Those who want XL to treat log(0) as zero will
probably never change their opinion. Of course, I suspect that if XL
ever did implement that 'feature,' some in the same crowd -- or in some
other crowd -- would be falling over each other trying to be the first
and the loudest to point out that MS can't get basic math right.

One very straightforward option is to transform the data and then plot
on a 'normal' y-axis. Now, zeros can be handled as one sees fit and
non-zero values would be mapped to log().

But, even if one insists on plotting the data set and setting the y-
axis to a log scale...

Just redefine 0=1 and the data will plot with log(0)=log(1)=0. Or if
one wants to interpolate over the zeros, use NA().

It's *so* simple with defined formulas. Below are *four* different
ways to get XL to 'handle' zeros in a log scale plot.

DataSet =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
DatasetSansZeros =IF(DataSet<>0,DataSet)

The first name defines the dataset. I chose a dynamic definition so
that the graph adjusts as one adds/subtracts data at the *bottom* of
the existing data set.

The 2nd is a work name that simplifies some downstream formulae.

ZerosAsNAs =IF(DataSet=0,NA(),DataSet)
ZerosAsOnes =IF(DataSet=0,1,DataSet)
ZerosAsSmallestDiv2 =IF(DataSet=0,MIN(DatasetSansZeros)/2,DataSet)
ZeroSpikes =IF(DataSet=0,MIN(DatasetSansZeros)/10,NA())

The first two should be self evident. The third, ZerosAsSmallestDiv2
plots zeros as the smallest non-zero value divided by 2. Obviously,
one can pick any desired value.

Now, plot either ZerosAsNAs, ZerosAsOnes, or ZerosAsSmallestDiv2 as the
y-values with the y-axis set to a log scale. For more on how to use
names in chart see Names in Charts (http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html)

ZeroSpikes plots *only* zero values and should be used in conjunction
with one of the other three. Effectively, it 'highlights' the zeros.
This series should be plotted with a marker and no line. A variant
would be to replace the MIN()/10 with 1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Obviously the 0 represents concentrations below the sensitivity of the
measurement device. Perhaps the OP should take Tushar's example

ZerosAsSmallestDiv2 =IF(DataSet=0,MIN(DatasetSansZeros)/2,DataSet)

but divide the minimum detectable level by 2.

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