MAX -1

[cheshire191]

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.

 

[bj]

check out large() in help

 

[KL]

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

Regards,
KL

 

[Harlan Grove]

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.

 

[KL]

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

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.

 

[Roger Govier]

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

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

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.

 

[KL]

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

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

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

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.

 

[Harlan Grove]

KL wrote...

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:

If row insertion is a concern for the final argument, why isn't it a
concern
for the ROW calls inside the OFFSET call? In any case, if the formula
will
be copied down, then use ROWS based on the topmost cell through current
cell.

X99 [topmost result cell]:
=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),ROWS(X$99:X99))

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):

While I like the COUNTIF(OFFSET(...),...) idiom, there's a simpler way
to do
this generally in a single cell. Use the following array formula.

=LARGE(IF(MATCH(A1:A10,A1:A10,0)-1=ROW(A1:A10)-CELL("Row",A1:A10),A1:A10),2)

 

[KL]

Hi Harlan,

First of all thanks for your valuable opinion.

If row insertion is a concern for the final argument, why isn't it a
concern for the ROW calls inside the OFFSET call?

I guess because no matter where or how many rows you insert it should not
affect the row calls inside OFFSET function all ranges will be updated
accordingly.

this generally in a single cell. Use the following array formula.
=LARGE(IF(MATCH(A1:A10,A1:A10,0)-1=ROW(A1:A10)-CELL("Row",A1:A10),A1:A10),2)

Thanks again - I didn't know about this solution. However, to me your
formula has two potential issues:

1. the use of the CELL function. Issue is that I work accross 37 countries
in Europe and I guess the first argument ("row") would fail to
authomatically translate in a diffrent language environment. So I would
either replace it with MIN(ROW(A1:A10)) or even ROW(A1)

2. it doesn't tolerate empty cells. and the array returned by
IF(MATCH(A1:A10,A1:A10,0)-1=ROW(A1:A10)-CELL("Row",A1:A10),A1:A10) may
contain numbers, logical and error values at the same time, which adds
complexity when reusing the array.

Regards,
KL

 

[KL]

Hi Harlan,

First of all, thanks for your valuable input.

If row insertion is a concern for the final argument, why isn't it a
concern for the ROW calls inside the OFFSET call?

I guess because no matter where or how many rows you insert it should not
affect the row calls inside OFFSET function all ranges will be updated
accordingly.

...Use the following array formula.
=LARGE(IF(MATCH(A1:A10,A1:A10,0)-1=ROW(A1:A10)-CELL("Row",A1:A10),A1:A10),2)

Thanks again - I didn't know about this solution. However, to me your
formula has two potential issues:

1. the use of the CELL function. Issue is that I work accross 37 countries
in Europe and I guess the first argument ("row") would fail to
authomatically translate in a diffrent language environment. So I would
either replace it with MIN(ROW(A1:A10)) or even ROW(A1)

2. it doesn't tolerate empty cells. and the array returned by
IF(MATCH(A1:A10,A1:A10,0)-1=ROW(A1:A10)-CELL("Row",A1:A10),A1:A10) may
contain numbers, logical and error values at the same time, which adds
complexity when reusing the array.

Regards,
KL

 

[Harlan Grove]

KL wrote...
....

Thanks again - I didn't know about this solution. However, to me your
formula has two potential issues:

1. the use of the CELL function. Issue is that I work accross 37 countries
in Europe and I guess the first argument ("row") would fail to
authomatically translate in a diffrent language environment. So I would
either replace it with MIN(ROW(A1:A10)) or even ROW(A1)

Internationalization can be an issue. There's a third alternative,

=ROW(INDEX(A1:A10,1,1))

Longer than the others, but on its own doesn't require array entry and
allows for use of a single common defined name referring to the range.

2. it doesn't tolerate empty cells. and the array returned by
IF(MATCH(A1:A10,A1:A10,0)-1=ROW(A1:A10)-CELL("Row",A1:A10),A1:A10) may
contain numbers, logical and error values at the same time, which adds
complexity when reusing the array.

Since the range would presumably contain only numbers and blank cells,
easy enough to modify to eliminate blank cells.

=LARGE(IF(MATCH(A1:A10,A1:A10+0,0)-1=ROW(A1:A10)-ROW(INDEX(A1:A10,1,1)),
A1:A10),2)

If you also want to toss in text, boolean and error values,

=LARGE(IF(ISNUMBER(1/(MATCH(A1:A10,A1:A10,0)-1=ROW(A1:A10)
-ROW(INDEX(A1:A10,1,1)))),A1:A10),2)

Note that your formula chokes when A1:A10 contains error values while
this one ignores them (you're the one that introduced the possibility
of error values, perhaps you should also have checked how they'd affect
your formula).

 

[KL]

....you're the one that introduced the possibility

of error values, perhaps you should also have checked how they'd affect
your formula...

I did, but since both formulae equally failed to handle error values in the
source range, I didn't raise that

=LARGE(IF(ISNUMBER(1/(MATCH(A1:A10,A1:A10,0)-1=ROW(A1:A10)

-ROW(INDEX(A1:A10,1,1)))),A1:A10),2)

This formula is awesome.

Regards,
KL