Excel 2007 : Calculate a Rating

**Deap** · 01-22-2011, 04:22 PM

Hi Guys,

Hope I can get some help with this one, I'm trying to make a customer focused Target system for engineers in our workshop, by calculating the following.

Each Engineer has a possibility of getting a top rating of 10 (no higher), targets below (we calculate each dependant on other ares of the business)

10 - Complete Repairs - 25% of total
(average "lower than") 1.7 - Parts Used Per Job - 10% of total
89% - First Time Fix - 15%
(less than) 1 - QA Failures - 25%
(less than ) 1 - Returns - 25%

The problems are

A - Setting a threshold, If an engineer gets 11 Complete repairs it goes over the maximum 2.5.

B - targeting the negatives, if an engineer uses average 2.5 parts per job he gets a higher score?

Can anyone suggest how to set a threshold and, turn the negatives scores into positive results?

Thanks all.

**TMS** · 01-22-2011, 04:36 PM

Pseudo formula:

=IF(Repairs>=10,2.5,0)+IF(Parts<1.7,1,0)+IF(FTF>=89%,1.5,0)+IF(QA<1,2.5,0)+IF(Returns<1,2.5,0)

Regards

**Deap** · 01-22-2011, 05:06 PM

Thanks TMShucks

that's good but that only gives me 10 or 0,

This is a ranking system so I need to be able to tell someone their score gradually.

So if an engineers Repair Target is 10 and they only get 5 their score out of a maximum 2.5 is 1.25

If their QA fail limit is 1 and they get more than 1 they loose the whole 2.5 of their total score. so the if statement works great here.

**TMS** · 01-22-2011, 05:19 PM

You didn't give any indication of a sliding scale, hence it wasn't included in the formula provided.

For the repair element,

=MIN(Repairs/10,1)*2.5

Regards