top 100 list

  • Thread starter Thread starter Ivan
  • Start date Start date
I

Ivan

Hi,

Would like to know how to create a "top 100" list in Excel
97. Also, how do you create a top 1%, 10%, 50% list?

Thanks
 
Data>Filter>Autofilter>*click a down arrow and Top 10...*
Change the value to Top 100 Items
The Items/Percent choice may be made and the appropriate value entered to
filter the list for Top (or Bottom) 5%, 10%, etc.
 
Hi, thanks for replying,

The list is very simple:
the first column is a list of names,
the second column has an amount paid.

The list is sorted by the amount paid with the largest
amount on top.

I would like to know, for example, how much the highest
paying 100 names pays as a percentage of all names.

Or another example, how much the highest 10% of names pays
as a percentage of all names.

Thanks again for responding.

Ivan
 
Hi, Thanks very much for your help.

Is there any way of doing the same thing without using the
Data>Filter>Autofilter option? e.g. using a formula
perhaps.

Thanks again

Ivan
 
Ok, assuming for simplicity names are in A2:A501 and amounts in B2:B501
then 1. would be

=SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT("1:100"))))/SUM(B2:B501)

format as percentage
maybe rounded to 2 decimals

=ROUND(SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT("1:100"))))/SUM(B2:B501),2)


2.

Since I used 500 rows with amounts 10% would be 50 top

=ROUND(SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT("1:50"))))/SUM(B2:B501),2)

or somewhat more generic

=ROUND(SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT("1:"&COUNT(B2:B501)*10%))))/SUM
(B2:B501),2)
 
Blimey... Thanks mate :o)
-----Original Message-----
Ok, assuming for simplicity names are in A2:A501 and amounts in B2:B501
then 1. would be

=SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT("1:100"))))/SUM (B2:B501)

format as percentage
maybe rounded to 2 decimals

=ROUND(SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT ("1:100"))))/SUM(B2:B501),2)


2.

Since I used 500 rows with amounts 10% would be 50 top

=ROUND(SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT ("1:50"))))/SUM(B2:B501),2)

or somewhat more generic

=ROUND(SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT("1:"&COUNT (B2:B501)*10%))))/SUM
(B2:B501),2)

--

Regards,

Peo Sjoblom




.
Click to expand...
 
Peo Sjoblom said:
Ok, assuming for simplicity names are in A2:A501 and amounts in B2:B501
then 1. would be

=SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT("1:100"))))/SUM(B2:B501)
Click to expand...
[...]

Would it not be better/desirable to have...

=SUMIF(B2:B501,">="&LARGE(B2:B501,100))/SUM(B2:B501)

since the last largest value might have 2 or more tokens?

For example:

The SumProduct/Sum formula, applied to

{28;49;49;49;49;53;58;62;71;26;59;69;60;30;49;56;35;31;21}

with N of Top N set to 10, would give:

0.64823

while the SumIf/Sum formula computes:

0.81084
 
Correction: What I suggested should be changed to:

=(SUMIF(Range,">"&LARGE(Range,N))+COUNTIF(Range,LARGE(Range,N))*LARGE(Range,
N))

where N = 100 in the OP's case.

For the example I cookep up, it becomes:

=(SUMIF(B2:B20,">"&LARGE(B2:B20,10))+COUNTIF(B2:B20,LARGE(B2:B20,10))*LARGE(
B2:B20,10))

Aladin Akyurek said:
Peo Sjoblom said:
Ok, assuming for simplicity names are in A2:A501 and amounts in B2:B501
then 1. would be

=SUMPRODUCT(LARGE(B2:B501,ROW(INDIRECT("1:100"))))/SUM(B2:B501)
Click to expand...
[...]

Would it not be better/desirable to have...

=SUMIF(B2:B501,">="&LARGE(B2:B501,100))/SUM(B2:B501)

since the last largest value might have 2 or more tokens?

For example:

The SumProduct/Sum formula, applied to

{28;49;49;49;49;53;58;62;71;26;59;69;60;30;49;56;35;31;21}

with N of Top N set to 10, would give:

0.64823

while the SumIf/Sum formula computes:

0.81084
Click to expand...
 
Aladin Akyurek said:
Correction: What I suggested should be changed to:

=(SUMIF(Range,">"&LARGE(Range,N))
+COUNTIF(Range,LARGE(Range,N))*LARGE(Range,N))

where N = 100 in the OP's case.

For the example I cookep up, it becomes:

