Employee trainings involve training modules/sessions and evaluations, which is usually a requirement for the company. Most often that not and ideally, 100% of the employees should pass. For this case, we're going to calculate the % score from Pass/Fails from an employee training.
Say we have a list of emplyoees who have undergone training. At the end of the rows, we'll have a column intended to display whether they've Passed or Failed (Formula fields) based from their scores, and we'll have a separate table to indicate the % of employees who have passed the training based from the populated 'Pass/Fail' texts. Simply put, we're calculating the instances a condition (Pass/Fail) is met and dividing it to the total number of rows.
To start, navigate your cursor to the left tab and choose either a 'Prefilled' or 'Default' table. Formulas work for either of the two.
For a Prefilled data table, use the formula to your display cell in the display table:
=COUNTIF(Source_Table!Column:Column, Criteria)/Number*100
Note: This formula is used for when you know the total number of rows in the prefilled table.
Note: You cross reference the formula to your Source_Table.
where:
Source_Table - is your prefilled data table
Column - the column which you want to solve the number of instances
Criteria - the condition which makes the condition true for it to count
Number - the total number of rows in your prefilled data table
or
=COUNTIF(Source_Table!Column:Column,Criteria)/COUNTA(Source_Table!Column:Column)*100
Note: This formula is used for when you don't know the total number of rows in the prefilled table such as (1) there is no auto-numbering of the rows; (2) it's not necessary to number them and; (3) there's too many items.
Note: You cross reference the formula to your Source_Table.
where:
Source_Table - is your prefilled data table
Column - the column which you want to solve the number of instances
Criteria - the condition which makes the condition true for it to count
For example,
Given a fixed number of employees who had undergong training, all 10 of them have completed training and only 8 of them passed. You need to know the percent passed of training and if it doesn't meet the company standards, they'd have to undergo the training again.
Table 1 Source Table (Prefilled table)
Employee Name | Score (out of 100) | Pass/Faill |
Employee 1 | 98 | Pass |
Employee 2 | 96 | Pass |
Employee 3 | 67 | Fail |
Employee 4 | 56 | Fail |
Employee 5 | 89 | Pass |
Employee 6 | 75 | Fail |
Employee 7 | 78 | Fail |
Employee 8 | 85 | Pass |
Employee 9 | 90 | Pass |
Employee 10 | 84 | Pass |
Table 2 Display/Conclusion table (Prefilled table)
Overall % Score | Conclusion |
(Formula field) | (Text field) |
Use the formula in the formula field,
=COUNTIF(Table1!C:C, "Pass")/Number*100
For a Default data table, use the formula to your display cell in the display table:
=COUNTIF(Source_Table!Column:Column,Criteria)/COUNTA(Source_Table!Column:Column)*100
Note: Since a default table has no fixed number rows and it depends on how many are logged when you're filling out the form, you use a COUNT() formula to count the total number of rows.
Note: You cross reference the formula to your Source_Table.
where:
Source_Table - is your prefilled data table
Column - the column which you want to solve the number of instances
Criteria - the condition which makes the condition true for it to count
For example,
Given a fixed number of employees who had undergong training, all 10 of them have completed training and only 8 of them passed. You need to know the percent passed of training and if it doesn't meet the company standards, they'd have to undergo the training again.
Table 1 Source Table (Prefilled table)
Employee Name | Score (out of 100) | Pass/Faill |
(Text field) | (Number field) | (Formula field; autopopulates based on score) |
Table 2 Display/Conclusion table (Prefilled table)
Overall % Score | Conclusion |
(Formula field) | (Text field) |
Use the formula in the formula field,
=COUNTIF(Table1!C:C,"Pass")/COUNTA(Table1!C:C)*100