Mathematical/Excel Problem - Distributing work based on time and working with averages

Hi Guys,

Some of you guys are at a level of excellence I will never achieve, so I would like to ask for some help!

So I have an excel/mathematical problem. I think it edges far more towards a mathematical problem actually. It involves the allocation of work on a practical level, but at a base level it's just numbers. So here we have an example:

First of all there's an amount of work, that takes a certain amount of time to do (Time Standard) which results in a certain amount of hours it takes to do the work.

There's a list of people in column a, and a list of their contracted hours in b, in c we have a percentage, which is their hours divided by the hours required to do the work.

I have a formula in d that divides the work total by the sum of all the percentages one by one, and then times it by the percentage in that row, this gives me an average proportionate to that persons working hours, and I have found this is better than just dividing it by the number of people and getting a flat figure.

The problem is that when the amount in column d is higher than what that person can do in their contracted hours, in column e I have an if statement that selects the max they can do instead. This arises in problems where even though there are more minutes/hours spare per person, all the work has not been allocated out, resulting in inefficiency. Obviously it isn't an issue if all the hours are used up because that's just a general lack of resource.

Is there a way around this? I have attached the spreadsheet. This problem has been puzzling me for quite some time, and the only way I can see around this is adding a column next to it where you manually top up the work until it has all been handed out.
Now this wouldn't be too bad on the example provided, but the real thing is on a larger scale and has to work with hundreds of people so would be quite time consuming.

Any solutions to the problem are welcome, even if someone provides an entirely better way of distributing a whole number between a group of rows proportionate to values like the hours listed.

Thanks for listening!

I'm not sure I understands you problem properly.

In your uploaded file you got 100,2 hrs of work to be done by 16 people so the average hrs pr person is 6.2625

Taking the average of the work hrs for the 16 persons this is 6.35 so in you cant do better than this as this is so close to 6.2625 that everybody must work.

But if the demand for work was 82.2 hrs should everybody work or should a number of persons be laid off in order to meet the target value 82.2 as close as possible? If you wanted to find who should work to get as close to 82.2 you could use the Excel add-in Solver.

In this case Solver says:

Persons b, g, l, and p should not work then you reach the target value 82.2 hrs

Alf

Hi Alf,

Sorry for being unclear, if the demand for work was 82.2 hrs the answer would be everyone would work.

Problem 1: Sometimes all of the work total isn't distributed despite there being more hours available to do this, i know this may be a problem with the logistics and mathematics rather than excel, can anyone think of another way or an additional step to distribute the rest of the work when these situations arise?

Aside from this, is there an alternative way of going through all the persons, distributing work one by one until the total is exhausted? Or would this require vba?