Dec 01 2022 09:37 AM
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
Dec 01 2022 10:17 AM
Dec 01 2022 11:19 AM - edited Dec 01 2022 11:36 AM
=MEDIAN(IF(SEQUENCE(,SUM(count_array))<=count_array,bins_array))
=AGGREGATE(17,6,bins_array/(SEQUENCE(,SUM(count_array))<=count_array),2)
Dec 01 2022 11:43 AM
Maybe this:
=LET(ratings,TEXTJOIN(,,REPT(E1:I1,E2:I2)),pts,MID(ratings,SEQUENCE(LEN(ratings)),1)*1,MEDIAN(pts))