VBA自定义CND函数移植到Dart出现结果不一致的问题排查
累积正态分布计算(CND)函数VBA转Dart结果偏差问题
问题背景
- 耗时数周将原有VBA实现的CND(累积正态分布计算)函数翻译为Dart版本,当前Dart版本可正常执行,但输出结果与原VBA环境下的正确计算结果存在偏差
- 因不熟悉VBA语法,多轮调整Dart实现仍未定位问题根因。曾计划在VBA代码中插入打印语句逐阶段输出变量值辅助排查,但不清楚VBA调试打印的实现方式,也不知道对应输出的查看渠道,需要问题定位协助与通用VBA调试方法参考
- 曾尝试通过Excel原生函数、在单元格中分步记录各阶段计算值复现整套计算逻辑,最终得到的结果与当前Dart版本输出完全一致,但该结果与权威数学函数参考书籍记载的原VBA正确输出不匹配,排查难度进一步提升
测试复现场景
- 输入参数为-0.3253时,原VBA实现输出为权威正确结果;当前Dart版本执行
cnd(-0.3253)返回值为0.4140535951706531,与Excel单元格分步复刻的计算结果完全一致,但与原VBA的正确输出存在偏差,需要定位核心偏差原因
原VBA实现代码
Function CND(X As Double) As Double Dim y As Double, Exponential As Double, SumA As Double, SumB As Double y = Abs(X) If y > 37 Then CND = 0 Else Exponential = Exp(-y ^ 2 / 2) If y < 7.07106781186547 Then SumA = 3.52624965998911E-02 * y + 0.700383064443688 SumA = SumA * y + 6.37396220353165 SumA = SumA * y + 33.912866078383 SumA = SumA * y + 112.079291497871 SumA = SumA * y + 221.213596169931 SumA = SumA * y + 220.206867912376 SumB = 8.83883476483184E-02 * y + 1.75566716318264 SumB = SumB * y + 16.064177579207 SumB = SumB * y + 86.7807322029461 SumB = SumB * y + 296.564248779674 SumB = SumB * y + 637.333633378831 SumB = SumB * y + 793.826512519948 SumB = SumB * y + 440.413735824752 CND = Exponential * SumA / SumB Else SumA = y + 0.65 SumA = y + 4 / SumA SumA = y + 3 / SumA SumA = y + 2 / SumA SumA = y + 1 / SumA CND = Exponential / (SumA * 2.506628274631) End If End If If X > 0 Then CND = 1 - CND End Function
自研Dart实现代码
double cnd (double x) { double y; double exponential; double sumA; double sumB; double value; y = x.abs(); if (y > 37) { value = 0; } else { exponential = exp(pow(-y, 2) / 2); if (y < 7.07106781186547) { sumA = 3.52624965998911E-02 * y + 0.700383064443688; sumA = sumA * y + 6.37396220353165; sumA = sumA * y + 33.912866078383; sumA = sumA * y + 112.079291497871; sumA = sumA * y + 221.213596169931; sumA = sumA * y + 220.206867912376; sumB = 8.83883476483184E-02 * y + 1.75566716318264; sumB = sumB * y + 16.064177579207; sumB = sumB * y + 86.7807322029461; sumB = sumB * y + 296.564248779674; sumB = sumB * y + 637.333633378831; sumB = sumB * y + 793.826512519948; sumB = sumB * y + 440.413735824752; value = exponential * (sumA / sumB); } else { sumA = y + 0.65; sumA = y + 4 / sumA; sumA = y + 3 / sumA; sumA = y + 2 / sumA; sumA = y + 1 / sumA; value = exponential / (sumA * 2.506628274631); } } if (x > 0) { value = 1 - value; } return value; }
内容的提问来源于stack exchange,提问作者infinitewizard1010
相关产品推荐
相关产品推荐

