Abstract

The workload of leaving staff is distributed onto remaining staff using their performance (measured by budget). How can you do this correctly?

A simple Approach

A simple formula which you can copy down from D3 to D12 is =ROUND(C3*$B$2/$C$2,2):

You can delete the values of leavers easily in column C. The order of deletions does not matter.

The obvious disadvantage of this approach is a potential rounding difference, because the sum of rounded values is not necessary equal to the rounded sum of not-rounded summands. The example above shows a difference of 0.02.

A correct Calculation

With the user defined function RoundToSum you can use the matrix formula {=RoundToSum(C4:C13*$B$3/$C$3,D1)}:

RoundToSum sometime needs to round to the ‘wrong’ side but then it ensures a minimal error.

In addition to that you can round to other decimals with RoundToSum, for example to 10s:

Please notice here, that RoundToSum cannot create the original sum, if that is not rounded to the number of desired decimals.

Example of Use

Clever-Excel-Forum.de (External link!) Prozentuale Aufteilung.

Download

Please read my Disclaimer.

Verteilung_nach_Restmenge.xlsm [24 KB Excel file, open and use at your own risk]