Creating a Boxplot in Excel 2016

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

Channel: Dr. Todd Grande

Views: 692,892

Rating: 4.7990808 out of 5

Keywords: Excel, Excel 2016, Microsoft Excel, Microsoft Excel 2016, 2016, boxplot, bar chart, interquartile range, quartile, QUARTILE.INC, chart, whiskers, minimum, maximum, quartile 1, quartile 3, median, median line, error bars, x axis, y axis, box and whisker, box, whisker, counseling, Grande, Statistics (Field Of Study)

Id: TxuretcM5Uk

Length: 12min 4sec (724 seconds)

Published: Wed Oct 14 2015