- #How to make box and whisker plot in excel 2011 how to
- #How to make box and whisker plot in excel 2011 series
This can be seen from cell F18 which contains the formula =MIN(0,F11:H11). The reason for this is that none of the lower whiskers go into negative territory. Note that despite the fact that there is a negative value, namely the outlier -300 for Brand B (cell B11 of Figure 1), no adjustment for the negative value is necessary when using the Box Plot with Outliers option of the Real Statistics Descriptive Statistics and Normality data analysis tool. Now repeat the above steps for the other outlier. You can also choose the Color (lower right-hand corner of Figure 10). Next, click on Built-in from the Marker Options and choose the Type that you like. Click on the Fill & Line icon and then select the Marker option. The approach is similar in earlier versions of Excel.Ĭlick on one of the outlier marker symbols and then select Chart Tools|Format > Current Selection|Format Selection.
#How to make box and whisker plot in excel 2011 how to
We show how to do this in Excel 2013 and later versions. The last step is to change the marker symbol used to indicate the outliers. The result is shown in Figure 9.įigure 9 – Box Plot with Outliers Changing the Outlier Markers We add the outlier -300 in a similar fashion.
#How to make box and whisker plot in excel 2011 series
Since the outlier 1850 (shown in cell R10 of Figure 7) is in Brand B, we must insert 2 in the Series X values field. Note that this time the default chart is a scatter chart (the last chart type selected) and so we are prompted for both X and Y values (unlike the prompt in Figure 3). Fill in the dialog box that appears as shown in Figure 8.įigure 8 – Add new series for one outlier This will bring up the dialog box shown in Figure 2.
We place the formula =IF(A4>F$15,A4,IF(A4 Data|Select Data. One approach for doing this is shown in Figure 7.
We now proceed to add the outliers to the chart, but first, we need to identify the outliers. The chart changes to the more acceptable format shown in Figure 6.įigure 6 – Box Plot after adding brand means Identifying Outliers Then press the OK button on the Change Chart Type dialog box. Now click on the dropdown menu for m (headed by Stacked Column) and select the first Scatter Plot option. The dialog box shown in Figure 5 will now appear.Īs we can see from Figure 5, the means are treated as a Stacked Column. Next, we select Change Series Chart Type … from the menu that appears. We do this by first clicking on the OK button of the Select Data Source dialog box (see Figure 2) that reappears to close this dialog box, and then right-clicking on any of the blue bars shown in Figure 4.
Thus, we need to tell Excel that this added series should be interpreted instead as a scatter plot. We get this result because Excel interprets the added means as stacked bar charts. When you press the OK button, the chart changes to that shown in Figure 4.
Fill in the dialog box that appears as shown in Figure 3. This will bring up the dialog box shown in Figure 2.Ĭlick on the Add button (on the left side of the dialog box). We use the following array formulas to calculate the values for Lower (i.e. The Q1, Median, Q3 and Mean values for Brand A in the range F12:F17 are calculated by the formulas =QUARTILE(A4:A13,1), =MEDIAN(A4,A13), =QUARTILE(A4:A13,3) and =AVERAGE(A4:A13). To create this box plot manually, you need to first create the values in range F12:F17. We now show how to construct this output manually using standard Excel capabilities. ExamplesĮxample 1: Repeat Example 2 of Special Charting Capabilities, showing outliers. The other difference is that we need to manually add a small circle or each data value greater than Q3 + 1.5*IQR or less than Q1 – 1.5*IQR. Similarly, the bottom whisker ends at the smallest data value greater than or equal to Q1 – 1.5*IQR. One key difference is that instead of ending the top whisker at the maximum data value, it ends at the largest data value less than or equal to Q3 + 1.5*IQR. The procedure for manually creating a box plot with outliers (see Box Plots with Outliers) is similar to that described in Special Charting Capabilities.
0 kommentar(er)
