Thanks for the feedback Carl.

Bob


"Carl Hilton" wrote in message
...
Thanks a lot Bob... I would say that your solution is ingenious.. and fits
my needs exactly, especially with the continum of the number range.

I had three other sets of numbers to work with and was able to adapt your
method to all solutions.

Carl


"Bob Phillips" wrote in message
...
Small mistake, it should be



=IF(AND(ISNUMBER(B2),B2=150,B2<=220),VLOOKUP(B2,{ 0,18.5;160,15.9;170,13.8;1
80,12.5;190,10.7;200,9.6;210,9.2},2)/2,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Carl Hilton" wrote in message
...
Trying Bob's forumla works with all my values except for a value of
218...
My formula should return 4.6 but Bob's returns 4.8... I will try to
figure
out how his forumla works so I can troubleshoot it.


"tjtjjtjt" wrote in message
...
As Bob suggested, use VLOOKUP. He provided an example that looks
like
it
will
do what you are attempting.

For more, see:
http://www.contextures.com/xlFunctions02.html

tj

"Carl Hilton" wrote:

OK, I see there is a 7 nested limit... How can I get around this
for
this
formula??

THANKS


"Carl Hilton" wrote in message
...
The following works:






=IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1





70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1


0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2

However, the below does NOT






=IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15





...9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=18 0,B22<=189),12.5,IF(AND(B22






=190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219),
9.2)))))))/2,"")

By adding the ISNUMBER and first IF, casuses the formula to not
work... Is
it the length of the formula?

Carl