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.
[TABLE="width: 529"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Total Employees (Excluding Seperated):[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Employee ID#[/TD]
[TD]Seperated[/TD]
[TD]Lesson 1[/TD]
[TD]Lesson 2[/TD]
[TD]Lesson 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2345[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3456[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4567[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5678[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7890[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8901[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9012[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Count:[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Completion %[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Termination Value:[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-100%[/TD]
[TD="align: right"]200%[/TD]
[TD="align: right"]200%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Actual Completion:[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]33%[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[TABLE="width: 553"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Employee ID#[/TD]
[TD]Seperated[/TD]
[TD]Lesson 1[/TD]
[TD]Lesson 2[/TD]
[TD]Lesson 3[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]2345[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4567[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]7890[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8901[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]9012[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]count current completed[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]percent completed[/TD]
[TD="align: right"]16.67%[/TD]
[TD="align: right"]33.33%[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
</tbody>[/TABLE]