Formula not working

[Carl Hilton]

The following works:

=IF(AND(B22>=150,B22<=159),18.5,IF(AND(B22>=160,B22<=169),15.9,IF(AND(B22>=1
70,B22<=179),13.8,IF(AND(B22>=180,B22<=189),12.5,IF(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>=180,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

 

[Bob Phillips]

Carl,

Yes it is. There is a limit of 7 nested IFs. You could try this though

=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;220,9.2},2)/2,0)

--

HTH

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

The following works:

 

[Carl Hilton]

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

THANKS

The following works:

 

[Carl Hilton]

That was quick... I looked up the VLOOKUP and still can not determine how
your formula works.

Carl

 

[Guest]

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]

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.

 

[Carl Hilton]

Never mind, I found the error... and think I undersdand... Where is a
discussion about making arrays with {}?

 

[Guest]

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

 

[Roger H.]

The limit for nested "IF" functions is seven- you have eight.So, in a sense
, yes, the formula is too 'long' as you have too many nested "IF"s.Plus,
what do you want returned for "True" in the first "IF"?

 

[Guest]

These may help. Your question is specifically about array constants. These
articles talk about Array Functions more broadly.
Look at the beginning of the second article for a quick rundown of Array
Constants.

http://office.microsoft.com/en-us/assistance/HA010872901033.aspx
http://office.microsoft.com/en-us/assistance/HA010872911033.aspx

tj

 

[Bob Phillips]

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]

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

 

[Aladin Akyurek]

=IF(ISNUMBER(B22),LOOKUP(B22,{-9.99999999999999E+307;150;160;170;180;190;200;210;220},{0;18.5;15.9;13.8;12.5;10.7;9.6;9.2;0})/2,"")

 

[Bob Phillips]

Thanks for the feedback Carl.

Bob

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



=IF(AND(ISNUMBER(B2),B2>=150,B2<=220),VLOOKUP(B2,{0,18.5;160,15.9;170,13.8;1 like