Highest contribution should get lowest errors and Lowest contribution should get highest

Hi Team,

I am the quality control analysts for a small BPO. I am trying account errors to the team. Errors should be based on their contribution to the project.

Total number of employees: 10
Total number of records processed: 45801
Total errors: 1250

Here are individual counts:

Employee Number Employee production

5065 3649
5754 4938
5857 3977
5989 5253
6067 5738
7700 4661
8294 5439
3642 4933
4563 5307
4568 1906

Total Production 45801
Total errors 1250

I want to charge 1250 errors to 10 members based on their contribution to the toal production.

Highest contributor should get lowest errors and lowest contribution should get highest errors.

Kindly help me out if there is any formula or Idea.

Thanks,
Ravi

Thank you so much for your reply.

Please find the attached excel file.

Column A has employee code
column B has employee production
I want to charge 1200 errors to each employee based on their contribution to the total production 45801 in column C.

kindly let me know if I am not clear with my questions.

Thanks,
Errors chaged.xlsxRavi

Actually I am looking for a formula how I can distribute errors based on their contribution.

Ex:

A employee count is 1200
B employee count is 1000

so total errors out of their production are 16

so A should get 6 errors (because he has more production) and B should get 10 errors (because he has less production).

I need a formula for this.

actually those 16 errors are randomly picked from both employees (but we do not know who is the employee commited the error). So I am planning to charge 16 errors based on their contribution.

Thank you so much, it works for me.

Hi Lem,

I just got another question from the team re the computation. In the above formula, we are just ranking the analysts and charging the total errors. But, we need to account total errors based on their contribution to the total productivity.

Total Errors 50
Total productivyt 500
A's Productivity 350
B's Productivity 100
C's Productivity 50

From the above example, I want to charge 50 errors to A,B and C analysts based on their productivity.

A has more productivity, so A should be charged more errors.
B has less productivity than A, so B should be charged less errors than A.
C has less productivity than A and B, so C should be charged less errors than A and B.

Could you help run a formula for this?.

Thanks,
Ravi