Nested If Statement Workaround
 

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

put these values in cells A1:B12

-500 0.111
29 0.137
34 0.200
39 0.300
44 0.675
49 0.852
54 1.405
59 2.389
64 3.869
69 6.451
74 0.000
79 -1

then use this formula

=VLOOKUP(B16,A1:B12,2)




"Greg" wrote:

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

VLOOKUP would be your best bet.
--
David Biddulph

"Greg" wrote in message
...
Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

Set up a 2 column table like this:

..............A.............B
1..........0.......... 0.099
2........30.......... 0.111
3........35.......... 0.137
4........40.......... 0.2
5........45.......... 0.3
6........50.......... 0.675
7........55.......... 0.852
8........60.......... 1.405
9........65.......... 2.389
10......70.......... 3.869
11......75.......... 6.451

Then:

=IF(B1679,0,VLOOKUP(B16,A1:B11,2)

Biff

"Greg" wrote in message
...
Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

considering B16 is non-negative number
table
A B
1 29 0.099
2 34 0.111
3 39 0.137
4 44 0.2
5 49 0.3
6 54 0.675
7 59 0.852
8 64 1.405
9 69 2.389
10 74 3.869
11 79 6.451

B16 = IF(B1679,0,LOOKUP(B16,$A$1:$A$11,$B$1:$B$11))

"Greg" wrote:

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

This is misleading - you imply that the formula goes into cell B16, but
you can't put the formula there as it refers to that cell.

Pete

driller wrote:

considering B16 is non-negative number
table
A B
1 29 0.099
2 34 0.111
3 39 0.137
4 44 0.2
5 49 0.3
6 54 0.675
7 59 0.852
8 64 1.405
9 69 2.389
10 74 3.869
11 79 6.451

B16 = IF(B1679,0,LOOKUP(B16,$A$1:$A$11,$B$1:$B$11))

"Greg" wrote:

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

The OP will get an error for values less than 29. Since Lookup returns the
largest value that is *smaller* than or equal to the lookup value - I think
your table should start w/ a number that will always be smaller than the
smallest possible criteria - which could be done w/ a formula:

=0+((B16-1)*(B16<0))

The rest of the ranges would be as Biff posted.



"driller" wrote:

considering B16 is non-negative number
table
A B
1 29 0.099
2 34 0.111
3 39 0.137
4 44 0.2
5 49 0.3
6 54 0.675
7 59 0.852
8 64 1.405
9 69 2.389
10 74 3.869
11 79 6.451

B16 = IF(B1679,0,LOOKUP(B16,$A$1:$A$11,$B$1:$B$11))

"Greg" wrote:

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

Forget the table and VLookup. Try:

=CHOOSE(INT((B16 - 24)/5), 0.099, 0.111, 0.137, 0.2, 0.3, 0.675, 0.852,
1.405, 2.389, 3.869, 6.451)

Regards,
Greg Wilson

"Greg" wrote:

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

30 through 33 returns 0.099 - it s/b 0.111 according to the OP. The ranges
are off a little. Also, w/ values under 29 your formula generates an error
as the first parameter for Choose will be 0 or negative. And values over 89
will return an error instead of 0 as the OP indicated (the first parameter
will exceed the number of elements in the choose function).

With some small changes, however, I believe it will work:
=CHOOSE(MIN(MAX(1,ROUNDUP((B16-24)/5,0)),12), 0.099, 0.111, 0.137, 0.2, 0.3,
0.675, 0.852, 1.405, 2.389, 3.869, 6.451,0)

Personally, I would prefer a lookup table as it would be easier to maintain
and update, but that is only my opinion.


"Greg Wilson" wrote:

Forget the table and VLookup. Try:

=CHOOSE(INT((B16 - 24)/5), 0.099, 0.111, 0.137, 0.2, 0.3, 0.675, 0.852,
1.405, 2.389, 3.869, 6.451)

Regards,
Greg Wilson

"Greg" wrote:

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

Or slightly shorter:

=CHOOSE(MEDIAN(1,B16/5-4,12),0.099,0.111,0.137,0.2,0.3,0.675,0.852,1.405, 2.389,3.869,6.451,0)

JMB wrote:

30 through 33 returns 0.099 - it s/b 0.111 according to the OP. The ranges
are off a little. Also, w/ values under 29 your formula generates an error
as the first parameter for Choose will be 0 or negative. And values over 89
will return an error instead of 0 as the OP indicated (the first parameter
will exceed the number of elements in the choose function).

With some small changes, however, I believe it will work:
=CHOOSE(MIN(MAX(1,ROUNDUP((B16-24)/5,0)),12), 0.099, 0.111, 0.137, 0.2, 0.3,
0.675, 0.852, 1.405, 2.389, 3.869, 6.451,0)

Personally, I would prefer a lookup table as it would be easier to maintain
and update, but that is only my opinion.


"Greg Wilson" wrote:

Forget the table and VLookup. Try:

=CHOOSE(INT((B16 - 24)/5), 0.099, 0.111, 0.137, 0.2, 0.3, 0.675, 0.852,
1.405, 2.389, 3.869, 6.451)

Regards,
Greg Wilson

"Greg" wrote:

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

this is nice, many responded to your post Greg...cheers
To mislead may mean to challenge
try this again!
considering your input cell B16 is not blank and has non-negative number (=0)
table
A B
1 29 0.099
2 34 0.111
3 39 0.137
4 44 0.2
5 49 0.3
6 54 0.675
7 59 0.852
8 64 1.405
9 69 2.389
10 74 3.869
11 79 6.451
B16=0
copy/paste formula below on another cell
= IF(B1679,0,LOOKUP(B16,$A$1:$A$11,$B$1:$B$11))

cheers...

"Greg" wrote:

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))

Nested If Statement Workaround
 

sorry guys, i paste the old formula with Lookup versatility...

here it is..
place formula say on cell H1 (good on as-is basis the step 5 increment but
can be modify )
=IF(G179,0,IF(G1LOOKUP(G1,$A$1:$A$12,$A$1:$A$12) ,LOOKUP((G1+5),$A$1:$A$12,$B$1:$B$12),LOOKUP((G1), $A$1:$A$12,$B$1:$B$12)))

place your lookup value on cell G1 (=0)

your table

A B
1 0 0.099
2 29 0.099
3 34 0.111
4 39 0.137
5 44 0.2
6 49 0.3
7 54 0.675
8 59 0.852
9 64 1.405
10 69 2.389
11 74 3.869
12 79 6.451

if u want to adjust the data on Column B, no need to adjust the formula...

"Greg" wrote:

Hi all,
Can anyone assist me with a workaround for the following formula. As you
will be able to see it will not work because of the limit of 8 nested if
statements, but I was hoping someone knew of something that would work
without me using two different cells. The formula is
=if(B16<=29,0.099,if(b16<=34,0.111,if(b16<=39,0.13 7,if(b16<=44,0.2,if(b16<=49,0.3,if(b16<=54,0.675,i f(b16<=59,0.852,if(b16<=64,1.405,if(b16<=69,2.389, if(b16<=74,3.869,if(b16<=79,6.451,0)))))))))))