|
The forecast function from Excel as well as moving average are two important functions that are missing from Microsoft Access. Recently I developed a small stock market application in Microsoft Access and needed both of these functions. I am making them available here in case anyone else can benefit from these two functions.
Function xl_forecast(num As Integer, x_values() As Double,
y_values() As Double) As Double
Dim x_Avg As Double
Dim y_Avg As Double
Dim b As Double
Dim a As Double
Dim i As Integer
Dim tempTop As Double
Dim tempBottom As Double
For i = 1 To UBound(x_values)
x_Avg = x_Avg + x_values(i)
Next
x_Avg = x_Avg / UBound(x_values)
For i = 1 To UBound(y_values)
y_Avg = y_Avg + y_values(i)
Next
y_Avg = y_Avg / UBound(y_values)
For i = 1 To UBound(y_values)
tempTop = tempTop + (x_values(i) - x_Avg) *
(y_values(i) - y_Avg)
tempBottom = tempBottom + ((x_values(i) -
x_Avg) * (x_values(i) - x_Avg))
Next
b = tempTop / tempBottom
a = y_Avg - b * x_Avg
xl_forecast = a + b * num
End Function
Function moving_average(x_values() As Double) As
Double
Dim x_Avg As Double
Dim i As Integer
For i = 1 To UBound(x_values)
x_Avg = x_Avg + x_values(i)
Next
moving_average = x_Avg / UBound(x_values)
End Function |