Super function

[Gusher]

This function will return the standard deviation for a named set of data over a defined period of months.

So for example if we have

Range = "START_DATE" = 30/6/2012
Range = "END_DATE" = 31/12/2015
Range = "DATA_NAME" = UK Interest Rate

Then the function will return the standard deviation of for Uk interest rates over the period June 12 - Dec 15

All the data is on sheet2. In column A we have month end dates in chronological order. In row 2 we have the data names. So for example in cell AH2 the data name is UK Interest Rate. The function must find the monthly data for the defined period from column AH and return the standard deviation.

I hope that makes sense. Many Thanks, Mark

 

[circledchicken]

Hi,

Here's one way you can try:

• I've assumed the column headers are in the range Sheet2!$A$2:$AZ$2
• I've assumed the dates are in the range Sheet2!$A$3:$A$1000
• Define a few additional named ranges as follows:
  • DATA_RANGE: =Sheet2!$A$3:$AZ$1000
  • START_ROWINDEX: =MATCH(START_DATE, Sheet2!$A$3:$A$1000)
  • END_ROWINDEX: =MATCH(END_DATE, Sheet2!$A$3:$A$1000)
  • DATA_COLUMNINDEX: =MATCH(DATA_NAME, Sheet2!$A$2:$AZ$2, 0)
• (For the last 3, you could alternatively choose to calculate these on the sheet instead and just let the named range refer to the result cell)
• Use the following formula for your result (replacing STDEV with whatever is the appropriate function for your use case):
  

  =STDEV(
      INDEX(DATA_RANGE, START_ROWINDEX, DATA_COLUMN):
      INDEX(DATA_RANGE, END_ROWINDEX, DATA_COLUMN))

This is the basic principle, but you can adjust the ranges, and implement it however you want e.g. with or without additional named ranges, dynamically finding your last row and last column etc.

For more on how this works see for example:

• The range reference operator (colon): https://support.office.com/en-us/ar...ecedence-48be406d-4975-4d31-b2b8-7af9e0e2878a
• The Imposing INDEX | Excel Hero Blog
• INDEX Formula in Excel - How to use it, tips & examples | Chandoo.org - Learn Microsoft Excel Online

 

[Gusher]

Hi,

Here's one way you can try:

• I've assumed the column headers are in the range Sheet2!$A$2:$AZ$2
• I've assumed the dates are in the range Sheet2!$A$3:$A$1000
• Define a few additional named ranges as follows:
  • DATA_RANGE: =Sheet2!$A$3:$AZ$1000
  • START_ROWINDEX: =MATCH(START_DATE, Sheet2!$A$3:$A$1000)
  • END_ROWINDEX: =MATCH(END_DATE, Sheet2!$A$3:$A$1000)
  • DATA_COLUMNINDEX: =MATCH(DATA_NAME, Sheet2!$A$2:$AZ$2, 0)
• (For the last 3, you could alternatively choose to calculate these on the sheet instead and just let the named range refer to the result cell)
• Use the following formula for your result (replacing STDEV with whatever is the appropriate function for your use case):
  

  =STDEV(
      INDEX(DATA_RANGE, START_ROWINDEX, DATA_COLUMN):
      INDEX(DATA_RANGE, END_ROWINDEX, DATA_COLUMN))

This is the basic principle, but you can adjust the ranges, and implement it however you want e.g. with or without additional named ranges, dynamically finding your last row and last column etc.

For more on how this works see for example:

• The range reference operator (colon): https://support.office.com/en-us/ar...ecedence-48be406d-4975-4d31-b2b8-7af9e0e2878a
• The Imposing INDEX | Excel Hero Blog
• INDEX Formula in Excel - How to use it, tips & examples | Chandoo.org - Learn Microsoft Excel Online

Okay many thanks, all makes sense, I will give it a try, Mark