Custom Function Needed for Evaluating Performance

[rajkumar]

Hi Experts,

i need a custom function to evaluate performance based on targets and actual. i have attached the sample workbook.

for each evaluation parameters i have targets and actual, and permissible evaluation criteria like unacceptable, development required,met expectation etc.

what i want is a formula that can evaluate under which parameter (Pre Sales Activites) what percentage target vs actual has met and result should be unacceptable or development needed etc. in cell E6.

a formula for total of parameters compared again with same evaluation criteria and result should come in cell C13.

Please help.

Raj

[SamT]

Adjust parameters by Row. Note descending order
E6 to E10: =IF(Actual/Plan>=1.1,"Exemplary Performance",IF(Actual/Plan>=1.05,"Exceeded Expectations",IF(Actual/Plan> Etc, Etc,Etc,"UnAcceptable"))))

Column F is hidden, adjust parameter by Row
F6 to F10: = IF(Actual/Plan>=1.05,1,0)

C13: = Sum(F6:F10)

[Paul_Hossler]

I did not see you using the weighting factors, so I added them

You can remove the & Format(N, " (##0.0)") in the function if you don't want to see them, or fix your formulas in row 11






Option Explicit

Function PerfEval(R As Range) As Variant

Dim sFormula As String
Dim N As Double

On Error GoTo NiceExit

'=100*SUMPRODUCT($B$6:$B$10*($D$6:$D$10/$C$6:$C$10))
sFormula = "=100*SUMPRODUCT(" & R.Columns(1).Address & "*(" & R.Columns(3).Address & "/" & R.Columns(2).Address & "))"

N = Application.Evaluate(sFormula)

'Un Acceptable Development Required Met Expectation Exceed Expectation Exemplary Performance
' <70 70-99 100-105 106-110 >110


Select Case N
Case Is <= 70
PerfEval = "Unacceptable" & Format(N, " (##0.0)")
Case Is <= 99
PerfEval = "Development Required" & Format(N, " (##0.0)")
Case Is <= 105
PerfEval = "Met Expectation" & Format(N, " (##0.0)")
Case Is <= 110
PerfEval = "Exceeded Expectation" & Format(N, " (##0.0)")
Case Else
PerfEval = "Exemplary Performance" & Format(N, " (##0.0)")
End Select

Exit Function

NiceExit:
PerfEval = CVErr(xlErrNA)

End Function




15409

[rajkumar]

Nice. Thanks Paul and Sam. Works fine. great job done.