[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1229
  • Last Modified:

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.  
0
Travidia
Asked:
Travidia
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now