Excel Challenge 16: Categorize Ages into Brackets

Challenge Scenario

In market research and customer segmentation, companies group customers into clear age brackets (such as "Senior", "Adult", or "Youth") to tailor their product offerings and marketing campaigns.

Instead of manually tagging hundreds of survey respondents, data analysts write nested logical formulas to automate category assignments instantly based on customer ages.

As a Marketing Research Analyst, you are categorizing 4 customer profiles according to the following tier rules:

Senior Bracket: Age of 60 or older (B2 >= 60) → "Senior".
Adult Bracket: Age of 18 to 59 (B2 >= 18) → "Adult".
Youth Bracket: Age under 18 → "Youth".
Summary Audit (Rows 8 to 10): Count total customer survey records using COUNTA in cell B8, count adult respondents in cell B9, and count senior respondents in cell B10 using COUNTIF.

Your Sheet Layout

Understanding how the customer segmentation sheet is structured:

Survey Respondents (Columns A & B): Shows customer names and their reported numeric ages (16 to 68).
Age Classification (Column C): Where your nested IF formula assigns the appropriate demographic bracket.
Demographic Summary (Rows 7 to 10): Audit block summarizing total respondent counts and group totals for Adults and Seniors.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:B5`	Customer Name, Age	Survey Data	Customer names and integer ages	Read-only baseline survey records.
`C2:C5`	Age Bracket	Classification	`=IF(B2 >= 60, "Senior", IF(B2 >= 18, "Adult", "Youth"))`	Segments customers into Senior, Adult, or Youth tiers.
`B8`	Total Profiles	Audit Summary	`=COUNTA(A2:A5)`	Counts total survey respondents (4).
`B9`	Adult Count	Audit Summary	`=COUNTIF(C2:C5, "Adult")`	Counts customers belonging to the Adult bracket (2).
`B10`	Senior Count	Audit Summary	`=COUNTIF(C2:C5, "Senior")`	Counts customers belonging to the Senior bracket (1).

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-C5

In Column C (C2:C5), classify Age into "Minor" (<18), "Adult" (<65), or "Senior" using IFS.

Objective #2

Cell: B8

In cell B8, count total customers using COUNTA(A2:A5).

Objective #3

Cell: C8

In cell C8, count "Adult" customers using COUNTIF(C2:C5, "Adult").

Objective #4

Cell: D8

In cell D8, count "Senior" customers using COUNTIF(C2:C5, "Senior").

Solution & Formula Breakdown

Let's examine how nested IF logic evaluates multiple criteria in order:

1. Evaluating Brackets with Nested IF (C2:C5)

=IF(B2 >= 60, "Senior", IF(B2 >= 18, "Adult", "Youth"))

Excel evaluates conditions from left to right in a logical waterfall:

Step 1: Is B2 >= 60? If yes, return "Senior" immediately.
Step 2: If not, evaluate the second condition: Is B2 >= 18? If yes, return "Adult".
Step 3: If neither condition was met (i.e. age is under 18), return "Youth".
Row 2 (Arthur Dent, Age 68): 68 ≥ 60 → "Senior"
Row 3 (Trillian Astra, Age 29): 29 < 60, but 29 ≥ 18 → "Adult"
Row 4 (Ford Prefect, Age 42): 42 < 60, but 42 ≥ 18 → "Adult"
Row 5 (Zaphod Beeblebrox, Age 16): 16 < 60 and 16 < 18 → "Youth"

2. Summary Auditing Formulas (B8, B9, B10)

Total Profiles (Cell B8): =COUNTA(A2:A5) returns 4.
Adult Count (Cell B9): =COUNTIF(C2:C5, "Adult") returns 2.
Senior Count (Cell B10): =COUNTIF(C2:C5, "Senior") returns 1.

Always order your tests from highest threshold to lowest (>= 60 then >= 18). If you checked >= 18 first, an age of 68 would match >= 18 and incorrectly be tagged as "Adult" before ever reaching the Senior test!