edmund dot r at gmail dot com

This is a HTML post.

I've searched high and low for a simple Excel box-plot that can do outliers, does not require drawing each line individually and can have more than one box in the plot. I could not find one and so devised one myself. All it needs is the basic summary data and once you create you can save it to your Custom Charts so next time its just a matter of selecting it like a normal chart.

Create Box Plot

EXCEL 2003
Open High Low Close
Q1 Upper Tail Lower Tail Q3 Med Group Med Outlier Group Outliers
Chicken 40 99 9 70 1 54 1 5
Fish 70 217.5 22.5 135 2 120 1 105
2 10
2 227
2 233


You only need to create the box plot once since you can save it to your user-defined custom chart types.
1) Create Open-High-Low-Close chart with the blue higlighted group (must select series in columns in the chart setup).
2) After chart is created add the median series (only the yellow, not x-values) to the the chart.
3) Select the median series in the chart and change it to the secondary axis: Select the top point of an upper tail, this will probably be Series 2. Then use the left or right arrow keys to move to the next series until Series 5 is selected; change the axis. Also, set the secondary axis scale equal to the primary axis.
4) Select the chart and with the chart selected open the Excel Visual Basic Editor. In the Immediate window change the "Series 5" plot type to Scatter Plot by entering;
ActiveChart.SeriesCollection(5).Type = -4196
Then press enter.
5) Return to the chart. Update Series 5 "X-values" in the Source Data|Series dialog with the median group values.
6) Change the "Series 5" plot pattern to no line and and the dashed symbol.
7) Add Series 5 X Error Bars in both directions of a fixed lenght of 0.195 with no end marker and matching the stlye and weight to that of the boxes. If you can see the end bars of these then adjust the width of the chart slightly until they fall in line.
8) Add the outliers series (only the pink, not x-values) to the chart
9) Select the outlier series in the chart, Series 6, and change it to the secondary axis.
10) Change Series 6 to Scatter Plot and add "X-Values" with outlier group as done with Series 5.
11) Change the outlier plot pattern to no line and the symbol you want to use for the outliers.
12) Be sure each axis is large enough to show the outliers and that the axis are equal to each other. Also, it maybe best to switch off Auto scaling for the min and max of both axis.
13) Select the chart and save it to your user-defined custom charts. Navigate to Chart|Chart Type|Custom Types. Then select the User-defined option and click add. See next for data setup for the box plot.

Use Box Plot

EXCEL 2003
Categories Q1 Upper Tail Lower Tail Q3 Med Outliers Outlier Group
Blue 40 99 9 70 54 5 1
Purple 70 217.5 22.5 135 120 105 1
Red 195 236 176 215 210 10 2
227 2
233 2
239 3


1) Arrange your data as above (you can have more or less categories and outliers).
2) Select the highlighted area and select to create a box plot from your user-defined custom charts. Be sure to select series in columns in the chart setup. Note that the outliers will not be in the correct group but this is corrected in the next step. Also you may have to change the scale of both y-axis for the data.
3) In the Source Data|Series dialog select Series 6 and select the Outlier Group (the last column) for its X-values and the Outliers (the second last column) for the Y-values.
4) Box plot!


edmund dot r at gmail dot com

Chicken Fish Plot from Create Box Plot


"Edmund" wrote in message ...
edmund dot r at gmail dot com

This is a HTML post.

I've searched high and low for a simple Excel box-plot that can do outliers, does not require drawing each line individually and can have more than one box in the plot. I could not find one and so devised one myself. All it needs is the basic summary data and once you create you can save it to your Custom Charts so next time its just a matter of selecting it like a normal chart.

Create Box Plot

EXCEL 2003
Open High Low Close
Q1 Upper Tail Lower Tail Q3 Med Group Med Outlier Group Outliers
Chicken 40 99 9 70 1 54 1 5
Fish 70 217.5 22.5 135 2 120 1 105
2 10
2 227
2 233


