Better way than Nesting, or other solutions

[Dane]

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="Fighter",C2=2),T("+2"),IF(AND(F3="Fighter",C2=3),T("+3"),IF(AND(F3="Fighter",C2=4),T("+3"),IF(AND(F3="Fighter",C2=5),T("+4"),IF(AND(F3="Fighter",C2=6),T("+5"),
IF(AND(F3="Fighter",C2=7),T("+6"),IF(AND(F3="Fighter",C2=8),T("+6"),IF(AND(F3="Fighter",C2=9),T("+7"),
IF(AND(F3="Fighter",C2=10),T("+8"),IF(AND(F3="Fighter",C2=11),T("+9"),IF(AND(F3="Fighter",C2=12),T("+9"),
IF(AND(F3="Fighter",C2=13),T("+10"),IF(AND(F3="Fighter",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="Illusionist",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(F3="Illusionist",C2=10),T("+3"),IF(AND(F3="Illusionist",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="Illusionist",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="Illusionist",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"),)))))))))))

 

[Harlan Grove]

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

 

[Greg Wilson]

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

 

[Greg Wilson]

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