Formula that reports over a 100%

[Bancell]

Wasn't even sure how to title this. What it boils down to is that I need help with a formula for a report I am working on.

I have certain bench marks that my employees must hit and am working on a spreadsheet to measure these benchmarks. One of the most important is how long it takes to respond to a customer. The target is 4 hours or less. If they hit 4 hours they get a 100% if they respond in lets say three hours they would get 125%. If they respond in 5 hours they only get 75%.

If I take the cell that has the response hours in lets call it cell A2 and divide it by the bench mark that is in B$1$ and there response is 4 hours they receive 100%. However I cant get the less than 4 hours and more than 4 hours figured out. I know it is probably some small thing that I am missing.

Any assistance any one can provide would be great.

Thank you in advance.

Brad

 

[Bancell]

Ok I knew it was something simple on that one and figured it out.

Now the harder question. I am grading in 4 areas lets name them area 1, 2, 3, and 4. Each has a target. If they meet that target they get 100%

What I have been doing is giving them a possible 400% that they could achieve by adding all 4 categories together. What I would like to do instead is have each category be worth maximum of 25% of the total 100%.

What I have done so far is take the total 400% and divide it by 4 but this throws the numbers off if they super exceed one area. I want to show this outstanding effort in the individual area but dont want it to give more than 25% towards the final total.

Any ideas?

 

[BBrandt]

If I understand you right...

If your maximum total percentage is still 100%, there's no way to acknowledge that outstanding effort in the individual area without allowing for the total to potentially be more than 100%. Capping each area at a maximum of 25% doesn't do that because it makes a 125% performance equal the same as a 100% performance.

However... if you want to allow an outstanding effort in one area to counterbalance a poor showing in another area, but never let the total for all areas exceed 100%, you could have something like this:

 =IF(.25*(total1+total2+total3+total4) >= 1, 1, .25*(total1+total2+total3+total4))

(note, you'll have to change this cell's number type to percent to get this to display as a percent... right click -> format cells -> number -> percentage)

What that does is gives a 25% weight to each individual area, but if the total of all areas is actually greater than 100%, it returns only 100%.

For example, if the person gets 150% in one area, 100% in another, 50% in another, and 90% in another, this returns 97.5%, allowing the 150% performance to offset the 50% performance in the final total.

However, if the person gets 150% in one area and 100% in the remaining 3, it returns 100%, effectively keeping that 150% performance out of the final total calculation.

Dunno how fair that is, but it seems like what you're looking for?

 

[Bancell]

BBrandt,

Thank you for the quick response. What I am finding is that an individual will concentrate on the easiest area to affect and drive the percentage to lets say 180% and partialy pay attention to the other areas and always come out on top. I want to push to have all areas concentrated on equally.

What you came up with worked for that portion. However now I am thinking I want to cap all areas at 100% to get all 4 areas concentrated on equally.

Any ideas on how to cap the percentage at 100%? per field.

I am using very simple formulas like below where L6 is the benchmark and K17 is the current stat.
=K17/$L$6

 

[BBrandt]

Try =IF(K17/$L$6>=1,1,K17/$L$6)

The IF statement syntax is IF(test,value if true, value if false). What the above statement does is checks to see if K17/L6 is greater than 100%, and if it is, it just returns 100%.

Now, from a logical standpoint, the risk you run by capping each area at 100%, is that there's now no incentive to beat the target time, since that outstanding effort now has no bearing on the others.

What you could do that might help the problem would be to cap both of them. For example, you could still reward outstanding performance in an individual area... but only up to, say 110%, and then still cap the overall performance at 100%, like so:

For the individual area, capped at 110%:
=IF(K17/$L$6>=[B][COLOR=red]1.1[/COLOR][/B],[B][COLOR=red]1.1[/COLOR][/B],K17/$L$6)
 
For the total, capped at 100% (same as before):
=IF(.25*(total1+total2+total3+total4) >= [B][COLOR=red]1[/COLOR][/B], [B][COLOR=red]1[/COLOR][/B], .25*(total1+total2+total3+total4))

It still rewards outstanding performance, but only up to a certain percentage (which you can change by varying the red numbers. 1 = 100%, 1.1 = 110% and so on) which would seem to help discourage slacking in the other areas (for example, the individual 180% performance you mentioned would come in as 110%, requiring an average performance of at least 96.66% in the remaining 3 areas to still get 100% overall average).

Think that'll work?

 

[Bancell]

I thought about capping the percentage as well but the thought of trying to figure out the formula started to make my head hurt.

I will plug in the formula you suggested and see how it turns out.

Thank you for your assistance.

 

[Bancell]

Ok this is the latest problem. The response hours formula works if it is over a 100% but doesnt if it is below.

Below is the formula I am using.

=IF(1-((O7/$P$6)+L17)<100%,100%,1-(O7/$P$6)+100%)

Any ideas?

 

[BBrandt]

Ok this is the latest problem. The response hours formula works if it is over a 100% but doesnt if it is below.

Below is the formula I am using.

=IF(1-((O7/$P$6)+L17)<100%,100%,1-(O7/$P$6)+100%)

Any ideas?

What is that formula supposed to do? Why are you using 1 (which = 100%) and 100% in the same formula? What is L17? I'm assuming O7 and %P%6 are the response time and the target time respectively?

 

[Bancell]

I copied the wrong formula, that was an old version. Here is the correct one.

=IF((O7/$P$6)<100%,100%,(O7/$P$6)+100%)

Yes O7 AND $P$6 are the response time and target.

Here is an example of what I am trying to do.


Target is 4 hours reps average response is 2 hours, his percentage will be 150%.
Target is 4 hours reps average response is 5 hours, his percentage will be 75%.

If the percentage is over a 100% I want it to report back 100% but if it is lower I want it to report the true number. With the current formula I get a return value of 225% on the second scenario.

 

[mikerickson]

If you have the four sub scores in A1:A4, the combined score would be

=(MAX(1, A1) + MAX(1,A2) + MAX(1,A3) + MAX(1,A4))/4