Suppose I have 2 columns of data. Column 1 holds a set of values and
column 2 holds the probability of any one of these values occuring. So
the data would look like:

Column 1................Column 2
-1,256.......................0.02
-567..........................0.04
345...........................0.10
1,234........................0.23
3,234........................0.39

Etc...

What I need are two sets of formulas. The first set would take a
user-specifed value and find the closest value in column 1 that is
-greater than or equal to- the value entered by the user, and then
return the looked up value -and- and the corresponding probability from
column 2.

The second set of formulas would take the same user-specifed value and
find the closest value in column 2 that is -less than or equal to- the
value entered by the user, and then return the looked up value -and-
the corresponding probability from column 2.

E.g. in the above data table, tell the spreadsheet to look up a value
of 1,500. The closest existing value that is greater than or equal to
1,500 is 3,234 and the corresponding probabiltiy is 0.39 so return
these values. The closest existing value that is less than or equal to
1,500 is 1,234 and the corresponding probability is 0.23 so return
these values.

I think I could use the VLOOKUP function for the second set of formulas
but it doesn't work when trying to look up the -greater than or equal
to- value, just the -less than or equal to- value.

Also, any formulas would have to be able to work with negative numbers
in column 1 (in the spreadsheet the range can be any set of real
numbers).

Many thanks
-Rob


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=471514

Assuming that A1:B5 contains your data, and D1 contains your lookup
value, such as 1500, try the following...

1) Less Than or Equal To

=LOOKUP(D1,A1:A5)

and

=LOOKUP(D1,A1:A5,B1:B5)

2) Greater Than or Equal To

E1:

=MIN(IF(A1:A5=D1,A1:A5))

....confirmed with CONTROL+SHIFT+ENTER

F1:

=VLOOKUP(MIN(IF(A1:A5=D1,A1:A5)),A1:B5,2,0)

....confirmed with CONTROL+SHIFT+ENTER

or

=VLOOKUP(E1,A1:B5,2,0)

Hope this helps!

In article ,
TheRobsterUK
wrote:

Suppose I have 2 columns of data. Column 1 holds a set of values and
column 2 holds the probability of any one of these values occuring. So
the data would look like:

Column 1................Column 2
-1,256.......................0.02
-567..........................0.04
345...........................0.10
1,234........................0.23
3,234........................0.39

Etc...

What I need are two sets of formulas. The first set would take a
user-specifed value and find the closest value in column 1 that is
-greater than or equal to- the value entered by the user, and then
return the looked up value -and- and the corresponding probability from
column 2.

The second set of formulas would take the same user-specifed value and
find the closest value in column 2 that is -less than or equal to- the
value entered by the user, and then return the looked up value -and-
the corresponding probability from column 2.

E.g. in the above data table, tell the spreadsheet to look up a value
of 1,500. The closest existing value that is greater than or equal to
1,500 is 3,234 and the corresponding probabiltiy is 0.39 so return
these values. The closest existing value that is less than or equal to
1,500 is 1,234 and the corresponding probability is 0.23 so return
these values.

I think I could use the VLOOKUP function for the second set of formulas
but it doesn't work when trying to look up the -greater than or equal
to- value, just the -less than or equal to- value.

Also, any formulas would have to be able to work with negative numbers
in column 1 (in the spreadsheet the range can be any set of real
numbers).

Many thanks
-Rob

Domenic,

That works just fine!

Thanks
-Rob


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=471514