You only need to create the box plot once since you can save it to your user-defined custom chart types.
1) Create Open-High-Low-Close chart with the blue higlighted group (must select series in columns in the chart setup).
2) After chart is created add the median series (only the yellow, not x-values) to the the chart.
3) Select the median series in the chart and change it to the secondary axis: Select the top point of an upper tail, this will probably be Series 2. Then use the left or right arrow keys to move to the next series until Series 5 is selected; change the axis. Also, set the secondary axis scale equal to the primary axis.
4) Select the chart and with the chart selected open the Excel Visual Basic Editor. In the Immediate window change the "Series 5" plot type to Scatter Plot by entering;
ActiveChart.SeriesCollection(5).Type = -4196
Then press enter.
5) Return to the chart. Update Series 5 "X-values" in the Source Data|Series dialog with the median group values.
6) Change the "Series 5" plot pattern to no line and and the dashed symbol.
7) Add Series 5 X Error Bars in both directions of a fixed lenght of 0.195 with no end marker and matching the stlye and weight to that of the boxes. If you can see the end bars of these then adjust the width of the chart slightly until they fall in line.
8) Add the outliers series (only the pink, not x-values) to the chart
9) Select the outlier series in the chart, Series 6, and change it to the secondary axis.
10) Change Series 6 to Scatter Plot and add "X-Values" with outlier group as done with Series 5.
11) Change the outlier plot pattern to no line and the symbol you want to use for the outliers.
12) Be sure each axis is large enough to show the outliers and that the axis are equal to each other. Also, it maybe best to switch off Auto scaling for the min and max of both axis.
13) Select the chart and save it to your user-defined custom charts. Navigate to Chart|Chart Type|Custom Types. Then select the User-defined option and click add. See next for data setup for the box plot.

Use Box Plot

EXCEL 2003
Categories Q1 Upper Tail Lower Tail Q3 Med Outliers Outlier Group
Blue 40 99 9 70 54 5 1
Purple 70 217.5 22.5 135 120 105 1
Red 195 236 176 215 210 10 2
227 2
233 2
239 3


1) Arrange your data as above (you can have more or less categories and outliers).
2) Select the highlighted area and select to create a box plot from your user-defined custom charts. Be sure to select series in columns in the chart setup. Note that the outliers will not be in the correct group but this is corrected in the next step. Also you may have to change the scale of both y-axis for the data.
3) In the Source Data|Series dialog select Series 6 and select the Outlier Group (the last column) for its X-values and the Outliers (the second last column) for the Y-values.
4) Box plot!


edmund dot r at gmail dot com

Colour plot from Use Blox Plot

"Edmund" wrote in message ...
edmund dot r at gmail dot com

This is a HTML post.

I've searched high and low for a simple Excel box-plot that can do outliers, does not require drawing each line individually and can have more than one box in the plot. I could not find one and so devised one myself. All it needs is the basic summary data and once you create you can save it to your Custom Charts so next time its just a matter of selecting it like a normal chart.

Create Box Plot

EXCEL 2003
Open High Low Close
Q1 Upper Tail Lower Tail Q3 Med Group Med Outlier Group Outliers
Chicken 40 99 9 70 1 54 1 5
Fish 70 217.5 22.5 135 2 120 1 105
2 10
2 227
2 233


