MEDIAN from aggregative data

Copper Contributor

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
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

@wirral1 

 

 

 

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

 

 

@wirral1 

Maybe this:

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