Solved

Random hours in timesheet

Posted on 2013-12-01
17
741 Views
Last Modified: 2013-12-02
This one is a bit tricky and almost Sudoku like in complexity – at least for me.

I’d like to find a way to randomly generate hours in a timesheet for a pay period based on certain criteria.

Each day and pay period will have hours spread among several projects.  Each project’s total hours for the pay period must total to a percentage of the time allocated to the project.

Each day must add up to 8 hours, and each pay period must total 80 hours.

For example, if project 1 is allocated 30% of the time for the pay period, then the total hours for project 1 in that pay period must add up to 24.  At the same time, the total number of hours for each day, across the multiple projects must total 8.

Some days can have zero hours assigned to a project, as long as the total across the pay period equals the total determined by the percentage.

The individual project/day hours can be one decimal place (e.g. 1.4, 0.6).

The goal is to create random and changing numbers that will add up to the specified daily and bi-weekly totals.

All advice, thoughts, and comments appreciated.

Timesheet Template
0
Comment
Question by:mullinsbc
  • 11
  • 3
  • 3
17 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
Summary
- Create a base case distribution that works for all your rules
- Make random "trades" between the projects that cannot violate the rules

Detailed Explanation
- Work out the expected number of hours to be worked on each project by multiplying the total number of hours by the percentage.
- Fill your 'table' in a completely systematic manner that respects your rules
- Randomly select a pair projects
- Randomly select 2 days
- Trade your minimum time unit (0.1 hours) between them
- Loop !!

Example Base Cases
1) Just assign 1/10 of each project to each day
Even Distribution Base Case2) Start from the first day and the first project and work left to right, top to bottom (i.e. sequential work)
Sequential Work Base Case
Example Trade
0.1 hours has been moved from project 1 to project 4 on the first Monday, and in response 0.1 hours has been moved the opposite direction the following Tuesday.

I considered trading random amounts but I think this might skew towards extremes e.g. 0 or 8 hours allocated more often than middle values. Plus it's easier to always trade the minimum unit and just run the loop a huge number of times.
Illustration of a trade
Filtering Out Invalid Trades
Random projects should be selected from only those with >0% total allocation (i.e. ignore projects with no work assigned anywhere)
Identify one project as the 'donor' and the other as the 'receiver'
Randomly select a 'donor day' where the 'donor' project has >0 work allocated AND the 'receiver' has less <8 hours allocated
Randomly select a 'receiver day' where the 'receiver' project has >0 work allocated AND the 'donor' has less <8 hours allocated

If the you cannot find a day (because one project only has work to send where the other is already maxed then just discard the result and continue with the loop
If you happen to select the same day as both donor and receiver day then it actually doesn't matter (the trade will work although in practice results in no change)

Pseudo Code
... tbd ...

Aside
I love trying to solve problems using raw Excel and sneaky formulas. I first tried using Excel formulas only but I think this problem may be impossible to solve reliably that way but it was fun to try.
0
 
LVL 22

Expert Comment

by:Flyster
Comment Utility
Here's a sample using RandBetween. In row 2 the formula is:

=RANDBETWEEN(0,80)/10

This will give you any number between 0.0 and 8.0. For row 3 the formula is:

=RANDBETWEEN(0,80-(C2*10))/10

This subtracts the value from above so your total will not exceed 8. Rows 4,5 & 6 also subtracts the rows above them. With this in mind, project 5 will usually have the lowest numbers. You can adjust the numbers in the formula which will give you higher numbers below, that is RANDBETWEEN(0,70)/10 will yield a maximum of 7 hours instead of 8.

Flyster
RandomHours.xlsm
0
 
LVL 22

Expert Comment

by:Flyster
Comment Utility
Slight correction to the above explanation. Row 6 uses formula:

 =8-SUM(C2:C5)

This will add the value that is needed to bring the sum of the above values to 8.
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
@Flyster: Maybe I misread the question but I had assumed the distribution percentages were inputs not outputs. If this is the case I think the Excel formula approach is impossible - either you have issues balancing each row or it loses the randomness.

Whether that was the question or not, I love your answer - very clean :)

@mullinsbc: I'll continue my answer and explanation in more detail if you do indeed want to specify the distributions as input.
0
 
