NORMDIST Function

[taltyr]

I know the Budget, Duration, Start Date and End Date for a Construction Project. I want to fill cells on a Gant Chart to spread the Budget between the Start Dates and End Dates using a Normal Distribution.

So far I have managed to get VBA to loop through the cells until it reaches the Column with the correct Start Date as the heading. From there it can fill the Budget accross the correct Duration. However all I can do is to get it to spread the Budget evenly, i.e. Budget/Duration in each month. A normal distribution to spread the Budget in what would be a Bell Curve (if I was to plot it) would be a better solution. I think NORMDIST gets me in the right ball park, however, I don't know how to code it to fill the range.

Any help appreciated.

Regards

Taltyr

 

[wigi]

Hello there,

welcome on Board.

Please have a look at this webpage: http://www.graphpad.com/faq/viewfaq.cfm?faq=966

 

[taltyr]

Thanks, I am only getting into VBA and this board has already been a great help.

I have had a look at the link you supplied howevre I still can't get my head around this one.

Below is an extract from the data I am working on.

<TABLE style="WIDTH: 1316pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1750 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" span=2 width=78><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" span=12 width=117><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=83 height=20></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=78>Start Date</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=78>Finish Date</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=107>Budget</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Jan-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Feb-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Mar-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Apr-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>May-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Jun-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Jul-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Aug-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Sep-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Oct-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Nov-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=117>Dec-11</TD></TR><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" height=31>Project 1</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Apr-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Aug-11</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 1,500,000.00 </TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 300,000.00 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 300,000.00 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 300,000.00 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 300,000.00 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 300,000.00 </TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" height=31>Project 2</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Jun-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Oct-11</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 5,107,010.73 </TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,021,402.15 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,021,402.15 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,021,402.15 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,021,402.15 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,021,402.15 </TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" height=31>Project 3</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Aug-11</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Dec-11</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 8,680,997.58 </TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,736,199.52 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,736,199.52 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,736,199.52 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,736,199.52 </TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> 1,736,199.52 </TD></TR><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" height=31></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
Take for example Project 1, starting in April 11 and Finishing in August 11 (5 Months) with a budget of 1,500,000. I can get the macro to populate the Schedule with numbers from April to August but only dividing the Budget into 5 equal parts and putting 300,000 in each of the relevant months. What I want to do is spread the budget accross these months but in a Normal Distribution/ Bell Curve spread.

Hope this clarifies.

Regards

Taltyr

 

[wigi]

For instance:

Sub testit()
    
    Dim rngCell As Range
    
    For Each rngCell In Selection 'change the selection to match your area
        rngCell.Value = RandGauss(100, 2) 'supply the mean and sd to the function
    Next

End Sub

Function RandGauss(Mean, Sd) As Double

