If you add a couple of helper columns, perhaps you can get the weighted average and median.
e.g.
Assuming your data is in A2:B19, then in C2 enter formula:
Code:
=--LEFT(A2,FIND(" ",A2)-1)
copied down. This will give the lower bound of each age group (we need a numeric representation to do the calcs).
Then in D2 add this formula:
copied down. This gives a running total of the count.
Now to get the Weighted Average:
Code:
=INDEX(A2:A19,MATCH(SUMPRODUCT(B2:B19,C2:C19)/SUM(B2:B19),C2:C19))
and weighted Median:
Code:
=INDEX(A2:A19,MATCH(LOOKUP(SUM(B2:B19)/2,D2:D19,C2:C19),C2:C19))
Bookmarks