Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default looking up figures in a column and selecting next highest match

How do I create a function tolook at a column of figures and select
either the match or the next highest. So if I have a column called
'current' and want to create a column called 'new', the function needs
to look at 'current', compare it with a list of figures and put the
closest higher figure from that list into the 'new' column.

Thanks
Jane

  #2   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

Let's say the list is in a named range called "List".

If your "current" column begins in A1 and the "new" column begins in B1,
enter the following formula in B1:

=A1+MIN(IF(List=A1,List-A1))

as an array formula with <Ctrl <Shift <Enter. Copy down as needed.

I'm sure there's a much simpler way but I can't figure one out at the
moment.

--

Vasant




wrote in message
oups.com...
How do I create a function tolook at a column of figures and select
either the match or the next highest. So if I have a column called
'current' and want to create a column called 'new', the function needs
to look at 'current', compare it with a list of figures and put the
closest higher figure from that list into the 'new' column.

Thanks
Jane



  #3   Report Post  
Harlan Grove
 
Posts: n/a
Default

Vasant Nanavati wrote...
Let's say the list is in a named range called "List".

If your "current" column begins in A1 and the "new" column begins in B1,
enter the following formula in B1:

=A1+MIN(IF(List=A1,List-A1))

....

An alternative,

=MAX(A1,MIN(IF(List=A1,List)))

which is a bit more explicit that it returns A1 when A1 is strictly
greater than any values in List.

  #4   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

Hi Harlan:

I believe my formula does that too, unless I'm missing something (been known
to happen).
--

Vasant


"Harlan Grove" wrote in message
oups.com...
Vasant Nanavati wrote...
Let's say the list is in a named range called "List".

If your "current" column begins in A1 and the "new" column begins in B1,
enter the following formula in B1:

=A1+MIN(IF(List=A1,List-A1))

...

An alternative,

=MAX(A1,MIN(IF(List=A1,List)))

which is a bit more explicit that it returns A1 when A1 is strictly
greater than any values in List.



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
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Activating "Todays Date" column upon opening? Jeremy H via OfficeKB.com Excel Discussion (Misc queries) 3 August 25th 05 02:36 AM
Selecting matching items in a column Panajohn Excel Discussion (Misc queries) 2 August 15th 05 09:52 PM
Selecting a column with an integer lashio Excel Discussion (Misc queries) 2 March 27th 05 07:50 PM
How can I sort an entire spreadsheet from a list prod sorter Excel Worksheet Functions 4 November 17th 04 04:43 AM


All times are GMT +1. The time now is 01:23 PM.

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"