R
rhon101
I have a work distribution sheet for processing claims. The worksheet
includes 13 different working locations and 15 employees. Each employee works
a different combination of locations and can work any number of hours up to
10. For example emp. A works loc 17, loc 30, loc 31 and loc 45. Emp B works
loc 30, loc 40, loc 41, loc 120 and only 100 in loc 122. Emp. C works loc 31,
loc 45, loc 120 and loc 122.
The current spread sheet calculates and distributes work to each employee
for their assigned locations based on the number of hours they are working.
The input for the spreadsheet is the total claims for each location and
number of work hours for each employee per day.
Here is the formula I am currently using and a description.
=IF($E$16=0,$M$21/loc41t*$E8,($M$21-$M$16)/loc41t*$E8)
=IF(Emp A work hours = 0, total claims for loc/total of all emp hours * emp
B work hours, (total claims for loc - 100 claims emp A will work)/total of
all emp hours * emp B work hours)
=IF(E16=0,1243/40*8,(1243-100)/40*8)
E16 represents the hours of an employee that works 100 claims in the loc.
M21 is the total number of claims for a location
loc41t is an array that adds the total work hours for all the employees that
will be working in the location. If employee is not there, it excludes them.
E8 is the number of hours for this emplyee
M16 is the 100 claims if E16 contains working hours
This works fine except it does not divide and distribute evenly.
Example
Total claims for loc 30 is 1709.
4 emp's working 8 hours
1 emp working 4 hours.
1 emp working only 100.
My formula returns 358 for the 4 emp's working 8 hours, returns 179 for the
emp working 4 hours and 100 for the other emp, which will equal 1711.
How can I get this to determine if emplyees are working then divide between
them and if there is a remainder to calculate back into an employees total?
Thanks.
includes 13 different working locations and 15 employees. Each employee works
a different combination of locations and can work any number of hours up to
10. For example emp. A works loc 17, loc 30, loc 31 and loc 45. Emp B works
loc 30, loc 40, loc 41, loc 120 and only 100 in loc 122. Emp. C works loc 31,
loc 45, loc 120 and loc 122.
The current spread sheet calculates and distributes work to each employee
for their assigned locations based on the number of hours they are working.
The input for the spreadsheet is the total claims for each location and
number of work hours for each employee per day.
Here is the formula I am currently using and a description.
=IF($E$16=0,$M$21/loc41t*$E8,($M$21-$M$16)/loc41t*$E8)
=IF(Emp A work hours = 0, total claims for loc/total of all emp hours * emp
B work hours, (total claims for loc - 100 claims emp A will work)/total of
all emp hours * emp B work hours)
=IF(E16=0,1243/40*8,(1243-100)/40*8)
E16 represents the hours of an employee that works 100 claims in the loc.
M21 is the total number of claims for a location
loc41t is an array that adds the total work hours for all the employees that
will be working in the location. If employee is not there, it excludes them.
E8 is the number of hours for this emplyee
M16 is the 100 claims if E16 contains working hours
This works fine except it does not divide and distribute evenly.
Example
Total claims for loc 30 is 1709.
4 emp's working 8 hours
1 emp working 4 hours.
1 emp working only 100.
My formula returns 358 for the 4 emp's working 8 hours, returns 179 for the
emp working 4 hours and 100 for the other emp, which will equal 1711.
How can I get this to determine if emplyees are working then divide between
them and if there is a remainder to calculate back into an employees total?
Thanks.