Zero values in a log chart

[Joelle_Smith]

I am trouble-shooting for an internal client. When he has zero values in a
log chart, he gets the following message:
"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: Enter only positive values (greater than
zero) in the cells used to create the chart, OR In the chart, click the axis
you want to change. On the Format menu, click Selected Axis. Click the Scale
tab, and then clear the Logarithmic Scale check box."

He wants to keep the logarithmic scale and would prefer a 3rd option in the
above message to be "Skip the negative or zero values"). He is getting his
data from another source, so re-formatting or modifying the information in
the table (to maybe a Null or blank value) to plot this way in the chart
needs to be a very simple task.

Does anyone have any good ideas around this.

 

[Jon Peltier]

Joelle -

This way's pretty easy. With the X and Y data in columns A and B, and column headers
in row 1, enter this formula in C2, and fill it to column D and as far down the
columns as needed:

=IF(AND(ISNUMBER(A2),A2>0),A2,NA())

Make the chart with columns C and D.

This removes negative and zero numerical values and any non numerical values from
the charted range.

If you have lots of charts already made and it would be a hardship to carry out the
above process, you could select the source data and run the following macro:

Sub LogEnableData()
Dim c As Range
Dim a As Range

If TypeName(Selection) = "Range" Then
For Each a In Selection.Areas
For Each c In a.Cells
If IsNumeric(c.Value) Then
If c.Value <= 0 Then
c.Value = CVErr(xlErrNA)
End If
Else
c.Value = CVErr(xlErrNA)
End If
Next
Next
End If
End Sub

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