Sample data:
Rank Minutes
1 34792
2 28301
3 26718
4 19282
5 16849
6 11002
....1500 0

Basically I then request to view let's say the top 5...then I need a formula
that will look for the top 5 minutes then determine what percentage that is
of the whole. For example, in this case, let's say the sum of all 1500
entries is 592,000. And I say top 3..then in this case the formula would
calculate 34792+28301+26718=89811/592,000 = 15% of total data. If I request
top 5 then it would give me 34792+28301+26718+19282+16849=125582/592,000=21%.
Any ideas on the formula?

Thanks.

Brute-force..........

=(LARGE(B:B,1)+LARGE(B:B,2)+LARGE(B:B,3)+LARGE(B:B ,4)+LARGE(B:B,5))/SUM(B:B)

all on one line, watch out for word wrap.

Vaya con Dios,
Chuck, CABGx3



"A.S." wrote:

Sample data:
Rank Minutes
1 34792
2 28301
3 26718
4 19282
5 16849
6 11002
...1500 0

Basically I then request to view let's say the top 5...then I need a formula
that will look for the top 5 minutes then determine what percentage that is
of the whole. For example, in this case, let's say the sum of all 1500
entries is 592,000. And I say top 3..then in this case the formula would
calculate 34792+28301+26718=89811/592,000 = 15% of total data. If I request
top 5 then it would give me 34792+28301+26718+19282+16849=125582/592,000=21%.
Any ideas on the formula?

Thanks.

One more way to get the sum of the 3 largest:
=SUMPRODUCT(LARGE(B:B,{1,2,3}))

And if you have lots of numbers at the top to review (say 12):
=SUMPRODUCT(LARGE(B:B,ROW(INDIRECT("1:12"))))

And then divide by the sum if you want.


A.S. wrote:

Sample data:
Rank Minutes
1 34792
2 28301
3 26718
4 19282
5 16849
6 11002
...1500 0

Basically I then request to view let's say the top 5...then I need a formula
that will look for the top 5 minutes then determine what percentage that is
of the whole. For example, in this case, let's say the sum of all 1500
entries is 592,000. And I say top 3..then in this case the formula would
calculate 34792+28301+26718=89811/592,000 = 15% of total data. If I request
top 5 then it would give me 34792+28301+26718+19282+16849=125582/592,000=21%.
Any ideas on the formula?

Thanks.

--

Dave Peterson

Let's say cell C1 has X. X can equal to anything and can vary. In other
words, I could have top 5, but I can change that to top 35 or top 125, etc.

So basically I was looking for a formula that looks at what is in C1 then
goes and finds the top 5 etc. So Again let's say that C1=5 then it adds the
top 5/SUM=%
or if C1=135 then it adds the top 135/SUM=%.

Hope this clears it up.
"CLR" wrote:

Brute-force..........

=(LARGE(B:B,1)+LARGE(B:B,2)+LARGE(B:B,3)+LARGE(B:B ,4)+LARGE(B:B,5))/SUM(B:B)

all on one line, watch out for word wrap.

Vaya con Dios,
Chuck, CABGx3



"A.S." wrote:

Sample data:
Rank Minutes
1 34792
2 28301
3 26718
4 19282
5 16849
6 11002
...1500 0

Basically I then request to view let's say the top 5...then I need a formula
that will look for the top 5 minutes then determine what percentage that is
of the whole. For example, in this case, let's say the sum of all 1500
entries is 592,000. And I say top 3..then in this case the formula would
calculate 34792+28301+26718=89811/592,000 = 15% of total data. If I request
top 5 then it would give me 34792+28301+26718+19282+16849=125582/592,000=21%.
Any ideas on the formula?

Thanks.

I think not with a formula, but probably with VBA, except I do not have time
at the moment.........maybe someone else can pick it up.......



"A.S." wrote:

Let's say cell C1 has X. X can equal to anything and can vary. In other
words, I could have top 5, but I can change that to top 35 or top 125, etc.

So basically I was looking for a formula that looks at what is in C1 then
goes and finds the top 5 etc. So Again let's say that C1=5 then it adds the
top 5/SUM=%
or if C1=135 then it adds the top 135/SUM=%.

Hope this clears it up.
"CLR" wrote:

Brute-force..........

=(LARGE(B:B,1)+LARGE(B:B,2)+LARGE(B:B,3)+LARGE(B:B ,4)+LARGE(B:B,5))/SUM(B:B)

all on one line, watch out for word wrap.

Vaya con Dios,
Chuck, CABGx3



"A.S." wrote:

Sample data:
Rank Minutes
1 34792
2 28301
3 26718
4 19282
5 16849
6 11002
...1500 0

Basically I then request to view let's say the top 5...then I need a formula
that will look for the top 5 minutes then determine what percentage that is
of the whole. For example, in this case, let's say the sum of all 1500
entries is 592,000. And I say top 3..then in this case the formula would
calculate 34792+28301+26718=89811/592,000 = 15% of total data. If I request
top 5 then it would give me 34792+28301+26718+19282+16849=125582/592,000=21%.
Any ideas on the formula?

Thanks.

This will sum the top C1 found in column B:
=SUMPRODUCT(LARGE(B:B,ROW(INDIRECT("1:"&C1))))

And you can still divide it by the sum if you want.

A.S. wrote:

Let's say cell C1 has X. X can equal to anything and can vary. In other
words, I could have top 5, but I can change that to top 35 or top 125, etc.

So basically I was looking for a formula that looks at what is in C1 then
goes and finds the top 5 etc. So Again let's say that C1=5 then it adds the
top 5/SUM=%
or if C1=135 then it adds the top 135/SUM=%.

Hope this clears it up.
"CLR" wrote:

Brute-force..........

=(LARGE(B:B,1)+LARGE(B:B,2)+LARGE(B:B,3)+LARGE(B:B ,4)+LARGE(B:B,5))/SUM(B:B)

all on one line, watch out for word wrap.

Vaya con Dios,
Chuck, CABGx3



"A.S." wrote:

Sample data:
Rank Minutes
1 34792
2 28301
3 26718
4 19282
5 16849
6 11002
...1500 0

Basically I then request to view let's say the top 5...then I need a formula
that will look for the top 5 minutes then determine what percentage that is
of the whole. For example, in this case, let's say the sum of all 1500
entries is 592,000. And I say top 3..then in this case the formula would
calculate 34792+28301+26718=89811/592,000 = 15% of total data. If I request
top 5 then it would give me 34792+28301+26718+19282+16849=125582/592,000=21%.
Any ideas on the formula?

Thanks.

--

Dave Peterson