In a box-and-whisker box, however, the size of a segment represents a difference between one value and another — like the difference between the quartile and the median, or between the median and the first quartile. So the box is really a stacked column with three segments. The first segment is the first quartile. Excel can aftermath percentile graphs — with a little help. Stockbyte/Stockbyte/Getty Images A percentile blueprint visualizes the percentiles of a accustomed abstracts set. The blueprint will appearance a box abeyant on the blueprint that represents the breadth amid the 25th and 75th percentile. For those who rely on Excel to do their data analysis (rather than mini-tab or JMP), occasionally the charts available are a little limiting. So I thought I would post this technique to allow you to perform a box plot analysis using Excel. Collate the data First you need to gather your data together. In this example, I have 10 sites, with values by month for the year 2011. For clarification, I have also uploaded the excel file, so please feel free to have a look! In the example attached, I have created a tab in excel for each site (labelled 1 to 10). How to search a website for a word on mac. You can of course use just one tab for all if you prefer! The first thing you need to do is calculate the median, minimum, maximum and upper quartile values for each site. ![]() ![]() You can see this on the “Box plot” tab of the excel sheet attached. The median is calculated by: =MEDIAN(‘1’!$C$2:$C$11) ‘1’ refers to tab “1” and it is looking at the values in column C from lines 2 to 13. Repeat this for each site (and tab) by changing the figure ‘1’ to ‘2’, then ‘3’ etc to form row 2 of the table below. The $ symbol ensures that the range used for column C never changes when you copy / paste the calculation across the row. I will come back to row 3 in a moment, for now let’s skip on to rows 4 and 5. The minimum is calculated by: =MIN(‘1’!$C$2:$C$11) The maximum is calculated by: =MAX(‘1’!$C$2:$C$11) Again, repeat this for each site (and tab) to form rows 4 and 5 of the table above. The minimum and maximum values will be used to create the whiskers of the box plot. Now let’s go back to row 3. Excel has a built in function “quartile” which we can use for the calculation for this row and for row 6. Row 3 ( Lower quartile, Q1) is calculated by: =QUARTILE(‘1’!$C$2:$C$11, 1) The value after the comma (in blue above) show that we want the lower quartile (Q1) Row 6 ( Upper quartile, Q3) is calculated by: =QUARTILE(‘1’!$C$2:$C$11, 3) Once again, repeat this for each site (and tab) to form rows 3 and 6 of the table also shown below. The Q1 and Q3 values will form the top and bottom of the box. The order they appear in, (median, Q1, Min, Max, Q3) is very important! Do not change this or the graph will be wrong! Create the chart Since excel doesn’t have a box plot chart we are going to have to create one. The first step is to adjust the two Y axes so that they are identical, i.e. Both go from 0 to 4.0. Double click on the left axis. In the menu that apears, ensure that Axis options is selected, then change the maximum value from Auto to Fixed and enter a value of 4.0. Then click on the Close button. Then right click on the blue filled median data on the graph and select change series chart type. In the menu that apears, change the graph type to line and choose the 4th option (Line with markers). Then click on the OK button. The graph will then look as shown below. There are a few more formatting points to do. • Change the fill of the box plot to none • Remove the interconnecting line of the median series • Double click on the boxes. In the menu that appears, ensure that fill is selected and choose No fill.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |