Formula to calculate efficiency of colleagues at work

[MariusMM]

Hello,

At work i have 5 colleagues and i have 5 different queues that have different targets per/hour. And i want to create a formula to count how much they reached their targets per day or per 7 hours.

[MariusMM]

I found a way to count per 1 queue, but when i have 5 different i'm just getting error message

[simarui]

think we'd need to see a sample of your data and your desired results to help out much with this one.

can you upload a desensitized sample workbook?

[MariusMM]

Screen Shot 2015-05-04 at 19.06.56.jpg

Here is example and this is the formula that i use: =SUM(D3/B3)/D2

But as soon as i select more queues for same colleague on the formula i'm getting error

[MariusMM]

Screen Shot 2015-05-04 at 19.08.59.jpg

this one is better screen print

[MariusMM]

Here is the link to the file: https://www.dropbox.com/s/k7s4n5vgic...Test.xlsx?dl=0

[simarui]

sorry, i'm still not really sure what you're going for here... i can tell you that sum() is not doing anything in that specific formula.

will the colleague only work in one specific queue per day? and then row 2 looks like the target # for one hour in a specific queue? since you're saying adding different queues gives you the error i'm guessing the answer to the first question is no - they might work in multiple queues in a given day? how do we know how many hours they spent in a given queue?

[shg]

Just guessing,

Row\Col	A	B	C	D	E	F	G	H	I
2		RE:	10	20	30	40	50
3	Colleague 1	7.00		140				100.00%	H3: =SUMPRODUCT(C3:G3/C$2:G$2)/B3
4	Colleague 2	7.00	2					2.86%

[MariusMM]

Colleague will be working on multiple queues per day.

Example, colleague is working 7 hours in queue, lets say he is in 1 queue all day that queue has 20/h target. so colleague in 7 hours has to action 140 and this would be my target 100% that he achieved.

But when i try to alter the formula to include more queue i'm getting error.

I've updated on dropbox if you can please download new version. and have a look at Colleague 2 and the formula in "Target"

Hope this makes sense

[MariusMM]

Colleague 2 in updated file, shows he actioned 140, but he also did 2 cases, and percentage will be higher then 100%, but cant get that formula to work

[MariusMM]

Yes that formula worked =SUMPRODUCT(C3:G3/C$2:G$2)/B3 thanks alot

[shg]

You're welcome.