Find the MAX number in range, then find...
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 |
Find the MAX number in range, then find...
Use autofilter, it has top x built in
otherwise look at replace the max with LARGE (see help for LARGE) -- Regards, Peo Sjoblom "pgarcia" wrote in message ... 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 |
Find the MAX number in range, then find...
For the nth largest value:
LARGE(range,n) where n = 1, 2, 3, 4 etc. LARGE(range,1) is the same as MAX(range) -- Biff Microsoft Excel MVP "pgarcia" wrote in message ... 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 |
Find the MAX number in range, then find...
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. "T. Valko" wrote: For the nth largest value: LARGE(range,n) where n = 1, 2, 3, 4 etc. LARGE(range,1) is the same as MAX(range) -- Biff Microsoft Excel MVP "pgarcia" wrote in message ... 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 |
Find the MAX number in range, then find...
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. "Peo Sjoblom" wrote: Use autofilter, it has top x built in otherwise look at replace the max with LARGE (see help for LARGE) -- Regards, Peo Sjoblom "pgarcia" wrote in message ... 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 |
All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com