Divide number and add remainder in another cell

[Guest]

Can someone help me? I made a work distribution spreadsheet and I can not get
it to divide evenly. What I am trying to do is divide the total number of
claims by the number of employees and distribute the result to each employee.
There are conditions also. For example one employee gets the max number of 50;
Here is my formula;

=IF(E18=0,0,IF(I19>250,50,(I19/(E8+E9+E10+E16)*E18)))
column E =# of hours working for each employee,
I19 = total number of claims.

If the total number of claims is 1500 and there are 5 people working it
returns 50 to the E18 employee and 363 to the rest, but this adds up to 1502
instead of 1500.
I want it to distribute 50, 362, 362, 363 and 363. Any advice?

 

[Dave O]

This will work mathematically, but I'm not sure it is as elegant as it
might be.

For sake of discussion let's assume the distribution for the 5
employees is in the range G24:K24.

G24 is for the guy that works 50 claims.
H24 is for the next employee: he works (1500-50)/4 claims, or 362.5
claims. Difficult to work half a claim, so use the ROUNDDOWN function
to set that to 362:
=ROUNDDOWN((1500-50)/4,0)
Same for the I24 employee.
The employee in J24 is a simple ROUND of the same formula:
=ROUND((1500-50)/4,0)
.... which results in 363.
The employee in K24 gets a number of claims that is calculated slightly
differently: the total number of claims minus the claims that have
already been distributed to the others:
=1500-SUM(G24:J24)
.... which also results in 363. This method will ensure that the number
of claims distributed to employees always equals the total number of
claims.

In all the formulas above I used your example of 1500 and 50,
hard-coded into the formula. However in your real world application
you can use cell references, since i19 is the number of claims.

 

[Guest]

Here is my formula;
column E =# of hours working for each employee,
I19 = total number of claims.
working it returns 50 to the E18 employee and 363 to the rest,
but this adds up to 1502 instead of 1500.

Such "quantization errors" are common whenever you
round/truncate floating-point computations to integral
values.

I want it to distribute 50, 362, 362, 363 and 363. Any advice?

I would put a different formula in each employee's cell.
I started with E18, the employee whose hours limited.

E18: =MIN(50,I19/5)
E8: =ROUND((I19-E18)/4,0)
E9: =ROUND((I19-E18-E8)/3,0)
E10: =ROUND((I19-E18-E8-E9)/2,0)
E16: =I19-E18-E8-E9-E10

When I19 is 1500, that yields the assignments 50, 363,
362, 363 and 362 respectively.

 

[Guest]

Errata ....

E18: =MIN(50,I19/5)

That should be =MIN(50,ROUND(I19/5,0))

E8: =ROUND((I19-E18)/4,0)
E9: =ROUND((I19-E18-E8)/3,0)
E10: =ROUND((I19-E18-E8-E9)/2,0)
E16: =I19-E18-E8-E9-E10

I probably misinterpreted what your E-cells represent.
These formulas probably belong elsewhere. But
hopefully the idea is clear.
..

 

[Guest]

This will work mathematically, but I'm not sure it is as
elegant as it might be.

I am not convinced that it works "mathematically" for all
cases.

G24 is for the guy that works 50 claims.

You neglect to say exactly what this formula is. I believe
there are two choices:

a. =MIN(50,ROUND(1500/5,0))
b. =MIN(50,ROUNDDOWN(1500/5,0))

An example of failure can be found for each. See below.

H24 is [...]:
=ROUNDDOWN((1500-50)/4,0)
Same for the I24 employee.
The employee in J24 is [...]:
=ROUND((1500-50)/4,0)
[....]
The employee in K24 gets a number of claims that is
calculated slightly differently [...]:
=1500-SUM(G24:J24)

If G24 is computed by #b and we substitute 104 for "1500",
the distribution is 21, 20, 20, 21 and 22, when the optimal
(most even) distribution is 21, 21, 21, 21 and 20.

If G24 is computed by #a and we substitute 109 for "1500",
the distribution is 22, 21, 21, 22 and 23, when the optimal
distribution is 21, 22, 22, 22 and 22.

The point is: your mixture of ROUNDDOWN and ROUND
with a constant divisor seems arbitrary and works only by
accident. Of course, the catch-all in K24 should ensure
that the total adds up [*], but the distribution might not be
optimal, as demonstrated.

[*] I am not convinced that we cannot find an example
where K24 is negative, especially for a larger number
of employees, depending how you would choose to
round or round down, which is seemingly arbitrary.
To ensure that cannot happen, it might be prudent to
always round down (truncate). Of course, that might
lead to even less optimal distributions, with the most
claims assigned to K24. But at least K24 would never
be negative. Of course, I'm not sure employee K24
would appreciate it .