在做一个google sheet，输入孩子年龄，身高，体重，自动列出身高体重BMI的percentile。求大神帮助。数据来自cdc

pmp6

终于成功啦！！！只要输入身高，体重，测量日期自动显示孩子BMI百分位！！！经过测试，和CDC网站所显示百分比一模一样！！！

在做一个google sheet，输入孩子年龄，身高，体重，自动列出身高体重BMI的percentile。求大神帮助。数据来自cdc

数据如下
https://www.cdc.gov/growthcharts ... iagerev.htm#females

在CDC的官网上测percentile，可以准确的得到一个百分比数字。问题是网站没有提供计算公式，如何在google sheet里实现相同的功能呢？感谢

现在的瓶颈如下：
数据库中的月份和BMI不是线性的。把实际身高带入后，如何准确定位现在的percentile。

比如数据库中
24个月时中等BMI（50%）是16.4234，
24.50个月的时候中等BMI（50%）是16.38804。
24个月时BMI（75%）是17.42746，
24.50个月的时候BMI（75%）是17.38582。

但孩子是24.3个月的时候测的BMI17.3。这个时候这么尽量准确的得出现在孩子的percentile是多少？感谢

下面是BMI growth chart，先递减，后递增


如果套用forcast函数，结果是一直递增。所以这个函数在这里使用是错误的。



终于成功啦！！！只要输入身高，体重，测量日期自动显示孩子BMI百分位！！！经过测试，和CDC网站所显示百分比一模一样！！！

zhouhaoaaaaa

