How to Reverse the Sign on a Difference

May 24, 2002 - by Juan Pablo Gonzalez

William asks:

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 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.

Assuming that the “Net Proceeds” are in Column A, and “Total Invested” is in Column B, try the following formula:

=A1–B1

In your example, if A1 = 12,000 and B1 = 10,000 then the formula would show a POSITIVE 2,000. If Net Proceeds is smaller than Total Invested then it would be a NEGATIVE number.