Calculating a % of fields in a prefilled table means that you're solving for the number of times a certain field is input divided the total number of rows in the table. This formula works for both a prefilled and a default table.
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 that you're looking for the percentage of the number of employees completing a certain number of hours in a day in your Daily Timesheet and you have a fixed number of employees working in a day. You have a total of 10 employees, you require 8+ hours of shift in a day at flexible working hours.
Table 1 (Data Prefilled Table)
Employee | Start Time | Lunch Time Out | Lunch Time In | End Time | Hours Worked |
Employee 1 | 8:00 | 12:00 | 13:00 | 17:00 | 8 |
Employee 2 | 9:00 | 12:00 | 13:00 | 17:00 | 7 |
Employee 3 | 8:00 | 12:00 | 13:00 | 17:00 | 8 |
Employee 4 | 7:00 | 12:00 | 13:00 | 17:00 | 9 |
Employee 5 | 7:00 | 12:00 | 13:00 | 17:00 | 9 |
Employee 6 | 7:00 | 12:00 | 13:00 | 17:00 | 9 |
Employee 7 | 7:00 | 12:00 | 13:00 | 17:00 | 9 |
Employee 8 | 8:00 | 12:00 | 13:00 | 17:00 | 8 |
Employee 9 | 9:00 | 12:00 | 13:00 | 17:00 | 7 |
Employee 10 | 7:00 | 12:00 | 13:00 | 17:00 | 9 |
Table 2 (Display Prefilled Table)
% of Employees Working 8+ hours today | Display Cell (Formula Field) |
Use the COUNTIF() formula to calculate the % of fields filled out in your display cell in Table 2,
=COUNTIF(Table1!F:F, ">=8")/COUNTA(Table1!A:A)*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 that you're looking for the percentage of the number of employees completing a certain number of hours in a day in your Daily Timesheet and you have a fixed number of employees working in a day. You don't know the total of employees to be filled out, and you require 8+ hours of shift in a day at flexible working hours.
Table 1 (Data Prefilled Table)
Employee | Start Time | Lunch Time Out | Lunch Time In | End Time | Hours Worked |
Table 2 (Display Prefilled Table)
% of Employees Working 8+ hours today | Display Cell (Formula Field) |