# MEDIAN from aggregative data

Copper Contributor

# MEDIAN from aggregative data

Is there a way to find the median from aggregated data?

For example, people were asked to rank something 1-5:

90x people ranked it 5

56x people ranked it 4

75x people ranked it 3

etc...

How can I figure the median out without typing 5 90x times, 4x 56 times, etc... and then using MEDIAN on all those numbers?

Preferably using excel but can use power BI if is easier

3 Replies

# Re: MEDIAN from aggregative data

can't you just use MEDIAN( [range of cell with the rank vale])
If this is a one-off you can just 'count' from top and bottom and see where the middle is:
rank 5 : 90
rank 4 : 56
rank 3 : 75
rank 2 : 48
rank 1 : 31
total ; 299 so looking for #150 so rank 1 + 2 = 79 and ranks 1-3 = 154 so #150 is inside rank 3

# Re: MEDIAN from aggregative data

``````=MEDIAN(IF(SEQUENCE(,SUM(count_array))<=count_array,bins_array))
=AGGREGATE(17,6,bins_array/(SEQUENCE(,SUM(count_array))<=count_array),2)``````

# Re: MEDIAN from aggregative data

Maybe this:

``=LET(ratings,TEXTJOIN(,,REPT(E1:I1,E2:I2)),pts,MID(ratings,SEQUENCE(LEN(ratings)),1)*1,MEDIAN(pts))``