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)


"Carl Hilton" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[Carl Hilton]

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

THANKS


"Carl Hilton" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[Carl Hilton]

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

Carl


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
>
>

[tjtjjtjt]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
>
>
>

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


"tjtjjtjt" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> > >
> >
> >
> >

[Carl Hilton]

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


"Carl Hilton" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

[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

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

[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"?
"Carl Hilton" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[tjtjjtjt]

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/as...872901033.aspx
http://office.microsoft.com/en-us/as...872911033.aspx

tj

"Carl Hilton" wrote:

> Never mind, I found the error... and think I undersdand... Where is a
> discussion about making arrays with {}?
>
>
> "Carl Hilton" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

[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


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
>
>

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

Carl Hilton wrote:
> 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]

Thanks for the feedback Carl.

Bob


"Carl Hilton" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
>
>