+ Reply to Thread
Results 1 to 4 of 4

add and subtract depending

  1. #1
    Registered User
    Join Date
    05-15-2008
    Posts
    38

    add and subtract depending

    Hi,
    I have three columns: A and B with a value.
    In column C we have indicated A, B or 0, making reference to the first column (A), or the second column (B), or neither (0).
    In column D I would like the result of add or subtract the values of A and B, depending of what is on column C.
    The column C marks which column has to be sum and wich one has to be subtract:
    if in column C is “A”, I look for the result of “+A-B”.
    3 5 A -2
    if in column C we have “B”, the result of “-A+B”.
    3 5 B +2
    And if in column C we have “0", the result of “-A-B/2”.
    3 5 0 -4

    In column D we have this result, that add to the result of the previous row. For example, in the next serie:

    3 5 B 2
    2 6 A -2
    3 5 A -4
    4 4 A -4
    5 3 0 -8
    4 2 A -6

    In the first row we subtract 3 and add 5 (-3+5=2).
    In the second row we add 2 and subtract 6 (2-6= -4). Because we had 2 as result in the previous row, we subtract 4 to 2 (2-4= -2)
    In the third row we subtract 5 to 3 (3-5= -2), and this we subtract to -2 (-2-2=-4), and this gives us the result in column D.
    In the fourth row we have 4-4 = 0. The result of the previous row doesn’t change.
    In the fifth row we have in column C “0", then we have to subtract both values, and calculate the half: (-5-3/2=-4), and this subtract to the previous value in column D (-4 -4= -8).

    I attached an example worksheet to make it clearer.

    Thank you very much,
    Caldera55
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-07-2008
    Posts
    126

    Re add and subtract depending

    Hi,

    Paste this formula in cell D1
    =IF(C1="A",A1-B1,IF(C1="B",B1-A1,IF(C1=0,(-A1-B1)/2,"")))

    And paste this formula in cell D2
    =D1+IF(C2="A",A2-B2,IF(C2="B",B2-A2,IF(C2=0,(-A2-B2)/2,"")))

    Then fill down the formula in D2 to your last row of data.

    matrex

  3. #3
    Registered User
    Join Date
    05-15-2008
    Posts
    38

    Thank you

    Thank you very much, Matrex,

    It works great!

  4. #4
    Forum Contributor
    Join Date
    06-07-2008
    Posts
    126
    You're welcome, and Glad to hear that this is what you wanted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1