Help with calculations in OpenOffice

I need help with some scripting so I can updating my current spreadsheet so it calculates my total profit/loss in pips and dollar amounts.

I'm stuck on calculating pips from a single trade. In column K, I want the total pips calculated from the Price and Close blocks to be displayed as whole numbers; red number for a loss, black for a win.

I'm also stuck on calculating the initial risk involved in dollars. In column L, I want the initial risk calculated from Price and Stop to be displayed as dollar amounts based on lot size.

Any help is appreciated.

For column K enter this formula:

=IF(C7="SELL";(F7-I7)*10000;IF(C7="BUY";(I7-F7)*10000;""))

This is for row 7 only. So for row 8 it should be (and so on):


=IF(C8="SELL";(F8-I8)*10000;IF(C8="BUY";(I8-F8)*10000;""))


To Change the color of the number to red when negative, highlight all the cells you want this for (click on the top cell, scroll down, hold shift and click on the last cell). Right click somewhere over the highlighted area and select "format cells". Under numbers tab select the numbers in red. You can also choose whether to have commas separating thousands, number of digits after decimal and dollar sign ($) when you want.

For column L (Initial Risk):

=IF(C7="BUY";((F7-G7)*100000)*D7;IF(C7="SELL";((G7-F7)*100000)*D7;""))

Just like you changed numbers to red for negative, go into format cells > numbers tab > select numbers with "$" to display as dollar amount.

That worked, 2+2=4ex. Thanks!

*Edited original formulas so that a blank cell (no "buy" or "sell") will leave eveything else blank also instead of trying to calculate.

Makes spreadsheet look alot cleaner.

Thanks, 2+2=4ex.

I updated the .zip file if anyone has interest in using it. It has helped me with my trading, it's sure to help others as well.