percentile from z score

haclcsw

New Member
Joined
Nov 15, 2017
Messages
4
I have a percentile data set bmiage from the CDC that includes the L,M, and S data---is there a formula in Excel that will calculate the Z-score and the percentile that corresponds to that Z score for a given parameter?

I need to be able to determine the child's percentile on a given day based on age and BMI for children age 2 and above.

That is, if the child is X months old and has BMI of Y; what percentile does this BMI fall at?

There is a similar posting for this issue but it uses the wtageinf table and I am not sure how to change the formulas (males, females)to use the bmiage data table.

Thanks,
Hank

 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
The STANDARIZE function in Excel will calculate the Z score.
NORMSDIST or NORM.S.DIST (depending on your ver. of Excel) will give the percentile.
 
Upvote 0
Use the NORM.S.DIST function to convert Z Scores to Percentiles, as in the table below.

The formula to calculate Percentile in cell C3 from the Z Score in B3 is =NORM.S.DIST(B3,TRUE)

N2NDf5r.png
 
Upvote 0
hanks for the example Jon! I am having another problem now with the formula for the z score. This is what I took from the CDC site Z=(((X/M)^L)-1)/(L*S)
where X is the BMI. I am using the bimage (BMI for age) table to get the values for L M & S. I cannot always match the results I get from their online calculator. A formula for wt to age for child below age 2 is for Boy Percentile:
=NORMSDIST(((B2/SUMPRODUCT({-8.8599305496373E-10;2.17283136483282E-07;-0.0000233795093578237;0.00139840973633909;-0.0472047169532743;0.966418510328367;3.5309169730499}*(A2+1E-100)^{6;5;4;3;2;1;0}))^SUMPRODUCT({1.88384740073058E-08;-2.56567599666441E-06;0.000144607330040403;-0.0043292114627782;0.0713064313127916;-0.600078349387445;1.81998545279799}*(A2+1E-100)^{6;5;4;3;2;1;0})-1)/(SUMPRODUCT({1.88384740073058E-08;-2.56567599666441E-06;0.000144607330040403;-0.0043292114627782;0.0713064313127916;-0.600078349387445;1.81998545279799}*(A2+1E-100)^{6;5;4;3;2;1;0})*SUMPRODUCT({6.12748133862247E-10;-7.8383864951688E-08;4.00890595653181E-06;-0.000104871007952312;0.00150638812494464;-0.0118615387584953;0.151787744888267}*(A2+1E-100)^{6;5;4;3;2;1;0})))
[TABLE="width: 64"]
<colgroup style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col width="64" style="width: 64px;"> </colgroup><tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR]
[TD="width: 64, bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 64"]
<colgroup style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col width="64" style="width: 64px;"> </colgroup><tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR]
[TD="width: 64, bgcolor: transparent"]B2 is wt

This may be more than you bargained for in responding so just let me know.
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks,
Hank
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,011
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top