Formula not working

C

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
 
B

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)


Carl Hilton said:
The following works:
 
C

Carl Hilton

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

Carl
 
C

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.
 
C

Carl Hilton

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

tjtjjtjt

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
 
R

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"?
 
B

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)
 
C

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
 
A

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,"")
 
B

Bob Phillips

Thanks for the feedback Carl.

Bob


Carl Hilton said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top