percentile from z score

[haclcsw]

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

​

 

[AhoyNC]

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.

 

[Jon Peltier]

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)

 

[haclcsw]

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})))​

<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;">
</tbody>

B2 is wt This may be more than you bargained for in responding so just let me know.

<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;">
</tbody>

Thanks,
Hank

 

[haclcsw]

Thanks for the distinction with the functions! I am using Excel 2016.
Hank

 

[Jon Peltier]

Wow, that's a nasty fit. I don't even know what to do with it.

 

[haclcsw]

Thanks for your example. I will continue to work on this.