Relative Strength Formula in Excel 2007

Hi All,

Trying to use excel to backtest momentum stock strategies in excel. I have all of the raw data and need some help putting the formulas together. I am probably making it sound more difficult than it is, but here is the objective:

"Purchase" the stock with the highest trailing returns. To mimic this purchase, we must first identify the highest value in I8:P8. Then, we must reflect only the % change in value for the stock with the highest relative strength. To do so, we need to calculate the change from row 8 to row 9 and so on. The percent gain/loss will be multiplied by a hypothetical starting value of a portfolio that invests in the strategy (cell not shown on the sample sheet, but it would be a single cell with a starting value of 1, for example).

For example, in the attached spreadsheet the highest value in I8:P8 is K8. Therefore, I want to only purchase Stock C using the value in C8 as the entry price and hold until the price in C9. The % gain/loss between c8/c9 would be multiplied by the starting value of the portfolio (not shown on the sheet). Then, repeat this calculation for each subsequent row.

Sorry, I didn't open the workbook, as I have a couple too many open atm, But in C8 '=Max(I8:P8)' will give you the highest Value of the range..

I'll close some workbooks down and load yours up

Okay, without some examples of what you expect to see in the cells, this is what I came up with

Formula:

=IF(MAX($I8:$P8)=I8,((MAX($I8:$P8)-OFFSET($J$8,1,MATCH(MAX($I8:$P8),$I8:$P8,0)))/100)*1,"")

if stock a is highest, subtract next stock a from it, divide by 100 for percent change, multiply by "1"(hypothetical starting value) and display, else leave blank

Drag down and across

Hope this helps

Okay, the question I have is : Is the basic formula I described above near the formula you are looking for? or am I completely off track here?

The cell referencing may have to be changed, which is one of the reasons I asked if I was on the the right track with how I described it, if the premise I used to create the formula is wrong, then there is no sense in using it

if stock a is highest, subtract next stock a from it, divide by 100 for percent change, multiply by "1"(hypothetical starting value) and display, else leave blank

Hmmm...guess quote tags don't work this way...lol :D...edit- fixed them

if you have the formula you want for your first row, you should be able to enter it, then highlight the cell, move cursor to lower right of cell till you see the '+', click And hold, drag down till you reach the end of the range..this is the method used by many contributors to this forum, as the actual cell references will change to meet the new cell they are in- only one caveat, the $ sign in front of any Column letter OR Row number will lock the reference to that Column/Row...