LVL 1

Author Comment

by:mullinsbc
Comment Utility
@Angelplay:  You are correct that the percentages are input.  Thanks for your response.  I'll have to play around with that to see if I can accomplish what you are suggesting.  

@Flyster:  Thanks for the response but the percentages are input, and while your solution does keep the columns to 8, it doesn't address the max that the rows need to be based on the input percentages.

I believe this will require an iterative search approach similar to many of the popular Sudoku excel sheets that are available.  Unfortunately, I'm not that great with VB so it's a little beyond me at the moment.
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
Hi mullinsbc.

I've almost finished a VB.NET example (hopefully will help you). I just paused to check it was still relevant. Will post shortly.

Note: I think your requirements differ sufficiently from sodoku that the solution is happily simpler :)
0
 
LVL 22

Expert Comment

by:Flyster
Comment Utility
Just reread your post. I knew this was too easy!
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
Example VB.NET Code

    Public Sub GetRandomWorkDistribution()

        ' I'm hardcoding the inputs but you may want to extract them from the worksheet
        Dim hoursPerDay As Decimal = 8D
        Dim daysPerPeriod As Integer = 10
        Dim totalHours As Decimal = hoursPerDay * daysPerPeriod
        Dim minimumTimeUnit As Decimal = 0.1D

        Dim percentAllocations As New Dictionary(Of Integer, Decimal)() _
            From {{1, 30D}, {2, 15D}, {3, 20D}, {4, 25D}, {5, 10D}}

        ' Calculate the actual allocations to the near unit
        ' ...and add the balance of time lost/gained from rounding to project 1
        Dim actualAllocations As IDictionary(Of Integer, Decimal) = _
            percentAllocations.ToDictionary(Function(kvp) kvp.Key, _
                                            Function(kvp) Math.Round(kvp.Value * totalHours / 100 / minimumTimeUnit) * minimumTimeUnit)

        Dim allocationRoundingError As Decimal = totalHours - actualAllocations.Values.Sum()
        actualAllocations(1) = actualAllocations(1) + allocationRoundingError

        ' Find out up front which projects actually have work
        Dim projectsWithWork As IEnumerable(Of Integer) = actualAllocations.Where(Function(kvp) kvp.Value > 0).Select(Function(kvp) kvp.Key)

        ' Setup the original base case
        ' -> First integer represents the project ID, second is the day ID, the double is the number of hours
        Dim workDistribution As New Dictionary(Of Integer, IDictionary(Of Integer, Decimal))()
        For Each project As Integer In actualAllocations.Keys
            ' Calculate the total time allocated to the project
            ' ...and add the balance of time lost/gained to the first day
            Dim projectAllocation As Decimal = actualAllocations(project)
            Dim perDayAllocation As Decimal = Math.Round(projectAllocation / daysPerPeriod / minimumTimeUnit) * minimumTimeUnit
            Dim perDayRoundingError As Decimal = projectAllocation - (perDayAllocation * daysPerPeriod)

            ' Create a dictionary for the period
            Dim days As New Dictionary(Of Integer, Decimal)(daysPerPeriod)
            days(1) = perDayAllocation + perDayRoundingError
            For d As Integer = 2 To daysPerPeriod
                days(d) = perDayAllocation
            Next

            workDistribution(project) = days
        Next

        ' Apply trades
        Dim rand As New Random()
        For i As Integer = 0 To 10000
            ' Randomly select projects
            Dim donorProject As Integer = projectsWithWork.ElementAt(rand.Next(0, projectsWithWork.Count))
            Dim receiverProject As Integer = projectsWithWork.ElementAt(rand.Next(0, projectsWithWork.Count))

            ' Get days with work allocated
            ' ...and skip if none available
            Dim donorDays As IEnumerable(Of Integer) = workDistribution(donorProject).Where(Function(kvp) kvp.Value > 0).Select(Function(kvp) kvp.Key)
            Dim receiverDays As IEnumerable(Of Integer) = workDistribution(receiverProject).Where(Function(kvp) kvp.Value > 0).Select(Function(kvp) kvp.Key)
            If donorDays.Count = 0 OrElse receiverDays.Count = 0 Then Continue For

            ' Randomly select days
            Dim donorDay As Integer = donorDays.ElementAt(rand.Next(0, donorDays.Count))
            Dim receiverDay As Integer = receiverDays.ElementAt(rand.Next(0, receiverDays.Count))

            ' Trade
            Dim donorDistribution As IDictionary(Of Integer, Decimal) = workDistribution(donorProject)
            Dim receiverDistribution As IDictionary(Of Integer, Decimal) = workDistribution(receiverProject)
            donorDistribution(donorDay) = donorDistribution(donorDay) - minimumTimeUnit
            donorDistribution(receiverDay) = donorDistribution(receiverDay) + minimumTimeUnit
            receiverDistribution(donorDay) = receiverDistribution(donorDay) + minimumTimeUnit
            receiverDistribution(receiverDay) = receiverDistribution(receiverDay) - minimumTimeUnit
        Next

        ' Do something to write the results back to the worksheet
        ' -> I'm just printing to the console
        For Each project As Integer In workDistribution.Keys
            Dim line As New System.Text.StringBuilder()
            line.Append(project.ToString()).Append(vbTab)

            Dim projectDistribution As IDictionary(Of Integer, Decimal) = workDistribution(project)
            For Each day As Integer In projectDistribution.Keys
                line.Append(projectDistribution(day)).Append(vbTab)
            Next

            Console.WriteLine(line.ToString())
        Next
    End Sub

