*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*E*3 ),(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