'from Numerical Recipes in C, second edition, page 289
'returns a random number from Gaussian distribution with mean and SD specified
    Static NextRnd As Double
    Static RndWaiting As Boolean
    Static Randomized As Boolean

    Dim fac As Double, rsq As Double, v1 As Double, v2 As Double, RandStd As Double

    If Not (Randomized) Then
        Randomize
        Randomized = True
    End If

    If Not (RndWaiting) Then
        Do
            v1 = 2# * Rnd() - 1#
            v2 = 2# * Rnd() - 1#
            rsq = v1 * v1 + v2 * v2
        Loop Until rsq <= 1#
        fac = Sqr(-2# * Log(rsq) / rsq)    'natural log
        NextRnd = v1 * fac
        RndWaiting = True
        RandStd = v2 * fac
    Else
        RndWaiting = False
        RandStd = NextRnd
    End If
    'RandStd has mean zero and SD=1.
    RandGauss = (RandStd * Sd) + Mean

End Function

 

[taltyr]

This is the code I am using to fill the Cells at the moment;

Note that this code also adds a 10% Advance Payment to the first month. Keeps 10% retention throughout the Project, releases half of this (5%) on the last month and the remainder 12 months later.

Sub FillCashFlow()

' Dim Cell As Object
Dim Duration As Integer
Dim Budget As Long
Dim MthExp As Long
Dim Mth1 As Long
Dim MthLast As Long
Dim Retention As Long
Dim StartDate As Date
Dim FinishDate As Date

Application.ScreenUpdating = False

'Clear Previous
Range("E2:AB55").Select
Selection.ClearContents
Selection.Interior.Pattern = xlNone
Selection.Interior.TintAndShade = 0
Selection.Style = "Comma"
Range("A2").Select

' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
Budget = 0
Duration = 0
StartDate = 0
FinishDate = 0

'Get Start Date

Cells(ActiveCell.Row, 1).Select

Do
If Cells(1, ActiveCell.Column) = "Start Date" Then StartDate = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Loop Until StartDate > 0



'Get Finish Date

Cells(ActiveCell.Row, 1).Select

Do
If Cells(1, ActiveCell.Column) = "Finish Date" Then FinishDate = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Loop Until FinishDate > 0

'Calculate Project Duration

Duration = DateDiff("M", StartDate, FinishDate) + 1


'Get Budget

Cells(ActiveCell.Row, 1).Select
Do
If Cells(1, ActiveCell.Column) = "Budget" Then Budget = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Loop Until Budget > 0


MthExp = (Budget * 0.8) / Duration
Mth1 = MthExp + Budget * 0.1

'Start at Start Date
Cells(ActiveCell.Row, 1).Select

Do

ActiveCell.Offset(0, 1).Select
If Cells(1, ActiveCell.Column) = StartDate Then ActiveCell.Value = Mth1
If Cells(1, ActiveCell.Column) = StartDate Then ActiveCell.Interior.Color = 65535
Loop Until Cells(1, ActiveCell.Column) = StartDate

ActiveCell.Offset(0, 1).Select

'Fill to End Date
Do
ActiveCell.Value = MthExp
Selection.Interior.Color = 65535
ActiveCell.Offset(0, 1).Select
Loop Until Cells(1, ActiveCell.Column) > FinishDate

'Release 1st half Retention and clear rounding error
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = (Budget * 0.8) - (MthExp * Duration) + MthExp + Budget * 0.05

'Release 2nd half Retention
ActiveCell.Offset(0, 12).Select
Selection.Interior.Color = 65535
ActiveCell.Value = Budget * 0.05


' MsgBox "Start Date " & StartDate & vbNewLine & "Finish Date " & FinishDate & vbNewLine & "Budget " & Budget
Cells(ActiveCell.Row, 1).Select
ActiveCell.Offset(1, 0).Select


Loop

Application.ScreenUpdating = True
End Sub

Where would I add your code?

Thank you for your time on this.

Regards

taltyr

 

[wigi]

If you have a section of code like this:

'Fill to End Date
Do
ActiveCell.Value = MthExp
Selection.Interior.Color = 65535
ActiveCell.Offset(0, 1).Select
Loop Until Cells(1, ActiveCell.Column) > FinishDate

which is called "Fill to End Date", chances are pretty big it's there you will need to insert code for generating a random number.

Look above to know how to apply the function.

Can you please put code tags around your VBA code here, rather than formatting the normal sentences? Thanks.
It also helps to anyone (beginners and advanced coders) to indent your code.

 

[shg]

There's a workbook that spreads labor (or cost) using NASA's Beta function at http://www.box.net/shared/ipkvdyc4bt

 

[taltyr]

Thanks for your help, I am almost there. I think I just need the answer to one more question, but I will out that on a different thread. We will consider this one solved!

Sorry about not referenceing my code correctly, I am new to the board and don't know how. I will fing out though.

Thanks

taltyr

 

[HarryS]

Try something like this

Private Sub CommandButton1_Click()
    doSpread "4/11/11", 10, 20000
End Sub
Sub doSpread(DateStart As Date, nMonths%, Amt!)
    Dim Mv!, Prob1V!, Prob2V!, Steps!, RR%
    Set ra = Range("d4")    ' where the column data to start
    Steps = 4 / nMonths    ' only doing -2z to + 2z
    For Mv = -2 + Steps To 2 - Steps Step Steps
        Prob2V = Application.WorksheetFunction.NormDist(Mv, 0, 1, True)
        RR = RR + 1
        ra(RR, 0) = DateStart + DateSerial(0, RR, 0)
        ra(RR, 1) = (Prob2V - Prob1V) * Amt
        ra(RR, 2) = ra(RR, 1) + ra(RR - 1, 2)
        Prob1V = Prob2V
    Next Mv
    RR = RR + 1
    ra(RR, 0) = DateStart + DateSerial(0, RR, 0)
    ra(RR, 1) = (1 - Prob2V) * Amt
    ra(RR, 2) = ra(RR, 1) + ra(RR - 1, 2)
End Sub

 

[bnm]

Looking at the same distribution of cash over time.


"This is the code I am using to fill the Cells at the moment;

Note that this code also adds a 10% Advance Payment to the first month. Keeps 10% retention throughout the Project, releases half of this (5%) on the last month and the remainder 12 months later.

Sub FillCashFlow()"

The above macros get me most of the way but error when the start date is not the 1st of the month,

Any ideas