multiple IF statements

[Guest]

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 J9>7<14,D20=J9-D21
IF J9=14, D20=0
IF J9>14<28,D20=J9-D21
IF J9=28, D20=0
IF J9>28<42,D20=J9-D21
IF J9=42, D20=0
IF J9>42<56, D20=J9-D21

thanks in advance!

 

[Guest]

=if(j9={7,14,28,42},0,j9-d21*(j9>7))

Doesn't deal with the case that J9 >= 56

If >=56, use

=IF(J9={7,14,28,42},0,J9-D21*(J9>7))*(J9<56)

 

[Don Guillett]

try this idea. Notice I did the 0's first and worked from the top down.
=J9-IF(OR(J9>55,J9=42,J9=28,J9=14,J9=7),0,IF(OR(J9>42,J9>28,J9>14,J9>7),D21))

 

[Guest]

Left out a portion

=IF(OR(J9={7,14,28,42}),0,J9-D21*(J9>7))
which ignores what happens if J9 > 56

If it should return 0 when > 56 then

=IF(OR(J9={7,14,28,42}),0,J9-D21*(J9>7))*(J9<56)

 

[Don Guillett]

Duke's is best but mine could be used by subtracting j9
=J9-IF(OR(J9>55,J9=42,J9=28,J9=14,J9=7),j9,IF(OR(J9>42,J9>28,J9>14,J9>7),D21))

 

[joeu2004]

what would be the best way to write this one? In cell D20:
IF J9<7,D20=J9
IF J9=7,D20=0
IF J9>7<14,D20=J9-D21
IF J9=14, D20=0
IF J9>14<28,D20=J9-D21
IF J9=28, D20=0
IF J9>28<42,D20=J9-D21
IF J9=42, D20=0
IF J9>42<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.

 

[joeu2004]

Errata....

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

Oops, misread the conditions in an attempt to simplify. That should
be:

Or perhaps the following would be more clear to you:
=if(or(J9<7, mod(J9,7)=0), 0, J9-D21)

That should be:

=if(J9<7, J9, if(mod(J9,7)=0, 0, J9-D1))

 

[Rick Rothstein \(MVP - VB\)]

This should work...

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

This should work...

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

Or a little more compactly, this...

=IF(MOD(J9,7)=0,0,J9-D21*(J9>7))

Rick

 

[joeu2004]

This should work...
=IF(MOD(J9,7)=0,0,J9-IF(J9<=7,0,D21))

I believe that returns 0 when J9=0, but the OP wants J9 when J9<7.

Therefore, I believe we must test J9<7 first. Note that this also
returns J9 when J9<0, which may or may not be want the OP intends, if
the OP even cares. But it does follow the OP's specifications.

 

[Guest]

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

 

[joeu2004]

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

Good eye!

 

[Rick Rothstein \(MVP - VB\)]

The OP's specs omit 21 and 35 as conditions where he

Good eye!

Yes indeed... I completely missed that!

Rick

 

[Rick Rothstein \(MVP - VB\)]

This should work...

I believe that returns 0 when J9=0, but the OP wants J9 when J9<7.

The formula is wrong for the reasons Duke pointed out, but as to your
statement... perhaps I am missing something in your statement, but isn't my
formula returning 0 when J9 equals zero the same thing as returning J9 when
J9 equals 0?

Rick

 

[Rick Rothstein \(MVP - VB\)]

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