Excel Challenge 51: Identify At-Risk Churn Customers by Inactivity Days

Challenge Scenario

In SaaS and subscription business models, customer churn happens when accounts stop logging in or ordering. By tracking the number of elapsed days since each customer's last active session against a reference audit date (2026-09-01 in cell G1), customer success teams can flag at-risk accounts before they cancel.

As a Customer Retention Analyst, you are auditing five client accounts:

Days Inactive (Column C): Subtract the customer's last active date from the audit date in $G$1: =$G$1-B2.
Risk Status (Column D): If inactive for more than 60 days (C2 > 60), flag as "HIGH RISK"; if more than 30 days (C2 > 30), mark as "MEDIUM RISK"; otherwise mark as "HEALTHY" using =IF(C2>60, "HIGH RISK", IF(C2>30, "MEDIUM RISK", "HEALTHY")).
Average Inactivity Days (Cell C8): Calculate the average inactive days using AVERAGE(C2:C6).
Total High Risk Accounts (Cell D8): Count how many accounts received "HIGH RISK" status using COUNTIF(D2:D6, "HIGH RISK").

Your Sheet Layout

Understanding the customer churn analysis table:

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:B6`	Account, Last Active Date	Account Activity	Company names and last login timestamps	Read-only CRM activity log.
`G1`	Audit Date	Benchmark Date	Locked reference date: 2026-09-01	Anchor date for date subtraction.
`C2:C6`	Days Inactive	Time Elapsed	`=$G$1-B2`	Calculates elapsed days without account activity.
`D2:D6`	Risk Status	Retention Alert	`=IF(C2>60, "HIGH RISK", IF(C2>30, "MEDIUM RISK", "HEALTHY"))`	Classifies customer churn vulnerability.
`C8`	Average Inactivity	Summary	`=AVERAGE(C2:C6)`	Average inactivity period (54 days).
`D8`	High Risk Accounts	Summary	`=COUNTIF(D2:D6, "HIGH RISK")`	Accounts requiring urgent intervention (2).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: C2

In cells C2:C6, calculate days inactive (= $G$1 - Last Active Date).

Objective #2

Cell: D2

In cells D2:D6, assign risk level ("HIGH RISK" if >60, "MEDIUM RISK" if >30, otherwise "HEALTHY").

Objective #3

Cell: C8

In cell C8, calculate the average inactivity days across all accounts.

Objective #4

Cell: D8

In cell D8, count the total number of accounts flagged as "HIGH RISK".

Solution & Formula Breakdown

Let's break down how date math and tiered IF rules classify customer health:

1. Date Subtraction with Absolute Cell Referencing (C2:C6)

=$G$1-B2

Row 2 (Apex): 2026-09-01 minus 2026-08-20 = 12 days.
Row 3 (Beacon): 2026-09-01 minus 2026-07-15 = 48 days.
Row 4 (Crestline): 2026-09-01 minus 2026-06-01 = 92 days.
Row 5 (Driftwood): 2026-09-01 minus 2026-08-28 = 4 days.
Row 6 (Ember): 2026-09-01 minus 2026-05-10 = 114 days.

2. Churn Risk Classification (D2:D6)

=IF(C2>60, "HIGH RISK", IF(C2>30, "MEDIUM RISK", "HEALTHY"))

Apex (12 days): <= 30 → HEALTHY.
Beacon (48 days): > 30 and <= 60 → MEDIUM RISK.
Crestline (92 days): > 60 → HIGH RISK.
Driftwood (4 days): <= 30 → HEALTHY.
Ember (114 days): > 60 → HIGH RISK.

3. Portfolio Metrics (C8 & D8)

Cell C8 (Average Inactivity): =AVERAGE(C2:C6) → 54 days.
Cell D8 (High Risk Count): =COUNTIF(D2:D6, "HIGH RISK") → 2 accounts.

Remember to lock cell $G$1 with dollar signs! If you write G1-B2 and copy it down, the formula will look at empty cells G2, G3, etc., producing invalid calculations.