From WikiEducator
Jump to: navigation, search

Description of activity and Excel instructions: ANOVA

Dataset: Direct link for flicker.xls

Calculate descriptive statistics for each value of the categorical variable

Pick an empty area in your worksheet and create a table where the row headers are "Min", "Q1", "Median", "Q3", "Max", "n", "Mean", "St Dev" and the column headers are the different values of the categorical variable: "Blue", "Brown", and "Green".

For each cell in the table, enter the formula to calculate the statistic listed in the row header. The data range to include in each formula is the range of data in the quantitative variable that corresponds to the categorical variable value listed in the column header.

The formulas you will need are:

  • Min: "=min([range])"
  • Q1: "=quartile([range],1)" [Note that in some operating systems the formula uses a semi-colon instead of a colon between the range and the numeral "1".]
  • Median: "=median([range])"
  • Q3: "=quartile([range],3)" [Note that in some operating systems the formula uses a semi-colon instead of a colon, between the range and the number "3".]
  • Max: "=max([range])"
  • n: "=count([range])"
  • Mean: "=average([range])"
  • St Dev: "=stdev([range])"

Create side-by-side boxplots

Unfortunately, Calc can't create boxplots directly, so we have to create a column graph and alter it to display as side-by-side boxplots. We start by using the calculated Descriptive Statistics to generate new values that will be the basis for our column graph.

Create the necessary statistics

  • In each successive cell under St Dev, type "25th Percentile", "50th Percentile", "75th Percentile", "Lower limit", and finally "Upper limit".

The values in these new rows, for each eye color, are calculated based on the 5-number summary values. Enter the necessary formulas, for each eye color, to obtain:

    • 25th Percentile = Q1 (Remember to copy the numbers from Q1 without copying the formulas.)
    • 50th Percentile = Median - Q1
    • 75th Percentile = Q3 - Median
    • Lower limit = Q1 - min
    • Upper limit values = Max - Q3

Create the boxplots

Now you are ready to create the boxplots.

  • Highlight the values in the 25th Percentile, 50th Percentile, and 75th Percentile rows
  • Choose Insert > Chart...

The Chart Wizard displays.

  • In 1. Chart Type', choose the stacked column option.
  • In 2. Data Range, ensure that the data is correctly specified.
  • In 3. Data Series, use the Categories window to specify the category labels.
  • In 4. Chart Elements, label and select appropriate options for your chart.

A stacked column graph displays. To create the boxplots, add error bars (to the min and max values) and adjust the color and outlines as needed.

  • Use the Insert Y Error Bars... option to add the Upper Limit error bar.
  • Use the Insert Y Error Bars... option to add the Lower Limit error bar. (Remember that to add this error bar you have to select the "lowest portion of the bar.")
  • For each section of the bars, adjust the color and border as necessary.
  • If needed, adjust the y-axis scale.

Use the boxplots to check that the CFF distribution is normal for each eye color.

Return to the ANOVA activity to complete the interpretive exercises.

ANOVA F-test

Calc does not include a data analysis package or formulas that can be used for calculating the F-statistic. You can use Excel for this activity, or the web-based statistics calculator for a one-way ANOVA at The statistics calculator uses the number, mean and standard deviation for each group to calculate the F-statistic along with the p-value. Use 3 decimal places when entering the mean and sd.

Return to the ANOVA activity to report results and draw conclusions.