Statistical Analysis with Excel for Dummies (Notes: Finding Your Center- AverageIF)

If you look at a group of numbers and try to find one that's somewhere in the middle, you're dealing with that group's central tendency.  Central tendency comes in several flavors.

That statistical term for an average is mean.  A mean is the sum of a bunch of numbers divided by the amount of numbers you added up.  For example the mean of reading scores in WPM that are 56, 78, 45, 49, 55, and 62.  Are the sum of those numbers divided by 6 which is 57.5.

The commonly used abbreviation for "Number" is x.  And a typical abbreviation for "Amount of numbers added up" is N.  Another abbreviation stands for Sum of.  It's the uppercase Greek Letter for S pronounced Sigma.  The abbreviation for mean is "X bar", an X with a bar over it.  Here's what the formula looks like:

The Excel function Average calculates mean.

AverageIF includes numbers in the average if a particular condition is met.
AverageIFS includes numbers in the average if more than one condition is met.

Here's the data we'll be working with for these formulas:

I also defined names for each of the cells.

To calculate the overall average, it's = AVG (RT_msec)

To calculate the average of trials that displayed a circle,we use the following AverageIF Formula:

=AVERAGEIF(Shape, "Circle", RT_msec)

To calculate the average of the first 8 trials, the formula is =AVERAGEIF(Trial,"<9",RT_msec)

To calculate the average of recation times fast than 400 msec:

=AVERAGEIF(RT_msec, ">400", RT_msec)

To calculate the average of trials on which Color = Green and Shape = Square is:

AVERAGEIFS(RT_msec, Color, "Green", Shape, "Square")

**Note that RT_msec is the first argument in the AVERAGEIFS but the last argument in AVERAGEIF.