Challenge Scenario
In occupational health and employee wellness programs, biometric screening helps evaluate health risk factors. Calculating Body Mass Index (BMI) and sorting participants into standard clinical health tiers enables targeted preventative wellness initiatives.
As a Corporate Wellness Coordinator, you are evaluating participant metrics:
- BMI (Column D): Compute
Weight / (Height ^ 2).
- Health Category (Column E): If BMI < 18.5, assign
"Underweight"; if BMI < 25, assign "Normal"; otherwise assign "Overweight" using IFS.
- Cohort KPIs (B8 & C8): Compute the average BMI and count participants in the
"Normal" weight bracket.
Your Sheet Layout
Before entering your formulas, take a moment to understand how this health evaluation model is organized. Separating raw biometric inputs from health metrics and population summaries makes calculations transparent and easy to audit.
The sheet is divided into three key operational zones:
- Raw Biometric Data (Columns A–C): Contains participant names along with their measured body weight (kg) and height (m).
- Health Calculation (Columns D & E): Computes exact Body Mass Index scores and maps each participant to their clinical weight category.
- Wellness KPI Summary (Rows 7–8): Aggregates overall group health metrics, including mean BMI and total participants in the normal weight range.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Participant, Weight (kg), Height (m) |
Data (Biometrics) |
Raw measurements |
Read-only baseline measurements for each participant. |
D2:D5 |
BMI |
Calculation |
=B2 / (C2^2) |
Calculate Body Mass Index using the standard weight / height squared formula. |
E2:E5 |
Health Category |
Category |
=IFS(D2 < 18.5, "Underweight", D2 < 25, "Normal", TRUE(), "Overweight") |
Classify each BMI value into Underweight, Normal, or Overweight tiers. |
B8 |
Average BMI |
Summary (Cohort Mean) |
=AVERAGE(D2:D5) |
Compute the average BMI across all participating members. |
C8 |
Normal Weight Count |
Summary (Target Tally) |
=COUNTIF(E2:E5, "Normal") |
Count how many individuals fall into the "Normal" health tier. |
This layout ensures raw clinical data flows cleanly through mathematical calculation and categorical classification into executive summary metrics.
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), calculate BMI using formula B2 / (C2^2).
2
In Column E (E2:E5), classify BMI into "Underweight" (<18.5), "Normal" (<25), or "Overweight" using IFS.
3
In cell B8, calculate average BMI using AVERAGE.
4
In cell C8, count "Normal" participants using COUNTIF.
Solution & Formula Breakdown
Calculating Body Mass Index and categorizing wellness cohorts is a common data analytics workflow. Let's explore how each formula works.
1. Calculating BMI (D2:D5)
=B2 / (C2^2)
The standard mathematical formula for BMI is weight / (height * height):
- In cell D2, Alex Reed weighs 68 kg at 1.75 m height →
68 / (1.75^2) = 68 / 3.0625 = 22.20.
- In cell D3, Taylor Swift weighs 52 kg at 1.68 m height →
52 / (1.68^2) = 52 / 2.8224 = 18.42.
2. Multi-Condition Category Assignment with IFS (E2:E5)
=IFS(D2 < 18.5, "Underweight", D2 < 25, "Normal", TRUE(), "Overweight")
The IFS function tests conditions from left to right and returns the value for the first condition that evaluates to TRUE:
D2 < 18.5: Checks if BMI is strictly under 18.5. If true, outputs "Underweight".D2 < 25: Because the first check failed, the BMI must be at least 18.5. If it is under 25, outputs "Normal".TRUE(), "Overweight": Acts as the fallback catch-all for any BMI 25 or higher.
3. Summary Rollup Metrics (B8 & C8)
- Cell B8 (Average BMI):
=AVERAGE(D2:D5) → Calculates the mean BMI across the cohort (~24.1).
- Cell C8 (Normal Count):
=COUNTIF(E2:E5, "Normal") → Tallies how many participants have normal weight (1).
In Excel, the caret operator (^) raises a number to a power. Wrapping (C2^2) in parentheses makes your formula easier to read and prevents mathematical order-of-operations confusion.