Profit Loss Formula Based on Negative Number (Stock Related)

[cloves]

Hi everyone, hit a little road block and was hoping for some insight. I have a daily profit loss statement I have to copy and paste into excel everyday and I am trying to get the profit/loss for each transaction by using two formula's. The cost and sell prices are both in the same "Amount" column. The cost in the original document shows up a - negative number (eg. -50 and -75 see below). Once the asset is sold, it shows me a total directly below in the same column (eg. 52 and 25 see below), but not whether it was profitable or a loss, just a total.
I made two new columns, column 1 has a "Profit/Loss" and column 2 "Profit/Loss Percent" which shows percentage. I was able to get the rough formula up and running, but since the cost is showing up as a negative number, my percent column isn't working correctly from what I am thinking.

The secondary is somehow figuring out how to add a "-" whenever their is a negative number which is show in parenthesis.

Amount	Profit/Loss	Profit/Loss Percent
-50
52	2	-4.00% <-- should be positive
-75
25	(50) <-- preferably add a negative when its a loss -(50)	66.67% <-- should be negative

So the formula's I am using are:
Profit/Loss
B3=SUM(A2:A3)
B5=SUM(A4:A5)

Profit/Loss Percentage
C3=(B3/A2)
C5=(B5/A4)

 

[Gerald Higgins]

Try in C3 . . .

=B3/-A2

 

[cloves]

Try in C3 . . .

=B3/-A2

Thanks Gerald, that fixes the issue for the specific cell. It effectively removes the negative. Is there anyway to implement an if then statement to check to see if the number is negative and change it to positive and vice versa? Essentially C3 should be positive and C5 needs to be a negative.

 

[cloves]

I was able to figure it out how to convert the values after thinking about it more. I took a look at the ABS function but needed to add more logic so I used an IF function and a true and false for each. So basically I converted the -50 using the ABS function and compared the 52 to see if it was great or equal to 50. If true then used ABS on the number if false then took the number and multiplied by -1.

So this is the revised formula works if I make a new cell. This is the formula I will use in the Profit/Loss Percent =IF(A3>=ABS(A2), ABS(B3/A2), B3/A2*-1)