I would like to write a User Defined Function to calculate performance ratings. Our employees are rated on a scale of I, M-, M, M+, E, where I would be the lowest level and E would be the highest. You can assign a number to each of these ratings from 1 to 5. Employees are then graded on each of their job responsibilities, which are weighted as a percentage of their total performance rating. For instance, say an employee has 5 areas of responsibility that are rated 10%, 25%, 35%, 25% and 5%, you would then multiply these percentages by the rating (1-5) to get an overall rating.
I've never written a UDF, but I've used several I've found on the internet and have updated some to fix issues, so I have some experience with them. As it stands, the ratings and weightings are not in contiguous ranges, although that could be overcome. If it were possible to write the UDF to accept two non-contiguous ranges that would be great, but not necessary. The data currently exists with the rating in once cell and the weighting in the cell directly below it, then the next rating might be 20+ rows down the page. Following is my poor attempt at trying to come up with the basis for the UDF.
The function as used in Excel would work like this:
=ePerformance(range of ratings which would be converted to numbers, range of weightings to be multiplied by the converted ratings)
In VBA it would look like:
Function ePerformance (Ratings As Range, Weightings As Range)
Dim rCell as Range
Dim eRating as Integer
Dim weightedRating as Integer
'Not sure what other variables I might need to declare here or if I've done these correctly
weightedRating = 0
For each rCell in Ratings
If rCell.Value = "E" Then
eRating = 5
If rCell.Value = "M+" Then
eRating = 4
If rCell.Value = "M" Then
eRating = 3 '
I don't expect my staff to get a rating below M, but if they would I think I could figure out the rest of the code
End If
'At this point I would need to multiply the resulting eRating by the corresponding weighting.
'I'm not sure how to cycle through the second range of weightings to make sure that I'm using the correct one.
'Also, I would need to keep up with the product of the weighting*rating to keep a running total
weightedRating = weightedRating + (eRating * Weight1,2,3, etc)
Next rCell
ePerformance = weightedRating
End Function
Ultimately, if I could reconvert the rating back to the letter grade that would be awesome, but if I can just get some help on the bulk of the function above, that would be enough. Any help I could get would be appreciated. Hopefully what I learn here I can apply to future functions that I might want to build. Is there anyway to write the function to accept a non-contiguous range? In the top of the function, could you write it like this:
Function ePerformance((Range1, Range2, Range3, ...),(Range 1, Range2, Range3, ...))
Thanks
I've never written a UDF, but I've used several I've found on the internet and have updated some to fix issues, so I have some experience with them. As it stands, the ratings and weightings are not in contiguous ranges, although that could be overcome. If it were possible to write the UDF to accept two non-contiguous ranges that would be great, but not necessary. The data currently exists with the rating in once cell and the weighting in the cell directly below it, then the next rating might be 20+ rows down the page. Following is my poor attempt at trying to come up with the basis for the UDF.
The function as used in Excel would work like this:
=ePerformance(range of ratings which would be converted to numbers, range of weightings to be multiplied by the converted ratings)
In VBA it would look like:
Function ePerformance (Ratings As Range, Weightings As Range)
Dim rCell as Range
Dim eRating as Integer
Dim weightedRating as Integer
'Not sure what other variables I might need to declare here or if I've done these correctly
weightedRating = 0
For each rCell in Ratings
If rCell.Value = "E" Then
eRating = 5
If rCell.Value = "M+" Then
eRating = 4
If rCell.Value = "M" Then
eRating = 3 '
I don't expect my staff to get a rating below M, but if they would I think I could figure out the rest of the code
End If
'At this point I would need to multiply the resulting eRating by the corresponding weighting.
'I'm not sure how to cycle through the second range of weightings to make sure that I'm using the correct one.
'Also, I would need to keep up with the product of the weighting*rating to keep a running total
weightedRating = weightedRating + (eRating * Weight1,2,3, etc)
Next rCell
ePerformance = weightedRating
End Function
Ultimately, if I could reconvert the rating back to the letter grade that would be awesome, but if I can just get some help on the bulk of the function above, that would be enough. Any help I could get would be appreciated. Hopefully what I learn here I can apply to future functions that I might want to build. Is there anyway to write the function to accept a non-contiguous range? In the top of the function, could you write it like this:
Function ePerformance((Range1, Range2, Range3, ...),(Range 1, Range2, Range3, ...))
Thanks
