NORMDIST Function

T

taltyr

New Member
Joined
Jul 20, 2011
Messages
31
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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
 
Upvote 0
For instance:

Code: 
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
 
Upvote 0
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
 
Upvote 0
If you have a section of code like this:

Code: 
'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.
 
Upvote 0
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
 
Upvote 0
Try something like this
Code: 
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
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Construction Budget distribution using GAMMADIST Function
Replies
4
Views
203
ibrahimdaas
I
J
  • Question
Spread cost with skewed normal distribution
Replies
0
Views
1K
JontyO
J
M
  • Question
Cashflow Spread
Replies
1
Views
666
JamesCanale
JamesCanale
JeffGrant
  • Solved
Array output - very very strange - I am a complete loss
Replies
4
Views
337
Peter_SSs
Peter_SSs
M
  • Question
Dynamic solution for costs over multiple periods using an S-Curve distribution
Replies
0
Views
2K
mmelels
M

Forum statistics

Threads
1,214,932
Messages
6,122,323
Members
449,077
Latest member
jmsotelo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top