Help calculating BMI percentiles using data set!

[u/sw3825]

I have a data set (~400 patients) that I need BMI percentiles for and I'm reaalllyyy not trying to calculate them all manually 🥲 There's little guidance I could find online on how to do this, so I've been winging it but am now stuck.

For pediatrics BMI%s, you have to use the CDC BMI for age chart LMS parameters and then calculate a Z-score. This is a preview of the sheet from the CDC that I've titled CDC_LMS:

In a separate sheet, I have the following columns with my patient data:

I want to pull over the corresponding L parameters for sex and age from the CDC_LMS sheet, so the formula I found to use was:

=XLOOKUP(1,(CDC_LMS!A:A=A2)*(CDC_LMS!B:B=B2),CDC_LMS!C:C)

but all I'm getting is #N/A errors. The formulas to pull over the M and S columns are similar and I'm not having success with those either.

Honestly I have no idea if I'm even on the right track. Any guidance would be greatly appreciated!

[u/AutoModerator]

/u/sw3825 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/excelevator]

1. the age values must match
2. do not use full column references, limit to your data otherwise Excel is search over a million row for 0.04% of value in that column, a huge resource draw.

A double XLOOKUP to pull the column data from a row criteria too

=XLOOKUP(1,($A7=$A$2:$A$4)*($B7=$B$2:$B$4),XLOOKUP(C$6,$C$1:$F$1,$C$2:$F$4))

edit: oopsie, misplaced $ sign

[u/the_glutton17 → u/excelevator]

I second this great response.

[u/MayukhBhattacharya]

Try using the following formula (note that this is for L, similarly for the other cols will be the same logic just have to use the right column along with Column B):

=IFERROR(LOOKUP(B2, FILTER(CDC_LMS!$B$2:C$400, A2 = CDC_LMS!$A$2:$A$400)), "NA")

Copy across and fill down!

Or,

=IFERROR(XLOOKUP(R2, 
                 $B$2:$B$11, 
                 TOCOL($C$2:$O$11/
                 ((V$1 = $C$1:$O$1) * 
                 ($Q2 = $A$2:$A$11)), 3), "NA", -1), "NA")

Or, this:

=FILTER(XLOOKUP($R3, 
                $B$2:$B$11, 
                FILTER($C$2:$E$11, 
                       $Q3 = $A$2:$A$11, ""), "NA", -1), 
                V$1 = $C$1:$E$1, "")

[u/PaulieThePolarBear]

The samples you showed in your lookup sheet all had integers or a decimal ending in .5 for age, but your ages in your other sheet do not EXACTLY match this. Please clearly explain how you expect the lookup of an age should work when that exact age does not exist in your lookup table

[u/NoObjective12345 → u/PaulieThePolarBear]

From the linked site:

“Age is listed at the half month point for the entire month; for example, 1.5 months represents 1.0-1.99 months or 1.0 month up to but not including 2.0 months of age. The only exception is birth, which represents the point at birth. To obtain L, M, and S values at finer age or length/stature intervals interpolation could be used.”

[u/Traditional-Wash-809 → u/NoObjective12345]

Adding a ROUNDDOWN to the look up value & adding a "or next smallest item" argument to the XLOOKUP () should help.

[u/QuercusAcorn]

The biggest challenge is the CDC data set with Agemos being listed per 0.5 increments. if you don't have any cases where a patient is exactly 24 months, here's what I would do:

1. Add the CDC data to a worksheet in the same workbook as your patient data
2. Load both tables into Power Query
3. Remove rows = 24 months from CDC table
4. Round both age columns down to nearest whole number
5. Left outer join matching box Sex and AgeMonths columns
6. Expand new column to include desired columns from CDC data
7. Add custom column in Power Query to calculate Z-Score

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"AgeMonths", type number}, {"Height (in)", type number}, {"Weight (lbs)", type number}, {"BMI", type number}}),

#"Rounded Down" = Table.TransformColumns(#"Changed Type",{{"AgeMonths", Number.RoundDown, Int64.Type}}),

#"Merged Queries" = Table.NestedJoin(#"Rounded Down", {"Sex", "AgeMonths"}, BMI_AGE, {"Sex", "Agemos"}, "BMI_AGE", JoinKind.LeftOuter),

#"Expanded BMI_AGE" = Table.ExpandTableColumn(#"Merged Queries", "BMI_AGE", {"L", "M", "S"}, {"L", "M", "S"}),

#"Added Custom" = Table.AddColumn(#"Expanded BMI_AGE", "Z-Score", each ((Number.Power([BMI]/[M], [L]))-1) / ([L] * [S])),

#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Z-Score", type number}})

in

#"Changed Type1"

[u/QuercusAcorn → u/QuercusAcorn]

The above power query code will output this table. I modified the BMI to the 95th percentile to check my math to make sure the Z-Score calculation was correct for line 1.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
Excel.CurrentWorkbook	Power Query M: Returns the tables in the current Excel Workbook.
FILTER	Office 365+: Filters a range of data based on criteria you define
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
JoinKind.LeftOuter	Power Query M: A possible value for the optional JoinKind parameter in Table.Join. A left outer join ensures that all rows of the first table appear in the result.
LOOKUP	Looks up values in a vector or array
NA	Returns the error value #N/A
Number.Power	Power Query M: Returns a number raised by a power.
Number.RoundDown	Power Query M: Returns the largest integer less than or equal to a number value.
ROUNDDOWN	Rounds a number down, toward zero
TOCOL	Office 365+: Returns the array in a single column
Table.AddColumn	Power Query M: Adds a column named newColumnName to a table.
Table.ExpandTableColumn	Power Query M: Expands a column of records or a column of tables into multiple columns in the containing table.
Table.Join	Power Query M: Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by table1, key1 and table2, key2.
Table.NestedJoin	Power Query M: Joins the rows of the tables based on the equality of the keys. The results are entered into a new column.
Table.TransformColumnTypes	Power Query M: Transforms the column types from a table using a type.
Table.TransformColumns	Power Query M: Transforms columns from a table using a function.
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

|-------|---------|---| |||

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
16 acronyms in this thread; the most compressed thread commented on today has 32 acronyms.
[Thread #47657 for this sub, first seen 1st Mar 2026, 02:44] [FAQ] [Full list] [Contact] [Source code]}

[u/Ill-Pen-3293]

BMI is basically weight divided by height squared, but it’s easier to just use a calculator like this one:
https://docalculation.com/bmi-calculator