employee evaluation scoring

[ldaniels827]

Any suggestions on how i figure out the wage increase using 10 areas, 6 ratings. i would like get a number that i could apply against a wage scale. I can easily do with 2 ratings - that is easy, but i just cannot figure out if i am even on the right track, since an employee could be rated in all 6 ratings.

ratings: N, U, F, S, G, E (E being excellent) - N is NA, so 0 score, U is unsatisfactory so 0 score, then 1, 2,3,4 for points of each score??

I am not sure what more information someone on this board needs, so please ask me.

 

[RustEE2020]

So you will be calculating a score then turning that into a ratio which you will be multiplying against their max potential raise to calculate the new raise?
Also should people have the NA rating actually work against them? I would think it would be a different value than Unsatisfactory since they are simply not able to be rated in that sector.

 

[dnorm]

Hi Idaniel827

Is this something like what you are looking for?

if so a formula like =IF(C4>"",0,IF(D4>"",0,IF(E4>"",1,IF(F4>"",2,IF(G4>"",3,IF(H4>"",4,"")))))) where C4 is rank N area 1 will give a tally, then just =SUM() to add them all up...
it will not matter what mark you place in the cells, as the formula looks for greater than empty.

 

[RustEE2020]

You would need to build a cross reference table to be able to convert the ratings into values.
You can then convert the rating into a value using an index/match combo.
Following that you can sum the ratings that you want, in the example below I left out the NA for my summation.

Rating

Value

N

NA

U

0​

F

1​

S

2​

G

3​

E

4​

Area 1

Area 2

Area 3

Area 4

Area 5

Area 6

Area 7

Area 8

Area 9

Area 10

Rating:

N

U

F

S

G

E

E

E

E

E

Total:

Ratio

Possible Raise

Calculated Raise

Rating Value

NA

0​

1​

2​

3​

4​

4​

4​

4​

4​

26​

0.722222​

2.5​

1.805555556​

In the row below the ratings each cell contains the formula "=INDEX($A$2:$B$7,MATCH(B13,$A$2:$A$7,0),2)" which converts the rating above to a numerical value based on the table in the top right. Then we calculate the total value under the "Total:" cell using the formula "=SUMIF(B14:K14,"<>NA")" which sums all the values which aren't NA. Then next to that the ratio is calculated with "=L14/(COUNTIF(B14:K14,"<>NA")*4)" which counts all the valid entries(those that aren't NA), multiplies them by 4 to get a total possible value and then divides the total by the possible total. This gives us a ratio of how well a person did with a perfect score being 1. The total possible raise is stored in the next cell over and the cell following that we multiply the ratio and the possible raise to get the calculated raise.

 

[ldaniels827]

So you will be calculating a score then turning that into a ratio which you will be multiplying against their max potential raise to calculate the new raise?
Also should people have the NA rating actually work against them? I would think it would be a different value than Unsatisfactory since they are simply not able to be rated in that sector.

Yes that is what i am wanting to do; and no the NA should not be used against them

 

[ldaniels827]

i cannot get the index formula to total - with the formula you mentioned. i tried it without the NA, as i think i could skip that, but i cannot get the index cells to total.

 

[RustEE2020]

Can you show me a visual of your worksheet with dummy information? I would also need to see how you have written the formulas to give you a hand. When you say you cannot total the index cells do you mean you are getting the ratings converted to their numerical value and cannot total the numeric values?