For inspections that require a % score based from the Pass/Fail scores in questions/requirements, you can easily use the COUNTIF() formula. Inspections usually have a fixed number of questions therefore we're going to be using a Prefilled table for the source table. At the end of the rows, there's a column intended to display whether it's a 'Pass' or 'Fail' (this could be a drop-down list). We'll then have a separate table to indicate the % of questions have passed from the total number of questions based from the 'Pass/Fail' selection.
To start, navigate your cursor to the left tab and choose either a 'Prefilled' table. Create your table as intended in the form.
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 (10 items) of inspection questions, you're required to check if the inspection unit/entity has passed or failed by selecting the 'Pass/Fail' in the drop down list. It is your goal to know the % of items that passed and give comments whether it's an overall pass or a re-do inspection in required.
Table 1 Source Table (Prefilled table)
Employee Name | Pass/Faill |
Question 1 | Pass |
Question 2 | Pass |
Question 3 | Fail |
Question 4 | Fail |
Question 5 | Pass |
Question 6 | Fail |
Question 7 | Fail |
Question 8 | Pass |
Question 9 | Pass |
Question 10 | Pass |
Table 2 Display/Conclusion table (Prefilled table)
Overall % Score | Conclusion |
(Formula field) | (Text field) |
Use the formula in the formula field,
=COUNTIF(Table1!B:B, "Pass")/10*100