Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: don't know how to write a formula for this

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is probably a breeze, but I'm stumped. I'm creating a tally sheet for my many day trades. One of the columns is "gain or loss". This is obviously established by simply establishing the difference between the "total invested" cell and the "net proceeds" cell. My problem is representing the result properly (gains as positive numbers and losses as negative numbers). If "net proceeds" is greater than "total invested" then I made a profit. If I invested 10,000 and had net proceeds of 12,000 then there's a profit of 2,000. Yet if subtraction is used to establish the difference, the 2,000 will be represented as a negative amount, and vice-versa. The following is what I think I need to accomplish in the formula cell for "gain or loss" but I don't know how to write the formula:

    If (total invested cell) > (net proceeds cell) then the difference is a negative number
    If (total invested cell) < = (net proceeds cell) then the difference is a positive number

    A solution would be tremendously appreciated.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    CA
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am probably missing something but why don't you use
    =(Net Proceeds cell)-(total invested cell) in the Gain/Loss column.

    12,000-10,000 = +2000 profit
    10,000-12,000 = -2000 loss

    You might also want to use conditional formatting (format: conditional formatting) for cell colors or borders to show more visibly your results.

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sometimes it just takes another mind. I was writing (total invested - net proceeds) which obviously wasn't working. I flipped the equation (your suggestion)and tested it on paper. Wouldn't you know it, I did the math wrong! So I threw out that format and then was lost.

    Thanks, its already built into the sheet.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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