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 viceversa. 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.
By Juan Pablo Gonzalez on 24May2002
MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
