Search code examples
excelif-statementrangemedian

Excel;seek medianvalue with if statement over a range of values

I seek the median value for a group of ages: 18-24, 25-29, 30-34 and so on. I have the data in sheeet Wages; wages in column G and ages in column I.

In the output sheet I have formed a range of ages in B9:B58 from 18 years in B9 to 67 years in B58 to "help" to form the formula. I tried this for 18-24 years (7 years interval in B9:B15):

=MEDIAN(IF((Wages!$I$1:$I$7000=B9:B15),Wages!$G$1:$G$7000)).

(The rest should be in 5 years interval)

Solution

  • Write or generate in columns A and B the bounds of your ranges, so that A9:B9 = 18, 24, A10:B10 = 25, 29 etc... until A18:B18 = 65, 69. Then write this Array Formula (paste then click Ctrl+Shift+Enter) in C9 :

    =MEDIAN(IF((Wages!$I$1:$I$7000>=$A9)*(Wages!$I$1:$I$7000<=$B9), Wages!$I$1:$I$7000))

    Then you can copy/paste C9 into C10:C18.