You only need to create the box plot once since you can save it to your user-defined custom chart types.
1) Create Open-High-Low-Close chart with the blue higlighted group (must select series in columns in the chart setup).
2) After chart is created add the median series (only the yellow, not x-values) to the the chart.
3) Select the median series in the chart and change it to the secondary axis: Select the top point of an upper tail, this will probably be Series 2. Then use the left or right arrow keys to move to the next series until Series 5 is selected; change the axis. Also, set the secondary axis scale equal to the primary axis.
4) Select the chart and with the chart selected open the Excel Visual Basic Editor. In the Immediate window change the "Series 5" plot type to Scatter Plot by entering;
ActiveChart.SeriesCollection(5).Type = -4196
Then press enter.
5) Return to the chart. Update Series 5 "X-values" in the Source Data|Series dialog with the median group values.
6) Change the "Series 5" plot pattern to no line and and the dashed symbol.
7) Add Series 5 X Error Bars in both directions of a fixed lenght of 0.195 with no end marker and matching the stlye and weight to that of the boxes. If you can see the end bars of these then adjust the width of the chart slightly until they fall in line.
8) Add the outliers series (only the pink, not x-values) to the chart
9) Select the outlier series in the chart, Series 6, and change it to the secondary axis.
10) Change Series 6 to Scatter Plot and add "X-Values" with outlier group as done with Series 5.
11) Change the outlier plot pattern to no line and the symbol you want to use for the outliers.
12) Be sure each axis is large enough to show the outliers and that the axis are equal to each other. Also, it maybe best to switch off Auto scaling for the min and max of both axis.
13) Select the chart and save it to your user-defined custom charts. Navigate to Chart|Chart Type|Custom Types. Then select the User-defined option and click add. See next for data setup for the box plot.

Use Box Plot

EXCEL 2003
Categories Q1 Upper Tail Lower Tail Q3 Med Outliers Outlier Group
Blue 40 99 9 70 54 5 1
Purple 70 217.5 22.5 135 120 105 1
Red 195 236 176 215 210 10 2
227 2
233 2
239 3


1) Arrange your data as above (you can have more or less categories and outliers).
2) Select the highlighted area and select to create a box plot from your user-defined custom charts. Be sure to select series in columns in the chart setup. Note that the outliers will not be in the correct group but this is corrected in the next step. Also you may have to change the scale of both y-axis for the data.
3) In the Source Data|Series dialog select Series 6 and select the Outlier Group (the last column) for its X-values and the Outliers (the second last column) for the Y-values.
4) Box plot!


edmund dot r at gmail dot com

Edmund wrote:


It would have been nice to have a boxplot macro that calculates the
outliers automatically.

Here's how I do it.
a) install Jon Peltier's boxcharter (new and improved)
http://peltiertech.com/Excel/Charts/BoxWhisker.html

Setup a regular data matrix.
A couple rows below that setup the matrix with the outliers

Run the box charter on the regular data matrix -- it calculates all the
values that define the boxes and does the chart.

Then, just copy and paste the outlier data into the chart, format these
new data series as chart type: line chart, with data markers and no lines.

The problem with this is that it calculate the median without the
outliers included (so it only works if you have an even number of high
and low outliers).

And this works better with "inliers" (I just invented a term),
as you can see he
http://lilt.ilstu.edu/gmklass/pos138...s/image024.gif

or he
http://lilt.ilstu.edu/gmklass/pos138...s.htm#Boxplots






Colour plot from *_Use Blox Plot_*

"Edmund"
wrote in message
...
edmund dot r at gmail dot com

/This is a HTML post./

I've searched high and low for a simple Excel box-plot that can do
outliers, does not require drawing each line individually and can
have more than one box in the plot. I could not find one and so
devised one myself. All it needs is the basic summary data and once
you create you can save it to your Custom Charts so next time its
just a matter of selecting it like a normal chart.

*_Create Box Plot_*

EXCEL 2003
Open High Low Close
Q1 Upper Tail Lower Tail Q3 Med Group Med Outlier Group
Outliers
Chicken 40 99 9 70 1 54 1 5
Fish 70 217.5 22.5 135 2 120 1 105
2 10
2 227
2 233


