Nice catch!!! I think this formula will account for all of the conditions...

D20: =IF(MOD(J9,14-7*(J9=7))<0,J9-IF(J9<=7,0,D21),0)

or, in slightly shorter form, this one...

D20: =(MOD(J9,14-7*(J9=7))<0)*(J9-D21*(J9=7))

While the formula produces values for J9<0 and J9=56, we are not in a
position to know what the OP wanted to happen there as he left his
intentions unstated.

Rick


"Duke Carey" wrote in message
...
Rick & Joe -

The OP's specs omit 21 and 35 as conditions where he wants a zero result,
so your solutions incorporating MOD() will be erroneous

"Rick Rothstein (MVP - VB)" wrote:

This should work...

=IF(MOD(J9,7)=0,0,J9-IF(J9<=7,0,D21))

Rick


"Tom" wrote in message
...
Hi gang,
my head is spinning around...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

thanks in advance!