Distribute value among group members only

[Seahawk]

Hi All

I have a complicated task in excel that I don't know if possible. this is a bit lengthy explanation so I hope someone will have time to understand

I am making a computation sheet for an incentive scheme for our plant. the plant has 3 divisions under it (furniture, fabrication, retail) and it shares some manpower across divisions. criteria: Efficiency 40%, Absences 35%, punctuality 25%.

I wanted to give incentives to contributors within the division based on the division's sales performance. this is solved in the efficiency part since the contributors are defined by the "mult" multiplier value which approximates one's contribution

My problem is with absences and tardiness of which an offense will have a deduction from one's share BUT I wanted the deduction to be distributed only among his group members not to everyone as they are the ones directly affected by the absence.

Ex. a machine operator from furniture group had 1 absence so his deduction from the 35% share must be distributed only by furniture group contributors

I am attaching my computation sheet and I hope someone can help me out.

Thanks in advance!


Ed