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?

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?

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?