EXCEL FORMULAS

[Dismal]

Could someone point me in the right direction with a formula. I am trying to
use excel to backtest some trading rules and one column designates a long or
short position and the next column designates the entry price. I am trying
to create a third column that will add up the difference between the short
and and long entries. Assuming that there is a position either long or short
all of the time.

For example:
A1 = 1, B1 = 44.00
A2 = 1,
A3 = 1.
A4 = -1, B4 = 47.00
A5 = -1,
A6 = -1,
A7 = 1, B7 = 48.00....

Is there a way to write a formula that will sum up the difference between
the long and short positions?

Regards,

Dismal

 

[Jason Morin]

Try:

=SUMIF(A:A,1,B:B)-SUMIF(A:A,-1,B:B)

or maybe:

=ABS(SUM(SUMIF(A:A,1,B:B),-SUMIF(A:A,-1,B:B)))

HTH
Jason
Atlanta, GA

 

[Dismal]

thanks Jason, but no luck. I am still trying to figure our the formula for
column J:


H I J
MARKET ENTRY DIFF
POSITION PRICE
1 100 18/32 10/32
-1 100 28/32
1 100 17/32 11/32
1
1
1
1
1
1
-1 101 4/32 9/32
-1
-1

 

[Arvi Laanemets]

Hi

Maybe you try to explain more clearly, what is your table setup, and what do
you want to calculate. In your example here, there are 3 column labels, but
4 column of data - with some mess of headers between them. And data from
which column do you want to sum? When from 3rd or 4th column, then what is
the data type in them - maybe it's text? And do you want the running sum
(i.e. from top of table to current row), or you want to sum the whole column
(the latter is nonsense IMHO - you get same value for all rows).

 

[Arvi Laanemets]

Hi

It would be useful to inform, that you use fractional format in second
column.

The first problem with your data is, that not all values in second column
are numbers - some of them are texts instead. Format the column with prices
as general, and you see it yourself. To correct this, recalculate or reenter
all text entries, so they will be converted to numbers, and after that
format all cells in Prices column as Custom '#" "??/32' (enter the format
without single quotes)

From your example data I deduced, that whenever market position value
changes, there always is a new price on this row. And there never is a new
price without change in market position. When otherwise, my formula doesn't
work, and it will be much harder to design a working formula.

With conditions above filled, the formula to calculate the price change for
p.e. row 28, with market position in A28 and price in B28, will be:
=IF(OR($B28="",ISERROR(MATCH(-$A28,$A28:$A$1000,0)),);"",$A28*(OFFSET($B28,M
ATCH(-$A82,$A28:$A$1000,0)-1,)-$B28))
(when your table contains more then 1000 rows of data, replace the number
1000 in both MATCH with any bigger then last used row number)

 

[Arvi Laanemets]

A correction

=IF(OR($B28="",ISERROR(MATCH(-$A28,$A28:$A$1000,0)),),"",$A28*(OFFSET($B28,M
ATCH(-$A82,$A28:$A$1000,0)-1,)-$B28))