By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

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

P: 5
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.

Expand|Select|Wrap|Line Numbers
  1. Dim Stock As String
  2. Dim StartDate As Date
  3. Dim EndDate As Date
  4. Dim DataFreQ As String
  5.  
  6. ' User inputs
  7. Fund = Worksheets("Sheet1").Range("A1")
  8. StartDate = Worksheets("Sheet1").Range("A2")
  9. EndDate = Worksheets("Sheet1").Range("A3")
  10. 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.
Apr 1 '12 #1

✓ answered by 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 :
    Expand|Select|Wrap|Line Numbers
    1. =Average({DeterminedRange})

Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 1 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
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 :
    Expand|Select|Wrap|Line Numbers
    1. =Average({DeterminedRange})
Apr 1 '12 #3

P: 5
Thanks for the speedy resonse NeoPa.

That makes perfect sense :)
Apr 2 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
Pleased to help Chanko. It's always easier when a member posts a question that's well expressed, as yours is.
Apr 2 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.