Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

A have a sheet with resources and week together with availabilty per week (hours). Like this.

Resource 1225 1226 1227 ... 1252
.NET     30   30   15   ... 30
Java     40   40   40   ... 40

In another table i´ve got his data

Resource PlannedHours StartWeek EndWeek AvailableHours Diff ActualEndWeek
.NET     326          1225      1242    400            76   ?    
Java     100          1225      1242    400            300  ?

Planned hours comes from hours project/ticket system, start and end week is entered manually. Availible hours and diff is calculated from the first table.

I´ve solved everything but the last column where I need help. How can a find the week when the availible resources in the first table sums up and is big enough to cover the planned hours. I need that column and put the week in actualendweek column?

share|improve this question
add comment

1 Answer 1

Function LeastFactors(MyArray As Range, Goal As Double) As Integer
Dim RunningTotal As Double
Dim i As Integer

For i = 1 To MyArray.Count
    RunningTotal = RunningTotal + WorksheetFunction.Large(MyArray, i)
    If RunningTotal >= Goal Then
        LeastFactors = i
        Exit For
    Else
        LeastFactors = 0
    End If
Next i
End Function

use would be =LeastFactors(Sheet1!B2:B40,Sheet2!B2) to give you how many weeks you would need. It will return zero if it can't find an answer

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.