Open in new window


Screenshots
Work Hours per Project (calculated from percentages)Initial (Even) Distribution of Project 1The First Trade in ActionFinal Distribution of Project 1 (after 10000 random trades)Console Dump of Final DistributionExcel View of Final Distribution (...showing the numbers still add up!)
Couple of words of warning:
- I had to use the VB.NET decimal type instead of double because double was rounding really badly
- By far the hardest part was the data structure to store two-dimensional data - perhaps I would have been better to use a 2D array (but in .NET the collection types are much easier to use)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:mullinsbc
Comment Utility
Thanks AngelPlay!  Um...  How do I get VB.NET code to work in Excel?

From my research it looks like I have to start in VB, then call Excel and run the code.  Is there a way to convert this to VBA or use some other device that is native to Excel?  

I did try modifying your code (changing Decimal to Variant then converting with CDec) but quickly went beyond my capabilities.
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
You're right that the most straight forward is to write VBA (Visual Basic for Applications) within your Excel document. The VB.NET code I wrote is just to give you an outline of the logic.

VBA is a pain to write. I had forgotten how painful the editor is. Let's step through it together a bit at a time to make sure we stay on the same page.

I'm assuming you're already this far - managed to open the VBA editor for Excel, created a module, defined the function and declared the first decimals:

Declaration of Decimals in VBA
    Public Sub GetRandomWorkDistribution()

        ' I'm hardcoding the inputs but you may want to extract them from the worksheet
        Dim daysPerPeriod As Integer, hoursPerDay, totalHours, minimumTimeUnit As Variant
        hoursPerDay = CDec(8)
        daysPerPeriod = CDec(10)
        totalHours = hoursPerDay * daysPerPeriod
        minimumTimeUnit = CDec(0.1)

        MsgBox (daysPerPeriod)

    End Sub

Open in new window


I'm just dumping out the answer to this basic piece to make sure it all works so far!

You can now see and run this as a Macro - probably we'll eventually bind this to a shortcut key (or a button in the form).

Macro Menu Listing Our MethodThe MsgBox Resulting from Running Our Macro
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
Ok, so next let's add a dictionary...

First add a project reference to the Microsoft Scripting Runtime (VBA Editor > Tools > References)

Adding a Reference
Now you'll have intellisense for the Scripting.Dictionary object :)
Defining a Dictionary with Intellisense
You can iterate over the dictionary with the For Each structure. Note again some intellisense :)

