CODEWITHSUNDEEP
excelexcel-formulamedianworksheet-functionworksheet
haziqh
haziqh

Reputation: 23

How to find median of values within the same group?

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!

Upvotes: 2

Views: 294

Answers (1)

DKoontz
DKoontz

Reputation: 307

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

Upvotes: 1

Related Questions