Calculating profit and loss

dismal

New Member
Greetings,

I 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))

Any help would be greatly appreciated.

Scott
Edit/Delete Message

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

ravishankar

Well-known Member
Hi
It is not clear to me what you want done. Assuming that profit calculation is based on col H
Paste the following codes in the macro window ( Alt F8)

Code:
``````for a = 4 to 55
if cells(a,8) = ABS(cells(a,8) then
cells(a, 15) = cells(a+1,9) - cells(a,9)
else
cells(a,15) = cells(a-1,9) - cells(a,9)
endif
next a``````

Run the macro. It will fill col O with subtracted values. If this is not what you want, explain step by step what you want done
Ravi

dismal

New Member
Hi Ravi, thanks for the reply.

It might be easier to explain if I could email you a piece of the spreadsheet.

Scott

Replies
2
Views
248
Replies
1
Views
673
Replies
5
Views
149
Replies
3
Views
733
Replies
0
Views
232

1,191,552
Messages
5,987,232
Members
440,086
Latest member
Mahi786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back