I am working on a trade tracking spreadsheet. The problem I need guidance with is the formula in the Capital Gain/Loss column. Seven columns are used for this scenario: 1. A for the position short/long 2. B for the entry price 3. C for the exit price 4. D for the points =B1-C1 5. E for the capital earned =D*F*G (for NQ's) 6. F for Contracts 7. G for contract value $20/contract NQ's This works for shorting(=A1-B1) in calculating the points earned. But I need the formula for "C" column to read =B1-A1 for Longs. Is there anyone who can provide me with a formula for column "D" that says: "If column A is short than(B1-C1) If column A is Long than (C1-B1)" Thanks in advance. Jeffrey
@if(A1="S",B1-C1,C1-B1) The only problem with this is it defaults to long if it doesn't say "S", but it should work for what your doing.
Thanks, I'll give it a try here. I had to go back and edit my post. I got confused between my stock and futures spread sheets. Again, Thanks.
The only way it defaults is if you don't fill in the position column short/long. These formula's work: Futures =IF(A1="SHORT",B1*C1*D1,-(B1*C1*D1)) 1. Where A column states the position SHORT or LONG 2. Where B column states the number of contracts 3. Where C column states the contract value 4. Where D column states the points calculated (entry minus exit) 5. Enter the above formula in column E to calculate Capital Gain Stockâs =IF(A1="SHORT",B1-C1,C1-B1) 1. Where A column states the position SHORT or LONG 2. Where B column states the entry price, or total cost. 3. Where C column states the exit price, or total revenue. 4. Enter the above formula in column D to calculate Capital Gain Jeffrey
Futures: Correction: The points column needs the formula to change the number to - if long. I'll have hold off before posting. Sorry, for any havoc. Jeffrey
Replace this qoute for futures with my previous post. The minus 1 added to point calculation is the cost of spread on NQ's. For a ES spreadsheet minus .5 for the spread. Jeffrey
Replace with =IF(A1="SHORT",B1*C1*D1,B1*C1*D1) Hopefully those who need a worksheet are smart enough to figure it out. I just wanted to correct what I started. I'm leaving anything else alone. Jeffrey