Forum Discussion
Automatic Shift Allocation
mathetes Thanks for your efforts & suggestions:
I'm attaching herewith a new sheet with exact problem.
- The "IF(AND" was also related to this sheet only. I thought "False" is because of limitation of nested if(and, but later on figured out that it is due change of day.
Plz peruse the sheet and give your expertise to resolve the "False" problem and also suggest any new short formula.
Here's a formula that works for your "FALSE" reply. I added one set of conditions, highlighted in red below. This formula takes advantage of the IFS function, rather than nesting all levels of IF(AND( ... and is easier to read (and debug) if you enter it as follows
=IFS(
AND(F2>=$B$2,F2<$C$2),$A$2,
AND(F2>=$B$5,F2<$C$5),$A$5,
AND(F2>=$B$6,F2<$C$6),$A$6,
AND(F2>=$B$7,F2<$C$7),$A$7,
AND(F2>=$B$10,F2<$C$10),$A$10,
AND(F2>=$B$11,F2<$C$11),$A$11,
AND(F2>=$B$12,F2>$C$12),$A$12,
AND(F2>=$B$15,F2<$C$15),$A$15,
AND(F2>=$B$16,F2<$C$16),$A$16,
AND(F2>0,F2<=0.16),$A$13
)
I still think you could make things clearer if you were to pay attention to my questions in my first reply. And a still simpler formula could be developed.