CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
KSokd
KSokd

Reputation: 33

Google Sheets Formula for Grouping Data and then finding the Median Value

Example data:

Category Value
Blue 200
Blue 200
Red 360
Green 300
Red 400
Green 300
Yellow 0
Red 0
Pink 800
Silver 900
---- ----
MEDIAN 330
MEDIAN OF CATEGORIES ???

The formula being used to calculate the median of all values (above 0) is

=MEDIAN(FILTER(B2:B11,B2:B11>0))

What formula would I need to use to Sum the Values of each category, then find the Median of the categories?

At this time I can only do this with a pivot table and the result is 760.

In its actual use case, the unique colours will change regularly and I could end up with several hundred.

Thank you!

Upvotes: 1

Views: 188

Answers (1)

Broly
Broly

Reputation: 921

You can use something like this

=MEDIAN(FILTER(SUMIF(A2:A, UNIQUE(A2:A), B2:B), SUMIF(A2:A, UNIQUE(A2:A), B2:B)>0))

This works for me: enter image description here

Upvotes: 2

Related Questions