*You only need to create the box plot once since you can save it to
your user-defined custom chart types.*
*1)* Create Open-High-Low-Close chart with the blue higlighted group
(*must select series in columns in the chart setup*).
*2)* After chart is created add the median series (only the yellow,
not x-values) to the the chart.
*3)* Select the median series in the chart and change it to the
secondary axis: Select the top point of an upper tail, this will
probably be Series 2. Then use the left or right arrow keys to move
to the next series until Series 5 is selected; change the axis.
Also, set the secondary axis scale equal to the primary axis.
*4)* Select the chart and with the chart selected open the Excel
Visual Basic Editor. In the Immediate window change the "Series 5"
plot type to Scatter Plot by entering;
ActiveChart.SeriesCollection(5).Type = -4196
Then press enter.
*5)* Return to the chart. Update Series 5 "X-values" in the Source
Data|Series dialog with the median group values.
*6)* Change the "Series 5" plot pattern to no line and and the
dashed symbol.
*7)* Add Series 5 X Error Bars in both directions of a fixed lenght
of 0.195 with no end marker and matching the stlye and weight to
that of the boxes. If you can see the end bars of these then adjust
the width of the chart slightly until they fall in line.
*8)* Add the outliers series (only the pink, not x-values) to the chart
*9)* Select the outlier series in the chart, Series 6, and change it
to the secondary axis.
*10)* Change Series 6 to Scatter Plot and add "X-Values"
with outlier group as done with Series 5.
*11)* Change the outlier plot pattern to no line and the symbol you
want to use for the outliers.
*12)* Be sure each axis is large enough to show the outliers and
that the axis are equal to each other. Also, it maybe best to switch
off Auto scaling for the min and max of both axis.
*13)* Select the chart and save it to your user-defined custom
charts. Navigate to Chart|Chart Type|Custom Types. Then select the
User-defined option and click add. /See next for data setup for the
box plot./
//
*_Use Box Plot_*

EXCEL 2003
Categories Q1 Upper Tail Lower Tail Q3 Med Outliers Outlier Group
Blue 40 99 9 70 54 5 1
Purple 70 217.5 22.5 135 120 105 1
Red 195 236 176 215 210 10 2
227 2
233 2
239 3


*1)* Arrange your data as above (you can have more or less
categories and outliers).
*2)* Select the highlighted area and select to create a box plot
from your user-defined custom charts. Be sure to select series in
columns in the chart setup. /Note that the outliers will not be in
the correct group but this is corrected in the next step. Also you
may have to change the scale of both y-axis for the data./
*3)* In the Source Data|Series dialog select Series 6 and select the
Outlier Group (the last column) for its X-values and the Outliers
(the second last column) for the Y-values.
*4)* /Box plot!/
//

edmund dot r at gmail dot com
//

I was brought up making box plots with the whisker length tied to the
interquartile spacing and outliers denoted by special points in the chart.
Once I tried making such a chart in Excel, but it was a long, tedious,
error-prone process, and I'd abandoned it. However, I've received a lot of
comments on my simple box charter utility, many of them suggesting the more
rigorous display features be incorporated.

I've revisited the issue, and my programming and charting skills have
increased substantially since my earlier attempts (in about 1998), so I've
started developing an improved version of the box charter utility. The
biggest challenge is making it dynamic with the original data. I don't know
when it will be available for testing, but it is definitely in the works.

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


"Gklass" wrote in message
...
Edmund wrote:


It would have been nice to have a boxplot macro that calculates the
outliers automatically.

Here's how I do it.
a) install Jon Peltier's boxcharter (new and improved)
http://peltiertech.com/Excel/Charts/BoxWhisker.html

Setup a regular data matrix.
A couple rows below that setup the matrix with the outliers

Run the box charter on the regular data matrix -- it calculates all the
values that define the boxes and does the chart.

Then, just copy and paste the outlier data into the chart, format these
new data series as chart type: line chart, with data markers and no lines.

The problem with this is that it calculate the median without the outliers
included (so it only works if you have an even number of high and low
outliers).

And this works better with "inliers" (I just invented a term),
as you can see he
http://lilt.ilstu.edu/gmklass/pos138...s/image024.gif

or he
http://lilt.ilstu.edu/gmklass/pos138...s.htm#Boxplots






Colour plot from *_Use Blox Plot_*

