spreadsheet newbie

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

 

Yahooooo!!!!


lol

 

@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.

 

www.mrexcel.com

Very quick answers to the most complex spreadsheet questions you can muster.

--opm8

 

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

 

Correction:

The default is set with the last entry.(Long or Short)

 

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