negative/zero values on logarithmic plot warning

[Guest]

Hello all,

I realize that a negative or a zero plot on a logarithmic scale is
meaningless. However, many times I'm working with data that is full of noise
an non-ideal, i.e. it contains zeros and/or negative values. I still would
like to plot them on a logarithmic scale. Excel does this quite well and
ignores the negative/zero values on the chart. However, IT PRODUCES A
WARNING EVERYTIME I TOUCH THE CHART! I make tons and tons of these graphs,
so in order to save what little sanity I have remaining, I must find a way to
turn this warning off. Please help me.

 

[Ed Ferrero]

Hi,

Try using a helper column with a formula like =IF(D6<=0,NA(),D6) and plot
that.
i.e. replace zero and negative values with #N/A

Ed Ferrero
www.edferrero.com

 

[Guest]

Hello Ed and I plot spectra;

I had a series that needed to be plotted along one axis. The numbers also
frequently came up with numbers that were Zero or (different)Larger than they
should be. This is the workaround that I came up with.

=IF(AND($B$141=1,G142>0),'Proctor Examinations'!H28,F143)

This sets the value appropriately if it calculates appropriately, but sets
it to the preceeding value if it is inaccurate. It effectively removed the
incorrectly calculated or (more likely) poorly input information out of the
loop. This kept my charts accurate and everyone loved me( better that sliced
bread, he he).

God Bless

Frank Pytel

 

[Martin Brown]

Hello all,

I realize that a negative or a zero plot on a logarithmic scale is
meaningless. However, many times I'm working with data that is full of noise
an non-ideal, i.e. it contains zeros and/or negative values. I still would
like to plot them on a logarithmic scale. Excel does this quite well and
ignores the negative/zero values on the chart. However, IT PRODUCES A
WARNING EVERYTIME I TOUCH THE CHART! I make tons and tons of these graphs,
so in order to save what little sanity I have remaining, I must find a way to
turn this warning off. Please help me.

It has to be one of the most irritating Excel error messages ever.
There should be a tick box somewhere to say "don't show this fault
again in this worksheet". There are many circumstances where a log
graph is useful for viewing wide dynamic range data, but small
negative offsets or noise in the baseline ellicits this error.

The simplest fix if you have all positive data with zeroes is to
select the column and do a search and replace all for 0 replacing with
empty cell. Sadly there is no equivalent unambiguous quick fix for
negative values, although swapping "-" for "%" might be adequate and
unambiguous if your negative baseline values are less than 100.

Regards,
Martin Brown

 

[Guest]

Thank you. This apparently is a problem that microsoft should fix. Yes, it
is always possible to modify the data in a column to remove this error, but
more often than not that is actually more bothersome than clicking the error
away for the data handling I do.