Count Top % Contributors

[JV0710]

Good day

Please can anyone help me with this query:

I have a list of about 2000 Suppliers.
In Column A is the Name of the Supplier and
in Column B is the % Contribution of the business I am doing with that Supplier -
Column B is sorted in decending order.

Part 1: In Cell C1 I would like a formula that counts the number of Suppliers that make up 80 % of the contributions. ( the closest to 80% give or take 5% )

Part 2: If the List is not sorted - What would the formula be, to count the TOP 80 % Contributions ( the closest to 80% give or take 5% )

So I would get, for example, 160 of the 2000 suppliers making up the top 80 % of the business.

Thanks

JVN

 

[DonkeyOte]

If you have your list in sorted order why not use a cumulative % column in C

ie C2 = C1+B2 and so forth

then you can use a simple countif in D = COUNTIF(C:C,"<=80%")

I'm not saying you can't do it the more complicated way but simplifying things if and where possible would always be my recommended first step...

 

[JV0710]

Thanks LASW10

Yes, I am doing that right now ( having a cummulative coulumn in Another sheet - just to calculate the %) . . . The thing is - This example is part of a template that I do not want to go and edit and hence the question.

The list of suppliers may also be sorted in clusters ( Suppliers by Region ) in Future and that is why I am looking for a way to count them without sorting as well

 

[Mike Blackman]

Hi,

You could probably use some VBA for this;

Sub Count_Suppliers

MsgBox Application.CountIf(Columns("C:C"), ">=0.8")

End Sub

 

[JV0710]

Thanks Schwarzmanne

I am not that familiar with VBA . . . But I will give it a try

JVN

 

[DonkeyOte]

You could iterate in VBA using LARGE and incrementing k until such time as the sum of the LARGE array exeeeds 80 but I suspect there's a clever way of doing this without VBA... it's just I'm not sure I'm clever enough to know it... Richard Scholar is around and he may well do... and if not some of the formula whizzkids like Aladin, Barry etc will be online in the coming hours ...

The prior VBA post is working off the assumption that you have a cumulative % in C which I believe you said you didn't want ?

 

[DonkeyOte]

Do you want to test this VBA function out ?

Function TopX(lmt As Long, rng As Range)
Dim i As Long
Dim val_i As Double
For i = 1 To lmt
    val_i = val_i + WorksheetFunction.Large(rng, i)
    If val_i > lmt Then
        i = i - 1
        Exit For
    End If
Next i
TopX = i
End Function

To return count of suppliers in the 80% threshold assuming B2:B2000 contains individual % in cell C1:

=TOPX(80,B2:B2000)

 

[DonkeyOte]

Disregard above code -- I had errors in there, sorry!

Function TopX(lmt As Double, rng As Range)
Dim i As Long
Dim val_i As Double
For i = 1 To rng.Rows.Count
    val_i = val_i + Round(WorksheetFunction.Large(rng, i), 5)
    If val_i > lmt Then
        i = i - 1
        Exit For
    End If
Next i
TopX = i
End Function

Assuming values in range (B) are %

=TOPX(.8,B2:B2000)

 

[JV0710]

Thanks LASW10

Like I said . . . I am a novice to VBA, But I will give it a bash

JVN

 

[Richard Schollar]

if it's sorted then I can get a value using:

<TABLE style="WIDTH: 112pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=149 border=0 x:str><COLGROUP><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 16pt; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=21 height=17> </TD><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 48pt; BORDER-BOTTOM: #e9e9e9; BACKGROUND-COLOR: silver" width=64>Ainul</TD><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 48pt; BORDER-BOTTOM: #e9e9e9; BACKGROUND-COLOR: silver" width=64>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Score</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Rich</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.3">30%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Bob</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.3">30%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Terry</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.1">10%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Ainul</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.03">3%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>6</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Howard</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.03">3%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>7</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Sarah</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.02">2%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>8</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Tim</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.02">2%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>9</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Karim</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.01">1%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>10</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">JinWe</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.01">1%</TD></TR></TBODY></TABLE>

=MATCH(0.8,SUMIF(OFFSET($B$1,1,0,ROW($B$2:$B$10)-ROW($B$2)+1),">0"),1)

This will give the row where the % adds to 80% (or the row immediately below the one where the % exceeds 80% if there is no exact match).

The above needs to be entered with ctrl+Shift+Enter (it is an array formula).

There are probably easier ways (I would definitely use a helper column), but I can't think of how you would reproduce this if the data wasn't sorted.