Formula, Averages by name, and differences of averages

**billyvnilly** · 02-19-2010, 04:36 PM

Hello I have a long spreadsheet of names with results from 2 different tests

I would like to be able to calculate the average test result per test per name, and then calculate the difference of those 2 averages, per name

name | A | B |AVE A | AVE B | diff
Bob | 2 | 4 |
Bob | 3 | 5 |
Bob | 2 | 4 | 6 | 4.3 | 1.7

Its a large data set, so i would not like to do each name manually.

thank you.

**Palmetto** · 02-19-2010, 04:48 PM

Since you have Excel 2007, you can use the AVERAGEIF function.

In D6: =AVERAGEIF($A$4:$A$16,$A6,$B$4:$B$16)
In E6: =AVERAGEIF($A$4:$A$16,$A6,$C$4:$C$16)
In F6: =ABS(D6-E6)

**billyvnilly** · 02-24-2010, 01:52 PM

Hello Palmetto,

Thank you for your help. Anyway to do this in 2003?

**Palmetto** · 02-24-2010, 05:35 PM

Anyway to do this in 2003?

in E6: =SUMPRODUCT(--($A$4:$A$16="Bob"),--($B$4:$B$16))/COUNTIF($A$4:$A$16,"Bob")

In F6: =SUMPRODUCT(--($A$4:$A$16="Bob"),--($C$4:$C$16))/COUNTIF($A$4:$A$16,"Bob")