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]

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

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

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