If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
Creating notches in box whisker plots in Microsoft Excel
Hi,
The technique describes how to use an xy-scatter 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
|
|||
|
|||
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 xy-scatter 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
|
|||
|
|||
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 xy-scatter 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
|
|||
|
|||
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 xy-scatter 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
|
|||
|
|||
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 0-4) -- 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
|
|||
|
|||
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 xy-scatter 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 half-ticks, 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
|
|||
|
|||
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 0-4) -- 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
|
|||
|
|||
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
|
|||
|
|||
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 self-explanatory 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 xy-scatter 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 half-ticks, 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. |
Thread Tools | |
Display Modes | |
|
|