New to =IF and ran into a problem

[Pickle735]

Hello, I am new to Excel and have to tackle a problem out of my league. Any help would be appreciated... I spent the day (8 hours to be exact - lol) writing out my =if(and( formulas only to receive this message: The specified formula cannot be entered because it uses more than 64 levels of nesting. Anyway around this?

 

[AlanY]

show us the formula you're working on

 

[Pickle735]

It's extremely long... Laugh silently please

=IF(AND(C14=0,C15=1,C21=5),"EXTRA Firefighter",IF(AND(C14=0,C15=1,C21=4),"Staffed",IF(AND(C14=0,C15=1,C21=3),"Needa Firefighter",IF(AND(C14=0,C15=1,C21=2),"Need 2Firefighters",IF(AND(C14=0,C15=1,C21=1),"Need 3 Firefighters",IF(AND(C14=0,C15=1,C21=0),"NeedStep Up Driver and 3 Firefighters",IF(AND(C14=1,C15=0,C21=5),"EXTRAFirefighter",IF(AND(C14=1,C15=0,C21=4),"Staffed",IF(AND(C14=1,C15=0,C21=3),"Needa Firefighter",IF(AND(C14=1,C15=0,C21=2),"Need 2Firefighters",IF(AND(C14=1,C15=0,C21=1),"Need 3Firefighters",IF(AND(C14=1,C15=0,C21=0),"Need Step up Driver and 3Firefighters",IF(AND(C14=1,C15=1,C21=5),"2 EXTRA Firefighter",IF(AND(C14=1,C15=1,C21=4),"EXTRAFirefighter",IF(AND(C14=1,C15=1,C21=3),"Staffed",IF(AND(C14=1,C15=1,C21=2),"Needa Firefighter",IF(AND(C14=1,C15=1,C21=1),"Need 2Firefighters",IF(AND(C14=1,C15=1,C21=0),"Need 3Firefighters",IF(AND(C14=0,C15=0,C21=5),"Need a Step Up Captain and EXTRAFirefighter",IF(AND(C14=0,C15=0,C21=4),"Need a Step UpCaptain",IF(AND(C14=0,C15=0,C21=3),"Need a Step Up Captain and aFirefighter",IF(AND(C14=0,C15=0,C21=2),"Need a Step Up Captain and 2Firefighters",IF(AND(C14=0,C15=0,C21=1),"Need a Step Up Captain and 3Firefighters",IF(AND(C14=0,C15=0,C21=0),"Need a Step Up Captain &Step Up Driver and 3 Firefighters",IF(AND(C14=20,C15=1,C21=5),"EXTRA Firefighter for 24 and EXTRA Firefighter for the 2ndHalf",IF(AND(C14=20,C15=1,C21=4),"EXTRA Firefighter for the 2ndHalf",IF(AND(C14=20,C15=1,C21=3),"Need a Firefighter for the 1stHalf",IF(AND(C14=20,C15=1,C21=2),"Need a Firefighter for 24 and aFirefighter for the 1st Half",IF(AND(C14=20,C15=1,C21=1),"Need 2Firefighters for 24 and a Firefighter for the 1stHalf",IF(AND(C14=20,C15=1,C21=0),"Need a Step Up Driver for the 1stHalf and 3 Firefighters for 24",IF(AND(C14=1,C15=20,C21=5)," EXTRA Firefighter for 24 and EXTRA Firefighter for the 2ndHalf",IF(AND(C14=1,C15=20,C21=4)," EXTRA Firefighter for the 2nd Half",IF(AND(C14=1,C15=20,C21=3),"Need a Firefighter for the 1stHalf",IF(AND(C14=1,C15=20,C21=2),"Need a Firefighter for 24 and aFirefighter for the 1st Half",IF(AND(C14=1,C15=20,C21=1),"Need 2Firefighters for 24 and a Firefighter for the 1stHalf",IF(AND(C14=1,C15=20,C21=0),"Need a Step Up Driver for the 1stHalf and 3 Firefighters for 24",IF(AND(C14=20,C15=20,C21=5),"Need aStep Up Captain for 1^st Half and EXTRA Firefighter 24 and 2EXTRA Firefighters the 2^ndHalf",IF(AND(C14=20,C15=20,C21=4),"Need a Step Up Captain for 1^stHalf and 2 EXTRA Firefighters the 2^ndHalf",IF(AND(C14=20,C15=20,C21=3),"Need a Step Up Captain and aFirefighter for the 1^stHalf",IF(AND(C14=20,C15=20,C21=2)," Need a Step Up Captain and aFirefighter for the 1^st Half and a Firefighters for24",IF(AND(C14=20,C15=20,C21=1),"Need a Step Up Captain and aFirefighter for the 1^st Half and 2 Firefighters for24",IF(AND(C14=20,C15=20,C21=0),"Need a Step Up Captain & Step UpDriver for the 1^st Half and 3 Firefighters for24",IF(AND(C14=1,C15=1,C21=5),"2 EXTRAFirefighter",IF(AND(C14=1,C15=1,C21=24),"EXTRA Firefighter",IF(AND(C14=1,C15=1,C21=43),"Staffed",IF(AND(C14=1,C15=1,C21=62),"Needa Firefighter for 1^stHalf",IF(AND(C14=1,C15=1,C21=81),"Need 2 Firefighters for 1^stHalf",IF(AND(C14=1,C15=1,C21=100),"Need 3 Firefighters for 1^stHalf",IF(AND(C14=30,C15=1,C21=5),"EXTRA Firefighter for 24 and EXTRAFirefighter for the 1^st Half",IF(AND(C14=30,C15=1,C21=4),"EXTRA Firefighter for the 1^stHalf",IF(AND(C14=30,C15=1,C21=3),"Need a Firefighter for the 2ndHalf",IF(AND(C14=30,C15=1,C21=2),"Need a Firefighter for 24 and aFirefighter for the 2^ndHalf",IF(AND(C14=30,C15=1,C21=1),"Need 2 Firefighters for 24 and aFirefighter for the 2^ndHalf",IF(AND(C14=30,C15=1,C21=0),"Need a Step Up Driver for the 2^ndHalf and 3 Firefighters for 24",IF(AND(C14=1,C15=30,C21=5),"EXTRA Firefighter for 24 and EXTRA Firefighter for the 1^stHalf",IF(AND(C14=1,C15=30,C21=4),"EXTRA Firefighter for the 1^stHalf",IF(AND(C14=1,C15=30,C21=3),"Need a Firefighter for the 2ndHalf",IF(AND(C14=1,C15=30,C21=2),"Need a Firefighter for 24 and aFirefighter for the 2^nd Half",IF(AND(C14=1,C15=30,C21=1),"Need2 Firefighters for 24 and a Firefighter for the 2^ndHalf",IF(AND(C14=1,C15=30,C21=0),"Need a Step Up Driver for the 2^ndHalf and 3 Firefighters for 24",IF(AND(C14=30,C15=30,C21=5),"Need aStep Up Captain for 2^nd Half and EXTRA Firefighter 24 and EXTRA Firefighters the 1^stHalf",IF(AND(C14=30,C15=30,C21=4),"Need a Step Up Captain for 2^ndHalf and EXTRA Firefighters the 1^stHalf",IF(AND(C14=30,C15=30,C21=3),"Need a Step Up Captain and aFirefighter for the 2^nd Half",IF(AND(C14=30,C15=30,C21=2),"Need a Step Up Captain and a Firefighter for the 2^nd Half and aFirefighters for 24",IF(AND(C14=30,C15=30,C21=1),"Need a Step UpCaptain and a Firefighter for the 2^nd Half and 2 Firefighters for24",IF(AND(C14=30,C15=30,C21=0),"Need a Step Up Captain & Step UpDriver for the 2^nd Half and 3 Firefighters for24",IF(AND(C14=1,C15=1,C21=5),"2 EXTRAFirefighter",IF(AND(C14=1,C15=1,C21=34),"EXTRAFirefighter",IF(AND(C14=1,C15=1,C21=63),"Staffed",IF(AND(C14=1,C15=1,C21=92),"Needa Firefighter for 2^ndHalf",IF(AND(C14=1,C15=1,C21=121),"Need 2 Firefighters for 2^ndHalf",IF(AND(C14=1,C15=1,C21=150),"Need 3 Firefighters for 2^ndHalf",IF(AND(C14=40,C15=1,C21=5)," EXTRA Firefighter for 24 and EXTRAFirefighter when Misc. Time Off Comes back",IF(AND(C14=40,C15=1,C21=4)," EXTRA Firefighter when Misc. Time OffComes Back",IF(AND(C14=40,C15=1,C21=3)," Need a Firefighter for Misc.Time Off ",IF(AND(C14=40,C15=1,C21=2)," Need a Firefighter for 24 anda Firefighter for Misc. Time Off",IF(AND(C14=40,C15=1,C21=1)," Need 2Firefighters for 24 and a Firefighter for Misc. TimeOff",IF(AND(C14=40,C15=1,C21=0)," Need a Step Up Driver for Misc.Time Off and 3 Firefighters for 24",IF(AND(C14=1,C15=40,C21=5),"EXTRA Firefighter for 24 and EXTRA Firefighter when Misc. Time OffComes back",IF(AND(C14=1,C15=40,C21=4),"EXTRA Firefighter when Misc. Time Off ComesBack",IF(AND(C14=1,C15=40,C21=3),"Need a Firefighter for Misc. TimeOff ",IF(AND(C14=1,C15=40,C21=2),"Need a Firefighter for 24 and aFirefighter for Misc. Time Off",IF(AND(C14=1,C15=40,C21=1),"Need 2Firefighters for 24 and a Firefighter for Misc. TimeOff",IF(AND(C14=1,C15=40,C21=0),"Need a Step Up Driver for Misc. TimeOff and 3 Firefighters for 24",IF(AND(C14=40,C15=40,C21=5),"Need aStep Up Captain for Misc Time Off and EXTRAFirefighter 24",IF(AND(C14=40,C15=40,C21=4),"Need a Step Up Captainfor Misc Time Off",IF(AND(C14=40,C15=40,C21=3),"Need a Step UpCaptain and a Firefighter for Misc TimeOff",IF(AND(C14=40,C15=40,C21=2)," Need a Step Up Captain and aFirefighter for Misc Time Off and a Firefighters for24",IF(AND(C14=40,C15=40,C21=1),"Need a Step Up Captain and aFirefighter for Misc Time Off and 2 Firefighters for24",IF(AND(C14=40,C15=40,C21=0),"Need a Step Up Captain & Step UpDriver for Misc Time Off and 3 Firefighters for24",IF(AND(C14=1,C15=1,C21=5),"2 EXTRAFirefighter",IF(AND(C14=1,C15=1,C21=44),"EXTRAFirefighter",IF(AND(C14=1,C15=1,C21=83),"Staffed",IF(AND(C14=1,C15=1,C21=122),"Needa Firefighter for Misc Time Off",IF(AND(C14=1,C15=1,C21=161),"Need 2Firefighters for Misc Time Off",IF(AND(C14=1,C15=1,C21=200),"Need 3Firefighters for Misc Time Off",IF(AND(C14=1,C15=1,C21=5),"2 EXTRAFirefighter",IF(AND(C14=1,C15=1,C21=54),"EXTRAFirefighter",IF(AND(C14=1,C15=1,C21=103),"Staffed",IF(AND(C14=1,C15=1,C21=152),"Needa Firefighter until EMT gets back from MedicSchool",IF(AND(C14=1,C15=1,C21=201),"Need 2 Firefighters until EMTgets back from Medic School",IF(AND(C14=1,C15=1,C21=250),"Need 3Firefighters until EMT gets back from Medic School")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))<o></o>

 

[RoryA]

Welcome to the forum.

I suggest you create a lookup table for the results. In the first column put the three possible column C value combinations delimited with say a | symbol, and in the second put the return text. So for example, the first few rows would look like this:

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]0|1|5[/td][td]EXTRA Firefighter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]0|1|4[/td][td]Staffed[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]0|1|3[/td][td]Need a Firefighter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]0|1|2[/td][td]Need 2Firefighters[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]0|1|1[/td][td]Need 3 Firefighters[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]0|1|0[/td][td]Need Step Up Driver and 3 Firefighters[/td][/tr]
[/table]

Then your formula simply becomes:

=VLOOKUP(C14&"|"C15&"|"C21,$C$1:$D$100,2,false)

adjusting the blue part to match your table.