Calculate Performance based on Efficiency

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.  
TravidiaAsked:
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.

ozoCommented:
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?
0
ozoCommented:
Do you want something like 100*(1-(cost/52))
0
TravidiaAuthor 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.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

TravidiaAuthor Commented:
OZO,

can you create a calculation that compared the current cost to the lowest cost?
0
ozoCommented:
100*(1-(current cost/lowest cost))
0
TravidiaAuthor 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
0
TravidiaAuthor Commented:
i'll follow up tomorrow.  Thanks for your help ozo.
0
ozoCommented:
I thought you wanted something like
100*(1-(.48/.52)) = 8
Now it sounds more like you want
100*(.70/.75) = 93
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
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)
0
ee_rleeCommented:
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)

Open in new window

PercentEfficiency.xls
0
ee_rleeCommented:
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...
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
Math / Science

From novice to tech pro — start learning today.