Find the MAX number in range, then find...

[pgarcia]

Hello all,
I'm lookin to find the MAX number in range, which I'm able to do. But I
would like the find the next low number from the MAX number. I'm also using
the INDEX-MATCH combo on this.
Background: I have list of 5 colums and I need to ge the top ten customer
info 1st by aging then by $ amount. So in my 1st top ten, it has the cutomer
info by oldest day. The 2nd top ten is customer info by hights $ amount.

Thanks

 

[Peo Sjoblom]

Use autofilter, it has top x built in

otherwise look at replace the max with LARGE (see help for LARGE)

--


Regards,


Peo Sjoblom

 

[T. Valko]

For the nth largest value:

LARGE(range,n)

where n = 1, 2, 3, 4 etc.

LARGE(range,1) is the same as MAX(range)

 

[pgarcia]

Sorry, I just remmber another time when I had to do this.

In cell A1:=IF(K2="","",K2-ROW()/10^10)
In cell B1:
=IF(ROW(K1)>COUNT($K:$K),"",INDEX(K:K,MATCH(LARGE($K:$K,ROW(K1)),$K:$K,0)))

Run that down to the end of your list and valua. You get your hights to
lowest value. Thanks for remiding me of LARGE.

 

[pgarcia]

Sorry, I just remmber another time when I had to do this.

In cell A1:=IF(K2="","",K2-ROW()/10^10)
In cell B1:
=IF(ROW(K1)>COUNT($K:$K),"",INDEX(K:K,MATCH(LARGE($K:$K,ROW(K1)),$K:$K,0)))

Run that down to the end of your list and valua. You get your hights to
lowest value. Thanks for remiding me of LARGE.