Rounding Up or Down

[Laguna11]

I have a list of 3 individuals, one per column. Each column counts the number of entries each individual has performed a specific task each month. The last row adds the number of entries per individual.

However the individuals are paid a bonus of the total tasks carried out as follows: Number tasks per individual/total number of tasks x 50. The total of all 3 can't exceed 50.

If I look at the following:
Person1 25 tasks
Person2 26 tasks
Person3 2 tasks

The total is 53 and the results I get are 24, 25 and 2 which add up to 51 and not 50. Since these amounts will vary on a monthly basis is there any way in which this can be done in a formula or in VBA?

 

[Laguna11]

What does the "-SUM($A19:A19)" at the end of the formula mean if there is no value held in that cell?

 

[Andrew Poulsom]

The formula calculates the cumulative apportionment and deducts the previous cumulative. When you copy the formula across it becomes (with changing references highlighted):

=ROUND(SUM($B16:B16)/SUM($B16:$D16)*50,0)-SUM($A19:A19)
=ROUND(SUM($B16:C16)/SUM($B16:$D16)*50,0)-SUM($A19:B19)
=ROUND(SUM($B16:D16)/SUM($B16:$D16)*50,0)-SUM($A19:C19)

That way the numbers will always add up to 50, because in the last formula the denominator is the same as the divisor.

 

[Laguna11]

Perfect! Thank you ever so much for your help and explanations!