Home |
Search |
Today's Posts |
#1
|
|||
|
|||
EXCEL FORMULAS
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 |
#2
|
|||
|
|||
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 -----Original Message----- 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 . |
#3
|
|||
|
|||
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 "Jason Morin" wrote: 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 -----Original Message----- 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 . |
#4
|
|||
|
|||
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). -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Dismal" wrote in message ... 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 "Jason Morin" wrote: 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 -----Original Message----- 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 . |
#5
|
|||
|
|||
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) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Dismal" wrote in message ... 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 "Jason Morin" wrote: 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 -----Original Message----- 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 . |
#6
|
|||
|
|||
A correction
=IF(OR($B28="",ISERROR(MATCH(-$A28,$A28:$A$1000,0)),),"",$A28*(OFFSET($B28,M ATCH(-$A82,$A28:$A$1000,0)-1,)-$B28)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want Excel to allow cells with formulas and unrelated text | Excel Discussion (Misc queries) | |||
Suddenly cannot calculate functions or formulas in Excel | Excel Worksheet Functions | |||
How do I save a copy of an excel file without the formulas? | Excel Worksheet Functions | |||
IF/AND formulas within excel | Excel Discussion (Misc queries) | |||
Problems with Excel formulas when 2002 upgraded to XP | Excel Worksheet Functions |