Help with weighted averages for scorecard

[gary2014]

Hello All,

I am preparing a scorecard for 200 employees where the Resolve Per Hour (RPH) target changed from 2.5 to 3.5 in middle of month. I need the help on calculating Production Efficiency % and achieved RPH when employees are working on different Resolve Per Hour target.Resolves per hour.xlsx.

Excel Sheet attached for reference.

Please help me with this task.

[SamT]

Thread moved to Excel

[p45cal]

In the attached, to do this most fairly, and accurately to reflect numbers of hours done by an individual and his targets on a day-to-day basis there's a grid (D13:P18) calculating an individual's target number of resolved cases.
For example, in cell D13 is Adam's hours worked that day, picked up from the other sheet matching name and date:
INDEX('Login Hours'!$A$1:$R$8,MATCH('Production '!$A3,'Login Hours'!$A:$A,0),MATCH('Production '!D$1,'Login Hours'!$1:$1,0))
which is 7.45 hours. His target for that day is 2.5 cases per hour, so we multiply 7.45 x 2.5 to get 18.625 cases that he's targeted to resolve on just that day.
We do that for all the dates for Adam, but the formula has taken into account the differing targets on a day-to-day basis (so we could change the targets every day if we wanted). The we add up the targets for Adam for all the days (Q13) and divide that into the actual number of cases he's resolved over all the days he's worked (Q3) and put the answer into cell V3.
Do the same for everyone.

This bit is just a reality check:
You've put a formula in cell T3: =S3/3.5 which assumes that ALL the targets were 3.5, so this figure underestimates the efficiency.
So I put a formula in cell U3: =S3/2.5 which assumes that ALL the targets were 2.5, so this figure overestimates the efficiency.
You would expect the correct result to lie between these values, which they all do.

Afterthought:
To do away with the grid in rows 13 and below, I've added a new column of formulae in column X (and that's all you need) which gives the same results as column V.
=Q3/SUMPRODUCT(INDEX('Login Hours'!$A$1:$R$8,MATCH($A3,'Login Hours'!$A:$A,0),MATCH(D$1:P$1,'Login Hours'!$1:$1,0))*D$2:P$2)
(At least you can see the workings.)

[gary2014]

Hello Friend,

Thank you very much for your reply.

My apologies for delayed reply. I was travelling around 1800 Miles with no access to internet. I was in a train when I posted this question for help.

I am able to get the Efficiency% using the grid table.

I am getting an error when I tried to replicate the formula in my excel sheet (Updated excel sheet attached)
=Q3/SUMPRODUCT(INDEX('Login Hours'!$A$1:$R$8,MATCH($A3,'Login Hours'!$A:$A,0),MATCH(D$1:P$1,'Login Hours'!$1:$1,0))*D$2:P$2)

I still prefer using this formula.

Further, is there any opportunity to know the actual Resolves Per Hour (MTD) due to change in targets.

"Resolves Per Hour (RPH) = Sum of Production/Actual Login Hours"

Best Regards,
Gary

Resolves Per Hour (RPH) = Sum of Production/Actual Login Hours

[p45cal]

You inserted a column on one of your sheets which means that the names and dates no longer appeared in the same columns/rows on both sheets (which I relied upon).
In the attached in column U of the Production sheet i've changed the formula to look at specific areas of the PU Day Wise sheet which you can easily adjust to correspond to any changes you make on either sheet.

Further, is there any opportunity to know the actual Resolves Per Hour (MTD) due to change in targets.

I'm not sure I understand this; when RPH is calculated there is no reference in the calculation to the targets, so are you asking if it's possible to detect if a person changes his RPH in line with changes to his targets? If so, how would you show his?
Perhaps, I'm missing the point and it's to do with 'MTD' which means what? Month to date? If so I need a very precise meaning of MTD because people use it to mean different things.


ps. Gary, it's best to post a new message rather than edit substantively an old one because I got alerted that you'd replied with 'I will try to implement your formula' but not that you'd updated 2 or 3 hours later - it was only by chance that I'd noticed you had updated it.

[gary2014]

Hello Friend,

Thank you very much for your reply.

I am geting and error when I have tried to replicate the formula in actual excel sheet which I trying to develop.

I am also getting the same error in the excel sheet which we are using in our conversations.

When click inside the formula bar, the “{ “ and “ }” gets disappear automatically, and once I click enter, the #Value Error comes up.

Request you to look into this and do the needful.

I have attached the excel sheet and error screen shot for reference.Error Message.docx

[p45cal]

Re the {} symbols: your version of Excel requires you to enter the formula using the Ctrl+Shift+Enter keys rather than just plain Enter. This is called array-entering a formula.

[gary2014]

Hello Friend,

Thank you very much.

Based on my discussion with colleagues, they wanted the calculation in a different method.

I have attached the excel sheet for your reference.

I have entered the Volume, Time and Target manually, which is time taking.

Request you to look into and help me with simple solution.

Thanks and regards,
Gary

[p45cal]

Table at cell N27 in the attached.

[gary2014]

Hello Friend,

Please help with Index Match in the new table (A17) for Employee, Volume and Time/PU Day Wise columns.

And in the Rel. Time coloumn (H), I am using the formula =IFERROR(D18/SUM($D$18:$D$30),""). But this is limited to only one employee, for second employee, I am changing the formula as =IFERROR(D31/SUM($D$31:$D$43),"") and so on.

Is there any simple method to get this done.

Requesting your help in this matter.

Thanks and regards,
Gary

[p45cal]

in H18:

=IFERROR(D18/SUMIF($A$18:$A$43,A18,$D$18:$D$30),"")

copied down.