Get Top N % using Formula

  • Hi Guys,


    Please could you help me out with a formula which will help me identify how many Rows belong to Top 10 % Group Or top 20% Group. For Example in the below case there are 20 rows and Top 10% are the first 2 rows.
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 402"]Row Labels[/TD]
    [TD="width: 92"]Amount[/TD]

    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    63,855,641.60

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    36,100,068.26

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    23,324,389.74

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    20,534,610.54

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    14,017,828.67

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    11,669,652.96

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    11,452,970.69

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    10,868,123.63

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    10,072,343.93

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    9,995,804.15

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    9,812,192.01

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    9,680,726.24

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    9,632,613.12

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    8,159,122.82

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    6,429,932.46

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    6,065,187.29

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    6,043,460.64

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    5,887,114.29

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    5,523,253.55

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    5,231,107.87

    [/td]


    [/tr]


    [/TABLE]

  • Try this:


    Excel 2016 (Windows) 32 bit
    [Table="width:, class:head"][tr=bgcolor:#888888][th][COLOR="#FFFFFF"] [/COLOR][/th][th]

    [COLOR="#FFFFFF"]A[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]B[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]C[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]D[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]E[/COLOR]

    [/th][/tr]
    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]1[/COLOR]

    [/td]

    [td]

    ABC

    [/td][td]

    63,855,641.60

    [/td][td]

    [/td][td]

    % required:

    [/td][td]

    10

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]2[/COLOR]

    [/td]

    [td]

    DEF

    [/td][td]

    36,100,068.26

    [/td][td]

    [/td][td]

    Total Rows:

    [/td][td]

    20

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]3[/COLOR]

    [/td]

    [td]

    SGH

    [/td][td]

    23,324,389.74

    [/td][td]

    [/td][td]

    Top 10 % of rows:

    [/td][td]

    2

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]4[/COLOR]

    [/td]

    [td]

    ABC

    [/td][td]

    20,534,610.54

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]5[/COLOR]

    [/td]

    [td]

    DEF

    [/td][td]

    14,017,828.67

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]6[/COLOR]

    [/td]

    [td]

    SGH

    [/td][td]

    11,669,652.96

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]7[/COLOR]

    [/td]

    [td]

    ABC

    [/td][td]

    11,452,970.69

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]8[/COLOR]

    [/td]

    [td]

    DEF

    [/td][td]

    10,868,123.63

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]9[/COLOR]

    [/td]

    [td]

    SGH

    [/td][td]

    10,072,343.93

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]10[/COLOR]

    [/td]

    [td]

    ABC

    [/td][td]

    9,995,804.15

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]11[/COLOR]

    [/td]

    [td]

    DEF

    [/td][td]

    9,812,192.01

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]12[/COLOR]

    [/td]

    [td]

    SGH

    [/td][td]

    9,680,726.24

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]13[/COLOR]

    [/td]

    [td]

    ABC

    [/td][td]

    9,632,613.12

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]14[/COLOR]

    [/td]

    [td]

    DEF

    [/td][td]

    8,159,122.82

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]15[/COLOR]

    [/td]

    [td]

    SGH

    [/td][td]

    6,429,932.46

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]16[/COLOR]

    [/td]

    [td]

    ABC

    [/td][td]

    6,065,187.29

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]17[/COLOR]

    [/td]

    [td]

    DEF

    [/td][td]

    6,043,460.64

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]18[/COLOR]

    [/td]

    [td]

    SGH

    [/td][td]

    5,887,114.29

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]19[/COLOR]

    [/td]

    [td]

    ABC

    [/td][td]

    5,523,253.55

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]20[/COLOR]

    [/td]

    [td]

    DEF

    [/td][td]

    5,231,107.87

    [/td][td]

    [/td][td]

    [/td][td]

    [/td]

    [/tr]
    [/table]
    [Table="width:, class:grid"]

    [tr][td]

    Sheet: Sheet1

    [/td][/tr]

    [/table]


    Excel 2016 (Windows) 32 bit
    [Table="width:, class:head"][tr=bgcolor:#888888][th][COLOR="#FFFFFF"] [/COLOR][/th][th]

    [COLOR="#FFFFFF"]D[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]E[/COLOR]

    [/th][/tr]
    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]1[/COLOR]

    [/td]

    [td]

    % required:

    [/td][td]

    10

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]2[/COLOR]

    [/td]

    [td]

    Total Rows:

    [/td][td]

    =COUNTA([COLOR="#0000FF"]A1:A50[/COLOR])

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]3[/COLOR]

    [/td]

    [td]

    ="Top "&E1&" % of rows:"

    [/td][td]

    =ROUND([COLOR="#0000FF"]E2/E1,0[/COLOR])

    [/td]

    [/tr]
    [/table]
    [Table="width:, class:grid"]

    [tr][td]

    Sheet: Sheet1

    [/td][/tr]

    [/table]

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!