Change the 220 in Bob's formula to 210.

Is it this part of the formula that is throwing you off:
{0,18.5;160,15.9;170,13.8;180,12.5;190,10.7;200,9. 6;210,9.2},

What this does is create what amounts to a 2 column by 7 row array of data.
Each comma means go to the next value in the row. Each semicolon means go to
the beginning of the next row.
The VLOOKUP then looks for the value in (as Bob wrote it) B2 in the first
column of that array. It then returns the value in the second column from the
same row.

This may be easier to understand if you create a data range on your
spreadhseet to store the values in.
Also, this site:
http://www.contextures.com/xlFunctions02.html
should help.
The example use mostly cell ranges instead of typed arrays--the values in
the {}.

hth,

tj

"Carl Hilton" wrote:

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