How do I subtract two columns from the same total?

[Guest]

Hi,

How do I subtract two columns with two different conditions from the same
total?

A B C D E
Article 1 200 12 -45
Article 2 350 12 50

Column E should always contain B - C, but column D should only be subtracted
if it is less than 0.

Thanks in advance!

 

[Guest]

Hi,
Here's what I would do (there's probably a more elegant way). In Column E:
=if(D2<0,B2-C2-D2,B2-C2)

Hope that helps.
Melinda

 

[Guest]

I tried that but it doesn't give me the answer I am looking for. Using the
formula you suggested the total of the first row was 338 when it should have
been 143. In other words, I want column E to be 200 minus 12 minus 45 (if 45
is less than 0). If the number in that column (column D) is a positivbe
number, it should be ignored in the total. The total of the second row was
0. What do do?

 

[Guest]

If the number in column D is negative and you are trying to subtract it you
need to change the formula to this:

=IF(D2<0,B2-C2+D2,B2-C2)

 

[Guest]

Well, I suspect that part of the problem is that you are subtracting a
negative number (which is the same as adding). The other part of the problem
is that I didn't understand your row set up.
Try this instead:
=(D1<0,12-C1+D1,B1-C1)
Now, in the second row, all of the 1's will need to be 2's. If you drag
down your formula, this will update automatically.

 

[Guest]

Oops. I think I had a typo in there.

=if(D1<0,B1-C1+D1,B1-C1)

 

[Domenic]

Try...

=B1-C1-IF(D1<0,ABS(D1))

or

=B1-C1+IF(D1<0,D1)

Hope this helps!

 

[Guest]

Thank you all so very much!

Try...

=B1-C1-IF(D1<0,ABS(D1))

or

=B1-C1+IF(D1<0,D1)

Hope this helps!