# Calculate The Profit And Loss

Jul 20, 2007I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.

I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.

Where:

Market position is either 1 or -1

Entry price is the price where the position is reversed.

For example:

H3=1 and I3=100.56, then

H4=-1 and I4=100.88, then

H5=1 and I5=100.53

I need to calculate the profit as follows:

I4 - I3 (because market position of 1 expects the price to go up) and then,

I4 - I5 (because market position of -1 expects the price to go down)

I want to calculate this as a running total to the bottom of the column.

This is the formula I was working with, but it doesn't work:

=IF(OR($J4="",ISERROR(MATCH(-$J4,$J4:$J$55,0)),),"",$J4*(OFFSET($K4,MATCH (-$J4,$J4:$J$55,0)-1,)-$K4))