Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find number within a range | Excel Worksheet Functions | |||
How to find the number of value within a range? | Excel Worksheet Functions | |||
How to find the number of value within a range? | Excel Discussion (Misc queries) | |||
Find a negative number in a range | Excel Worksheet Functions | |||
find the cell above any number in any range | Excel Worksheet Functions |