Home |
Search |
Today's Posts |
#1
|
|||
|
|||
allow user to disable annoying warning dialog on log charts
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/comm...excel.charting |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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/ _______ Roger wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offer a feature to disable the sort warning in Excel 2003 | Excel Discussion (Misc queries) | |||
Disable Query Refresh warning dialog box in Excel 2003 | Excel Discussion (Misc queries) | |||
disable sort warning | Excel Discussion (Misc queries) | |||
Macro to pause for user input in dialog box | Excel Discussion (Misc queries) | |||
How do you disable save file dialog? | Setting up and Configuration of Excel |