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.
I thought you wanted something like
100*(1-(.48/.52)) = 8
Now it sounds more like you want
100*(.70/.75) = 93
0
rohitsinhaCommented:
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)
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
And how much should performance percentl go up as the cost goes down?