Looping Over a Microsoft Scripting Dictionary
Here's the full code so far with an updated MsgBox :)
    Public Sub GetRandomWorkDistribution()

        ' I'm hardcoding the inputs but you may want to extract them from the worksheet
        Dim daysPerPeriod As Integer, hoursPerDay, totalHours, minimumTimeUnit As Variant
        hoursPerDay = CDec(8)
        daysPerPeriod = CDec(10)
        totalHours = hoursPerDay * daysPerPeriod
        minimumTimeUnit = CDec(0.1)

        Dim percentAllocations As New Scripting.Dictionary
        percentAllocations.Add 1, CDec(30)
        percentAllocations.Add 2, CDec(15)
        percentAllocations.Add 3, CDec(20)
        percentAllocations.Add 4, CDec(25)
        percentAllocations.Add 5, CDec(10)

        ' Calculate the actual allocations to the near unit
        ' ...and add the balance of time lost/gained from rounding to project 1
        Dim actualAllocations As New Scripting.Dictionary
        For Each pKey In percentAllocations.Keys
            actualAllocations.Add pKey, Round((percentAllocations.Item(pKey) * totalHours / 100 / minimumTimeUnit) * minimumTimeUnit)
        Next

        Dim allocationsRoundingError As Variant
        allocationsRoundError = totalHours
        For Each aKey In actualAllocations.Keys
            allocationsRoundError = allocationsRoundError - actualAllocations(aKey)
        Next
        actualAllocations.Items(0) = actualAllocations.Items(0) + allocationsRoundingError

        ' Dump it back out to show where we are...
        Dim temp As String
        For Each aKey In actualAllocations.Keys
            temp = temp & aKey & "-" & actualAllocations.Item(aKey) & vbCrLf
        Next
        MsgBox (temp)

    End Sub

Open in new window

Outputting the Actual Allocations Dictionary
A word of warning: It seems this Dictionary type has a pair of methods Item/Items. From what I can tell the first accesses by key and the second is a collection that can be iterated over i.e. myDict.Item(1) gives you the entry with key '1' not the entry at index 1 (which would be the second since indexes are zero-based).
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
Next we're adding the collection type to manage the list of projects with time available to trade. Inexplicably the VBA collection type has one-based indexing (not zero-based) unlike the scripting dictionary.

        ' Find out up front which projects actually have work
        Dim projectsWithWork As New Collection
        For Each aKey In actualAllocations.Keys
            If actualAllocations.Item(aKey) > 0 Then projectsWithWork.Add (aKey)
        Next

        ' Dump it back out to show where we are...
        Dim temp As String
        Dim pIndex As Integer
        For pIndex = 1 To projectsWithWork.Count
            temp = temp & pIndex & "-" & projectsWithWork.Item(pIndex) & vbCrLf
        Next
        MsgBox (temp)

Open in new window


Output Showing Projects with Time Allocated (all of them in our example)
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
The next bit is quite big - setting up the base case. If you compare you'll see it's very similar to the VB.NET logically.

        ' Setup the original base case
        ' -> First integer represents the project ID, second is the day ID, the double is the number of hours
        Dim workDistribution As New Scripting.Dictionary ' Int > (Int > Decimal)
        Dim days As Scripting.Dictionary ' Int > Decimal
        For Each pKey In actualAllocations.Keys
            ' Calculate the total time allocated to the project
            ' ...and add the balance of time lost/gained to the first day
            Dim projectAllocation, perDayAllocation, perDayRoundingError As Variant ' Decimal
            projectAllocation = actualAllocations(pKey)
            perDayAllocation = Round(projectAllocation / daysPerPeriod / minimumTimeUnit) * minimumTimeUnit
            perDayRoundingError = projectAllocation - (perDayAllocation * daysPerPeriod)

            ' Create a dictionary for the period
            Set days = New Scripting.Dictionary ' Int > Decimal
            days.Add 1, perDayAllocation + perDayRoundingError
            Dim dIndex As Integer
            For dIndex = 2 To daysPerPeriod
                days.Add dIndex, perDayAllocation
            Next

            workDistribution.Add pKey, days
        Next

Open in new window


The main bit to be aware of is to use Set when assigning object types (i.e. the line in the loop that sets days to point to a new scripting dictionary each turn through the loop).

...I do not envy VBA programmers, .NET avoids a lot of this faff...

If you get stuck remember that you can set breakpoints in your code (click on a line except a declaration and hit F9), and you can then step through the code (F8) and add hover / add watches to view the current values.

