Forum Discussion

Terri Grassby's avatar
Terri Grassby
Copper Contributor
May 15, 2017

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.

Formulas & Functions
Need Help

2 Replies

  • Yury Tokarev's avatar
    Yury Tokarev
    Iron Contributor
    May 16, 2017

    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

  • Jon Wittwer's avatar
    Jon Wittwer
    Copper Contributor
    May 15, 2017

    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

     

     

Resources