I am attempting to program a function in VBA that allows me to distribute costs, based on the bell
curve, over a period of months. I would like that period to be dynamic, as in, the user can merely specify the beginning and
end months and, given a "test" date, the function returns the percentage allocated to that month. The beginnings of
the algorithm come from the attached sheet. Here is what I have come up with so far, however, I keep recieving a #NAME error
when I call the function in a cell. The second sheet on the attached shows my attempted implementation of this code.
Function BellPDF(TestDate As Date, EarlyDate As Date, LongDate As Date, Dev As Long)
Dim nMons, Mean, Factor As Double
Dim relMo, Beta As Double
nMons = (Year(LongDate) - Year(EarlyDate)) * 12 + Month(LongDate) - Month(EarlyDate)
Factor = 1 / (1 - 2 * NormDist(0, nMos / 2, Dev, True))
BellPDF = NormDist(Month(TestDate), nMos / 2, Dev, True) - NormDist(Month(TestDate) - 1, nMos / 2, Dev, True) * Factor
Any help at all would be greatly appreciated. I feel like I am literally just staring at the problem but I have been at it so
long with the same eyes that I cannot see something simple I am doing incorrectly.