Calculating statistical measures based upon a given start and end date.

[Chanko]

Hi there,

I have investment returns for stocks that I would like to calculate the mean for based upon a certain date range i.e. 1st May 2010 to 14th May 2010 (daily date to daily date) or January to March (month to month).

The spreadsheet is set up in that Sheet2 has the daily dates in "column A", "column B" has Stock_A daily returns, "column C" has Stock_B daily returns, and "column D" has Stock_C returns.

Sheet1 has user inputs i.e. Stock, StartDate, EndDate, and DataFrequency.

I would like to create a dynamic procedure that would allow the user to pick the stock, enter the start date in question, enter the end date in question, the data frequency in question, and the mean value for that period would appear in Sheet3 in cell A1 for example.

The below is me just thinking about it.

Dim Stock As String
Dim StartDate As Date
Dim EndDate As Date
Dim DataFreQ As String
 
' User inputs
Fund = Worksheets("Sheet1").Range("A1")
StartDate = Worksheets("Sheet1").Range("A2")
EndDate = Worksheets("Sheet1").Range("A3")
DataFrequency = Worksheets("Sheet1").Range("A4")

I’m currently studying up on John Walkenbach’s excel reference guide but it’s taking time.

I’m still learning and definitely not a VBA master yet. Hence, my main question is how do I create procedure that calculates the mean for a certain start date and end date range.

Any assistance would be much appreciated.

Thanks.

[NeoPa]

Please explain (and maybe give some example data of) the layout of the data in Sheet2. [code] tags are mandatory so please don't forget to use them for showing the data.

[NeoPa]

Assuming that each date only has one row of data for it in Sheet2, then once the operator selections have been made in Sheet1 you would want to follow the steps below :

1. Use Range.Find in Column A of Sheet2 to determine the Row number of the first required date.
2. Use Range.Find in Column A of Sheet2 (again) to determine the Row number of the last required date.
3. Determine the column from whatever the operator enters that selects the column (I guess it must be the fund value).
4. Now you have the actual range of cells required you can set the formula of Sheet3.A1 to :
   

   =Average({DeterminedRange})

[Chanko]

Thanks for the speedy resonse NeoPa.

That makes perfect sense :)

[NeoPa]

Pleased to help Chanko. It's always easier when a member posts a question that's well expressed, as yours is.