Forum Discussion

wirral1's avatar
wirral1
Copper Contributor
Dec 01, 2022

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

BI & Data Analysis
excel
Formulas and Functions
power bi
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Dec 01, 2022

    wirral1 

     

     

     

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

     

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 01, 2022
    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

Resources