Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Does anyone know how to create the notches in the vertical box whisker
charts? An example can be found in figure 10 of the following article: http://www.qualitydigest.com/oct97/html/excel.html Thank you!! 
#2
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Hi,
The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. Cheers Andy wrote: Does anyone know how to create the notches in the vertical box whisker charts? An example can be found in figure 10 of the following article: http://www.qualitydigest.com/oct97/html/excel.html Thank you!! 
#3
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
On Jun 15, 8:47 am, Andy Pope wrote:
Hi, The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. Cheers Andy wrote: Does anyone know how to create the notches in the verticalboxwhisker charts? An example can be found in figure 10 of the following article: http://www.qualitydigest.com/oct97/html/excel.html Thank you!! Hide quoted text   Show quoted text  Can you elaborate on the technique? 
#4
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Use this set of xy pairs to draw a notched version
X Y Min 1 3 Min 1 5 Min 1 4 25th 3 4 25th 3 7 TopNotchLeft 5.342592593 7 TopNotchMid 5.5 6.5 TopNotchRight 5.657407407 7 75th 8 7 75th 8 1 BottomNotchRight 5.657407407 1 BottomNotchMid 5.5 1.5 BottomNotchLeft 5.342592593 1 25th 3 1 25th 3 4 Skip Median 5.5 1.5 Median 5.5 6.5 Skip 75th 8 4 Max 10 4 Max 10 5 Max 10 3 The formula for X value of NotchLeft is =MEDIAN($A$1:$A$50)(1.7*((1.25*(PERCENTILE($A$1:$A$50,0.75)PERCENTILE($A$1:$A$50,0.25))/(1.35*50)))) The formula for X value of NotchRight is =MEDIAN($A$1:$A$50)+(1.7*((1.25*(PERCENTILE($A$1:$ A$50,0.75)PERCENTILE($A$1:$A$50,0.25))/(1.35*50)))) All other formula are as William W. Dorner's example. Cheers Andy wrote: On Jun 15, 8:47 am, Andy Pope wrote: Hi, The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. Cheers Andy wrote: Does anyone know how to create the notches in the verticalboxwhisker charts? An example can be found in figure 10 of the following article: http://www.qualitydigest.com/oct97/html/excel.html Thank you!! Hide quoted text   Show quoted text  Can you elaborate on the technique? 
#5
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
On Jun 16, 8:27 am, Andy Pope wrote:
Use this set of xy pairs to draw a notched version X Y Min 1 3 Min 1 5 Min 1 4 25th 3 4 25th 3 7 TopNotchLeft 5.342592593 7 TopNotchMid 5.5 6.5 TopNotchRight 5.657407407 7 75th 8 7 75th 8 1 BottomNotchRight 5.657407407 1 BottomNotchMid 5.5 1.5 BottomNotchLeft 5.342592593 1 25th 3 1 25th 3 4 Skip Median 5.5 1.5 Median 5.5 6.5 Skip 75th 8 4 Max 10 4 Max 10 5 Max 10 3 The formula for X value of NotchLeft is =MEDIAN($A$1:$A$50)(1.7*((1.25*(PERCENTILE($A$1:$A$50,0.75)PERCENTILE($A$*1:$A$50,0.25))/(1.35*50)))) The formula for X value of NotchRight is =MEDIAN($A$1:$A$50)+(1.7*((1.25*(PERCENTILE($A$1:$ A$50,0.75)PERCENTILE($A$*1:$A$50,0.25))/(1.35*50)))) All other formula are as William W. Dorner's example. Cheers Andy wrote: On Jun 15, 8:47 am, Andy Pope wrote: Hi, The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. Cheers Andy wrote: Does anyone know how to create the notches in the verticalboxwhisker charts? An example can be found in figure 10 of the following article: http://www.qualitydigest.com/oct97/html/excel.html Thank you!! Hide quoted text   Show quoted text  Can you elaborate on the technique? Hide quoted text   Show quoted text  You ROCK!!! 
#6
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
On Sat, 16 Jun 2007, in microsoft.public.excel.charting,
Andy Pope said: The formula for X value of NotchLeft is =MEDIAN($A$1:$A$50)(1.7*((1.25*(PERCENTILE($A$1:$A$50,0.75)PERC ENTILE($A$1:$A$50,0.25))/(1.35*50)))) The formula for X value of NotchRight is =MEDIAN($A$1:$A$50)+(1.7*((1.25*(PERCENTILE($A$1: $A$50,0.75)PERC ENTILE($A$1:$A$50,0.25))/(1.35*50)))) Any particular reason for preferring PERCENTILE(<range,0.75)PERCENTILE(<range,0.25) to QUARTILE(<range,3)QUARTILE(<range,1) ? (I'm such a fond user of quartiles that I sometimes use them instead on MIN, MAX and MEDIAN, because the five values are so simple to copy down a column next to the numbers 04)  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#7
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
On Fri, 15 Jun 2007, in microsoft.public.excel.charting,
Andy Pope said: The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. While I have as much reverence for the late John Tukey as the next person, I don't see that boxes and whiskers as such are necessary these days, except that they're a familiar idiom that the graph viewer will usually recognise. And even that isn't true for notched boxes, which I don't think many people have seen. Certainly most couldn't interpret without them a guide; I never even knew until reading that article just now what the notches were supposed to represent I thought they were just meant to enphasise the median in some way. If we abandon the need to copy Tukey's shapes, doing this stuff in Excel immediately gets a lot easier. Here's my idea of a boxless "box" and whisker distribution chart, with circled outliers and an error range around the median, all just using the standard Excel symbol shapes. http://i146.photobucket.com/albums/r264/del_c/ infographics/not_boxplot.gif It would be simple to substitute circles, diamonds, or halfticks, and alter the thickness or colour of the Excel error bars, to suit your preferences, and I think the point comes across even though they're not the traditional boxes.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#8
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Hi Del,
No preference just using the same formula as the example the OP was having problems with. Cheers Andy  Andy Pope, Microsoft MVP  Excel http://www.andypope.info "Del Cotter" wrote in message ... On Sat, 16 Jun 2007, in microsoft.public.excel.charting, Andy Pope said: The formula for X value of NotchLeft is =MEDIAN($A$1:$A$50)(1.7*((1.25*(PERCENTILE($A$1:$A$50,0.75)PERC ENTILE($A$1:$A$50,0.25))/(1.35*50)))) The formula for X value of NotchRight is =MEDIAN($A$1:$A$50)+(1.7*((1.25*(PERCENTILE($A$1 :$A$50,0.75)PERC ENTILE($A$1:$A$50,0.25))/(1.35*50)))) Any particular reason for preferring PERCENTILE(<range,0.75)PERCENTILE(<range,0.25) to QUARTILE(<range,3)QUARTILE(<range,1) ? (I'm such a fond user of quartiles that I sometimes use them instead on MIN, MAX and MEDIAN, because the five values are so simple to copy down a column next to the numbers 04)  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#9
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
On Tue, 19 Jun 2007, in microsoft.public.excel.charting,
Andy Pope said: No preference just using the same formula as the example the OP was having problems with. Oops! I hadn't noticed the Excel formula example in the article; I thought you were starting from scratch following the principles in the article. Sorry.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#10
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Del 
Your chart allows plenty of different quantities to be shown, but I suspect it may become cluttered, and at least for now, it's unfamiliar, and forces a lot of back and forth between the chart and the legend. Don't knock a "familiar idiom". The box plot is pretty much selfexplanatory especially since it is familiar, and the difference between the box itself and the whiskers is immediately recognizable (compared to your multiple error bars colored different shades of gray, which is slower to be interpreted). If you could make whiskers of various line lengths, that might help. I agree that the notched box plot must be rather obscure, as I've never seen it used in any real display of information.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Fri, 15 Jun 2007, in microsoft.public.excel.charting, Andy Pope said: The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. While I have as much reverence for the late John Tukey as the next person, I don't see that boxes and whiskers as such are necessary these days, except that they're a familiar idiom that the graph viewer will usually recognise. And even that isn't true for notched boxes, which I don't think many people have seen. Certainly most couldn't interpret without them a guide; I never even knew until reading that article just now what the notches were supposed to represent I thought they were just meant to enphasise the median in some way. If we abandon the need to copy Tukey's shapes, doing this stuff in Excel immediately gets a lot easier. Here's my idea of a boxless "box" and whisker distribution chart, with circled outliers and an error range around the median, all just using the standard Excel symbol shapes. http://i146.photobucket.com/albums/r264/del_c/ infographics/not_boxplot.gif It would be simple to substitute circles, diamonds, or halfticks, and alter the thickness or colour of the Excel error bars, to suit your preferences, and I think the point comes across even though they're not the traditional boxes.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#11
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Duh, every now and then even proved experts (even with a degree in
statistics) say something that leaves me deeply perplexed. Less than five minutes of googling reveals these five fine examples:  Exhibit 1 (electronics / mobile phone manufacturing, 1996) http://ieeexplore.ieee.org/iel3/4031...rnumber=561268  Exhibit 2 (experimental / cognitive psychology, 1996) http://faculty.washington.edu/jmiyam...f%20pref. pdf  Exhibit 3 (physical anthropology / monkeys, 2002) http://www.hopkinsmedicine.org/FAE/CBR2002AJPA.pdf  Exhibit 4 (entomology / PhD thesis on honeybee parasites, 1994) [I mean, I've published in truly numerous fields of medicine, psychology, statistics, computer science, nuclear physics, physiotherapy, management, phylosophy and more and what not, but is this topic exotic or what?!] http://doc.rero.ch/lm.php?url=1000,4...se_RickliM.pdf  Exhibit 5 (a physicist teaching maths presenting grade distribution at an exam, 2006/7) [trust me from plenty of experience with such people that being a physicist and/or teaching mathematics otherwise tends to preclude knowledge and understanding of statistics] http://www.maths.qmul.ac.uk/~ob/MAS2...docs/stats.pdf [if URLs are broken across lines, please put them together in your browser] Note that I've selected only freely downloadable publications, while from an academic institution with subscription to various online services from major scientific publishers there are literaly dozens more readily available examples! Of course, your definition of "real display of information" might exclude any kind of scientific or even technical publication, thus meaning only "business" stuff and the general press. (Though I sincerly hope that it is not what you meant.) In that case, my objection should be disregarded. Regards, Gaj Vidmar, PhD Univ. of Ljubljana, Fac. of Medicine, Inst. of Biomedical Informatics "Jon Peltier" wrote in message ... Del  Your chart allows plenty of different quantities to be shown, but I suspect it may become cluttered, and at least for now, it's unfamiliar, and forces a lot of back and forth between the chart and the legend. Don't knock a "familiar idiom". The box plot is pretty much selfexplanatory especially since it is familiar, and the difference between the box itself and the whiskers is immediately recognizable (compared to your multiple error bars colored different shades of gray, which is slower to be interpreted). If you could make whiskers of various line lengths, that might help. I agree that the notched box plot must be rather obscure, as I've never seen it used in any real display of information.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Fri, 15 Jun 2007, in microsoft.public.excel.charting, Andy Pope said: The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. While I have as much reverence for the late John Tukey as the next person, I don't see that boxes and whiskers as such are necessary these days, except that they're a familiar idiom that the graph viewer will usually recognise. And even that isn't true for notched boxes, which I don't think many people have seen. Certainly most couldn't interpret without them a guide; I never even knew until reading that article just now what the notches were supposed to represent I thought they were just meant to enphasise the median in some way. If we abandon the need to copy Tukey's shapes, doing this stuff in Excel immediately gets a lot easier. Here's my idea of a boxless "box" and whisker distribution chart, with circled outliers and an error range around the median, all just using the standard Excel symbol shapes. http://i146.photobucket.com/albums/r264/del_c/ infographics/not_boxplot.gif It would be simple to substitute circles, diamonds, or halfticks, and alter the thickness or colour of the Excel error bars, to suit your preferences, and I think the point comes across even though they're not the traditional boxes.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#12
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Gaj 
I'm not an entomologist, nor have I read much on the anthropology of monkeys. I've worked in scientific research as a metallurgist (for my doctorate and a dozen years of employment following that), and as an engineer in manufacturing. Maybe not the widest mathematical background, and I'm not degreed in statistics (though I've taken a graduate level course or three). I've encountered thousands of box and whisker charts and their variants, but I've never seen a notched box chart used in the heat of battle.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Gaj Vidmar" wrote in message ... Duh, every now and then even proved experts (even with a degree in statistics) say something that leaves me deeply perplexed. Less than five minutes of googling reveals these five fine examples:  Exhibit 1 (electronics / mobile phone manufacturing, 1996) http://ieeexplore.ieee.org/iel3/4031...rnumber=561268  Exhibit 2 (experimental / cognitive psychology, 1996) http://faculty.washington.edu/jmiyam...f%20pref. pdf  Exhibit 3 (physical anthropology / monkeys, 2002) http://www.hopkinsmedicine.org/FAE/CBR2002AJPA.pdf  Exhibit 4 (entomology / PhD thesis on honeybee parasites, 1994) [I mean, I've published in truly numerous fields of medicine, psychology, statistics, computer science, nuclear physics, physiotherapy, management, phylosophy and more and what not, but is this topic exotic or what?!] http://doc.rero.ch/lm.php?url=1000,4...se_RickliM.pdf  Exhibit 5 (a physicist teaching maths presenting grade distribution at an exam, 2006/7) [trust me from plenty of experience with such people that being a physicist and/or teaching mathematics otherwise tends to preclude knowledge and understanding of statistics] http://www.maths.qmul.ac.uk/~ob/MAS2...docs/stats.pdf [if URLs are broken across lines, please put them together in your browser] Note that I've selected only freely downloadable publications, while from an academic institution with subscription to various online services from major scientific publishers there are literaly dozens more readily available examples! Of course, your definition of "real display of information" might exclude any kind of scientific or even technical publication, thus meaning only "business" stuff and the general press. (Though I sincerly hope that it is not what you meant.) In that case, my objection should be disregarded. Regards, Gaj Vidmar, PhD Univ. of Ljubljana, Fac. of Medicine, Inst. of Biomedical Informatics "Jon Peltier" wrote in message ... Del  Your chart allows plenty of different quantities to be shown, but I suspect it may become cluttered, and at least for now, it's unfamiliar, and forces a lot of back and forth between the chart and the legend. Don't knock a "familiar idiom". The box plot is pretty much selfexplanatory especially since it is familiar, and the difference between the box itself and the whiskers is immediately recognizable (compared to your multiple error bars colored different shades of gray, which is slower to be interpreted). If you could make whiskers of various line lengths, that might help. I agree that the notched box plot must be rather obscure, as I've never seen it used in any real display of information.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Fri, 15 Jun 2007, in microsoft.public.excel.charting, Andy Pope said: The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. While I have as much reverence for the late John Tukey as the next person, I don't see that boxes and whiskers as such are necessary these days, except that they're a familiar idiom that the graph viewer will usually recognise. And even that isn't true for notched boxes, which I don't think many people have seen. Certainly most couldn't interpret without them a guide; I never even knew until reading that article just now what the notches were supposed to represent I thought they were just meant to enphasise the median in some way. If we abandon the need to copy Tukey's shapes, doing this stuff in Excel immediately gets a lot easier. Here's my idea of a boxless "box" and whisker distribution chart, with circled outliers and an error range around the median, all just using the standard Excel symbol shapes. http://i146.photobucket.com/albums/r264/del_c/ infographics/not_boxplot.gif It would be simple to substitute circles, diamonds, or halfticks, and alter the thickness or colour of the Excel error bars, to suit your preferences, and I think the point comes across even though they're not the traditional boxes.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#13
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Duh, duh, exhibits ignored, personal defensive stance taken, while my aim
was quite the opposite (I thought it was obvious, but apparently ...). Anyway, my point was and is that notched boxplots *ARE* used in "real display of information", "heat of the battle" or whatever one calls that  at least every now and then. To add to the ignored exhibits (deliberately as diverse as I could find  I thought it was obvious that wide as my interest and experience may be, neither am I an entomologist or anthropologist, but apparently ...), they are not exceedingly rare in medical articles. Related to that, I remember MedCalc (www.medcalc.be, a cliniciannotstatistician oriented package) being praised in a software review in a medical journal for producing them. And Minitab produces them also, which has a reputation of "canning" only the actually used methods rather than as many as possible in comparison to some other stats packages. Anyway, precisely the attention of Excel experts (Pope, Cotter, Peltier; be the attitude positive or negative) or even my own rant in an Excel forum might make them less "obscure" for the "general public". Now, if that does eventually happen, a further point worth mentioning is the problem with what the whole point of the notches is, i.e., visually assessing whether medians are [statistically significantly] different. Namely, just like with error bars representing confidence interval for mean, once you compare more than two samples, you run into the problem of multiple comparisons ... A publicly available reference from statistics education (BTW, full of notched boxplots :o) mentioning this is http://www.amstat.org/publications/j...2/garrett.html (Note that the main point is comparing variability rather then centre, so the variable being plotted in Fig. 2 is absolute deviation from the median; the key quote is "Groups for which the boxplot notch intervals do not overlap are likely different in variability. (Here we encounter once again the multiple comparison issue.)" So, to summarise, if the aim is inference, perhaps the notches should be appropriately <shortened to compensate for multiple comparisons, i.e., prevent inflated type I error ('false alarms' in layman terms). Sort of like the basic idea of Analysis of Means (ANOM) as a graphical alternative to ANOVA. Anyway, this is an Excel forum, not a statistical one (though, fortunately, with precisely those experts I mentioned above, and some others, "under the surface" actually making it mainly quite statistically sound). So, please, Dr. Peltier, Mr. Cotter and others, take this just as praise and inspiration for your knowledge and talents and good will to take notched boxplots into account, and perhaps also ANOM. To push thins further, both are, IMHO, among the many candidates for an ambitious projects of a publicly available Excel "charting" addin (perhaps we should buzzcall it InfoVis addin) ... Especially with Excel 2007 still leaving so many thing to be desried in this department ...  I know that you, Dr. Peltier, have already done *A LOT* of great work in this direction, but I'm just abusing this oportunity to ask you (and others  Mr. Cinquegrani comes to my mind first, and also Mr. O'Day) to think how much time and funding this would take.  Namely, for a while I've been baldly thinking of an applied scientific project (with EU funding, which can be seriously substantial; you <do most of the work, I <take care of the scientific references and pompous justification to actually get the project), and even Microsoft support is not unrealistic ...  If you think it's an uterly silly idea, or that the last thing you need is my advice and cooperation, please, let me know, and likewise if you think it's not so silly. It might even happen at some point after you will have finished and successfully marketed your commercial addin for a while  the project and funding could perhaps just make the addin more comprehensive and compensate for making it public domain. Anyway, enough of digressions. Please, take the things I write utterly benevolently  like they are *ALWAYS* meant. Cordial regrads, Gaj "Jon Peltier" wrote in message ... Gaj  I'm not an entomologist, nor have I read much on the anthropology of monkeys. I've worked in scientific research as a metallurgist (for my doctorate and a dozen years of employment following that), and as an engineer in manufacturing. Maybe not the widest mathematical background, and I'm not degreed in statistics (though I've taken a graduate level course or three). I've encountered thousands of box and whisker charts and their variants, but I've never seen a notched box chart used in the heat of battle.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Gaj Vidmar" wrote in message ... Duh, every now and then even proved experts (even with a degree in statistics) say something that leaves me deeply perplexed. Less than five minutes of googling reveals these five fine examples:  Exhibit 1 (electronics / mobile phone manufacturing, 1996) http://ieeexplore.ieee.org/iel3/4031...rnumber=561268  Exhibit 2 (experimental / cognitive psychology, 1996) http://faculty.washington.edu/jmiyam...f%20pref. pdf  Exhibit 3 (physical anthropology / monkeys, 2002) http://www.hopkinsmedicine.org/FAE/CBR2002AJPA.pdf  Exhibit 4 (entomology / PhD thesis on honeybee parasites, 1994) [I mean, I've published in truly numerous fields of medicine, psychology, statistics, computer science, nuclear physics, physiotherapy, management, phylosophy and more and what not, but is this topic exotic or what?!] http://doc.rero.ch/lm.php?url=1000,4...se_RickliM.pdf  Exhibit 5 (a physicist teaching maths presenting grade distribution at an exam, 2006/7) [trust me from plenty of experience with such people that being a physicist and/or teaching mathematics otherwise tends to preclude knowledge and understanding of statistics] http://www.maths.qmul.ac.uk/~ob/MAS2...docs/stats.pdf [if URLs are broken across lines, please put them together in your browser] Note that I've selected only freely downloadable publications, while from an academic institution with subscription to various online services from major scientific publishers there are literaly dozens more readily available examples! Of course, your definition of "real display of information" might exclude any kind of scientific or even technical publication, thus meaning only "business" stuff and the general press. (Though I sincerly hope that it is not what you meant.) In that case, my objection should be disregarded. Regards, Gaj Vidmar, PhD Univ. of Ljubljana, Fac. of Medicine, Inst. of Biomedical Informatics "Jon Peltier" wrote in message ... Del  Your chart allows plenty of different quantities to be shown, but I suspect it may become cluttered, and at least for now, it's unfamiliar, and forces a lot of back and forth between the chart and the legend. Don't knock a "familiar idiom". The box plot is pretty much selfexplanatory especially since it is familiar, and the difference between the box itself and the whiskers is immediately recognizable (compared to your multiple error bars colored different shades of gray, which is slower to be interpreted). If you could make whiskers of various line lengths, that might help. I agree that the notched box plot must be rather obscure, as I've never seen it used in any real display of information.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Fri, 15 Jun 2007, in microsoft.public.excel.charting, Andy Pope said: The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. While I have as much reverence for the late John Tukey as the next person, I don't see that boxes and whiskers as such are necessary these days, except that they're a familiar idiom that the graph viewer will usually recognise. And even that isn't true for notched boxes, which I don't think many people have seen. Certainly most couldn't interpret without them a guide; I never even knew until reading that article just now what the notches were supposed to represent I thought they were just meant to enphasise the median in some way. If we abandon the need to copy Tukey's shapes, doing this stuff in Excel immediately gets a lot easier. Here's my idea of a boxless "box" and whisker distribution chart, with circled outliers and an error range around the median, all just using the standard Excel symbol shapes. http://i146.photobucket.com/albums/r264/del_c/ infographics/not_boxplot.gif It would be simple to substitute circles, diamonds, or halfticks, and alter the thickness or colour of the Excel error bars, to suit your preferences, and I think the point comes across even though they're not the traditional boxes.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#14
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
On Tue, 19 Jun 2007, in microsoft.public.excel.charting,
Jon Peltier said: Your chart allows plenty of different quantities to be shown, but I suspect it may become cluttered, Possibly, but the example I showed was bound to look a little cluttered compared to a simple pair of notched boxes, due to the sheer number of data points on that graph, and my not taking the trouble to clean up the legend. This version looks less cluttered, I hope. http://i146.photobucket.com/ albums/r264/del_c/infographics/not_boxplot2.gif and at least for now, it's unfamiliar, and forces a lot of back and forth between the chart and the legend. Don't knock a "familiar idiom". Very true.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#15
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
If we are introducing different visuals for the error of the median, why not
retain the "familiar idiom" of the box chart, and merely add a visual to represent the error. This could be a line across the bar (like your red markers in either version of your chart) or some other type of marker. It avoids reinvention of the entire wheel.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Tue, 19 Jun 2007, in microsoft.public.excel.charting, Jon Peltier said: Your chart allows plenty of different quantities to be shown, but I suspect it may become cluttered, Possibly, but the example I showed was bound to look a little cluttered compared to a simple pair of notched boxes, due to the sheer number of data points on that graph, and my not taking the trouble to clean up the legend. This version looks less cluttered, I hope. http://i146.photobucket.com/ albums/r264/del_c/infographics/not_boxplot2.gif and at least for now, it's unfamiliar, and forces a lot of back and forth between the chart and the legend. Don't knock a "familiar idiom". Very true.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#16
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Hi Gaj 
I took what you said as cordial, and was just trying to indicate that while such features as notches in a box plot might be useful and common in some disciplines, my experience did not include such features. I must need to adjust my medications, however, as a few times in the past month or so I've been accused of being defensive or offensive, and that is never my intention. I apologize for the brusqueness of my post. The idea of constructing a large utility to overcome the shortfalls within Excel has occurred to me. But it is a rather large undertaking, and I have only been able to whittle away in my spare time at its edges, with my tutorials and more recently with a handful of utilities. I have given some thought to notched bar charts in the past despite never using them. Andy's technique to draw the outlines of such a chart with an XY series is interesting, but how does one fill the outline? I have also thought of using trapezoids as a custom series fill for two additional series in the chart. However, the nice matching of custom shapes that I remember from Excel 97 has become a more fuzzy matchup in recent releases (in 2003 and I'm sure more so in 2007). So I've put my ideas on the back burner. Emails from a dozen users of my Box and Whisker utility have requested the display of outliers, while none have mentioned the notched bars. So in fact, I've got the outlier feature mostly coded but not debugged, so it's not yet ready for serious work.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Gaj Vidmar" wrote in message ... Duh, duh, exhibits ignored, personal defensive stance taken, while my aim was quite the opposite (I thought it was obvious, but apparently ...). Anyway, my point was and is that notched boxplots *ARE* used in "real display of information", "heat of the battle" or whatever one calls that  at least every now and then. To add to the ignored exhibits (deliberately as diverse as I could find  I thought it was obvious that wide as my interest and experience may be, neither am I an entomologist or anthropologist, but apparently ...), they are not exceedingly rare in medical articles. Related to that, I remember MedCalc (www.medcalc.be, a cliniciannotstatistician oriented package) being praised in a software review in a medical journal for producing them. And Minitab produces them also, which has a reputation of "canning" only the actually used methods rather than as many as possible in comparison to some other stats packages. Anyway, precisely the attention of Excel experts (Pope, Cotter, Peltier; be the attitude positive or negative) or even my own rant in an Excel forum might make them less "obscure" for the "general public". Now, if that does eventually happen, a further point worth mentioning is the problem with what the whole point of the notches is, i.e., visually assessing whether medians are [statistically significantly] different. Namely, just like with error bars representing confidence interval for mean, once you compare more than two samples, you run into the problem of multiple comparisons ... A publicly available reference from statistics education (BTW, full of notched boxplots :o) mentioning this is http://www.amstat.org/publications/j...2/garrett.html (Note that the main point is comparing variability rather then centre, so the variable being plotted in Fig. 2 is absolute deviation from the median; the key quote is "Groups for which the boxplot notch intervals do not overlap are likely different in variability. (Here we encounter once again the multiple comparison issue.)" So, to summarise, if the aim is inference, perhaps the notches should be appropriately <shortened to compensate for multiple comparisons, i.e., prevent inflated type I error ('false alarms' in layman terms). Sort of like the basic idea of Analysis of Means (ANOM) as a graphical alternative to ANOVA. Anyway, this is an Excel forum, not a statistical one (though, fortunately, with precisely those experts I mentioned above, and some others, "under the surface" actually making it mainly quite statistically sound). So, please, Dr. Peltier, Mr. Cotter and others, take this just as praise and inspiration for your knowledge and talents and good will to take notched boxplots into account, and perhaps also ANOM. To push thins further, both are, IMHO, among the many candidates for an ambitious projects of a publicly available Excel "charting" addin (perhaps we should buzzcall it InfoVis addin) ... Especially with Excel 2007 still leaving so many thing to be desried in this department ...  I know that you, Dr. Peltier, have already done *A LOT* of great work in this direction, but I'm just abusing this oportunity to ask you (and others  Mr. Cinquegrani comes to my mind first, and also Mr. O'Day) to think how much time and funding this would take.  Namely, for a while I've been baldly thinking of an applied scientific project (with EU funding, which can be seriously substantial; you <do most of the work, I <take care of the scientific references and pompous justification to actually get the project), and even Microsoft support is not unrealistic ...  If you think it's an uterly silly idea, or that the last thing you need is my advice and cooperation, please, let me know, and likewise if you think it's not so silly. It might even happen at some point after you will have finished and successfully marketed your commercial addin for a while  the project and funding could perhaps just make the addin more comprehensive and compensate for making it public domain. Anyway, enough of digressions. Please, take the things I write utterly benevolently  like they are *ALWAYS* meant. Cordial regrads, Gaj "Jon Peltier" wrote in message ... Gaj  I'm not an entomologist, nor have I read much on the anthropology of monkeys. I've worked in scientific research as a metallurgist (for my doctorate and a dozen years of employment following that), and as an engineer in manufacturing. Maybe not the widest mathematical background, and I'm not degreed in statistics (though I've taken a graduate level course or three). I've encountered thousands of box and whisker charts and their variants, but I've never seen a notched box chart used in the heat of battle.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Gaj Vidmar" wrote in message ... Duh, every now and then even proved experts (even with a degree in statistics) say something that leaves me deeply perplexed. Less than five minutes of googling reveals these five fine examples:  Exhibit 1 (electronics / mobile phone manufacturing, 1996) http://ieeexplore.ieee.org/iel3/4031...rnumber=561268  Exhibit 2 (experimental / cognitive psychology, 1996) http://faculty.washington.edu/jmiyam...f%20pref. pdf  Exhibit 3 (physical anthropology / monkeys, 2002) http://www.hopkinsmedicine.org/FAE/CBR2002AJPA.pdf  Exhibit 4 (entomology / PhD thesis on honeybee parasites, 1994) [I mean, I've published in truly numerous fields of medicine, psychology, statistics, computer science, nuclear physics, physiotherapy, management, phylosophy and more and what not, but is this topic exotic or what?!] http://doc.rero.ch/lm.php?url=1000,4...se_RickliM.pdf  Exhibit 5 (a physicist teaching maths presenting grade distribution at an exam, 2006/7) [trust me from plenty of experience with such people that being a physicist and/or teaching mathematics otherwise tends to preclude knowledge and understanding of statistics] http://www.maths.qmul.ac.uk/~ob/MAS2...docs/stats.pdf [if URLs are broken across lines, please put them together in your browser] Note that I've selected only freely downloadable publications, while from an academic institution with subscription to various online services from major scientific publishers there are literaly dozens more readily available examples! Of course, your definition of "real display of information" might exclude any kind of scientific or even technical publication, thus meaning only "business" stuff and the general press. (Though I sincerly hope that it is not what you meant.) In that case, my objection should be disregarded. Regards, Gaj Vidmar, PhD Univ. of Ljubljana, Fac. of Medicine, Inst. of Biomedical Informatics "Jon Peltier" wrote in message ... Del  Your chart allows plenty of different quantities to be shown, but I suspect it may become cluttered, and at least for now, it's unfamiliar, and forces a lot of back and forth between the chart and the legend. Don't knock a "familiar idiom". The box plot is pretty much selfexplanatory especially since it is familiar, and the difference between the box itself and the whiskers is immediately recognizable (compared to your multiple error bars colored different shades of gray, which is slower to be interpreted). If you could make whiskers of various line lengths, that might help. I agree that the notched box plot must be rather obscure, as I've never seen it used in any real display of information.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Fri, 15 Jun 2007, in microsoft.public.excel.charting, Andy Pope said: The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. While I have as much reverence for the late John Tukey as the next person, I don't see that boxes and whiskers as such are necessary these days, except that they're a familiar idiom that the graph viewer will usually recognise. And even that isn't true for notched boxes, which I don't think many people have seen. Certainly most couldn't interpret without them a guide; I never even knew until reading that article just now what the notches were supposed to represent I thought they were just meant to enphasise the median in some way. If we abandon the need to copy Tukey's shapes, doing this stuff in Excel immediately gets a lot easier. Here's my idea of a boxless "box" and whisker distribution chart, with circled outliers and an error range around the median, all just using the standard Excel symbol shapes. http://i146.photobucket.com/albums/r264/del_c/ infographics/not_boxplot.gif It would be simple to substitute circles, diamonds, or halfticks, and alter the thickness or colour of the Excel error bars, to suit your preferences, and I think the point comes across even though they're not the traditional boxes.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#17
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
On Wed, 20 Jun 2007, in microsoft.public.excel.charting,
Jon Peltier said: If we are introducing different visuals for the error of the median, why not retain the "familiar idiom" of the box chart, and merely add a visual to represent the error. This could be a line across the bar (like your red markers in either version of your chart) or some other type of marker. It avoids reinvention of the entire wheel. My intention was to give the people who need to click on the Wizard the opportunity to construct a graph that portrays what they want, and doesn't require changing series chart types and so on. I don't want to get into an argument about this; I don't have anything against the traditional box as such. I just say it's not absolutely necessary. I do have something against notches, though, and I've worked out what it is: I always got the impression that the width of the waist meant something, which is why I was so surprised to finally find out that it was all just about the height of the notch, not how deeply it cuts into the box. Because the notches were straight lines, they looked like this: < and had different angles for different heights. To counteract this, I may see if I can use my limited artistic skills to design an AutoShape or group of Autoshapes a bit like this: } { and substitute it for the central bar of a stacked bar chart. The idea is that because the vertical lines will stay vertical for all values, it will be more obvious that the information being presented is all about heights, and widths should be ignored. I'm hoping that the final visual effect will be intuitively obvious to people used to traditional notches, and not cause them any confusion.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#18
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Del 
My intention was to give the people who need to click on the Wizard the opportunity to construct a graph that portrays what they want, and doesn't require changing series chart types and so on. I don't want to get into an argument about this; I don't have anything against the traditional box as such. I just say it's not absolutely necessary. I do have something against notches, though, and I've worked out what it is: I always got the impression that the width of the waist meant something, which is why I was so surprised to finally find out that it was all just about the height of the notch, not how deeply it cuts into the box. Because the notches were straight lines, they looked like this: < and had different angles for different heights. To counteract this, I may see if I can use my limited artistic skills to design an AutoShape or group of Autoshapes a bit like this: } { and substitute it for the central bar of a stacked bar chart. The idea is that because the vertical lines will stay vertical for all values, it will be more obvious that the information being presented is all about heights, and widths should be ignored. I'm hoping that the final visual effect will be intuitively obvious to people used to traditional notches, and not cause them any confusion. I don't mean to be arguing, I was thinking of an alternative to the notch that fits within the constraints of the box chart. I'm not wild about the notch construction, and your observation about the width and depth of the notch touches on my unfamiliarity with it. I had in mind a version with a new rectangle in the middle of the stack to replace the notch, which keeps the focus on heights and not on widths. I understand now your intention to give users a choice not to hassle with the extensive mechanics of box chart creation. You're right: the boxes are not required. The challenge as always is to display the information clearly.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ 
#19
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
On Wed, 20 Jun 2007, in microsoft.public.excel.charting,
Jon Peltier said: I do have something against notches, though, and I've worked out what it is: I always got the impression that the width of the waist meant something, which is why I was so surprised to finally find out that it was all just about the height of the notch, not how deeply it cuts into the box. I'm not wild about the notch construction, and your observation about the width and depth of the notch touches on my unfamiliarity with it. I had in mind a version with a new rectangle in the middle of the stack to replace the notch, which keeps the focus on heights and not on widths. Yes! new rectangle in the middle of the stack would work for me, with ticks at the centre point of that to mark the median.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. 
#20
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
On Jun 19, 8:26 am, "Jon Peltier"
wrote: Del  Yourchartallows plenty of different quantities to be shown, but I suspect it may become cluttered, and at least for now, it's unfamiliar, and forces a lot of back and forth between thechartand the legend. Don't knock a "familiar idiom". Theboxplot is pretty much selfexplanatory especially since it is familiar, and the difference between theboxitself and the whiskers is immediately recognizable (compared to your multiple error bars colored different shades of gray, which is slower to be interpreted). If you could make whiskers of various line lengths, that might help. I agree that the notchedboxplot must be rather obscure, as I've never seen it used in any real display of information.  Jon  Jon Peltier,MicrosoftExcelMVP Tutorials and Custom Solutions Peltier Technical Services, Inc. http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Fri, 15 Jun 2007, inmicrosoft.public.excel.charting, Andy Pope said: The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. While I have as much reverence for the late John Tukey as the next person, I don't see that boxes and whiskers as such are necessary these days, except that they're a familiar idiom that the graph viewer will usually recognise. And even that isn't true for notched boxes, which I don't think many people have seen. Certainly most couldn't interpret without them a guide; I never even knew until reading that article just now what the notches were supposed to represent I thought they were just meant to enphasise the median in some way. If we abandon the need to copy Tukey's shapes, doing this stuff inExcel immediately gets a lot easier. Here's my idea of a boxless "box" and whiskerdistributionchart, with circled outliers and an error range around the median, all just using the standardExcelsymbol shapes. http://i146.photobucket.com/albums/r264/del_c/ infographics/not_boxplot.gif It would be simple to substitute circles, diamonds, or halfticks, and alter the thickness or colour of theExcelerror bars, to suit your preferences, and I think the point comes across even though they're not the traditional boxes.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. Hide quoted text   Show quoted text  Wow!! I did not know my initial request generated such an exchange of academic and technical passion. Just to let you know, I have decided that if I need to generate a significant number of notched box whisker charts, I am going to obtain the latest version of SigmaPlot (v 10 being the latest version) in order to generate them. Jon, if your addin can generate notched box whisker plots to the scope and degree that SigmaPlot can, please let me know!!! Bruce 
#21
Posted to microsoft.public.excel.charting




Creating notches in box whisker plots in Microsoft Excel
Nope, I have not ever tried to generate notched box plots. There is no easy
way in an Excel chart to integrate a nonrectangular shape into the chart series. I will give it further thought, of course, since other people find it a more important feature than I thought it was. If you want to show outliers as individual points outside the span of the whiskers, stay tuned, because an unreleased version of the utility can handle this.  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc.  http://PeltierTech.com _______ wrote in message ps.com... On Jun 19, 8:26 am, "Jon Peltier" wrote: Del  Yourchartallows plenty of different quantities to be shown, but I suspect it may become cluttered, and at least for now, it's unfamiliar, and forces a lot of back and forth between thechartand the legend. Don't knock a "familiar idiom". Theboxplot is pretty much selfexplanatory especially since it is familiar, and the difference between theboxitself and the whiskers is immediately recognizable (compared to your multiple error bars colored different shades of gray, which is slower to be interpreted). If you could make whiskers of various line lengths, that might help. I agree that the notchedboxplot must be rather obscure, as I've never seen it used in any real display of information.  Jon  Jon Peltier,MicrosoftExcelMVP Tutorials and Custom Solutions Peltier Technical Services, Inc. http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Fri, 15 Jun 2007, inmicrosoft.public.excel.charting, Andy Pope said: The technique describes how to use an xyscatter to construct the boxes. You need to add a few more xy pairs in order to reduce the width of the Median line and form the notches. While I have as much reverence for the late John Tukey as the next person, I don't see that boxes and whiskers as such are necessary these days, except that they're a familiar idiom that the graph viewer will usually recognise. And even that isn't true for notched boxes, which I don't think many people have seen. Certainly most couldn't interpret without them a guide; I never even knew until reading that article just now what the notches were supposed to represent I thought they were just meant to enphasise the median in some way. If we abandon the need to copy Tukey's shapes, doing this stuff inExcel immediately gets a lot easier. Here's my idea of a boxless "box" and whiskerdistributionchart, with circled outliers and an error range around the median, all just using the standardExcelsymbol shapes. http://i146.photobucket.com/albums/r264/del_c/ infographics/not_boxplot.gif It would be simple to substitute circles, diamonds, or halfticks, and alter the thickness or colour of theExcelerror bars, to suit your preferences, and I think the point comes across even though they're not the traditional boxes.  Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder please send your email to del3 instead. Hide quoted text   Show quoted text  Wow!! I did not know my initial request generated such an exchange of academic and technical passion. Just to let you know, I have decided that if I need to generate a significant number of notched box whisker charts, I am going to obtain the latest version of SigmaPlot (v 10 being the latest version) in order to generate them. Jon, if your addin can generate notched box whisker plots to the scope and degree that SigmaPlot can, please let me know!!! Bruce 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Creating notches in box whisker plots in Microsoft Excel  Excel Discussion (Misc queries)  
Creating notches in box whisker plots  Excel Discussion (Misc queries)  
How can you create Box Plots (BoxandWhisker Plots) in Excel?  Charts and Charting in Excel  
Boxandwhisker chart (box plots)  Charts and Charting in Excel  
how do I create box and whisker plots in Excel?  Charts and Charting in Excel 