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

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

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

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

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

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

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

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

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

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

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

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