top ten

H

Harold

Hi,
I have a spreadsheet which has 3 rows of 40 entrys, Is there
formula which will pick the top 10 entrys out of it

thankyo
 
F

Frank Kabel

Hi
you may have a look at LARGE.
e.g.
=LARGE(A1:C20,2)
to get the second highest value
 
B

Bob Phillips

Assuming data in A1:AJ3, in

A5: =LARGE(A1:AJ3,ROW(A1))

and copy down to A14

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
K

kkknie

If you want to add them to gether, you could use:

=MAX(A1:C40)+LARGE(A1:C40,2)+LARGE(A1:C40,3)+LARGE(A1:C40,4)+LARGE(A1:C40,5)+LARGE(A1:C40,6)+LARGE(A1:C40,7)+LARGE(A1:C40,8)+LARGE(A1:C40,9)+LARGE(A1:C40,10)

Assuming your data is in A1:C40.

I don't know any other formula way, but then again, I'm not all tha
bright...
 
C

Chip Pearson

For the SUM of the largest 10 integers in A1:C40, you can use the
following formula:

=SUMPRODUCT(LARGE(A1:C40,ROW(INDIRECT("1:10"))))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
H

Harold

Thankyou using the "large" on the down the list gave me a top ten ... I
said it before and I say it again, you guys are legends
 
B

Bob Phillips

or more simply

=SUMPRODUCT(LARGE(A1:C40,ROW(1:10)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Chip Pearson said:
For the SUM of the largest 10 integers in A1:C40, you can use the
following formula:

=SUMPRODUCT(LARGE(A1:C40,ROW(INDIRECT("1:10"))))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
D

Dave Peterson

But that could cause problems if a row is inserted/deleted in 1:10.



Bob said:
or more simply

=SUMPRODUCT(LARGE(A1:C40,ROW(1:10)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top