Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
Hi,
I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
I think this will work for you
=INDIRECT("A" & 14 + B15) Dan E "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
It looks like...
=INDEX($A$15:$A$27,B15) "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
Tx guys.
Let me try both ways and I will post again here real soon. Sat "Aladin Akyurek" wrote in message ... It looks like... =INDEX($A$15:$A$27,B15) "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
re-read.
Use INDEX as suggested -- Don Guillett SalesAid Software Granite Shoals, TX "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
I tried the INDEX one, and....
That is BEAUTIFUL baby!!!!!!!! Tx, "Aladin Akyurek" wrote in message ... It looks like... =INDEX($A$15:$A$27,B15) "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
I went with the index one as simpler.
But I do appreciate your suggestion which will come in handy sometime soon for me. Best, Sat "Dan E" wrote in message ... My apologies, i didn't notice that hiccup when B15 = 12 If I read that right B15 < 12 is A15 - A25 B15 = 12 is A23 B15 12 is A26 up =IF(B15<12,INDIRECT("A"&14+B15),IF(B1512,INDIRECT ("A"&13+B15),A23)) Dan E "Dan E" wrote in message ... I think this will work for you =INDIRECT("A" & 14 + B15) Dan E "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
Hi Aladin, I'm probably being dense here I know, but I can't get the last few values to work with
that - 12/13/14 to return A23/A26/A27. Is the Ops sequence wrong, or what am I missing here? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference ---------------------------------------------------------------------------- "Aladin Akyurek" wrote in message ... It looks like... =INDEX($A$15:$A$27,B15) "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
This does not do what you originally intended. Per your post, as Dan pointed
out, when B15 = 12 the result should be A23 not A26 , as this solution results(B15=13 and B15=14 are also different). If you really want the result to equal A23 when B15=12, then I suggest you use Dan's second solution. tim "saturnin02" wrote in message ... I tried the INDEX one, and.... That is BEAUTIFUL baby!!!!!!!! Tx, "Aladin Akyurek" wrote in message ... It looks like... =INDEX($A$15:$A$27,B15) "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
Ken,
I hope the OP's posted seq is wrong. I just jumped to the conclusion while reading that there was an orderly seq. & that's the reason why I prefixed my answer with "It looks like...". Aladin "Ken Wright" wrote in message ... Hi Aladin, I'm probably being dense here I know, but I can't get the last few values to work with that - 12/13/14 to return A23/A26/A27. Is the Ops sequence wrong, or what am I missing here? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP -------------------------------------------------------------------------- -- Attitude - A little thing that makes a BIG difference -------------------------------------------------------------------------- -- "Aladin Akyurek" wrote in message ... It looks like... =INDEX($A$15:$A$27,B15) "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
Index just works perfectly for my purpose.
Tx to everybody that contributes btw. Sat "Don Guillett" wrote in message ... re-read. Use INDEX as suggested -- Don Guillett SalesAid Software Granite Shoals, TX "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
NESTED IF's too many
Just threw me a tad when he said it worked, but then didn't qualify the post wrt to the comments
made by the others about the sequence not being as sequential as everyone first thought. Think you were probably correct in your guess though. :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference ---------------------------------------------------------------------------- "Aladin Akyurek" wrote in message ... Ken, I hope the OP's posted seq is wrong. I just jumped to the conclusion while reading that there was an orderly seq. & that's the reason why I prefixed my answer with "It looks like...". Aladin "Ken Wright" wrote in message ... Hi Aladin, I'm probably being dense here I know, but I can't get the last few values to work with that - 12/13/14 to return A23/A26/A27. Is the Ops sequence wrong, or what am I missing here? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP -------------------------------------------------------------------------- -- Attitude - A little thing that makes a BIG difference -------------------------------------------------------------------------- -- "Aladin Akyurek" wrote in message ... It looks like... =INDEX($A$15:$A$27,B15) "saturnin02" wrote in message ... Hi, I need to calculate If staments based on the formula below. Unfortunately, there are too many nested IF statements. How can I write this formula in a more intellignet way that will work? =IF(B15=1,A15,IF(B15=2,A16,IF(B15=3,A17,IF(B15=4,A 18,IF(B15=5,A19,IF(B15=6,A 20,IF(B15=7,A21,if(B15=8,A22,IF(B15=9,A23,IF(B15=1 0,A24,IF(b15=11,A25,IF(B15 =12,A23,IF(B15=13,A26,IF(B15=14,A27))))))) Tx, Sat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested If's | Excel Worksheet Functions | |||
Nested if's and lookup | Excel Worksheet Functions | |||
A problem with nested IF's | Excel Worksheet Functions | |||
Nested If's | Excel Worksheet Functions | |||
How many nested IF's??? | Excel Discussion (Misc queries) |