Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Getting an exact match
I have a list of about 20 labor categories and hourly rates, and a total
dollar figure. I do not have the hours associated with each category. I suspect that some combination of hours and hourly rates will exactly equal the total, and that the hours will be positive integers. Most likely all labor categories will have at least one hour. Is there a method that can check for a possible solution? |
#2
|
|||
|
|||
I think you probably need the professional version of Solver for this,
available for several hundred dollars from Frontline Systems. On Mon, 24 Jan 2005 19:43:02 -0800, Roland wrote: I have a list of about 20 labor categories and hourly rates, and a total dollar figure. I do not have the hours associated with each category. I suspect that some combination of hours and hourly rates will exactly equal the total, and that the hours will be positive integers. Most likely all labor categories will have at least one hour. Is there a method that can check for a possible solution? |
#3
|
|||
|
|||
I have $5.00 in my pocket
Which coins make up the total? As you see there are many solutions to the problem. As Myrna stated, Solver should be able to do it. A1:A20 Labour Categories B1:B20 Hourly Rates C1:C20 Actual Hours - will be set by Solver so leave blank D1:D20 Category Cost - set this to formula: =B1*C1 then fill down D21 =SUM(D1:D20) Then from the Tools menu, select Solver Set Target Cell $D$21 Equal To: Value Of: 250 (this is the Total you have) By Changing Cells: $C$1:$C$20 Subject to the Constraints: $C$1:$C$20 = integer $C$1:$C$20 = 0 (make this =1 if you want every category to have at least one hour) Then Solve -- Rob van Gelder - http://www.vangelder.co.nz/excel "Roland" wrote in message ... I have a list of about 20 labor categories and hourly rates, and a total dollar figure. I do not have the hours associated with each category. I suspect that some combination of hours and hourly rates will exactly equal the total, and that the hours will be positive integers. Most likely all labor categories will have at least one hour. Is there a method that can check for a possible solution? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match & Index | Excel Worksheet Functions | |||
LookUp - Exact only | Excel Worksheet Functions | |||
How do i Match all COLUMNS? | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |