Calculating Productivity Percentage

[rsharma017]

I am not sure if this would be a place to seek assistance but I thought I would try.

I am assisting a team to calculate their productivity percentage (score tied to bonus).

Its a little complicated then just regular productivity. The team has 10 days to complete a task. I would like to assign a productivity percentage based on how many days it took them to complete it. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]DAYS TOOK TO COMPLETE
[/TD]
[TD]PRODUCTIVITY %
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]LOWEST % SCORE
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD] 100% SINCE 10 DAYS IS THE GOAL
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]HIGHEST % SCORE
[/TD]
[/TR]
</tbody>[/TABLE]

 

[GR00007]

Give this a try:

=IF(A2=10,1,IF(A2 < 10,(10 -(10 -A2)) / 10,((10 +A2) / (10 +MAX(A:A))) * 2))

If you want a percentage rated from low to high without 100% being on 10 days, you could try this:

=PERCENTRANK.INC(A:A,A2,2) * 2