Calculating First Time Pass Rate

macattackpro

macattackpro

Board Regular
Joined
Nov 27, 2017
Messages
55
Greetings!

I was unable to find anything similar to my issue and been banging my head on this all day.

I have a table with multiple columns of training information. I need to figure a first time pass rate for each course by each individual. I do have a unique PSID for each employee, a unique code for each course, and a unique confirmation number for each time they enroll in the course. Passing grade is 80%. I have the below formula that works up until calculating whether the row being evaluated has the lowest confirmation number for that employee and that course. Ideas?

Code: 
{=IF($K2="Completed",COUNTIFS($D$2:$D$50000,$D2,$J$2:$J$50000,$J2,$L2,">=80"),"")}

Learner Status
Department ID
Department
PSID
Hire Date
Rehire Date
First Name
Last Name
Course
Course Code
Status
Score
Completion Date
Confirmation
FTPR
Active
8041
Acme
1234
2/4/2002
11/20/2017
Robert
Smith
Information Resource Use Agreement
2015_IRUA
Completed
100
9/13/2017
2604534
Active
8041
Acme
1234
2/4/2002
11/20/2017
Robert
Smith
Information Resource Use Agreement
2017_IRUA
Completed
100
10/11/2017
2646747
Active
8041
Acme
1234
2/4/2002
11/20/2017
Robert
Smith
Information Resource Use Agreement
2017_IRUA
In-Progress
2757344
Active
8041
Acme
1234
2/4/2002
11/20/2017
Robert
Smith
OIG Ethics Training
ET2015
Completed
100
9/13/2017
2604912
Active
8041
Acme
1234
2/4/2002
11/20/2017
Robert
Smith
OIG Ethics Training
ET2017
Completed
60
10/11/2017
2692762
Active
8041
Acme
1234
2/4/2002
11/20/2017
Robert
Smith
OIG Ethics Training
ET2017
Completed
100
11/21/2017
2757346
Active
8041
Acme
1234
2/4/2002
11/20/2017
Robert
Smith
Sexual Harassment Refresher
2017_SHR
Completed
70
10/11/2017
2625680
Active
8041
Acme
1234
2/4/2002
11/20/2017
Robert
Smith
Sexual Harassment Refresher
2017_SHR
Completed
100
11/21/2017
2757332

<tbody>
</tbody>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
I couldn't get your formula to work... and not for lack of trying!

Anyways...

If you're ok with adding two extra columns (for calculations) here's a solution, providing I've understood what it is you're looking for.



Excel 2010
ABCDEFGHIJKLMNOPQ
1Learner StatusDepartment IDDepartmentPSIDHire DateRehire DateFirst NameLast NameCourseCourse CodeStatusScoreCompletion DateConfirmationFTPRUnique IdenitifierMinCalc
2Active8041Acme123402/04/200211/20/2017RobertSmithInformation Resource Use Agreement2015_IRUACompleted1009/13/20172604534112342015_IRUACompleted2604534
3Active8041Acme123402/04/200211/20/2017RobertSmithInformation Resource Use Agreement2017_IRUACompleted10010/11/20172646747112342017_IRUACompleted2646747
4Active8041Acme123402/04/200211/20/2017RobertSmithInformation Resource Use Agreement2017_IRUAIn-Progress275734412342017_IRUAIn-Progress2757344
5Active8041Acme123402/04/200211/20/2017RobertSmithOIG Ethics TrainingET2015Completed1009/13/2017260491211234ET2015Completed2604912
6Active8041Acme123402/04/200211/20/2017RobertSmithOIG Ethics TrainingET2017Completed6010/11/201726927621234ET2017Completed2692762
7Active8041Acme123402/04/200211/20/2017RobertSmithOIG Ethics TrainingET2017Completed10011/21/201727573461234ET2017Completed0
8Active8041Acme123402/04/200211/20/2017RobertSmithSexual Harassment Refresher2017_SHRCompleted8510/11/20172625680112342017_SHRCompleted2625680
9Active8041Acme123402/04/200211/20/2017RobertSmithSexual Harassment Refresher2017_SHRCompleted10011/21/2017275733212342017_SHRCompleted0

<tbody>
</tbody>
Sheet12​