Stepping Through with the Debugger
...so this finally gets us a base distribution:
Base Distribution for Project 1
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
For the last bit we need some random numbers. To make it simply I've defined a private function that does this work:
    Private Function RandBetween(lowerbound As Double, upperbound As Double) As Integer
        RandBetween = CInt(Int((upperbound - lowerbound + 1) * Rnd())) + lowerbound
    End Function

Open in new window


Then we can re-write the VB.NET trades code as follows:
        ' Apply trades
        Dim tIndex, donorProject, receiverProject, donorDay, receiverDay As Integer
        Dim projectDistribution As Scripting.Dictionary
        Dim donorDays, receiverDays As Collection
        For tIndex = 0 To 10000
            ' Randomly select projects
            donorProject = projectsWithWork.Item(RandBetween(1, projectsWithWork.Count))
            receiverProject = projectsWithWork.Item(RandBetween(1, projectsWithWork.Count))

            ' Get days with work allocated
            ' ...and skip if none available
            Set donorDistribution = workDistribution(donorProject)
            Set donorDays = New Collection
            For Each dKey In donorDistribution.Keys
                If donorDistribution(dKey) > 0 Then donorDays.Add dKey
            Next
            
            Set receiverDistribution = workDistribution(receiverProject)
            Set receiverDays = New Collection
            For Each dKey In receiverDistribution.Keys
                If receiverDistribution(dKey) > 0 Then receiverDays.Add dKey
            Next
            
            If donorDays.Count <> 0 And receiverDays.Count <> 0 Then
                ' Randomly select days
                donorDay = donorDays.Item(RandBetween(1, donorDays.Count))
                receiverDay = receiverDays.Item(RandBetween(1, receiverDays.Count))

                ' Trade
                donorDistribution(donorDay) = donorDistribution(donorDay) - minimumTimeUnit
                donorDistribution(receiverDay) = donorDistribution(receiverDay) + minimumTimeUnit
                receiverDistribution(donorDay) = receiverDistribution(donorDay) + minimumTimeUnit
                receiverDistribution(receiverDay) = receiverDistribution(receiverDay) - minimumTimeUnit
            End If
        Next

        ' Dump it back out to show where we are...
        Dim temp As String
        For Each dKey In workDistribution(1)
            temp = temp & dKey & "-" & workDistribution(1).Item(dKey) & vbCrLf
        Next
        MsgBox (temp)

Open in new window


And we get our result!
Distribution of Project 1 After 10000 Random Trades
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 500 total points
Comment Utility
...final touches, added some code to loop through and print out to the work sheet.

Ping me if you have any questions :)

The Final Output
Example XSLM attached

