Spreading Percentage

[Msgjazz]

I have a evaluation form that I NEED help with. There are 4 Rating levels to choose, E, S, NI, and NA with a value of 3, 2, 1, and 0. Each Category (Blue row) has subtask (Green rows) with a percentage weight for each. In the example below, you can see in Column F that each weight is 25% for all 4 subtask. When I make the last task an NA (cell C13), then the other 3 subtask weight changes to 33.33%, If I change 2 to NA then the remaining changes to 50%. Easy as there are only 4 subtask and each are 25

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Rating Category	Actual Rating	Cumm. Rating	Score	Weight	Weight (0.0 to 1.0)	Points	Weighted Score	Preserved Score
2	II. Communication / Facilitating Change		S	2.00	20.0%	20.0%	2	0.40	0.400
3	Action										N/A Count	0	1	2	3
4	1. Recommends Innovative Ideas		E	3	33.3%	25.0%		1.00			1	0.25	0.3333	0.5	1
5	Action
6	Evaluation Justification:
7	2. Seeks Personal Training Development Opportunities		S	2	33.3%	25.0%		0.67
8	Action
9	Evaluation Justification:
10	3. Written Communication		NI	1	33.3%	25.0%		0.33
11	Action
12	Evaluation Justification:
13	4. Verbal communication and information sharing		NA	0	0.0%	25.0%		0.00
14	Action
15	Evaluation Justification:				100.0%	100.0%
IT Bus Analyst

But on the following Category, there are 7 subtask with different weight (20%, 15%, and 10%). So on this one, if I make a subtask NA, then that weight needs to be distributed to the other 6 subtasks but keep the same ration.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
16	III. Job Knowledge & Performance		NI	1.50	25.0%	25.0%	1	0.25	0.375
17	To demonstrate knowledge, understanding and proper application of information in the performance of job duties. Ability to efficiently manage time and resources.										1	0	1	2	3	4	5	6
18	SubTask 1		NA	0	0.0%	20.0%		0.00				0	0.16667	0.2	0.25	0.33333	0.5	1
19	Action
20	Evaluation Justification:
21	SubTask 2		S	2	15.0%	15.0%		0.30
22	Evaluation Justification:
23	SubTask 3		NI	1	15.0%	15.0%		0.15
24	Action
25	Evaluation Justification:
26	SubTask 4		E	3	10.0%	10.0%		0.30
27	Action
28	Evaluation Justification:
29	SubTask 5		E	3	10.0%	10.0%		0.30
30	Action
31	Evaluation Justification:
32	SubTask 6		NA	0	0.0%	15.0%		0.00
33	Action
34	Evaluation Justification:
35	SubTask 7		E	3	15.0%	15.0%		0.45
36	Action
37	Evaluation Justification:				65.0%	100.0%
IT Bus Analyst

So making subtask 1 an NA, then the other 6 task would need to be their original % (Column E) PLUS and additional 4%(20%/6) So 10% changes to 13.3 and 15% changes to 18.3. If I then also make Subtask 6 an NA, then the remaining 5 will get and additional 7% (35%/5 [20 and 15]). So whichever subtask is made an NA, the remaining task need to change appropriately.

 

[istiasztalos]

Hi,

You can use this array formula (add with ctrl+shift+enter, Excel will add { } around it when done correctly)

First it sums up all the %for rows with have NA rating, then it divides the remainder by the number of non-NA ratings and adds this to the base

'scores' is your column of ratings and 'basew' is your column of base weight %s

A	B	C - score	D	E - actual weight	F- base weight
subtask 1		na		=IF(C17="na",0,F17+(SUM(IF(scores="na",basew))/COUNTIF(scores,"<>na")))	20%
subtask 2		s		0.21	15%
subtask 3		S		0.21	15%
subtask 4		na		0	10%
subtask 5		s		0.16	10%
subtask 6		s		0.21	15%
subtask 7		s		0.21	15%

<tbody>
</tbody>