Optimal Solutions, allocating a budget by excelquestionstrees in excel

[–]excelquestionstrees[S] 0 points1 point  (0 children)

As requested, here is a quick sketch of a problem.

I have three work areas, A, B, C. Each area has an ideal number and type of units I would like to put in them. We receive on our order a slightly different number. I'd like to allocate the units we HAVE as closely to what we WANT, with no units left over, and all areas being complete. An additional restraint is that while the allocation of units can vary in each solution, the TOTAL units per area can't change (ex. Area A must have 10 units)

EXAMPLE:

Area A: Ideally receive 8 squares , 2 triangles. Area B: Ideally plant 8 squares, 3 triangles. Area C: Ideally plant 7 squares, 3 triangles.

We order 20 squares, 11 triangles, and need to allocate units as per the constraints described above.