不知道google sheet的公式是什么，但是如果是excel, 那就是先用xloookup找到age对应的区间，然后用最简单的线性插值去算出这个age每个percentile的值(黄色那行）。然后用输入的的身高体重算出来的BMII17.3 去新的percentile那行里查找。大概还是在50%到70%之间，而且离70%更近。这个时候也可以接着再用线性插值去算是50%到70%的具体某个值。但是肯定就只是更近似的估算值了。

pmp6

zhouhaoaaaaa 发表于 2024-7-15 10:09
不知道google sheet的公式是什么，但是如果是excel, 那就是先用xloookup找到age对应的区间，然后用最简单的 ...

大神厉害。我用FORECAST函数来进行线性插值计算非整月的百分位数。但是得到的奇怪的数字，不知道哪里出了问题

“=FORECAST(IZ$30,$B33: $HL33,$B$30: $HL$30)”

39.23个月时3%百分位数，上面公式求出来是12.79
明明数值应该在38.5个月（13.75）和39.50个月（13.73）中间

pmp6

zhouhaoaaaaa 发表于 2024-7-15 10:09
不知道google sheet的公式是什么，但是如果是excel, 那就是先用xloookup找到age对应的区间，然后用最简单的 ...

我在想我是不是用错函数了。我其实不需要预测未来的日子（forcast），而是需要定位过去的某一天

tvc889

pmp6 发表于 2024-7-15 12:01
我在想我是不是用错函数了。我其实不需要预测未来的日子（forcast），而是需要定位过去的某一天 ...

24个月时中等BMI（50%）是16.4234，
24.50个月的时候中等BMI（50%）是16.38804。
24个月时中等BMI（75%）是17.42746，
24.50个月的时候中等BMI（75%）是17.38582。

但孩子是24.3个月的时候测的BMI17.3。这个时候这么尽量准确的得出现在孩子的percentile是多少？感谢

24个月到24.5月BMI提高17.38582-16.38804=0.99778。
24个月到24.5月BMI percentile提高75-50=25。
也就是说BMI改变0.0399112，BMI percentile改变1。

24.3个月的时候测的BMI17.3，和24个月时中等BMI（50%）的16.4234比较，多了0.91196。
相当于BMI percentile提高0.91196/0.0399112=22.84972639点。
所以24.3个月的percentile是50+22.84972639=72.8。

这东西只能算个大概，没法太准确。

pmp6

tvc889 发表于 2024-7-15 12:38
24个月时中等BMI（50%）是16.4234，
24.50个月的时候中等BMI（50%）是16.38804。
24个月时中等BMI（75%） ...

感谢。你知道google sheet中有什么formula可以完成这个功能吗？谢谢

tvc889

pmp6 发表于 2024-7-15 13:34
感谢。你知道google sheet中有什么formula可以完成这个功能吗？谢谢

这个过程的需要几个公式，你一步一步分解后写公式就容易了。不大可能一个公式搞定。

那种所谓的线性回归公式不适合这个。再说，你岂不是要做很多个公式？就像我说得那样最简单的就可以。

pmp6

tvc889 发表于 2024-7-15 14:18
这个过程的需要几个公式，你一步一步分解后写公式就容易了。不大可能一个公式搞定。

那种所谓的线性回归 ...

这个大公式做好就一劳永逸了。正在研究中。有点费眼睛

=INDEX($B33:$HL33, MATCH(IO$30,$B$30:$HL$30,1))+(INDEX($C33:$HL33, MATCH(IO$30,$B$30:$HL$30,1))-INDEX($B33:$HL33, MATCH(IO$30,$B$30:$HL$30,1)))*(IO$30-INDEX($B$30:$HL$30, MATCH(IO$30,$B$30:$HL$30,1)))/(INDEX($C$30:$HL$30, MATCH(IO$30,$B$30:$HL$30,1))-INDEX($B$30:$HL$30, MATCH(IO$30,$B$30:$HL$30,1)))

上面这个公式可以算出所有时间点对应的BMI。。。。太费眼睛了。

小小白虎

楼主这是要监测细到每个月的数据么？？？

pmp6

小小白虎 发表于 2024-7-15 14:28
楼主这是要监测细到每个月的数据么？？？

不是的。有点复杂，请看主贴。纯数学和google sheet函数应用问题。做出来后，可以精确track孩子的发展曲线。

bunnie2020

直接在蓝本本上记录各时间点曲线不就出来了么。

zhouhaoaaaaa

pmp6 发表于 2024-7-15 10:51
大神厉害。我用FORECAST函数来进行线性插值计算非整月的百分位数。但是得到的奇怪的数字，不知道哪里出了 ...

不能用Forecast，这个应该是基于线性回归，所以会有你的数据的问题，这种数据不是线性的，所以没有办法去用一个线性方程去拟合。我说的线性插值感觉和你最后的那个大公式应该差不多。但是所有的这些都是瞎搞。因为。。。。

最后的最后，我刚才去看了下这个网站的功能，它的确会计算出来一个数字显示在那张图片上。里面的算法非常复杂。

算法在这里

https://www.cdc.gov/nccdphp/dnpao/growthcharts/resources/sas.htm

网站里用javascript实现的。估计把算法扒出来对你也有难度。只能用近似的方法随便弄弄了

pmp6

zhouhaoaaaaa 发表于 2024-7-15 19:24
不能用Forecast，这个应该是基于线性回归，所以会有你的数据的问题，这种数据不是线性的，所以没有办法去 ...

因为啥？感谢

pmp6

zhouhaoaaaaa 发表于 2024-7-15 19:24
不能用Forecast，这个应该是基于线性回归，所以会有你的数据的问题，这种数据不是线性的，所以没有办法去 ...

我套用这个公式（从chatgpt里），还是有点错误。正在找原因。

你知道怎么把Java的公式抓出来在google sheet里实现同样的计算吗？感激不尽

rainyfriend

小小白虎 发表于 2024-7-15 14:28
楼主这是要监测细到每个月的数据么？？？

他是要监测过去某一天的数据。

pmp6

终于成功啦！！！只要输入身高，体重，测量日期自动显示孩子BMI百分位！！！经过测试，和CDC网站所显示百分比一模一样！！！