Home |
Search |
Today's Posts |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))))))))))) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested "IF" Statement | Excel Worksheet Functions | |||
Nested IF statement with VLOOKUP | Excel Discussion (Misc queries) | |||
:confused: Nested if then else statement | Excel Worksheet Functions | |||
Nested IF Statement Question | Excel Discussion (Misc queries) | |||
7+ nested if statement? | Excel Worksheet Functions |