yogi_Compute Stats In A Trade Log Of Long And Short Trades

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #688   Aug 12, 2012     www.energyefficientbuild.com.

user kfink85 said: (http://productforums.google.com/forum/?zx=76albpq7qu02#!category-topic/docs/spreadsheets/phFqqob-PwQ%5B1-25%5D
Problems combining IF statements 
Here is the spreadsheet in question:  https://docs.google.com/spreadsheet/ccc?key=0AkwNbsttMbU5dEozbVZTU1JJTnU3WW16WlRrV0lrU0E#gid=0

I am trying to add an additional IF function to columns H, I, and J. Here is a breakdown on what each is currently set up to do:

H: =IF(C3="Long",(D3*G3)-(D3*E3),(D3*E3)-(D3*G3))-14 This determines whether the stock is a Long or Short in order to calculate the profit between the entry and exit point. It also deducts 14 for commission at the end.

I: =H3/(D3*E3) This calculates the percentage gain relative to the trade size.

J: =F3-A3+1 This calculates how many days the trades were open.

Also, please notice the tables on the right side of the spreadsheet, which analyze these results based on different criteria. I have currently eliminated the formulas from certain fields because since the trades are still open, and there is no exit price, it would generate wrong results. For example, if I were to carry the column H and I formula down to row 4, it would show a complete loss of $3014 and 100%. This would in turn skew the right tables' overall analysis.

So my question is this, what formula would I need to put in to these columns so that still open trades will generate a 0 or N/A or something that would not affect my analysis in the right tables, AND trades that have completed will calculate the results as I have them now. I have tried everything but I can't figure it out. Please help! Any help would be greatly appreciated.

-----------------------------------------------------------------------------------------------

following is a solution to the problem