ok .. i used the MAX function to return the largest number in a range of
cells. but what if i want to return the 2nd or 3rd largest number within the
range?

thanks in advance.

check out large() in help

"cheshire191" wrote:

ok .. i used the MAX function to return the largest number in a range of
cells. but what if i want to return the 2nd or 3rd largest number within the
range?

thanks in advance.

=LARGE(A1:A10,2)
=LARGE(A1:A10,3)
=LARGE(A1:A10,4)
etc.

Regards,
KL


"cheshire191" wrote in message
...
ok .. i used the MAX function to return the largest number in a range of
cells. but what if i want to return the 2nd or 3rd largest number within
the
range?

thanks in advance.

KL wrote...
=LARGE(A1:A10,2)
=LARGE(A1:A10,3)
=LARGE(A1:A10,4)
etc.
....

If the range contained {1;2;2;3;3;3;4;4;4;4}, your formulas would
return the same value as MAX(A1:A10). Maybe that's what the OP wants if
there could be duplicate largest values. On the other hand, if the OP
wants distinct values in descending order, easier to list them
together.

C1:
=MAX(A1:A10)

C2 [array formula]:
=MAX(IF(A$1:A$10<C1,A$1:A$10))

Fill C2 down as needed.

Good point! Actually, I guess that can also be achieved by the following
array formula:

=LARGE(IF(COUNTIF(OFFSET($A$1:$A$10,,,ROW($A$1:$A$ 10)-ROW($A$1)+1),$A$1:$A$10)=1,$A$1:$A$10),2)

which obviously can't compete with your solution performance wise [though it
does everything in one cell] , but just in case performance was not an issue
and just for the sake of giving an alternative :-)

Regards,
KL


"Harlan Grove" wrote in message
oups.com...
KL wrote...
=LARGE(A1:A10,2)
=LARGE(A1:A10,3)
=LARGE(A1:A10,4)
etc.
...

If the range contained {1;2;2;3;3;3;4;4;4;4}, your formulas would
return the same value as MAX(A1:A10). Maybe that's what the OP wants if
there could be duplicate largest values. On the other hand, if the OP
wants distinct values in descending order, easier to list them
together.

C1:
=MAX(A1:A10)

C2 [array formula]:
=MAX(IF(A$1:A$10<C1,A$1:A$10))

Fill C2 down as needed.

Just a quick heads up, but I think that if you are going to give the OP his
2nd, 3rd largest etc. the final part of your formula should be
ROW(2:2) not just 2 so that it can be copied down and give the desired
results.
=LARGE(IF(COUNTIF(OFFSET($A$1:$A$10,,,ROW($A$1:$A$ 10)-ROW($A$1)+1),$A$1:$A$10)=1,$A$1:$A$10),ROW(2:2))

Array entered of course.

--
Regards

Roger Govier


"KL" wrote in message
...
Good point! Actually, I guess that can also be achieved by the following
array formula:

=LARGE(IF(COUNTIF(OFFSET($A$1:$A$10,,,ROW($A$1:$A$ 10)-ROW($A$1)+1),$A$1:$A$10)=1,$A$1:$A$10),2)

which obviously can't compete with your solution performance wise [though
it does everything in one cell] , but just in case performance was not an
issue and just for the sake of giving an alternative :-)

Regards,
KL


"Harlan Grove" wrote in message
oups.com...
KL wrote...
=LARGE(A1:A10,2)
=LARGE(A1:A10,3)
=LARGE(A1:A10,4)
etc.
...

If the range contained {1;2;2;3;3;3;4;4;4;4}, your formulas would
return the same value as MAX(A1:A10). Maybe that's what the OP wants if
there could be duplicate largest values. On the other hand, if the OP
wants distinct values in descending order, easier to list them
together.

C1:
=MAX(A1:A10)

C2 [array formula]:
=MAX(IF(A$1:A$10<C1,A$1:A$10))

Fill C2 down as needed.

Hi Roger,

Thanks for this. My only concern is that if you insert a new row on top of
the cell with the formula the ROW(2:2) will change to ROW(3:3), that is why
I prefer the hardcoding. Alternatively, you could do something like this:

=LARGE(IF(COUNTIF(OFFSET($A$1:$A$10,,,ROW($A$1:$A$ 10)-ROW($A$1)+1),$A$1:$A$10)=1,$A$1:$A$10),ROW(A2)-ROW($A$2)+2)

or maybe introducing the desired numbers 2, 3, 4, etc. in a parallel range
and then make a direct reference:

[D1]=2
[C1]=LARGE(IF(COUNTIF(OFFSET($A$1:$A$10,,,ROW($A$1:$A$ 10)-ROW($A$1)+1),$A$1:$A$10)=1,$A$1:$A$10),D1)

[D2]=3
[C2]=LARGE(IF(COUNTIF(OFFSET($A$1:$A$10,,,ROW($A$1:$A$ 10)-ROW($A$1)+1),$A$1:$A$10)=1,$A$1:$A$10),D2)

In any case, I would use Harlan's solution for this particular task. I guess
the beauty of the formula I suggested is that it sort of figures out how to
return an array of unique values from a range without using additional cells
(somebody has probably aleady suggested this before, but I personally
haven't been able to find a "one-cell" solution for this task on the net):

=IF(COUNTIF(OFFSET($A$1:$A$10,,,ROW($A$1:$A$10)-ROW($A$1)+1),$A$1:$A$10)=1,$A$1:$A$10)

Regards,
KL


"Roger Govier" wrote in message
...

Just a quick heads up, but I think that if you are going to give the OP
his 2nd, 3rd largest etc. the final part of your formula should be
ROW(2:2) not just 2 so that it can be copied down and give the desired
results.
=LARGE(IF(COUNTIF(OFFSET($A$1:$A$10,,,ROW($A$1:$A$ 10)-ROW($A$1)+1),$A$1:$A$10)=1,$A$1:$A$10),ROW(2:2))

Array entered of course.

--
Regards

Roger Govier


"KL" wrote in message
...
Good point! Actually, I guess that can also be achieved by the following
array formula:

=LARGE(IF(COUNTIF(OFFSET($A$1:$A$10,,,ROW($A$1:$A$ 10)-ROW($A$1)+1),$A$1:$A$10)=1,$A$1:$A$10),2)

which obviously can't compete with your solution performance wise [though
it does everything in one cell] , but just in case performance was not an
issue and just for the sake of giving an alternative :-)

Regards,
KL


"Harlan Grove" wrote in message
oups.com...
KL wrote...
=LARGE(A1:A10,2)
=LARGE(A1:A10,3)
=LARGE(A1:A10,4)
etc.
...

If the range contained {1;2;2;3;3;3;4;4;4;4}, your formulas would
return the same value as MAX(A1:A10). Maybe that's what the OP wants if
there could be duplicate largest values. On the other hand, if the OP
wants distinct values in descending order, easier to list them
together.

C1:
=MAX(A1:A10)

C2 [array formula]:
=MAX(IF(A$1:A$10<C1,A$1:A$10))

Fill C2 down as needed.