"Edmund"
wrote in message
...
edmund dot r at gmail dot com
/This is a HTML post./
I've searched high and low for a simple Excel box-plot that can do
outliers, does not require drawing each line individually and can
have more than one box in the plot. I could not find one and so
devised one myself. All it needs is the basic summary data and once
you create you can save it to your Custom Charts so next time its
just a matter of selecting it like a normal chart.
*_Create Box Plot_*
EXCEL 2003 Open High Low Close Q1 Upper Tail Lower Tail Q3 Med Group
Med Outlier Group
Outliers
Chicken 40 99 9 70 1 54 1 5
Fish 70 217.5 22.5 135 2 120 1 105
2 10
2 227
2 233
*You only need to create the box plot once since you can save it to
your user-defined custom chart types.*
*1)* Create Open-High-Low-Close chart with the blue higlighted group
(*must select series in columns in the chart setup*).
*2)* After chart is created add the median series (only the yellow,
not x-values) to the the chart.
*3)* Select the median series in the chart and change it to the
secondary axis: Select the top point of an upper tail, this will
probably be Series 2. Then use the left or right arrow keys to move
to the next series until Series 5 is selected; change the axis. Also,
set the secondary axis scale equal to the primary axis.
*4)* Select the chart and with the chart selected open the Excel
Visual Basic Editor. In the Immediate window change the "Series 5"
plot type to Scatter Plot by entering;
ActiveChart.SeriesCollection(5).Type = -4196
Then press enter.
*5)* Return to the chart. Update Series 5 "X-values" in the Source
Data|Series dialog with the median group values.
*6)* Change the "Series 5" plot pattern to no line and and the
dashed symbol.
*7)* Add Series 5 X Error Bars in both directions of a fixed lenght
of 0.195 with no end marker and matching the stlye and weight to
that of the boxes. If you can see the end bars of these then adjust
the width of the chart slightly until they fall in line.
*8)* Add the outliers series (only the pink, not x-values) to the
chart
*9)* Select the outlier series in the chart, Series 6, and change it
to the secondary axis.
*10)* Change Series 6 to Scatter Plot and add "X-Values"
with outlier group as done with Series 5.
*11)* Change the outlier plot pattern to no line and the symbol you
want to use for the outliers.
*12)* Be sure each axis is large enough to show the outliers and
that the axis are equal to each other. Also, it maybe best to switch
off Auto scaling for the min and max of both axis.
*13)* Select the chart and save it to your user-defined custom
charts. Navigate to Chart|Chart Type|Custom Types. Then select the
User-defined option and click add. /See next for data setup for the
box plot./
// *_Use Box Plot_*
EXCEL 2003 Categories Q1 Upper Tail Lower Tail Q3 Med Outliers
Outlier Group
Blue 40 99 9 70 54 5 1
Purple 70 217.5 22.5 135 120 105 1
Red 195 236 176 215 210 10 2
227 2
233 2
239 3

*1)* Arrange your data as above (you can have more or less
categories and outliers).
*2)* Select the highlighted area and select to create a box plot
from your user-defined custom charts. Be sure to select series in
columns in the chart setup. /Note that the outliers will not be in
the correct group but this is corrected in the next step. Also you
may have to change the scale of both y-axis for the data./
*3)* In the Source Data|Series dialog select Series 6 and select the
Outlier Group (the last column) for its X-values and the Outliers
(the second last column) for the Y-values.
*4)* /Box plot!/
// edmund dot r at gmail dot com
//

Gklass - that is a cute box-plot addin but it lacks a major feature;
outliers. Also, standard convention is whiskers of length 1.5IQR. Your plot
does min and max. It's cute but not a statisticians choice.


"Gklass" wrote in message
...
Edmund wrote:


It would have been nice to have a boxplot macro that calculates the
outliers automatically.

Here's how I do it.
a) install Jon Peltier's boxcharter (new and improved)
http://peltiertech.com/Excel/Charts/BoxWhisker.html

Setup a regular data matrix.
A couple rows below that setup the matrix with the outliers

Run the box charter on the regular data matrix -- it calculates all the
values that define the boxes and does the chart.

