Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I subtract two columns from the same total?
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! |
#2
|
|||
|
|||
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 "Lollycat" wrote: 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! |
#3
|
|||
|
|||
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? "Melinda" wrote: 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 "Lollycat" wrote: 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! |
#4
|
|||
|
|||
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) "Lollycat" wrote: 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? "Melinda" wrote: 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 "Lollycat" wrote: 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! |
#5
|
|||
|
|||
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. "Lollycat" wrote: 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? |
#6
|
|||
|
|||
Oops. I think I had a typo in there.
=if(D1<0,B1-C1+D1,B1-C1) |
#7
|
|||
|
|||
Try...
=B1-C1-IF(D1<0,ABS(D1)) or =B1-C1+IF(D1<0,D1) Hope this helps! In article , "Lollycat" wrote: 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! |
#8
|
|||
|
|||
Thank you all so very much!
"Domenic" wrote: Try... =B1-C1-IF(D1<0,ABS(D1)) or =B1-C1+IF(D1<0,D1) Hope this helps! In article , "Lollycat" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get Msg "Cannot shift objects off sheet" when hidng columns" Why? | Excel Discussion (Misc queries) | |||
Having trouble totaling columns in excel 2003, always worked b4 | Excel Worksheet Functions | |||
Eliminate unused columns | Excel Discussion (Misc queries) | |||
Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) | |||
Pulling data from 2 columns for a total | Excel Worksheet Functions |