Tushar,

Thanks for the suggestion. That's pretty much what I've used as a
work-around when the message gets *most* annoying, but obviously it requires
that I continuously modify my worksheets of data by adding columns of copies
of the data into new cells via the formula, and plotting the formula results
as opposed to the original data. (And since it's real lab data, there's
always something different in the number or rows or columns of data in each
set, so even automating it in worksheet form to accomodate a reasonable
variety of likely layouts is a lot of work.) Also I end up with other
subsequent formulas that don't do exactly what I want when they encounter
NA(); so then I end up filtering with ISERR and ISERROR and other kludges.
One difficulty compounds into another, when it would be so much simpler to
have the charts simply ignore the data in the first place without a warning.

I noticed that you or someone posted an answer to somebody else's question
on disabling a spell-checker alert, using VBA. I'm not a VBA programmer
(yet, but I'm getting pushed that way!), but your answer here may provide yet
another push: any chance there's an object attribute or something (a la that
spell checker alert) that would enable you to run a little VBA macro and turn
off that specific chart alert, or failing that, a whole class of alerts that
might happen to include that negative-value chart alert?

As for mudslinging, I would certainly restrain myself from that unless I
somehow found out that you, personally, were responsible for Microsoft's
choice of handling that alert! Your reponse makes me curious, however, about
how often this topic comes up? You imply that it happens often, but the
reason I made a new post was because no search I've ever done has turned up
any prior history on the matter. If it comes up as often as all that, why
hasn't Microsoft made any adjustment? I guess they just do what they do,
regardless of whether it addresses a common complaint?

Anyway, thanks again,
Roger
----
Roger Stout, PE
Senior Research Scientist
ON Semiconductor


"Tushar Mehta" wrote:

Please note that every time this topic comes up, the 'discussion'
quickly degenerates into a mud-slinging fest. So, if you have the urge
to attack me personally do keep in mind that I am posting this to help
you and you are free to reject the suggestions.

Given the number of requests for something like this feature hopefully
MS will consider it. Also, I don't know what options you have
considered and rejected. However, you may want to look at:

Consider a plot on a log scale of the foll. data starting from A1:

01-Jan -1
02-Jan 0
03-Jan 1
04-Jan 2
05-Jan -1
06-Jan 100
07-Jan 1000

There are two ways to do this. First, in C1 enter the formula =IF(B1<=
0,NA(),B1). Copy this down to cover all rows with data. Now, plot
columns A and C.

The second option is to use a named formula. For my test, I used
PlotVals =IF(Sheet1!$B$1:$B$7<=0,NA(),Sheet1!$B$1:$B$7)
Now, plot column A and the named formula PlotVals.

If you have data that are missing, XL handles that in one of two
ways. If a cell is truly empty (not just a zero length string such as
""), select the chart, then Tools | Options... | Chart tab. Select how
XL should handle missing points.

The second option is to simply put NA() in the cells you don't want XL
to plot.

Finally, you could use VBA code to sanitize the data before plotting.

Granted, none of them are as easy as clicking on a chart. But, that's
what avaiable right now.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I know perfectly well that I can't plot negative values on a log chart. That
doesn't mean that my data doesn't have negative numbers in it. For years
I've been trying to outsmart Excel so that it effectively ignores negative
data on log plots, but it seems to me that it should simply happen (like it
does in most other mathematical tools). If the user isn't smart enough to
know that he can't plot negative numbers on a log scale, well, warn him by
default, if you must, but allow the skilled user to disable the message! It
is incredibly annoying, and it reduces my productivity by 100% when I'm
analyzing very typical data in thermal tests.

I can turn off just about anything other automatic stuff else that bothers
me (like converting cells with "@" into email addresses, for crying out loud
- that took me a long time to figure out how to disable). What's wrong with
a "don't show me this again" checkbox on the "Negative values cannot be
plotted correctly on a log chart." warning dialog? What's wrong with simply
ignoring the negative data and not plotting it, just like you do with #NA and
#VALUE and all those other unplottable values?

I don't want to have to build my own log charts that ignore negative numbers
(I could, but what's the point of a built-in log chart if you can't use it).
I don't want to have to write formulas for cells to make the offending
negative value "invisible" to the chart (I've tried dozens of ways, and they
all fail to be user-friendly at some level, like making OTHER dependent
formulas fail gracelessly). What I want is to tell Excel that I'm smart
enough to know that when the chart has a hole in it, it's because it couldn't
plot the data. What's so hard about that?

This has frustrated me for years. Can you tell?