Selecting top 10% of items with High Score

[chandramouliarun]

Hi All,

Hope you are doing well!..I am trying to select the top 10% of the items that have high scores by vendor (name in my worksheet)...For Example if If have 32 items for a vendor .. then I should select the items with highest scores and 10% of 32 =3.2 and I need to select 4 items ie ceiling(3.2)...Can you please help with the same....Attached the worksheet with input and expected output in two separate tabs..


Thanks,
Arun

[Olly]

You can do this with Power Query. Format your source data as a table, then use:

Please Login or Register  to view this content.

Close and Load To a table.

See attached worked example.

[Bo_Ry]

Helper column need at
Input D2
=AGGREGATE(14,6,$C$2:$C$91*($B$2:$B$91=B2),ROUNDUP(COUNTIF($B$2:$B$91,B2)/10,))

Output A2:C11
=IFERROR(INDEX('Input '!A:A,AGGREGATE(15,6,ROW('Input '!A$2:A$91)/('Input '!$C$2:$C$91>='Input '!$D$2:$D$91),ROWS(A$2:A2))),"")

[chandramouliarun]

Hi Olly,

Thanks a lot !!..Really appreciate your help!!..

Thanks,
Arun

[chandramouliarun]

Hi Bo!..

This is awesome and works!...Really appreciate your help!!...

Thanks,
Arun

[Olly]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You may also 'add reputation' to those who helped you. Thanks.