How to disable annoying log warnings?

[Ric]

Hi,

I frequently need to change the axis of charts to logarithmic or back to
linear. If there are data value of zero I get the following error:

Negative or zero values cannot be plotted correctly on log charts. Only
positive values can be interpreted on a logarithmic scale. To correct the
problem, do one of the following:

etc, etc, etc...

OK

which pops up every time I modify anything in the chart or the source data.
I'm perfectly aware that I can't plot a 0 on a log axis and am perfectly ok
with Excel simply not plotting those values (which is what it does). But I
hate have to dismiss that warning every time I do anything. Is there any way
to disable this dialogue?

Thanks,
Ric

[Peltier]

Hey Ric:

I don't know about disabling the warning. But you could change the
negative or zero values. I assume you have formulas that produce these
numbers, since you would already have changed whatever values are in the
cells. Change your formulas using this syntax, where [my formula] is
the original formula:

=IF(ISERROR(LOG([my formula])),NA(),[my formula])

The NA() produces a #N/A error in the cell, which the chart doesn't care
about. It will just omit those points.

- Jon
_______

[Ric]

For convenience sake I end up copying chunks from different areas on several
sheets/workbooks to a new spreadsheet where I can plot collected data. To
avoid problems with relative cells I tend to just copy the values. I could
follow your suggestion or just remove the 0 values from the chart, but just
seems lot a lot extra work. Also, I sometimes alternate between the log
charts and linear charts, and it's nice to have the zeros to round off the
curves on the linear charts.

I discovered a neat little utility called "push the freakin button" that
lets you program it to autorespond to dialogue boxes. So using that I can at
least have those warning dialogues close automatically, but they still pop
up and make annoying beep before they're disposed off, so it would be nice
to disable the warning altogether...

Ric

"Peltier" <pel...@home.com> wrote in message
news:39A45BC0...@home.com...

[Ric]

Interesting suggestion... I may try that. Doesn't really resolve the problem
but a decent workaround.

Thanks again,
Ric

"Peltier" <pel...@home.com> wrote in message

news:39A5C197...@home.com...
> If there are only zeros, and no blanks, you can write a couple
> procedures and assign them to buttons. As soon as you paste the range,
> hit the button, then plot. To make the other kind of plot, hit the
> other button. Here's the code:
>
> Option Explicit
>
> Sub BlankToZero()
> ' Assign to button "Blank to Zero"
> Selection.SpecialCells(xlCellTypeBlanks).Value = 0
> End Sub
>
> Sub ZeroToBlank()
> ' Assign to button "Zero to Blank"
> Dim myCell As Range
> For Each myCell In Selection.Cells
> If myCell = 0 Then myCell.ClearContents
> Next
> End Sub
>
> Otherwise you're stuck pushin that freakin button!

[Peltier]

If there are only zeros, and no blanks, you can write a couple
procedures and assign them to buttons. As soon as you paste the range,
hit the button, then plot. To make the other kind of plot, hit the
other button. Here's the code:

Option Explicit

Sub BlankToZero()
' Assign to button "Blank to Zero"
Selection.SpecialCells(xlCellTypeBlanks).Value = 0
End Sub

Sub ZeroToBlank()
' Assign to button "Zero to Blank"
Dim myCell As Range
For Each myCell In Selection.Cells
If myCell = 0 Then myCell.ClearContents
Next
End Sub

Otherwise you're stuck pushin that freakin button!

- Jon

[Martin Brown]

Peltier wrote:

> If there are only zeros, and no blanks, you can write a couple
> procedures and assign them to buttons. As soon as you paste the range,
> hit the button, then plot. To make the other kind of plot, hit the
> other button. Here's the code:
>
> Option Explicit
>
> Sub BlankToZero()
> ' Assign to button "Blank to Zero"
> Selection.SpecialCells(xlCellTypeBlanks).Value = 0
> End Sub
>
> Sub ZeroToBlank()
> ' Assign to button "Zero to Blank"
> Dim myCell As Range
> For Each myCell In Selection.Cells
> If myCell = 0 Then myCell.ClearContents
> Next
> End Sub

I prefer for converting zeroes to empty cells.

Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

It's much faster than an explicit VBA loop on a large dataset...

> Otherwise you're stuck pushin that freakin button!

It is definitely a very annoying error message that ought to be made optional.

You can automate suitably housetrained behaviour with chart events.

Regards,
Martin Brown

[GraphMan]

In article <eK415nI...@cppssbbsa02.microsoft.com>,

"Ric" <ric...@hotmail.com> wrote:
>
> Hi,
>
> I frequently need to change the axis of charts to logarithmic or back
to
> linear. If there are data value of zero I get the following error:
>
> Negative or zero values cannot be plotted correctly on log charts.
Only
> positive values can be interpreted on a logarithmic scale. To
correct the
> problem, do one of the following:
>
> etc, etc, etc...
>
> OK
>
> which pops up every time I modify anything in the chart or the source
data.
> I'm perfectly aware that I can't plot a 0 on a log axis and am
perfectly ok
> with Excel simply not plotting those values (which is what it does).
But I
> hate have to dismiss that warning every time I do anything. Is there
any way
> to disable this dialogue?

