Forum Discussion
Combining AVERAGE, MEDIAN and STDEV with IF functions
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.
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
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
