How to find median of values within the same group?

user_18556307 0
Viewed 68

Median help

Hi there, I'm trying to find the median of the values (Price) that share the same group (Quarter) as in the picture I have included. I have colour-coded them to show their corresponding values and included example medians on the right-hand side. I hope this makes my question easier to understand.

I need to do this for a larger column where the Quarters are changing. Is it possible to automate this process? If so, what kind of formula would I need to input where the '?' is placed as seen in the bottom right-hand corner?

Also, would it work on a column where the group (Quarter) is not sorted like in the picture?

Thank you for any help in advance!

worksheet worksheet-function median excel-formula excel
1 Answers

This might work for your purposes, instead of listing median along side data, this formula takes a list of unique the quarters in your range and calcualtes the median for each corresponding quarter.

All you have to do here is change the quarter and price range, A2:A7 and B2:B7 in my example, to line up with your data.

This formula will work no matter what order the quarters are in.

=+LET(
quarters,(B2:B7),
prices,(A2:A7),
uniqueQuarters, UNIQUE(quarters),
median, BYROW(uniqueQuarters,LAMBDA(x,MEDIAN(IF(quarters=x,prices)))),
CHOOSE({1,2},uniqueQuarters,median))

enter image description here

user_165632860
Related