VBA, Formula, or Data Validation Excel form

[username_rand]

Hi, i am working on a new employee performance evaluation form. Managers will fill the form for each of their employees semi-annually (attached). Each employee should have a minimum of 5 objectives and a maximum of 10 per each 6-month period. In the old evaluation form, each objective had a weight (out of 100%) in order to ensure fairness and accuracy of the evaluation, so if the manager entered 6 objectives, they would have to assign a weight to each objective ensuring that the total sum of the weights is 100%. Now we want to follow this same approach but instead of actual weights, managers have to enter Priorities (in wording) but each of these priorities represent a weight that we as the creators of the form know but managers & other employees don't. Priorities and their meaning are as follows:
1. Very High = 20%
2. High = 15%
3. Medium = 10%
4. Low = 5%
the issue here is how can we make sure that priorities chosen by the managers always end up being 100% in terms of weight, never more and never less, to ensure the results are accurate? and how to ensure that whether the number of objectives is 5, 6, 7, 8, 9, or 10, that there are constraints to how many times managers can assign these priorities all in relevance to the weights they represent.

 

[NateSC]

I didn't see any responses so I thought I would take a quick stab at this one.

If you want to avoid macros and just do things within the sheet, I would make some additional columns that would be hidden. I believe your sheet uses A to M so the added columns are N to Q (adjust based on your actual layout). To make this work best, I would use List defined Data Validation on Column C and make it look at Column L for the accepted values. This way you don't have to account for various spellings. Set Column N to count the number of instances of each Priority listing. Column O is a field for you to define a limit for the number of occurrences allowed for each Priority. Column C then has conditional formatting where if the number of occurrences exceeds the limit, the cells will turn Red. For the scoring, I would use VLOOKUP (inside an IF) to determine the Priority Score based on Column C. At the bottom of that column (I used cell P15), I added a sum to see if the percentage exceeded 100%. Then Column Q calculates the weighted score for each line so that it always adds up to 100.

Not sure if you needed this part:
Cells C15:J15 are then merged and set to word wrap. Then I added a formula to check if the Priority Score exceeds 100. If it does, it shows a message that the total priority score exceeds 100%. However this may not be necessary with the Limit of Occurrences included above. The only way for them to reduce this score below 100 would be to know the relative scores for each priority. You can leave this section off if it is not needed.

Hope this helps.

temp macro work.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3	#	objective	priority	tv	tv min or max	method	eval	actual mv	ob comp	score based on priority		priorities	score	Occurances	Limit of Occurances	priority score	weighted score
4	1		very high									very high	20	1	2	20	33.33333333
5	2		high									high	15	1	3	15	25
6	3		medium									medium	10	1	4	10	16.66666667
7	4		low									low	5	3	5	5	8.333333333
8	5		low											4		5	8.333333333
9	6		low													5	8.333333333
10	7															0	0
11	8															0	0
12	9															0	0
13	10															0	0
14
15																60
Sheet12

Cell Formulas
Range	Formula
P4:P13	P4	=IF(C4="",0,VLOOKUP(C4,$L$4:$M$8,2,FALSE))
Q4:Q13	Q4	=P4/$P$15*100
N4:N7	N4	=COUNTIF($C$4:$C$13,L4)
N8	N8	=COUNTIF($C$4:$C$13,"")
C15	C15	=IF(P15>100,"The objectives exceed 100%. Please reduce the priority or number of objectives." & "The current total value is: " & P15,"")
P15	P15	=SUM(P4:P13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:C13	Expression	=AND(C4=$L$4,$N$4>$O$4)	text	NO
C4:C13	Expression	=AND(C4=$L$5,$N$5>$O$5)	text	NO
C4:C13	Expression	=AND(C4=$L$6,$N$6>$O$6)	text	NO
C4:C13	Expression	=AND(C4=$L$7,$N$7>$O$7)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C4:C13	List	=$L$4:$L$8