Generating a text (Pass/Fail) at the bottom of a table based on a majority score means that if the number of conditions are met, it will generate your desired text (Pass/Fail).
You have two options for your display/summary:
If your scores are in a default table, use another table as your summary table to display the text Pass/Fail
If your scores are in a prefilled table, you can have the summary at the end of the table.
To start, navigate your cursor to the left tab and choose which table you prefer or is applicable in your form. Set your conditions whether it be "Yes" or "No", and if that there is a certain count of "Yes", then the display cell displays "Pass", otherwise "Fail".
Use the formula IF() to your display cell:
=IF(COUNTIF(Range, Criteria)>=Number), Condition_Text, Otherwise_Text)
where:
Range- the column where you're going to count the number of instances
Number - the condition number of counts
Condition_Text - the text that shows if the statement is true
Otherwise_Text - the text that shows if the statement is false
Example:
For a default table
Suppose you have an audit report and you want to automate the results after filling out the forms. The condition to make it as pass if if 80% of the questions are answered "Yes". You're using a default table because every audit report has different questions/checklist questions.
Audit/Checklist:
Question | Yes | No | Comments |
| (B1 - list field) |
|
Summary Table:
Use the formula in the display cells in Column B:
=IF(COUNTIF(Table1!B:B, "Yes")>=COUNTA(Table1!A:A)*0.80, "Pass", "Fail")
Summary (Pass | Fail) | Comments |
=IF(COUNTIF(Table1!B:B, "Yes")>=COUNTA(Table1!A:A)*0.80, "Pass", "Fail") |
|
Default tables don't have a fixed number of rows when you're creating a template. Your formulas will reference columns, so each time you add a new row in filling out your form, the same formula applies to each cell in that column.
For a prefilled table
Suppose you have an audit report and you want to automate the results after filling out the forms. The condition to make it as pass if if 80% of the questions are answered "Yes". You're using a prefilled table because the questions are fixed.
Use the formula in the display cells in cell B6:
=IF(COUNTIF(B1:B5, "Yes")>=3, "Pass", "Fail")
NOTE: The display cell has a default display of "false" as there are no selection
Question | Yes | No |
Question 1 | (B1 - list field) |
Question 2 | (B2 - list field) |
Question 3 | (B3 - list field) |
Question 4 | (B4 - list field) |
Question 5 | (B5 - list field) |
Pass/Fail? | =IF(COUNTIF(B1:B5, "Yes")>=3, "Pass", "Fail") |
Calculations can occur across all rows & columns within the prefilled table. So whichever format you choose, you can reference any cell.