On Jul 27, 7:46 am, Tom wrote:
what would be the best way to write this one? In cell D20:
IF J9<7,D20=J9
IF J9=7,D20=0
IF J97<14,D20=J9-D21
IF J9=14, D20=0
IF J914<28,D20=J9-D21
IF J9=28, D20=0
IF J928<42,D20=J9-D21
IF J9=42, D20=0
IF J942<56, D20=J9-D21

"Best" way? That's debatable. One way is:

=if(J9<7, 0, (mod(J9,7)<0)*(J9-D21))

Or perhaps the following would be more clear to you:

=if(or(J9<7, mod(J9,7)=0), 0, J9-D21)

Note that this ass-u-me-s that you want 0 for all multiples of 7,
including 56 and beyond, and that you want J9-D21 for non-multiples of
7, including 57 and beyond.