Use index function to shift range in a patterned formula


New Member
Jun 29, 2011
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:
where _RR is a dynamic named range referring to:

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.


Any help is much appreciated.

Forum statistics

Latest member

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...