-
Notifications
You must be signed in to change notification settings - Fork 0
/
VBA Script
64 lines (57 loc) · 2.2 KB
/
VBA Script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
Function DistributeGoal(distributionType As String, totalMonths As Integer, currentPosition As Integer, target As Double) As Double
Dim value As Double
value = 0
Select Case distributionType
Case "linear"
' Linear distribution: equally distribute the goal among the months
value = target / totalMonths
Case "logarithmic"
' Logarithmic distribution
Dim logSum As Double
logSum = 0
Dim i As Integer
For i = 1 To totalMonths
logSum = logSum + Log(1 + i)
Next i
Dim logValue As Double
logValue = Log(1 + currentPosition)
value = (logValue / logSum) * target
Case "exponential"
' Exponential distribution
Dim expSum As Double
expSum = 0
For i = 1 To totalMonths
expSum = expSum + Exp(i)
Next i
Dim expValue As Double
expValue = Exp(currentPosition)
value = (expValue / expSum) * target
Case "normal"
' Normal (Gaussian) distribution
Dim mean As Double
mean = (totalMonths + 1) / 2
Dim variance As Double
variance = totalMonths / 6
Dim normalSum As Double
normalSum = 0
For i = 1 To totalMonths
normalSum = normalSum + Exp(-((i - mean) ^ 2) / (2 * variance))
Next i
Dim normalValue As Double
normalValue = Exp(-((currentPosition - mean) ^ 2) / (2 * variance))
value = (normalValue / normalSum) * target
Case "quadratic"
' Quadratic distribution (Ski Jump)
Dim quadSum As Double
quadSum = 0
For i = 1 To totalMonths
quadSum = quadSum + i ^ 2
Next i
Dim quadValue As Double
quadValue = currentPosition ^ 2
value = (quadValue / quadSum) * target
Case Else
Err.Raise vbObjectError + 513, "DistributeGoal", "Unknown distribution type: " & distributionType
End Select
DistributeGoal = value
End Function