Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 15th 07, 12:27 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 6
Default 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   Report Post  
Old June 15th 07, 01:47 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,489
Default 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   Report Post  
Old June 15th 07, 11:53 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 6
Default 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   Report Post  
Old June 16th 07, 01:27 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,489
Default 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   Report Post  
Old June 18th 07, 12:23 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 6
Default 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   Report Post  
Old June 18th 07, 10:15 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 560
Default 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   Report Post  
Old June 18th 07, 11:16 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 560
Default 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   Report Post  
Old June 19th 07, 08:51 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,489
Default 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   Report Post  
Old June 19th 07, 09:18 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 560
Default 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   Report Post  
Old June 19th 07, 01:26 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default 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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating notches in box whisker plots in Microsoft Excel [email protected] Excel Discussion (Misc queries) 1 June 17th 07 08:55 PM
Creating notches in box whisker plots [email protected] Excel Discussion (Misc queries) 0 June 14th 07 07:20 PM
How can you create Box Plots (Box-and-Whisker Plots) in Excel? Amy Bass Charts and Charting in Excel 3 December 28th 06 01:18 AM
Box-and-whisker chart (box plots) James Charts and Charting in Excel 2 April 3rd 06 01:14 AM
how do I create box and whisker plots in Excel? Gary Greeno Charts and Charting in Excel 2 November 10th 05 08:22 AM


All times are GMT +1. The time now is 11:59 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017