allow user to disable annoying warning dialog on log charts

[Roger]

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?
--
Roger Stout, PE
Senior Research Scientist
ON Semiconductor

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...1cf13c8ef7&dg=microsoft.public.excel.charting

 

[Tushar Mehta]

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

 

[Roger]

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

 

[Jon Peltier]

Roger -

You may have noticed the lack of enhancements to the charting module in
Excel over the past N versions. Asking why Microsoft hasn't fixed the
log chart warning is like asking why they haven't fixed the
numbering/bullets in a Word forum; well, no, it's not quite as violent
as that. But they have other important things to conquer, like XML.

The easiest way to deal with two sets of data is to put the charting
data onto another sheet in the same configuration as on the original
data sheet. This way, adjustments for the size of the range are as easy
as you can get, given the 1:1 correspondence between the two sheets.
Where the original sheet has a bad value, the chart data sheet has a
formula that returns #N/A. The original data can still be used for
subsequent calculations. The display data should optimally be even a
third sheet, formatted for human eyes.

Sure it seems wasteful to have three sheets for the same data, but each
sheet is used for a different purpose: tabular display, graphical
display, and numerical processing. Worksheets aren't nearly as expensive
as they used to be, in terms of bits and bytes and RAM. Use a little
more worksheet real estate to make your life easier. Hide the extra
sheets if you're worried about appearances.

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