# Use index function to shift range in a patterned formula

#### ndube50

##### New Member
Hi all,

I have been playing around with a function all day and it is driving me nuts.

My goal is to automatically calculate the volatility of a warrant's (equity) price over time as it approaches the end of its term (in this case 60 months).

I want to create a formula that takes the STDEV of the Compounded Rate of Return and multiply that by the square root of the period (12 months). But I want to automate it somewhat by allowing me to copy the formula to other cells over time.

PROBLEM: For each new time period that I add, I need to subtract two periods off the backend of the calculation. So, the range essentially shifts down two, but increases by one as well at the bottom.

Example: Download the workbook here: DivShare File - Warrant fair value calculation - shifting.xls
Range I11:I79 contains Rate of Return percentages over time
I want to calculate volatility as STDEV(PERIOD)*SQRT(12) every month until the warrant expires in 60 months.
In my example, you can see I have tried to use the following formula with some success in cell N72:
=STDEV(INDEX(_RR,COLUMNS(N72:\$O72)):INDEX(_RR,COLUMNS(N72:\$O72)+59))*SQRT(12)
where _RR is a dynamic named range referring to:
=OFFSET(Volatility!\$I\$13,0,0,COUNTA(Volatility!\$I13:I80),1)

I need to be able to copy and paste this function (and have it automatically fill in the correct formula) in cells N12:N79. I also need to be able to add new entries to the end.

Thanks!

Any help is much appreciated.

1,082,382
Messages
5,365,129
Members
400,825
Latest member
Sreekanth_21

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...