=(SUMIF(B2:B20,">"&LARGE(B2:B20,10))
+COUNTIF(B2:B20,LARGE(B2:B20,10))*LARGE(B2:B20,10))
Click to expand...
....

I thought you were doing this to handle ties for 10th largest, so only
counting one of them. But it doesn't work for that if there are, e.g., 3
scores tied for 9th place, in which case you're going to pull only 8 values
in the first SUMIF and 3 more in the COUNTIF*LARGE.

The other suggestions, using SUMPRODUCT(LARGE(X,{1,..,10})), avoid this.
Your approach would need to be changed to

=SUMIF(X,">"&LARGE(X,10))+(10-COUNTIF(X,">"&LARGE(X,10)))*LARGE(X,10)

if only 10 values should be included even if the 11th, 12th, etc. are the
same as the 10th, or just make it

=SUMIF(X,">="&LARGE(X,10))

if all entries equal to the 10th largest should be included.

Also, in the first formula having to call LARGE this many times, along with
SUMIF rather than SUMPRODUCT and COUNTIF, I think you'd need to demonstrate
the recalc speed gain to justify all these rather expensive function calls.
Your approach may be faster on recalc, but it's not obvious.
 
Harlan Grove said:
...

I thought you were doing this to handle ties for 10th largest, so only
counting one of them. But it doesn't work for that if there are, e.g., 3
scores tied for 9th place, in which case you're going to pull only 8 values
in the first SUMIF and 3 more in the COUNTIF*LARGE.
Click to expand...

No, quite the opposite. The intent is to include the ties of the Nth value.
For the clear cut questions like summing 4 best scores out of 5, we have
'Sum *exactly 4* largest scores'. A SumProduct formula incorporating Large
with the Row/Indirect idiom or a pre-computed array ({1,2,3,4}) is the
right thing to use. The OP's question in this thread Sum 100 largest values
and divide the result by the sum of all values doesn't sound (to me) as
'exactly 100'. N should be self-adjusting, that is, the ties of Nth sould be
included or at least considered for inclusion. The issue of the
ties/multiple instances here has a similarity to the Top N questions.
Someone I helped with his Top 10 question has explicitly asked for the
inclusion of such ties. A while later I read you mentioning this issue to an
Op at the worksheet.functions. This issue is also involved with questions
like "How can I retieve a value associated with the Min/Max value" (Top N,
where N=1, question of course). Hence my attempt at the formula you took up.
The other suggestions, using SUMPRODUCT(LARGE(X,{1,..,10})), avoid this.
Your approach would need to be changed to

=SUMIF(X,">"&LARGE(X,10))+(10-COUNTIF(X,">"&LARGE(X,10)))*LARGE(X,10)
Click to expand...

I don't think this modfied formula for *exactly 10* should replace the way
we treat questions of Sum *exactly N* largest/smallest values.
if only 10 values should be included even if the 11th, 12th, etc. are the
same as the 10th, or just make it

=SUMIF(X,">="&LARGE(X,10))

if all entries equal to the 10th largest should be included.
Click to expand...

Won't do because it includes too much.
Also, in the first formula having to call LARGE this many times, along with
SUMIF rather than SUMPRODUCT and COUNTIF, I think you'd need to demonstrate
the recalc speed gain to justify all these rather expensive function calls.
Your approach may be faster on recalc, but it's not obvious.
Click to expand...

Well, the Large(X,N) can better have a cell of its own for reasons of
efficiency.
 
OK, Just a short a recap...

=SUMIF(X,">="&LARGE(X,N))

suffices for the inclusion of the ties.

If the OP wants to include ties...

=SUMIF(B2:B501,">="&LARGE(B2:B501,100))/SUM(B2:B501)

For *exactly N*, we have:

[1]

=SUMPRODUCT(LARGE(X,ROW(INDRECT("1:"&N))))

and

[2]

=SUMIF(X,">"&LARGE(X,N))+(N-COUNTIF(X,">"&LARGE(X,N)))*LARGE(X,N)

including your modification.

I timed

=SUMPRODUCT(LARGE(A2:A1001,ROW(INDIRECT("1:"&C2))))

where C2 houses 10,

against

=SUMIF(A2:A1001,">"&C3)+(C2-COUNTIF(A2:A1001,">"&C3))*C3

where C2 houses 10 and C3

=LARGE(A2:A1001,C2)

The latter comes out as clear cut winner.
 
Back
Top