Hello, this is Dr.. [gandhi] welcome to my video on creating a box plot using Microsoft excel
I'll be using excel 2016 for this example and
This is the first version of excel that has the box plot as a chart that you can select
However, it is [possible] to create a box plot using earlier versions of excel and have another video that covers that
so I have loaded some fictitious data into
this excel worksheet you can see column a and Column B, and
Let's assume that
these represent scores on a [depression] [inventory] [and]
these are t scores
Which have a mean of 50 and a standard deviation of 10?
So before I create a box plot. I want to calculate
Some values based on these two variables
Which I've simply referred to as variable 1 and variable 2
So I want the minimum
The quartile 1
the Median quartile 3 and
the maximum as well as the mean and the range of each variable
So I can use one function to calculate these 5 values
Using the same range, but a different
Argument at the end of function
that's the quartile dot a and C function, so I'll go into minimum and
enter
quartile
inclusive and
The array in this case is going to be
all the [values] in a so starting with I want to start here at the bottom a 50 and then move up to a
1 and
Then I'm going to click F4 to make this an absolute reference, so it will not change as I autofill
And then the comma you can see that there are five
possible arguments
to put in place here after the comma
The first is minimum value, and it's zero so I'm going to put in zero because I do want the minimum in this case
And this twenty seven point eight eight
I'm going to autofill the same function all the way down and
[just] go in and change
This argument in this case from a 0 to 1 which will give me the first quartile
then for Median I
Change it to a two to the median value or the 50th percentile and then for a quartile three
Change this to three
And the maximum value would be change this argument to a four so the same function
Can give us all the values we need here?
Because I have 50 values in Column b. What I can do here with this function for variable one
which is
Which is the minimum value from Column a is?
in the absolute reference I can remove the dollar sign in front of
the letter
now to remove it in both places for a one and for a 50 and
Then I can autofill it to the right you
[can] see now it's B1 [B] 50
now I ought to fill this down and
then make the same changes as
I did for variable one
and how the maximum here and that's for
So here we have all these values for both variable 1 and variable 2
now to calculate the mean
That's fairly straightforward and excel the function is average
so for the values in Column a
[this] be average, and I'm going to select a
50 through a 1
and
Similarly for the average of Column B
acceptabie
be 50 through b 1 and
Then the range is going to be the maximum value
minus the Minimum value, so
this case
Seventy nine point five two
Minus twenty seven point eight eight I'll make the same calculation for variable 2
So be seventy three point four two minus
Twenty two point seventy [four]
These values are important to have on hand to understand
the information displayed in a box plot
So to generate the box plot. I'm just going to highlight a one down through
[B50] and actually start at B 50
Work my way up
so highlight this region and then insert and
Here we have
insert statistic chart
[alright], and there's a down arrow click that and you can see histogram is available
A couple different types. I want to move down to
box and whisker
otherwise known as a box plot
and I'm going to delete the chart title and
the label here at the bottom
and increase the size to
make this chart agree with the
Values I generate here
I'm going to right-click and
Format Data series
And you can see for the quartile calculation it has inclusive and exclusive
median I use the inclusive median, so I'm going to switch this to inclusive median and then
Close that [dialogue]
So now this box plot is consistent with my values
So now let's interpret the box plot [for] variable one
You can see you have a whisker
Extending down and this represents the minimum value you can see 27 point 88
Matches what I have here for [the] minimum value
Then you have this [rectangle] at the bottom of the rectangle this represents quartile one
Forty two point five four and
the top of the rectangle represents
quartile three which is 56 point seven eight this line and
It separates the rectangle here. That's the median it's the median value can see it's just below
Fifty Forty Nine Point Seven two and
then this top whisker
This is the Maximum value
excluding any
Outliers
So in this case it's seventy seven point ninety five even though the true maximum. You can see is seven nine point five to
That value is considered an outlier and you can find that here this dot
Seven Nine Point Five -
So how does excel?
Determine if
a value is an outlier
Well to understand that we first have [to] understand the concept of interquartile range
So I'm just going to put that down
below Range Here, [I]
[queue] our
interquartile range
now that's the difference between
Quartile 3 and quartile 1 so in this case. I can calculate it pretty easily you
Take quartile three and subtract quartile one
it's
Fourteen point two four
Now for a value to be considered an outlier
It has to be greater than the interquartile range times one point five
Added to quartile three or less than the interquartile range times one point five
subtracted from quartile one
So first let me take the interquartile range
multiply it by one point five
It gives us [Twenty] [one] point [Thirty] [six]
And then I'm going to calculate
the lower
Outlier
[cutoff] which would be
Forty two point five four
- twenty-One Point 36 so equals sign
quartile [1] [minus] [twenty] [one] [Thirty] [six] that's twenty one point one eight and
for the
upper cutoff for an Outlier
It will be equal sign
quartile three plus
Twenty One Point Thirty six
so seven eight point one four is the upper cutoff so you can see we've exceeded that because we have the
seven nine point five two value, which is here in
Column a it's a seven, so if I were to change this value to seven eight point one four
You can see now there are no outliers
that's the new Maximum value and
it's
Inside the cutoff it matches the cutoff value
So it's not displays an outlier if I were to move [this] to seven eight point one five
You can see now it is an outlier. It's greater than seven eight point one four
Now as you can see this chart is dynamic. It changes as I change the values
so if I were to change this value to say a more extreme value [C10] [for]
you see how it redistributes the point and
changes the values on the y-Axis
So of course 1 0 4 is an outlier, and you can see it way up here now
So if I were to go back and reset this back to the original
Outlying value
79.5 2. You see the lower bound here is
21.18 and
the minimum is twenty seven point eight eight so if I move down here to the minimum
you can see that values in a twenty seven twenty seven point eight eight and
Let's say I made that twenty
you can see how the graph now shifts and the new
Bottom whisker is Twenty nine point nine, and of course the value of twenty now becomes an Outlier I
hope this video on creating box plots in Microsoft excel
Was useful as always if you have any questions, or concerns feel free to contact me, and I'll be happy to assist you