The five indicator formulas
This is the single most important formula in the final report. It calculates the percent of valid-contact workers who have been visited at home in the last 30 days.
Step 1 · Write the numerator
The numerator is the count of workers with a housecall date inside the last 30 days, who also have a valid contact:
=COUNTIFS(Housecall_Date, ">"&TODAY()-30, Valid_Contact, TRUE)
Read it inside-out: TODAY() - 30 is the date 30 days ago. Housecall_Date > that picks every row visited in the last 30 days. Valid_Contact = TRUE further restricts to rows with a real phone number.
Step 2 · Write the denominator
The denominator is the count of all valid contacts in the unit:
=COUNTIFS(Valid_Contact, TRUE)
This is the cleaned denominator. It excludes (000) -, N/A, and the other placeholders you flagged in Module 4.
Step 3 · Put them together
=COUNTIFS(Housecall_Date, ">"&TODAY()-30, Valid_Contact, TRUE) / COUNTIFS(Valid_Contact, TRUE)
Format the cell as a percentage. You now have the most important number in the report.
The other four indicator formulas
| Indicator | Formula |
|---|---|
| Card support % | =COUNTIFS(Card_Date, "<>", Valid_Contact, TRUE) / COUNTIFS(Valid_Contact, TRUE) |
| Structure-test response | =COUNTIFS(Structure_Test_Status, "yes", Valid_Contact, TRUE) / COUNTIFS(Valid_Contact, TRUE) |
| Committee seats filled | =COUNTIF(Committee_Role, "<>") & " / " & 9 |
| Unit cohesion (avg days since contact) | =AVERAGE(TODAY() - Last_Contact_Date) |
Action: Mark this page complete when you have finished the activity above.