Performance Indicator - Quality Scoring

**tomlancaster** · 12-04-2009, 03:24 PM

Hello, could you please assist with my formula.

I am trying to indicate peoples performance based on their quality %

E.g: If someone was working at 20% quality or less, they would get a performance indicator of 5. If they were working at 100% or above they would get a 1.

I am trying to work this out in a formula using IF >= <= but its giving the wrong numbers.

I have attached an example workbook. Can anyone help figure out the formula as my mind is numb! Thanks

**DonkeyOte** · 12-04-2009, 03:30 PM

Quite a few routes in truth... based on your requirements

B9: =6-(CEILING(B8,20%)/20%)
formatted as General

If you want to use your table I would suggest you revise your layout so it is vertical rather than horizontal - ie Col A % B score

EDIT: hang on I think I've missed a tier here... as have you I think - what happens for > 20% to <= 40% ?

**JBeaucaire** · 12-04-2009, 03:31 PM

Your table appears to SKIP the values from 20-40. Look at your text in B2 and D2, you'll see. So I added a ZERO range for values less than 20 and made 20-40 the "5" range.

In B9:

=LOOKUP(B8,{0,0.2,0.4,0.6,0.8,1},{0,5,4,3,2,1})

**mewingkitty** · 12-04-2009, 03:35 PM

Meh, as Donkeyote stated, there's a lot of ways to go about doing this.
To use a series of "IF"s, as you mentioned, (By the way this is the sloppiest way of doing this, and I would highly recommend one of the other solutions) :

Please Login or Register  to view this content.

in b4. Then copied over.

**DonkeyOte** · 12-04-2009, 03:35 PM

Hi JB, I "think" here the 20 would be included in the first range (ie 0 to 20) - might be able to simply use 21 or 20.01 etc - decimal sig. is not stipulated as I see it (or use CEILING if bands are consistent).

We shall see..

**JBeaucaire** · 12-04-2009, 03:56 PM

Maybe. All the other ranges read as "Greater than or equal to" so I made the 20 range start AT 20 like all the others above it do, leaving only the values less the 20 to the new zero range.

Truth is, this is such a significant omission, I'm sure the OP is going to have to rethink the numbers scheme to properly account for 6 ranges properly.

**mewingkitty** · 12-04-2009, 04:01 PM

I was just running with the assumption that no one operates at over 100% capacity, unless they have Dragonball Z powers and can morph into some sort of super saiyan worker...

**tomlancaster** · 12-07-2009, 08:24 AM

Thanks for your responses. I quickly rushed the example and got it wrong.

Attached is what I would like to achieve...

Is there a formula out there I could use?

Or do I need to do between values? Ideally I don't want to build numbers into the formula itself but use the table instead so we can change the criteria and scores without messing with the actual formula.

Thanks
Tom

**JBeaucaire** · 12-07-2009, 03:55 PM

A standard lookup table is assembled in two columns with search values ascending. The table works by matching to the closest value BELOW the search value.

Please Login or Register  to view this content.

If the value to search (33%) is in D12, then you could use a lookup value on the table above...

=LOOKUP(D12, Sheet1!A1:A6, Sheet1!B1:B6)

...and would give the answer of 4.

=======
Now that you can do one, you can do the others and just add them together.