Then, just copy and paste the outlier data into the chart, format these
new data series as chart type: line chart, with data markers and no lines.

The problem with this is that it calculate the median without the outliers
included (so it only works if you have an even number of high and low
outliers).

And this works better with "inliers" (I just invented a term),
as you can see he
http://lilt.ilstu.edu/gmklass/pos138...s/image024.gif

or he
http://lilt.ilstu.edu/gmklass/pos138...s.htm#Boxplots






Colour plot from *_Use Blox Plot_*

"Edmund"
wrote in message
...
edmund dot r at gmail dot com
/This is a HTML post./
I've searched high and low for a simple Excel box-plot that can do
outliers, does not require drawing each line individually and can
have more than one box in the plot. I could not find one and so
devised one myself. All it needs is the basic summary data and once
you create you can save it to your Custom Charts so next time its
just a matter of selecting it like a normal chart.
*_Create Box Plot_*
EXCEL 2003 Open High Low Close Q1 Upper Tail Lower Tail Q3 Med Group
Med Outlier Group
Outliers
Chicken 40 99 9 70 1 54 1 5
Fish 70 217.5 22.5 135 2 120 1 105
2 10
2 227
2 233
*You only need to create the box plot once since you can save it to
your user-defined custom chart types.*
*1)* Create Open-High-Low-Close chart with the blue higlighted group
(*must select series in columns in the chart setup*).
*2)* After chart is created add the median series (only the yellow,
not x-values) to the the chart.
*3)* Select the median series in the chart and change it to the
secondary axis: Select the top point of an upper tail, this will
probably be Series 2. Then use the left or right arrow keys to move
to the next series until Series 5 is selected; change the axis. Also,
set the secondary axis scale equal to the primary axis.
*4)* Select the chart and with the chart selected open the Excel
Visual Basic Editor. In the Immediate window change the "Series 5"
plot type to Scatter Plot by entering;
ActiveChart.SeriesCollection(5).Type = -4196
Then press enter.
*5)* Return to the chart. Update Series 5 "X-values" in the Source
Data|Series dialog with the median group values.
*6)* Change the "Series 5" plot pattern to no line and and the
dashed symbol.
*7)* Add Series 5 X Error Bars in both directions of a fixed lenght
of 0.195 with no end marker and matching the stlye and weight to
that of the boxes. If you can see the end bars of these then adjust
the width of the chart slightly until they fall in line.
*8)* Add the outliers series (only the pink, not x-values) to the
chart
*9)* Select the outlier series in the chart, Series 6, and change it
to the secondary axis.
*10)* Change Series 6 to Scatter Plot and add "X-Values"
with outlier group as done with Series 5.
*11)* Change the outlier plot pattern to no line and the symbol you
want to use for the outliers.
*12)* Be sure each axis is large enough to show the outliers and
that the axis are equal to each other. Also, it maybe best to switch
off Auto scaling for the min and max of both axis.
*13)* Select the chart and save it to your user-defined custom
charts. Navigate to Chart|Chart Type|Custom Types. Then select the
User-defined option and click add. /See next for data setup for the
box plot./
// *_Use Box Plot_*
EXCEL 2003 Categories Q1 Upper Tail Lower Tail Q3 Med Outliers
Outlier Group
Blue 40 99 9 70 54 5 1
Purple 70 217.5 22.5 135 120 105 1
Red 195 236 176 215 210 10 2
227 2
233 2
239 3

*1)* Arrange your data as above (you can have more or less
categories and outliers).
*2)* Select the highlighted area and select to create a box plot
from your user-defined custom charts. Be sure to select series in
columns in the chart setup. /Note that the outliers will not be in
the correct group but this is corrected in the next step. Also you
may have to change the scale of both y-axis for the data./
*3)* In the Source Data|Series dialog select Series 6 and select the
Outlier Group (the last column) for its X-values and the Outliers
(the second last column) for the Y-values.
*4)* /Box plot!/
// edmund dot r at gmail dot com
//