Calculating a % of fields based on a drop down means that you're solving for the number of times a certain field is selected divided the total number of rows in the table. This formula works for both a prefilled and a default table.
In your summary table (prefilled 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 have a Construction Incident Risk Table (in a Default table), and you're counting the number of instances High, Medium, and Low Risks are selected.
Table 1 Construction Incident Risk Table (Default table in this example, but it could be a prefilled table as well)
Incident Type | Description | Likelihood | Impact | Risk Level | Mitigation Measures |
|
|
|
| (Drop down list) - E+ |
|
Table 2 Summary of Counts & % (Prefilled table)
Risk | Counts | % |
High | (Formula field) | (Formula field) - C1 |
Medium | (Formula field) | (Formula field) - C2 |
Low | (Formula field) | (Formula field) - C3 |
Use the formulas to solve for the following,
% of High in cell C1,
=COUNTIF(Table1!E:E, "High")/COUNTA(Table1!A:A)*100
% of Medium in cell C2,
=COUNTIF(Table1!E:E, "Medium")/COUNTA(Table1!A:A)*100
% of Low in cell C3,
=COUNTIF(Table1!E:E, "Low")/COUNTA(Table1!A:A)*100