Diff for "FAQ/ExcelErrorBarPlot" - CBU statistics Wiki
location: Diff for "FAQ/ExcelErrorBarPlot"
Differences between revisions 11 and 18 (spanning 7 versions)
Revision 11 as of 2009-04-29 13:06:11
Size: 2181
Editor: PeterWatson
Comment:
Revision 18 as of 2013-03-08 10:17:41
Size: 2864
Editor: localhost
Comment: converted to 1.6 markup
Deletions are marked like this. Additions are marked like this.
Line 3: Line 3:
You can't produce boxplots directly in EXCEL but there is a rather convoluted work around described [http://support.microsoft.com/kb/155130 here.] I also reproduce Microsoft's solution below for some example data. You can't produce boxplots directly in EXCEL but there is a rather convoluted work around described [[http://support.microsoft.com/kb/155130|here.]] I also reproduce Microsoft's solution below for some example data.
Line 7: Line 7:
||||||<style="TEXT-ALIGN: left"> '''A1: Statistic''' || '''B1: a''' || '''C1: b''' || '''D1: c''' ||
||||||<style="TEXT-ALIGN: left"> '''A2: median''' || '''B2: 40''' || '''C2: 45''' || '''D2: 50''' ||
||||||<style="TEXT-ALIGN: left"> '''A3: q1''' || '''B3: 20''' || '''C3: 22''' || '''D3: 30''' ||
||||||<style="TEXT-ALIGN: left"> '''A4: min''' || '''B4: 10''' || '''C4: 15''' || '''D4: 18''' ||
||||||<style="TEXT-ALIGN: left"> '''A5: max''' || '''B5: 100''' || '''C5: 110''' || '''D5: 90''' ||
||||||<style="TEXT-ALIGN: left"> '''A6: q3''' || '''B6: 70''' || '''C6: 75''' || '''D6: 57''' ||
||||||<style="TEXT-ALIGN: left"> ''A1: Statistic'' || ''B1: a''  || ''C1: b''  || ''D1: c'' ||
||||||<style="TEXT-ALIGN: left"> ''A2: median''    || ''B2: 40'' || ''C2: 45''  || ''D2: 50'' ||
||||||<style="TEXT-ALIGN: left"> ''A3: q1''        || ''B3: 20'' || ''C3: 22''  || ''D3: 30'' ||
||||||<style="TEXT-ALIGN: left"> ''A4: min''       || ''B4: 10'' || ''C4: 15''  || ''D4: 18'' ||
||||||<style="TEXT-ALIGN: left"> ''A5: max''       || ''B5: 100'' || ''C5: 110''  || ''D5: 90'' ||
||||||<style="TEXT-ALIGN: left"> ''A6: q3''        || ''B6: 70'' || ''C6: 75''  || ''D6: 57'' ||
Line 14: Line 14:
2. Select cells A1:D6. On the Insert menu, click Chart. 2. Select cells A1:D6. On the '''Insert''' menu, click '''Chart'''.
Line 16: Line 16:
3. On the Standard types tab, click Stock under Chart type, and then click the fourth chart sub-type. 3. On the '''Standard types''' tab, click '''Stock''' under '''Chart type''', and then click the fourth chart sub-type.
Line 21: Line 21:
4. Click Next. 4. Click '''Next'''.
Line 23: Line 23:
5. On the Data Range tab, click Rows under Series in, and then click Next. 5. On the '''Data Range''' tab, click '''Rows''' under '''Series in''', and then click '''Next'''.
Line 25: Line 25:
6. On the Legend tab, click to clear the Show legend check box. 6. On the '''Legend''' tab, click to clear the '''Show legend''' check box.
Line 27: Line 27:
7. On the Axes tab, click to clear the Value (Y) Axis check box under Secondary axis, and then click Finish. 7. On the '''Axes''' tab, click to clear the '''Value (Y) Axis''' check box under '''Secondary axis''', and then click '''Finish'''.
Line 29: Line 29:
8. Click once on any one of the colored columns to select the series. Do not click one of the white columns. 8. Click once on any one of the coloured columns to select the series. Do not click one of the white columns.
Line 31: Line 31:
9. On the Chart menu, click Chart Type. Under Chart type, click Line, and then click OK. 9. On the '''Chart''' menu, click '''Chart Type'''. Under '''Chart type''', click '''Line''', and then click '''OK'''.
Line 35: Line 35:
10. Click once on the line, and then click Selected Data Series on the Format menu and under Display line choose None and under Display marker choose None. Click OK. 10. Click once on the line, and then click '''Selected Data Series''' on the '''Format''' menu and under '''Display line''' choose '''None''' and under '''Display marker''' choose '''None'''. Click '''OK'''.
Line 37: Line 37:
After some tidying up including the removal of the gridlines and the changing of the background and box colours you should end up with something like [attachment:box.xls this] made using EXCEL 2003. After some tidying up including the removal of the gridlines and the changing of the background and box colours you should end up with something like [[attachment:box.xls|this]] made using EXCEL 2003.

Lane and Sandor (2009) advocate and illustrate the use of boxplots because they encapsulate graphically information on medians, interquartile ranges, ranges and outliers.

__References__

Elliott, J and Marsh, C (2008) Exploring Data: An Introduction to Data Analysis for Social Scientists. Second Edition. Polity Press:Cambridge. Chapter 8 introduces boxplots.

Lane, DM and Sandor, A (2009) Designing better graphs by including distributional information and integrating words, numbers and images. ''Psychological Methods'' '''14(3)''' 239-257.

How do I plot a boxplot in EXCEL?

You can't produce boxplots directly in EXCEL but there is a rather convoluted work around described here. I also reproduce Microsoft's solution below for some example data.

1. In a new worksheet, type the following data:

A1: Statistic

B1: a

C1: b

D1: c

A2: median

B2: 40

C2: 45

D2: 50

A3: q1

B3: 20

C3: 22

D3: 30

A4: min

B4: 10

C4: 15

D4: 18

A5: max

B5: 100

C5: 110

D5: 90

A6: q3

B6: 70

C6: 75

D6: 57

2. Select cells A1:D6. On the Insert menu, click Chart.

3. On the Standard types tab, click Stock under Chart type, and then click the fourth chart sub-type.

The following explanation appears below the chart sub-type: Volume-Open-High-Low-Close. Requires five series of values in this order.

4. Click Next.

5. On the Data Range tab, click Rows under Series in, and then click Next.

6. On the Legend tab, click to clear the Show legend check box.

7. On the Axes tab, click to clear the Value (Y) Axis check box under Secondary axis, and then click Finish.

8. Click once on any one of the coloured columns to select the series. Do not click one of the white columns.

9. On the Chart menu, click Chart Type. Under Chart type, click Line, and then click OK.

A line that connects the three white columns appears in the chart.

10. Click once on the line, and then click Selected Data Series on the Format menu and under Display line choose None and under Display marker choose None. Click OK.

After some tidying up including the removal of the gridlines and the changing of the background and box colours you should end up with something like this made using EXCEL 2003.

Lane and Sandor (2009) advocate and illustrate the use of boxplots because they encapsulate graphically information on medians, interquartile ranges, ranges and outliers.

References

Elliott, J and Marsh, C (2008) Exploring Data: An Introduction to Data Analysis for Social Scientists. Second Edition. Polity Press:Cambridge. Chapter 8 introduces boxplots.

Lane, DM and Sandor, A (2009) Designing better graphs by including distributional information and integrating words, numbers and images. Psychological Methods 14(3) 239-257.

None: FAQ/ExcelErrorBarPlot (last edited 2013-03-08 10:17:41 by localhost)