Full Excel VBA Source:
    Public Sub GetRandomWorkDistribution()

        ' I'm hardcoding the inputs but you may want to extract them from the worksheet
        Dim daysPerPeriod As Integer, hoursPerDay, totalHours, minimumTimeUnit As Variant
        hoursPerDay = CDec(8)
        daysPerPeriod = CDec(10)
        totalHours = hoursPerDay * daysPerPeriod
        minimumTimeUnit = CDec(0.1)

        Dim percentAllocations As New Scripting.Dictionary
        percentAllocations.Add 1, CDec(30)
        percentAllocations.Add 2, CDec(15)
        percentAllocations.Add 3, CDec(20)
        percentAllocations.Add 4, CDec(25)
        percentAllocations.Add 5, CDec(10)

        ' Calculate the actual allocations to the near unit
        ' ...and add the balance of time lost/gained from rounding to project 1
        Dim actualAllocations As New Scripting.Dictionary
        For Each pKey In percentAllocations.Keys
            actualAllocations.Add pKey, Round((percentAllocations.Item(pKey) * totalHours / 100 / minimumTimeUnit) * minimumTimeUnit)
        Next

        Dim allocationsRoundingError As Variant
        allocationsRoundError = totalHours
        For Each aKey In actualAllocations.Keys
            allocationsRoundError = allocationsRoundError - actualAllocations(aKey)
        Next
        actualAllocations.Items(0) = actualAllocations.Items(0) + allocationsRoundingError

        ' Find out up front which projects actually have work
        Dim projectsWithWork As New Collection
        For Each aKey In actualAllocations.Keys
            If actualAllocations.Item(aKey) > 0 Then projectsWithWork.Add (aKey)
        Next

        ' Setup the original base case
        ' -> First integer represents the project ID, second is the day ID, the double is the number of hours
        Dim workDistribution As New Scripting.Dictionary ' Int > (Int > Decimal)
        Dim days As Scripting.Dictionary ' Int > Decimal
        For Each pKey In actualAllocations.Keys
            ' Calculate the total time allocated to the project
            ' ...and add the balance of time lost/gained to the first day
            Dim projectAllocation, perDayAllocation, perDayRoundingError As Variant ' Decimal
            projectAllocation = actualAllocations(pKey)
            perDayAllocation = Round(projectAllocation / daysPerPeriod / minimumTimeUnit) * minimumTimeUnit
            perDayRoundingError = projectAllocation - (perDayAllocation * daysPerPeriod)

            ' Create a dictionary for the period
            Set days = New Scripting.Dictionary ' Int > Decimal
            days.Add 1, perDayAllocation + perDayRoundingError
            Dim dIndex As Integer
            For dIndex = 2 To daysPerPeriod
                days.Add dIndex, perDayAllocation
            Next

            workDistribution.Add pKey, days
        Next

        ' Apply trades
        Dim tIndex, donorProject, receiverProject, donorDay, receiverDay As Integer
        Dim projectDistribution As Scripting.Dictionary
        Dim donorDays, receiverDays As Collection
        For tIndex = 0 To 10000
            ' Randomly select projects
            donorProject = projectsWithWork.Item(RandBetween(1, projectsWithWork.Count))
            receiverProject = projectsWithWork.Item(RandBetween(1, projectsWithWork.Count))

            ' Get days with work allocated
            ' ...and skip if none available
            Set donorDistribution = workDistribution(donorProject)
            Set donorDays = New Collection
            For Each dKey In donorDistribution.Keys
                If donorDistribution(dKey) > 0 Then donorDays.Add dKey
            Next
            
            Set receiverDistribution = workDistribution(receiverProject)
            Set receiverDays = New Collection
            For Each dKey In receiverDistribution.Keys
                If receiverDistribution(dKey) > 0 Then receiverDays.Add dKey
            Next
            
            If donorDays.Count <> 0 And receiverDays.Count <> 0 Then
                ' Randomly select days
                donorDay = donorDays.Item(RandBetween(1, donorDays.Count))
                receiverDay = receiverDays.Item(RandBetween(1, receiverDays.Count))

                ' Trade
                donorDistribution(donorDay) = donorDistribution(donorDay) - minimumTimeUnit
                donorDistribution(receiverDay) = donorDistribution(receiverDay) + minimumTimeUnit
                receiverDistribution(donorDay) = receiverDistribution(donorDay) + minimumTimeUnit
                receiverDistribution(receiverDay) = receiverDistribution(receiverDay) - minimumTimeUnit
            End If
        Next

        ' Dump it back out to show where we are...
        'Dim temp As String
        'For Each dKey In workDistribution(1)
        '    temp = temp & dKey & "-" & workDistribution(1).Item(dKey) & vbCrLf
        'Next
        'MsgBox (temp)

        ' Dump it all out to Excel
        Dim xIndex, yIndex As Integer
        Dim ws As Worksheet
        
        Set ws = ActiveSheet
        yIndex = 1
        With ws
            ' Label the days
            For xIndex = 1 To daysPerPeriod
                .Cells(1, xIndex + 1).Value = xIndex
            Next
            
            For Each pKey In workDistribution
                yIndex = yIndex + 1
                ' Label the projects
                .Cells(yIndex, 1).Value = pKey
            
                ' Output the distribution
                Set projectDistribution = workDistribution(pKey)
                For Each dKey In projectDistribution
                    .Cells(yIndex, dKey + 1).Value = projectDistribution(dKey)
                Next
            Next
        End With

    End Sub

    Private Function RandBetween(lowerbound As Double, upperbound As Double) As Integer
        RandBetween = CInt(Int((upperbound - lowerbound + 1) * Rnd())) + lowerbound
    End Function

Open in new window

0
 
LVL 1

Author Closing Comment

by:mullinsbc
Comment Utility
THANK YOU!  This is perfect and does exactly what I need. I really appreciate all your help and especially the lessons.
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
You're welcome mullinsbc :)

I had a lot of fun doing it.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now