Calculating relative productivity percentage with 3 variables?

Hello all,

I am having some difficulty coming up with an Excel algorithm that will successfully calculate a relative productivity percentage.

For example:
If I have a raw product that weighs 90 and 3 people can process this product in 1 hour and 15 min, I want this to be a perfect score of 100% productivity. The calculation doesn't need to result in exactly 100%.

If the same weight (the unit is unimportant) can be processed in  less than 1 hour and 15 minutes, it is acceptable for the result to be greater than 100%. On the other hand, if the product is completed in greater than the aforementioned time, the productivity will become accordingly lower than 100%.

Both the product weight and the number of people will also vary.

Finally, which cell format do I use in order to input unit time in the form of 1.59 (for 1 hour and 59 minutes)?

Thanks so much!
Simon
LVL 4
S ConnellyTechnical WriterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

Is that what you are looking for?

Regards
EBVV20141022.xlsx
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Rgonzo,

Wrong question or wrong workbook?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
In general, you divide a nominal value by a actual value (or vice versa, depending on the desired result) for each factor, and multiply the results. Assuming the time needed is entered in the proper way (that is as 1:15, and formatted as date with "h.mm"), the formula can be:
= actweight / 90 * 3 / actpeople * 75*60/86400 / acttime

Open in new window

where I have used the column headers instead of cell addresses.
Q-28542250.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Glenn RayExcel VBA DeveloperCommented:
It turns out that your ideal ratio of 90 quantity: 1:15 processing time: 3-persons is 576:
=90/1:15:00/3

In Excel the formula is:
=(90/TIME(1,15,0)/3)

So if you divide your measured quantity by the process time, then by the number of persons, then divide that result by 576, you'll get a weighted ratio against your ideal scenario.

You won't be able to change the input mask for the time value to allow for a decimal in place of a colon; Excel will consider your value as a decimal value in days.  I do not recommend that you change this.

-Glenn

PS  Here is my example file from your previous question with this new productivity formula included.
EE-Q28539418.xlsx
0
S ConnellyTechnical WriterAuthor Commented:
All,
Thanks for your assistance. I'll evaluate and respond this evening.

Simon
0
S ConnellyTechnical WriterAuthor Commented:
Hello all,

Qlemo: Excellent!
Glenn Ray: Excellent!

Except for the wonky time input, this is works very well. I'll have to experiment with a better way of entering the time value.
0
S ConnellyTechnical WriterAuthor Commented:
Thank you to all!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.