Excel - Dynamic Standard Deviation, Based on Date Input

[JStandard]

Greetings,

I am attempting to develop both year to date and 1 year rolling Standard Deviation for a set of data recorded monthly across several years which would be based around a date reference point input by the user.

Ex. Stock Returns on a monthly basis for two different companies.
1 A B C D O
2 ... 12/31/03 1/31/04 2/28/04 .. 1/31/05
3Company A 3.2% 1.5% -0.55% .. 1.7%
4Company B -1.52% 5.2% 2.3% .. 3.1%

So, on the control panel worksheet, if a user were to input "1/31/05" for the date, the sheet would compute a year to date standard deviation which in this case would just be =stdev(O3:O3), whereas if the user entered "2/28/04" the year to date would be something akin to =stdev(C33).

The same would apply for a rolling 1 year standard deviation, if a user entered say 1/31/05 on the control panel worksheet, it would calculate =stdev(D3:03).

For the dynamic year to date calculation (the smaller priority, as it is potentially more difficult) I am really uncertain as to how I would handle it formula wise.

For the 1 year rolling standard deviation I have been playing with using OFFSET and HLOOKUP, doing something along the lines of (this doesn't work)

=OFFSET(HLOOKUP(), 0, 0, 1, -11)

Unfortunately, the HLOOKUP, based on the user input date, returns the VALUE of the field I want to use as my reference point in OFFSET, where I need to feed the offset formula with the CELL REFERENCE of the value the HLOOKUP returns.

Hopefully the above makes light of what I am trying to achieve, I'll gladly provide more information upon request.

Thanks kindly,

Jeff

 

[SkipVought]

Hi,

OFFSET's FIRST argument is a cell REFERENCE and not a cell value.

You'd want to use the MATCH function to get a column offset for the 3rd argument.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[JStandard]

Skip,

Yes, that is my problem, and why my 1 year rolling calculation is not working. I basically need something that works just like HLOOKUP, but returns the CELL REFERENCE and not the VALUE as HLOOKUP does.

Not too certain what you mean about the MATCH function, could you please elaborate a bit on it?

Thanks Skip,

Jeff

 

[SkipVought]

the cell ref should be ABSOLUTE.

Then use the Match function to lookup the date in the row range. Match returns an index value (the offset in rows of columns) from the start of the lookup range. That will be used in your Col Offset (3rd arg) in your instance.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[SkipVought]

Based on your layout, if I entered a date in A7 and a company in B7...
[tt]
=OFFSET($A$2,MATCH(B7,$A$3:$A$4,0),MATCH(A7,$B$2:$E$2,0),1,1)
[/tt]
returns a value reflecting one of your percentages.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[JStandard]

Skip,

Ah, yes, now that I've understood the use of Match() to determine the horizontal movement of the target cell.

Worked like a charm, thanks kindly.

Jeff.