unomadx.blogg.se

1. Box and whiskers plot excel 2011 how to#
2. Box and whiskers plot excel 2011 manual#
3. Box and whiskers plot excel 2011 series#

Box and whiskers plot excel 2011 manual#

This is evident from the fact that cell F18 of Figure 11 contains the negative value -50.ĭespite this, no change is required to the manual procedure described for Example 1. If we repeat all the steps described for Example 1, this time we see that the lower whisker for Brand B does go into negative territory. Handling Negative DataĮxample 2: Use the Box Plot with Outliers option of the Real Statistics Descriptive Statistics and Normality data analysis tool to create the box plots for the data in range A6:A16 of Figure 11. As long as this value is zero and not negative, then no adjustment is necessary. 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.

Box and whiskers plot 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.

Box and whiskers plot 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.