Combining AVERAGE, MEDIAN and STDEV with IF functions

[Terri Grassby]

I have a rather ragged data set, which is split over three sets of columns. In columns A, H and O I have multiple instances of the same numbers representing sample codes. In the other columns I have individual attributes which have been scored out of 100.

I want to do average, median and stdev calculations for each sample code for each attribute. I am currently cutting and pasting values and seem to have an AVERAGEIF statement =AVERAGEIF($M$2:$M$76,$M78,N$2:N$76) that works, but I'd really like to be able to do MEDIAN and STDEV equivalents.

[#2]

You could try an array formula using MEDIAN(IF(),IF(),IF()) and STDEV(IF(),IF(),IF()) as explained here:

http://stackoverflow.com/questions/6752724/help-needed-with-median-if-in-excel

Jon

[#3]

Hi Terri,

in line with the approach referred to by Jon, your formulas would be as follows:

Median:

=MEDIAN(IF($M$2:$M$76=$M$78,$N$2:$N$76,""))

STDEV:

=STDEV(IF($M$2:$M$76=$M$78,$N$2:$N$76,""))

You need to array-enter the formulas by activating the formula cell (F2), then pressing 'Ctrl+Shift+Enter'

Yury