G
Guest
I have a function that calculates the standard deviation of the last X orders
for a particular day of the week (Sun-Sat). Some products have a longer
sales history than others, and new items could be added in the future. The
spreadsheet is arranged with a worksheet for each product as follows:
weekday in column A (int 1 to 7)
date in column C
order amount in column D
M2:Q8 is a matrix showing the average order level for each day of the week
for about the last 20 weeks
the for loop is supposed to go from the day before the current day back to
the first day used in calculating the average (so that my Stdev and average
are for the same data set).
I use a dynamic array because the data set may hold 20 values, or only a
couple.
When I run the macro I get an "Invalid Call" error. Before I used a static
array and it worked fine. The debugger highlights the last line of code
(where I call StDevP(arrQty)) with the error.
Function FindStdDev(ItemName As String, Day As Integer) As Single
Dim arrQty() As Integer
Dim CurrDateRow As Single
Dim i As Single
Dim k As Integer
Dim currdate As Single
k = 1
currdate = Date
With ThisWorkbook.Worksheets(ItemName)
CurrDateRow = Application.WorksheetFunction.Match(currdate, _
ThisWorkbook.Worksheets(ItemName).Range("C1:C1104"), 0)
For i = CurrDateRow - 1 To CurrDateRow - _
(7 * .Cells(Day + 1, 16)) Step -1
If .Cells(i, 1) = Day Then
ReDim Preserve arrQty(k)
arrQty(k) = .Cells(i, 4)
k = k + 1
End If
Next
End With
FindStdDev = Application.WorksheetFunction.StDevP(arrQty)
End Function
for a particular day of the week (Sun-Sat). Some products have a longer
sales history than others, and new items could be added in the future. The
spreadsheet is arranged with a worksheet for each product as follows:
weekday in column A (int 1 to 7)
date in column C
order amount in column D
M2:Q8 is a matrix showing the average order level for each day of the week
for about the last 20 weeks
the for loop is supposed to go from the day before the current day back to
the first day used in calculating the average (so that my Stdev and average
are for the same data set).
I use a dynamic array because the data set may hold 20 values, or only a
couple.
When I run the macro I get an "Invalid Call" error. Before I used a static
array and it worked fine. The debugger highlights the last line of code
(where I call StDevP(arrQty)) with the error.
Function FindStdDev(ItemName As String, Day As Integer) As Single
Dim arrQty() As Integer
Dim CurrDateRow As Single
Dim i As Single
Dim k As Integer
Dim currdate As Single
k = 1
currdate = Date
With ThisWorkbook.Worksheets(ItemName)
CurrDateRow = Application.WorksheetFunction.Match(currdate, _
ThisWorkbook.Worksheets(ItemName).Range("C1:C1104"), 0)
For i = CurrDateRow - 1 To CurrDateRow - _
(7 * .Cells(Day + 1, 16)) Step -1
If .Cells(i, 1) = Day Then
ReDim Preserve arrQty(k)
arrQty(k) = .Cells(i, 4)
k = k + 1
End If
Next
End With
FindStdDev = Application.WorksheetFunction.StDevP(arrQty)
End Function