Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 304
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 964
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 304
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 304
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find number within a range Mona Excel Worksheet Functions 3 November 28th 07 08:54 PM
How to find the number of value within a range? Eric Excel Worksheet Functions 11 February 28th 07 08:24 PM
How to find the number of value within a range? Eric Excel Discussion (Misc queries) 4 February 27th 07 01:16 PM
Find a negative number in a range Scott at Culvers Excel Worksheet Functions 1 November 22nd 05 05:55 PM
find the cell above any number in any range steve alcock Excel Worksheet Functions 12 May 27th 05 05:48 AM


All times are GMT +1. The time now is 10:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"