Abstract

How do you fairly assign work units to your staff, taking into account their delivered units?

Yellow cells show input values, green ones are intermediate or helper cells, and blue cells mark final output values.

Note: You need to enter Units done in descending order.

In this example 90.6 units have already been delivered, but 86 more units are to be assigned to 28 lecturers. A fair share for each lecturer would be (90.6 + 86) / 28 = 6.3, but 7 lecturers have already delivered more than that.

The key worksheet formula is in cell C5: =MAX(0,B$4-B5-SUMPRODUCT(–(C$4:C4=0),B$4:B4-B$4)/(ROWS(B$5:B$32)-SUMPRODUCT(–(C$4:C4=0))+1))

Please notice that the fair share has been put into cell B4 intentionally and C4 has been kept empty, so that this formula could be just entered into C5 and copied down.

Column C shows the fractional results. In column D a simple worksheet function approach has been applied to round values of column C to integers, preserving their original sum.

As you can easily see, column E shows smoother results using the user defined function RoundToSum.

See RoundToSum (VBA) vs Other Methods why RoundToSum is to be preferred.

Example of Use

MrExcel.com (External link!) Distribute values in excel based on weight Inversely

Download

Please read my Disclaimer.

Assign_work_units_reduced_by_delivered.xlsm [25 KB Excel file, open and use at your own risk]