Problem with logarithmic axis on a graph in Excel 1

[yalamo]

I have a spreadsheet which contains an X-Y graph where the Y axis is logarithmic. Since I'm constantly adding data points to the graph, I defined the source data series with more cells than data points, so I could add data points in the future without having to redefine the series each time. In other words, there are a lot of cells (below the already defined data points) that are blank.

It seems that Excel gets very grouchy about this. Whenever I save the spreadsheet, a message box pops up saying:

"Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logasrithmic scale. To correct the problem..."

If I click OK, it goes ahead and saves the spreadsheet. Excel obviously thinks that the blank cells have a value of zero, and feels aggrieved that it has to save that kind of a spreadsheet.

Does anyone know how I can get rid of the annoying message box? (The corrections it suggests for the problem are useless.)

 

[Zathras]

Excel 2003 doesn't seem to have that problem.

You could upgrade.

Alternatively you could try to fill the unused cells with the formula
[tt]
=NA()
[/tt]
That should do it for you.

 

[yalamo]

I really don't think it's worthwhile to upgrade to Excell2003 (I have Excel2000).

Filling cells with NA() doesn't fix the problem. Any other ideas?

 

[Zathras]

Hmmm. There must be more to this than you have told us. I just did a test with Excel 97 and did not experience the error message. The worksheet saved and re-opened just fine with blank cells in the data range of a log graph. It's possible Excel2000 has a bug that is not in 97 nor 3000, but I doubt it.

Try starting small to see at which point the problem arises. I just put values in B2:E2 and created a log graph over the range B2:H2, and saved it with no error message.

If all else fails, start a new post with the subject "Excel 2000 log graph problem" -- Perhaps someone with Excel 2000 will pick up on it and be able to help you.

The more specific you can be when stating a problem, the better the odds of finding a solution.

 

[Zathras]

I can cause the error message to appear if I put a blank (i.e. a space) in one of the cells in the graphing range.

So when you say the cells are blank, are they really blank or do they have one or more space characters in them? If so, then they are not really blank.

 

[yalamo]

That may be the problem. I blanked all the "blank" cells in the data series, and I haven'd seen the message box after saving the spreadsheet twice.

So thanks, Zathras, and a star.