Completion Rate in Percent Based on If Variable (?)

[rfmartinez]

Hi all.

I teach curriculum at work. My goal is to run a consistent completion rate for each material of curriculum. For instance each month there is new training. I want to be able to calculate the percentage of completion. When it is completed at the end of each week, I update it by placing an x in the material's column and to the right of the employee name.

My problem is that I also have terminated employees that I do not want removed from the sheet. I already have it filtered that if I have an x in the "Separated" column, I want it hidden. The only problem is that depending on if that person completed a standard task/curriculum it upticks my percentage. If the person separated, it skews it lower.

How can I solve this? All help is appreciated.

		Total Employees (Excluding Seperated):	6
		Employee ID#	Seperated	Lesson 1	Lesson 2	Lesson 3
		1234				x
		2345			x	x
		3456	x		x
		4567		x		x
		5678	x		x
		6789				x
		7890	x	x	x
		8901			x	x
		9012	x	x	x
		1123				x
	Count:	10	4	3	6	6
	Completion %			50%	100%	100%
	Termination Value:			-100%	200%	200%
			Actual Completion:	17%	33%	33%

<colgroup><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[rfmartinez]

Not sure how to post correctly... hope this helps.

 

[liveinhope]

I have the "main" data running from A1 to E11 , a blank row, then summary data in rows 13 AND 14

ROW 13 Count current employees who have completed task in C13 =COUNTIFS($B$2:$B$11,"",C$2:C$11,"x") .... copy across to D ,E, ETC
row 14 % Current employees who have completed task in D13 =COUNTIFS($B$2:$B$11,"",C$2:C$11,"x")/COUNTIFS($B$2:$B$11,"") .. COPY ACROSS

Employee ID#	Seperated	Lesson 1	Lesson 2	Lesson 3
1234				x
2345			x	x
3456	x		x
4567		x		x
5678	x		x
6789				x
7890	x	x	x
8901			x	x
9012	x	x	x
1123				x
	count current completed	1	2	6
	percent completed	16.67%	33.33%	100.00%

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>