P and L

[iainsteel]

Hello
I am trying to run a p and l spreadheet and I am having an issue with the p and l on sells:

QTY Price In Price Out P and L
5 10.00 6.00 -20
3 11.00 12.00 5
-5 12.00 9.00 -15
-5 15.00 17.00 10

so the p and l calculation is (price out - price in)*qty
so that works when qty is positive but not when qty is negative
(i can sell first then buy back later as well).
How can I modify the formula: (price out - price in)*qty to account for
-ve values in qty. Because using the same formula I am getting the wrong values:
i.e -5 @ 12.00 and then +5 @ 9.00 should equal +15 not -15
and -5 @ 15.00 and then +5 17.00

I hope this is clear - sorry for a not a more challenging question.
Thanks

 

[Andrew Poulsom]

I don't understand why there should be plus signs here:

i.e -5 @ 12.00 and then +5 @ 9.00 should equal +15 not -15
and -5 @ 15.00 and then +5 17.00

 

[iainsteel]

Hi
it does not have be plus signs in my spreadsheet it is just 5
Thanks

 

[Andrew Poulsom]

I mean I don't see why the formula doesn't give the right answer for negative quantities. Why should the negative QTY be change to positive for Price Out?

 

[iainsteel]

Hi Andrew
Because its a 2 way trade u can buy first then sell or you can sell first then buy.
Thanks

 

[JB_Scotland]

Cell D2 "=(C2-B2)*A2" works fine

C2 = Price Out
B2 = Price In
A2 = Quantity

I don't see the problem?

 

[iainsteel]

Sorry about all this i overlooked it completely
all i had to do was to:
(a1-b1)*c1 (c1 being a reference to + or -ve. qty.)
apologise for wasting everyones time
Thanks