I certainly agree that Excels' dialog box warning about Zero and
negative values is clunky and inconvenient. I just don't have a
suggestion how Excel might handle this situation better than it does.

Certainly, values =< zero are totally inappropriate on a log chart and
the system shouldn't simply ignore them without comment. Imagine
trying to do this the old fashioned way - plotting the graph with
pencil on a blank sheet of Log or Semi-Log paper. There is simply no
place to put the Zero values!

In truth table containing analogue measurements should never contain
Zeros. I realize they often do but what the zeros usually mean is that
the measured value was some very small number below the detectable
limits of the measuring device - but definitely NOT zero.

Pigs can't fly, zeros can't be correctly plotted on Log charts, and
sorry, I haven't a clue as to how one might disable the dialog box.

OK - what to do?

Stop arguing with mother nature and eliminate the zeros (and negative
numbers) that don't belong. It is my opinion that the <= zero values
should all be replaced with the small, positive non-zero value that
represents the detection limit of the measurement system.

That's my humble opinion. I would love to hear what a real
statistician would have to say about this subject.

-Graphman

Sent via Deja.com http://www.deja.com/
Before you buy.

[Ric]

What's a chart event? And how does one use it?

Thanks,
Ric

"Martin Brown" <martin...@pandora.be> wrote in message
news:39A61C8A...@pandora.be...

>
> Peltier wrote:
>
> > If there are only zeros, and no blanks, you can write a couple
> > procedures and assign them to buttons. As soon as you paste the range,
> > hit the button, then plot. To make the other kind of plot, hit the
> > other button. Here's the code:
> >
> > Option Explicit
> >
> > Sub BlankToZero()
> > ' Assign to button "Blank to Zero"
> > Selection.SpecialCells(xlCellTypeBlanks).Value = 0
> > End Sub
> >
> > Sub ZeroToBlank()
> > ' Assign to button "Zero to Blank"
> > Dim myCell As Range
> > For Each myCell In Selection.Cells
> > If myCell = 0 Then myCell.ClearContents
> > Next
> > End Sub
>

> I prefer for converting zeroes to empty cells.
>
> Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
> SearchOrder:=xlByRows, MatchCase:=False
>
> It's much faster than an explicit VBA loop on a large dataset...
>

> > Otherwise you're stuck pushin that freakin button!
>

[Ric]

Well, I'm not a real statistician... but in my case the problem arises from
the fact that the function being plotted is not naturally logarithmic. It's
really more of a hyperbola, but it's convenient to represent the x-axis as
logarithmic as this allows data of different orders of magnitude to be
displayed on the same graph with the salient features still visible. The
hyperbola is transformed into a sigmoid, where the inflection point
represents one of the function's parameters. While the zero is appropriate
for the hyperbola, plotted on a linear axis, there's simply no way to
represent it on a log axis. You could use some arbitrarily small number to
represent zero, but in the end it doesn't really add anything to the
interpretation of the graph so it's easier to just ignore it.

The error given by Excel is correct and appropriate. It just doesn't take
into account situations like mine. It would just be nice if it gave you an
option to ignore the error in future and have Excel just automatically
ignore zeros for you. There are some dialogues that have a box you can check
to disable future warnings. I wish this was one of them.

But seeing as there doesn't seem to be a way to disable them, I'll either
live with them or convert my zeros to blanks when plotting this way...

Thanks,
Ric

"GraphMan" <tbar...@hotmail.com> wrote in message
news:8o5s7h$h6k$1...@nnrp1.deja.com...

[Peltier]

I would have just nuked all the zeroes, but Ric likes to keep them for
non-log plots.

- Jon
_______

Martin Brown wrote:

>
> Peltier wrote:
>
> > If there are only zeros, and no blanks, you can write a couple
> > procedures and assign them to buttons. As soon as you paste the range,
> > hit the button, then plot. To make the other kind of plot, hit the
> > other button. Here's the code:
> >
> > Option Explicit
> >
> > Sub BlankToZero()
> > ' Assign to button "Blank to Zero"
> > Selection.SpecialCells(xlCellTypeBlanks).Value = 0
> > End Sub
> >
> > Sub ZeroToBlank()
> > ' Assign to button "Zero to Blank"
> > Dim myCell As Range
> > For Each myCell In Selection.Cells
> > If myCell = 0 Then myCell.ClearContents
> > Next
> > End Sub
>

> I prefer for converting zeroes to empty cells.
>
> Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
> SearchOrder:=xlByRows, MatchCase:=False
>
> It's much faster than an explicit VBA loop on a large dataset...
>

> > Otherwise you're stuck pushin that freakin button!
>