Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way than Nesting, or other solutions
I have a formula that nests... 220 times. How do I get it in there?? It's for
calculating different numbers when a different choice from a drop-down list is picked. Here is the formula: [spoiler]=IF(AND(F3="Fighter",C2=1),T("+1"),IF(AND(F3="Figh ter",C2=2),T("+2"),IF(AND(F3="Fighter",C2=3),T("+3 "),IF(AND(F3="Fighter",C2=4),T("+3"),IF(AND(F3="Fi ghter",C2=5),T("+4"),IF(AND(F3="Fighter",C2=6),T(" +5"), IF(AND(F3="Fighter",C2=7),T("+6"),IF(AND(F3="Fight er",C2=8),T("+6"),IF(AND(F3="Fighter",C2=9),T("+7" ), IF(AND(F3="Fighter",C2=10),T("+8"),IF(AND(F3="Figh ter",C2=11),T("+9"),IF(AND(F3="Fighter",C2=12),T(" +9"), IF(AND(F3="Fighter",C2=13),T("+10"),IF(AND(F3="Fig hter",C2=14),T("+11"),IF(AND(F3="Fighter",C2=15),T ("+12"),IF(AND(F3="Fighter",C2=16),T("+12"),IF(AND (F3="Fighter",C2=17),T("+13"),IF(AND(F3="Fighter", C2=18),T("+14"),IF(AND(F3="Fighter",C2=19),T("+15" ),IF(AND(F3="Fighter",C2=20),T("+15"), IF(AND(F3="Spell User",C2=1),T("+1"),IF(AND(F3="Spell User",C2=2),T("+1"),IF(AND(F3="Spell User",C2=3),T("+1"),IF(AND(F3="Spell User",C2=4),T("+1"),IF(AND(F3="Spell User",C2=6),T("+2"),IF(AND(F3="Spell User",C2=6),T("+2"),IF(AND(F3="Spell User",C2=7),T("+2"),IF(AND(F3="Spell User",C2=8),T("+2"),IF(AND(F3="Spell User",C2=9),T("+3"),IF(AND(F3="Spell User",C2=10),T("+3"),IF(AND(F3="Spell User",C2=11),T("+3"),IF(AND(F3="Spell User",C2=12),T("+3"),IF(AND(F3="Spell User",C2=13),T("+4"),IF(AND(F3="Spell User",C2=14),T("+4"),IF(AND(F3="Spell User",C2=15),T("+4"),IF(AND(F3="Spell User",C2=16),T("+4"),IF(AND(F3="Spell User",C2=17),T("+5"),IF(AND(F3="Spell User",C2=18),T("+5"),IF(AND(F3="Spell User",C2=19),T("+5"),IF(AND(F3="Spell User",C2=20),T("+5"), IF(AND(F3="Illusionist",C2=1),T("+1"),IF(AND(F3="I llusionist",C2=2),T("+1"),IF(AND(F3="Illusionist", C2=3),T("+1"),IF(AND(F3="Illusionist",C2=4),T("+1" ),IF(AND(F3="Illusionist",C2=5),T("+2"),IF(AND(F3= "Illusionist",C2=6),T("+2"),IF(AND(F3="Illusionist ",C2=7),T("+2"),IF(AND(F3="Illusionist",C2=8),T("+ 2"),IF(AND(F3="Illusionist",C2=9),T("+3"),IF(AND(F 3="Illusionist",C2=10),T("+3"),IF(AND(F3="Illusion ist",C2=11),T("+3"),IF(AND(F3="Illusionist",C2=12) ,T("+3"),IF(AND(F3="Illusionist",C2=13),T("+4"),IF (AND(F3="Illusionist",C2=14),T("+4"),IF(AND(F3="Il lusionist",C2=15),T("+4"),IF(AND(F3="Illusionist", C2=16),T("+4"),IF(AND(F3="Illusionist",C2=17),T("+ 5"),IF(AND(F3="Illusionist",C2=18),T("+5"),IF(AND( F3="Illusionist",C2=19),T("+5"),IF(AND(F3="Illusio nist",C2=20),T("+5"), IF(AND(F3="Rogue ",C2=1),T("+1"),IF(AND(F3="Rogue ",C2=2),T("+1"),IF(AND(F3="Rogue ",C2=3),T("+2"),IF(AND(F3="Rogue ",C2=4),T("+2"),IF(AND(F3="Rogue ",C2=5),T("+3"),IF(AND(F3="Rogue ",C2=6),T("+3"),IF(AND(F3="Rogue ",C2=7),T("+4"),IF(AND(F3="Rogue ",C2=8),T("+4"),IF(AND(F3="Rogue ",C2=9),T("+5"),IF(AND(F3="Rogue ",C2=10),T("+5"),IF(AND(F3="Rogue ",C2=11),T("+6"),IF(AND(F3="Rogue ",C2=12),T("+6"),IF(AND(F3="Rogue ",C2=13),T("+7"),IF(AND(F3="Rogue ",C2=14),T("+7"),IF(AND(F3="Rogue ",C2=15),T("+8"),IF(AND(F3="Rogue ",C2=16),T("+8"),IF(AND(F3="Rogue ",C2=17),T("+9"),IF(AND(F3="Rogue ",C2=18),T("+9"),IF(AND(F3="Rogue ",C2=19),T("+10"),IF(AND(F3="Rogue ",C2=20),T("+10"), IF(AND(F3="Apprentice ",C2=1),T("+1"),IF(AND(F3="Apprentice ",C2=2),T("+1"),IF(AND(F3="Apprentice ",C2=3),T("+2"),IF(AND(F3="Apprentice ",C2=4),T("+2"),IF(AND(F3="Apprentice ",C2=5),T("+3"),IF(AND(F3="Apprentice ",C2=6),T("+3"),IF(AND(F3="Apprentice ",C2=7),T("+4"),IF(AND(F3="Apprentice ",C2=8),T("+4"),IF(AND(F3="Apprentice ",C2=9),T("+5"),IF(AND(F3="Apprentice ",C2=10),T("+5"),IF(AND(F3="Apprentice ",C2=11),T("+6"),IF(AND(F3="Apprentice ",C2=12),T("+6"),IF(AND(F3="Apprentice ",C2=13),T("+7"),IF(AND(F3="Apprentice ",C2=14),T("+7"),IF(AND(F3="Apprentice ",C2=15),T("+8"),IF(AND(F3="Apprentice ",C2=16),T("+8"),IF(AND(F3="Apprentice ",C2=17),T("+9"),IF(AND(F3="Apprentice ",C2=18),T("+9"),IF(AND(F3="Apprentice ",C2=19),T("+10"),IF(AND(F3="Apprentice ",C2=20),T("+10"),IF(AND(F3="Adept ",C2=1),T("+1"),IF(AND(F3="Adept ",C2=2),T("+1"),IF(AND(F3="Adept ",C2=3),T("+1"),IF(AND(F3="Adept ",C2=4),T("+1"),IF(AND(F3="Adept ",C2=5),T("+2"),IF(AND(F3="Adept ",C2=6),T("+2"),IF(AND(F3="Adept ",C2=7),T("+2"),IF(AND(F3="Adept ",C2=8),T("+2"),IF(AND(F3="Adept ",C2=9),T("+3"),IF(AND(F3="Adept ",C2=10),T("+3"),IF(AND(F3="Adept ",C2=11),T("+3"),IF(AND(F3="Adept ",C2=12),T("+3"),IF(AND(F3="Adept ",C2=13),T("+4"),IF(AND(F3="Adept ",C2=14),T("+4"),IF(AND(F3="Adept ",C2=15),T("+4"),IF(AND(F3="Adept ",C2=16),T("+4"),IF(AND(F3="Adept ",C2=17),T("+5"),IF(AND(F3="Adept ",C2=18),T("+5"),IF(AND(F3="Adept ",C2=19),T("+5"),IF(AND(F3="Adept ",C2=20),T("+5"), IF(AND(F3="All-Around ",C2=1),T("+1"),IF(AND(F3="All-Around ",C2=2),T("+1"),IF(AND(F3="All-Around ",C2=3),T("+2"),IF(AND(F3="All-Around ",C2=4),T("+2"),IF(AND(F3="All-Around ",C2=5),T("+3"),IF(AND(F3="All-Around ",C2=6),T("+3"),IF(AND(F3="All-Around ",C2=7),T("+4"),IF(AND(F3="All-Around ",C2=8),T("+4"),IF(AND(F3="All-Around ",C2=9),T("+5"),IF(AND(F3="All-Around ",C2=10),T("+5"),IF(AND(F3="All-Around ",C2=11),T("+6"),IF(AND(F3="All-Around ",C2=12),T("+6"),IF(AND(F3="All-Around ",C2=13),T("+7"),IF(AND(F3="All-Around ",C2=14),T("+7"),IF(AND(F3="All-Around ",C2=15),T("+8"),IF(AND(F3="All-Around ",C2=16),T("+8"),IF(AND(F3="All-Around ",C2=17),T("+9"),IF(AND(F3="All-Around ",C2=18),T("+9"),IF(AND(F3="All-Around ",C2=19),T("+10"),IF(AND(F3="All-Around ",C2=20),T("+10"),)))))))))))[/spoiler] |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way than Nesting, or other solutions
"Dane" wrote...
I have a formula that nests... 220 times. How do I get it in there?? It's for calculating different numbers when a different choice from a drop-down list is picked. Here is the formula: =IF(AND(F3="Fighter",C2=1), T("+1"), And what do you believe the benefits of T("+1") are over just "+1"? IF(AND(F3="Fighter",C2=2), T("+2"), IF(AND(F3="Fighter",C2=3), T("+3"), IF(AND(F3="Fighter",C2=4), T("+3"), If both C2=3 and C2=4 result in "+3", why not use just one IF, i.e., IF(AND(F3="Fighter",OR(C2={3,4}),"+3",... ? This is all entirely unnecessary. Use lookup tables. Create a table with top row containing the different F3 values and the rows below in each column containing the values corresponding to the C2 values, e.g., Fighter__Spell User__Illusionist__Rogue __ . . . ___1_________1____________1__________1____ . . . ___2_________1____________1__________1____ . . . ___3_________1____________1__________2____ . . . ___3_________1____________1__________2____ . . . ___4_________2____________2__________3____ . . . ___5_________2____________2__________3____ . . . ___6_________2____________2__________4____ . . . ___6_________2____________2__________4____ . . . : : Name this table TBL. Then use a formula like =IF(AND(COUNTIF(INDEX(TBL,1,0),F3),C2=1,C2<=20,C2 =INT(C2)), "+"&HLOOKUP(F3,TBL,C2+1,0),"invalid") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way than Nesting, or other solutions
Example:
1. Put the numeric portion of the return values for "Fighter" in range A10:A29. The formula will concatenate the "+" to these values. So only list the numeric part. 2. Put the numeric portion of the return values for "Spell User" in range B10:B29. 3. Put the numeric portion of the return values for "Illusionist" in range C10:C29. 4. Continue this same pattern for "Rogue", "Apprentice", "Adept" etc. This formula will only work fro "Fighter" through "Illusionist". Continue the same logic: =IF(F3="Fighter","+" & INDEX(A10:A29,C2),IF(F3="Spell User","+" & INDEX(B10:B29,C2),IF(F3="Illusionist","+" & INDEX(C10:C29,C2)))) Regards, Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way than Nesting, or other solutions
Harlan beat me to it and his is better. Mine is probably easier to
understand. Use it for illustrative purposes only and go with Harlan's. Greg "Greg Wilson" wrote: Example: 1. Put the numeric portion of the return values for "Fighter" in range A10:A29. The formula will concatenate the "+" to these values. So only list the numeric part. 2. Put the numeric portion of the return values for "Spell User" in range B10:B29. 3. Put the numeric portion of the return values for "Illusionist" in range C10:C29. 4. Continue this same pattern for "Rogue", "Apprentice", "Adept" etc. This formula will only work fro "Fighter" through "Illusionist". Continue the same logic: =IF(F3="Fighter","+" & INDEX(A10:A29,C2),IF(F3="Spell User","+" & INDEX(B10:B29,C2),IF(F3="Illusionist","+" & INDEX(C10:C29,C2)))) Regards, Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba solutions a.s.a.p please | Excel Programming | |||
Looking for Solutions | Excel Programming | |||
Spreadsheet Solutions | Excel Programming | |||
Using VBA: Trying to write new solutions ONLY | Excel Programming | |||
Looking for Solutions | Excel Programming |