don't know how to write a formula for this

taylornicolesdad · May 9th, 2002, 12:39 PM

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.

geolefty · May 9th, 2002, 12:48 PM

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.

taylornicolesdad · May 9th, 2002, 03:03 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 9th, 2002, 12:39 PM	#1
taylornicolesdad New Member Join Date: May 2002 Posts: 2	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.

May 9th, 2002, 12:48 PM	#2
geolefty Board Regular Join Date: Mar 2002 Location: CA Posts: 180	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.

May 9th, 2002, 03:03 PM	#3
taylornicolesdad New Member Join Date: May 2002 Posts: 2	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.