How can I get the two largest numbers from a column

[Larry Fish]

In Lotus I could create a formula @large(a1..a12,2) and I would get the second largest number in the range. Leave out the two and I would get the largest number. Or do I need to establish my worksheet like this.

..... A..........B............C.............D..........E...........F
1... Tree......Hight..... Profit........Yield.....Age........Hight
2....Apple.....>10................................................<16
3....Pear
4....Cherry
5
6.....Tree......Hight.......Profit.......Yield......Age
7... ...Apple... .18..........135..........14........20
8.... ..Cherry....12............96..........10........12
9.... ..Apple.... .13..........100...........9........14
10......Pear........9............75..........14........15
11......Cherry......8...........105.........10..........8


I can get the largest number with this formula =Dmax(A6..E11,"Profit",A1,A4) I would like this formula to give this result:
Apple, 135, 14

Then I need a formula that will give the same information for the next largest number: Cherry, 105, 10

Sorting is not an option.

Thanks for the help in advance

 

[XLGibbs]

There is a LARGE function in Excel

same exact syntax as your @Large syntax from Lotus


It looks like you could do this:

Book1
	A	B	C	D	E	F	G	H
3		A	B	C	D	E	F
4	1	Tree	Hight	Profit	Yield	Age	Hight
5	2	Apple	>10	<16
6	3	Pear
7	4	Cherry
8	5
9	6	Tree	Hight	Profit	Yield	Age		Apple,105,7
10	7	Apple	18	135	14	20
11	8	Cherry	13	105	9	14
12	9	Pear	12	96	23	6
13	10	Cherry	9	25	11	10
14	11	Apple	15	100	7	15
Sheet1

Formula

=LOOKUP(LARGE(D10:D14,2),D10:D14,B10:B14)  & "," & LARGE(D10:D14,2) & "," & LOOKUP(LARGE(D10:D14,2),E10:E14,E10:E14)

 

[BobUmlas]

=match(large(C7:C11,2),C7:C11,0)+6 will give the row of the 2nd largest #.
If that formula is placed in H1, for example, then these will give Cherry,105,10:
=INDEX(A:A,H1)
=INDEX(C:C,H1)
=INDEX(D:D,H1)

Bob Umlas
Excel MVP

 

[Larry Fish]

Gibbs

When I type in the formula I get an error

I changed the cells to meet my needs

 

[XLGibbs]

Larry, you gotta be more specific

Which formula, where did you type it, did you account for the cell references that may need to be changed...

If possible use the HTML maker (see the sticky posts at the top of the board)

 

[Larry Fish]

Gibbs

I can get your formula to work to this point. =Lookup(Large(D7:D12,2), D7:D13)

This is adjusted to my worksheet. When I add the rest I come up with an error.

Could you e-mail the formula to me at llfish@charter.net

 

[XLGibbs]

Have you tried copying the formula from the webpage and pasting the text into your formula window?

 

[Larry Fish]

Yes but it locks up excell

 

[XLGibbs]

Copy the text from where I wrote the formula between the code tags

like this

=LOOKUP(LARGE(D10:D14,2),D10:D14,B10:B14)  & "," & LARGE(D10:D14,2) & "," & LOOKUP(LARGE(D10:D14,2),E10:E14,E10:E14)

just highlight from the = sign to the end, and hit Ctrl - C

go to your excel sheet, go into the formula window for your cell (or anywhere) and hit Ctrl V.


I did not save my sample into a worksheet.

 

[Aladin Akyurek]

...
I can get the largest number with this formula =Dmax(A6..E11,"Profit",A1,A4) I would like this formula to give this result:
Apple, 135, 14

Then I need a formula that will give the same information for the next largest number: Cherry, 105, 10

...

It looks like you want a Top N list (with N = 2) of trees based on profit. Right?