About MrExcel Consulting Services Learn Excel Resources Challenge of the Month MrExcel Seminars Message Board MrExcel Store Podcast Search Contact Home

 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 numberIf (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 – B1In 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 24-May-2002 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.

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.