Worksheet Formulas
CellFormula
P2=D2&J2&K2
P3=D3&J3&K3
P4=D4&J4&K4
P5=D5&J5&K5
P6=D6&J6&K6
P7=D7&J7&K7
P8=D8&J8&K8
P9=D9&J9&K9

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
O2{=IF(AND($K2="Completed",$L2>=80,$Q2=$N2),COUNTIFS($D$2:$D$50000,$D2,$J$2:$J$50000,$J2,$L$2:$L$50000,$L2),"")}
O3{=IF(AND($K3="Completed",$L3>=80,$Q3=$N3),COUNTIFS($D$2:$D$50000,$D3,$J$2:$J$50000,$J3,$L$2:$L$50000,$L3),"")}
O4{=IF(AND($K4="Completed",$L4>=80,$Q4=$N4),COUNTIFS($D$2:$D$50000,$D4,$J$2:$J$50000,$J4,$L$2:$L$50000,$L4),"")}
O5{=IF(AND($K5="Completed",$L5>=80,$Q5=$N5),COUNTIFS($D$2:$D$50000,$D5,$J$2:$J$50000,$J5,$L$2:$L$50000,$L5),"")}
O6{=IF(AND($K6="Completed",$L6>=80,$Q6=$N6),COUNTIFS($D$2:$D$50000,$D6,$J$2:$J$50000,$J6,$L$2:$L$50000,$L6),"")}
O7{=IF(AND($K7="Completed",$L7>=80,$Q7=$N7),COUNTIFS($D$2:$D$50000,$D7,$J$2:$J$50000,$J7,$L$2:$L$50000,$L7),"")}
O8{=IF(AND($K8="Completed",$L8>=80,$Q8=$N8),COUNTIFS($D$2:$D$50000,$D8,$J$2:$J$50000,$J8,$L$2:$L$50000,$L8),"")}
O9{=IF(AND($K9="Completed",$L9>=80,$Q9=$N9),COUNTIFS($D$2:$D$50000,$D9,$J$2:$J$50000,$J9,$L$2:$L$50000,$L9),"")}
Q2{=IF(MIN(IF($P$2:$P$9=P2,$N$2:$N$9))=N2,N2,0)}
Q3{=IF(MIN(IF($P$2:$P$9=P3,$N$2:$N$9))=N3,N3,0)}
Q4{=IF(MIN(IF($P$2:$P$9=P4,$N$2:$N$9))=N4,N4,0)}
Q5{=IF(MIN(IF($P$2:$P$9=P5,$N$2:$N$9))=N5,N5,0)}
Q6{=IF(MIN(IF($P$2:$P$9=P6,$N$2:$N$9))=N6,N6,0)}
Q7{=IF(MIN(IF($P$2:$P$9=P7,$N$2:$N$9))=N7,N7,0)}
Q8{=IF(MIN(IF($P$2:$P$9=P8,$N$2:$N$9))=N8,N8,0)}
Q9{=IF(MIN(IF($P$2:$P$9=P9,$N$2:$N$9))=N9,N9,0)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>




Let me know if that's what you were looking for?!
 
Upvote 0
try this


Excel 2013/2016
ADGHJKLMNO
1Learner StatusPSIDFirst NameLast NameCourse CodeStatusScoreCompletion DateConfirmationFTPR
2Active1234RobertSmith2015_IRUACompleted10013/09/20172604534100
3Active1234RobertSmith2017_IRUACompleted10011/10/20172646747100
4Active1234RobertSmith2017_IRUAIn-Progress2757344100
5Active1234RobertSmithET2015Completed10013/09/20172604912100
6Active1234RobertSmithET2017Completed6011/10/2017269276260
7Active1234RobertSmithET2017Completed10021/11/2017275734660
8Active1234RobertSmith2017_SHRCompleted7011/10/2017262568070
9Active1234RobertSmith2017_SHRCompleted10021/11/2017275733270
Sheet1
Cell Formulas
RangeFormula
O2{=INDEX($L$2:$L$9,MATCH(SMALL(IF($D$2:$D$9=$D2,IF($J$2:$J$9=$J2,$N$2:$N$9)),1),$N$2:$N$9,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
you can just add if>80, "Pass" etc
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,216,180
Messages
6,129,339
Members
449,504
Latest member
Alan the procrastinator

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top