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.

Unfortunately, Excel does not have an option to skip negative or zero values in a logarithmic scale chart. However, there are a few workarounds that your client can try:

1. Replace zero values with a small positive value: Your client can replace the zero values in the data with a small positive value, such as 0.1 or 0.01. This will allow the data to be plotted on a logarithmic scale without affecting the overall shape of the chart.
   
2. Use a secondary axis: Your client can create a secondary axis for the data with zero values. This will allow the data to be plotted on a linear scale while the rest of the data is plotted on a logarithmic scale. To do this, your client can follow these steps:
   
   1. Select the data series with zero values.
   2. Right-click and select "Format Data Series."
   3. In the "Series Options" tab, select "Secondary Axis."
   4. Adjust the axis scales as needed.
3. Use a different chart type: Your client can try using a different chart type, such as a linear scale chart or a stacked bar chart. This may be a better option if the zero values are important to the overall analysis.

I hope these suggestions help your client find a solution that works for their needs. Let me know if you have any other questions or if there's anything else I can help with.

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),A20),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/
_______


Joelle_Smith wrote:

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.