Schedule Help

[WTFT]

I am trying to have my schedule tell me when I am missing shifts. I need a total of 3 D and 2 N. Theses represent 12hr day and 12 hr N, but they can be made up with d8, n8, and e8. I have entered separate lines to count each type of shift and then used a separate line to show me the "needs". The problem is that I am using If And and there are so many variation that I can't cover all possibilities. For example:

If(and(a1=3,a2=0,a3=2,a4=0,a5=0),"",if(and(a1=2,a2=0,a3=2,a4=0,a5=0),"D",if(and(a1=2,a2=1,a3=1,a4=0,a6=1),"d8")))

Where a1=D, a2=d8, a3=N,a4=n8,a5=e8, e6=15-19

I have a separate line for "D" needs, "N" needs, "e8"needs, and "15-19" needs.

There are more than 7 possibilities. Does anyone have a suggestion of another function that I can nest as much as i want to, or is there a way to combine with other functions so that i can account for everything.

I am also looking to see if employee changes in seniority having their line number return their name on the schedule.

Thanks in advance

 

[mole999]

7 IF were an older limitation, I think its 64 on newer versions but still not ideal

 

[FDibbins]

this...
If(and(a1=3,a2=0,a3=2,a4=0,a5=0),"",if(and(a1=2,a2=0,a3=2,a4=0,a5=0),"D",if(and(a1=2,a2=1,a3=1,a4=0,a6=1),"d8")))
could be shortened a bit to this...
If(a1&a2&a3&a4&a5="30200","",if(a1&a2&a3&a4&a5="20200","D",if(a1&a2&a3&a4&a5="21101","d8")))

But if you have more than that, then perhaps consider creating a small table with all possible combinations of a1&a2&a3&a4&a5 and their respective values