ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Calculating Profit and Loss in one column (https://www.excelbanter.com/excel-worksheet-functions/29338-calculating-profit-loss-one-column.html)

Dismal

Calculating Profit and Loss in one column
 
Hi,
I am trying to total the profit column using this formula without any success:

=IF(OR($J3="",ISERROR(MATCH(-$J3,$J3:$J$1002,0)),),"",$J3*(OFFSET($K3,MATCH
(-$J3,$J3:$J$1002,0)-1,)-$K3))

Where a (1) means the purchase price subtracted from (-1) sales price and
the same (-1) is the sales price and the next (1) is the purchase price and
so on.

Calculating by hand would look like this:
101.125 - 100.531 = +.594 and,
101.125 - 94.750 = +6.375 and,
98.250 - 94.750 = +3.500

MARKET ENTRY PROFIT
POSITION PRICE
1 100.531
1
1
1
1
1
1
-1 101.125
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
1 94.750
1
1
-1 98.250

Any help would be greatly appreciated,

Regards,

Scott


All times are GMT +1. The time now is 07:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com