We help IT Professionals succeed at work.

# Calculate Performance based on Efficiency

on
I need to calculate a performance percentage based on the lowest cost in history and the current cost.  Example: For all Fridays, I have a lowest cost of \$0.52.  I want to convert this into a percentage.  So if we go below that - maybe to \$0.48 that would be a good performance number.  As .48 is a 8% efficiency, I want this to produce a # like 90%.  And as the cost goes down, the performance percent will go up.

I am using MS Access or Excel to create this calculation.
Comment
Watch Question

## View Solutions Only

SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2015

Commented:
Did you say you want .48 to convert to  8% or  90%?
And how much should performance percentl go up as  the cost goes down?
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2015

Commented:
Do you want something like 100*(1-(cost/52))

Commented:
I am shooting for a number between 80 and 90%.  The number cannot exceed 100%.  I think 1% per 1 cent in price drop will work.

Commented:
OZO,

can you create a calculation that compared the current cost to the lowest cost?
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2015

Commented:
100*(1-(current cost/lowest cost))

Commented:
ozo,

that measures the efficiency, but not producing a number near 90%.

Example: Current Cost = .75   MinCost = .70
100*(1-(.75/.70)) = -7.14

Commented:
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2015
Commented:
I thought you wanted something like
100*(1-(.48/.52)) = 8
Now it sounds more like you want
100*(.70/.75) = 93

Commented:
You can use the following formula

MS Excel: 1- ((Benchmark- Current Value)/ Benchmark)
MS Access
Field 1: For Benchmark use "Min Value" in group by query
Field 2: Current Value
Field 3: Use the above formula.

Depending upon your requirements you can change the denominator as current value. Also, if you want different calculations for increase and decrease in current prices then you can use IF function (Iff- in MS Access)
Top Expert 2008
Commented:
hi

since you want a 1% change in eff for every cent and you have a 90% eff at \$0.48, then your eff at \$0.52 will be 86%.

from the formula below, just change 0.52 to your 'lowest cost' and 0.48 to your  'current cost'. adjust 86 to your efficiency when the 'current cost' = 'lowest cost. change 4.322 to change the rate of change in efficiency per change in cost.

i have also attached an excel doc. please check if this is what you need
``````=86 + ((LOG10(4.322 + 0.52 - 0.48) - LOG10(4.322)) * 1000)
``````
PercentEfficiency.xls
Top Expert 2008

Commented:
if you are looking for a linear solution, use this:

86+(0.52-0.48)*100

this will have an efficiency of 90 at 0.48, 91 at 0.47, etc...