Annual appraisal score formula

[Rob_010101]

Hello,

The below sheet calculates the figures based on number of objectives set and how many of these objectives have received 'needs development', 'meets expectations' and 'exceeds expectations' criterion.

I need to be able to calculate an overall status in A8, based on the following.

Annual Review Template DRAFT.xlsx
	A	B	C	D	E
1		Number	Percentile
2	Objectives Total	16
3	Needs development	5	31%
4	Meets expectations	0	0%
5	Exceeds expectations	11	69%
6
7	Overall review outcome
8
9
10
Scoring

Cell Formulas
Range	Formula
B2	B2	=COUNTIF('Setting Annual Objectives'!A9:A11, "*")+COUNTIF('Setting Annual Objectives'!A13:A15, "*")+COUNTIF('Setting Annual Objectives'!A17:A46, "*")
B3:B5	B3	=COUNTIF('Objectives Annual Review'!D:D, Scoring!A3)
C3:C5	C3	=SUM(B3/$B$2)*100%
A8	A8	=IF(C5>=80, "Exceeds expectations", "")&IF(C4>=60, "Meets expectations","")&IF(C3>=50, "Needs development", "")

1. If 'exceeds expectations' equals or exceeds 70% of objectives total, return exceeds expectations
2. If 'exceeds expectations' DOES NOT equal or exceed 70% of objectives total, return whichever of 'needs development' or 'meets expectations' has the higher %

In the example above, the formula would return 'needs development'.

Those % seem to me to be a fair and accurate way of doing things but happy for suggestions!

Thanks
Chris

 

[Kerryx]

=IF(C5>=70%,"Exceeds expectations",IF(C3>C4,A3,A4))

 

[Rob_010101]

=IF(C5>=70%,"Exceeds expectations",IF(C3>C4,A3,A4))

Thank you!