Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Activating "Todays Date" column upon opening? | Excel Discussion (Misc queries) | |||
Selecting matching items in a column | Excel Discussion (Misc queries) | |||
Selecting a column with an integer | Excel Discussion (Misc queries) | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |