比例互补误差函数，erfcx(x)，避免算术溢出的计算-VBA/Excel [英] Scaled Complementary Error Function, erfcx(x), computation avoiding arithmetic overflow - VBA/Excel

问题描述

我需要一种算法/近似值来计算Scaled Complementary Error Function erfcx(x)以达到双浮点精度.

I need an algorithm/approximation to compute the Scaled Complementary Error Function, erfcx(x) to double-float precision.

我在工作PC上，因此只能使用Excel和VBA，并且不能使用外部库或加载项:我需要自己对此进行编码.

I'm on a work PC so I’m limited to using Excel and VBA and I cannot use external libraries or add-ins: I need to code this myself.

Excel仅提供erf()和erfc()函数.

Excel only provides erf() and erfc() functions.

关系erfcx(x)= exp(x ^ 2)erfc(x)很有用，但是x的算术上溢/下溢大于26.5，我需要大于此值.

The relationship erfcx(x) = exp(x^2) erfc(x) is obviously useful, however there is arithmetic over/underflow for x larger than around 26.5 and I need to go larger than this.

以下帖子讨论了python的实现-但据我所知，它似乎无法解决问题.它使用其他库或近似值无法满足我的需求来提供解决方案.

The below post discussed a python implementation – but it doesn’t seem to resolve the issue from what I can tell. It provides solutions using other libraries or an approximation that isn’t precise enough for my needs.

在python中是否存在缩放的互补误差函数有空吗?

有什么建议吗?

更新:

我使用了我在Wikipedia上找到的连续分数表示形式 以及用于求解连续分数的算法的修改版本，请参见 http://finance4traders.blogspot.nl/2009/07/continued-fractions-and-modified-lentzs.html

I used this Continued Fraction representation I found on Wikipedia
and a modified version of the algorithm for solving continued fractions found here http://finance4traders.blogspot.nl/2009/07/continued-fractions-and-modified-lentzs.html

以下代码似乎有效，并且对于较大的输入参数实际上需要较少的迭代.

The following code seems to work and actually takes fewer iterations for larger input parameters.

Function erfcx(x) As Variant
Dim Ai As Double
Dim Bi As Double
Dim Ci As Double
Dim Di As Double
Dim Ei As Double
Dim Fi As Double
Dim X2 As Double
Dim i As Long

Const SQRPI As Double = 1.7724538509055
Const MAX_ITERATIONS = 1000


If x < 26.5 Then
    erfcx = Application.WorksheetFunction.ErfC_Precise(x) * Exp(x ^ 2)
Else
    X2 = x ^ 2
    Bi = X2
    Fi = X2
    Ci = X2
    Di = 0
    Do
        i = i + 1
        Ai = i / 2
        If i Mod 2 = 0 Then
            Bi = X2
        Else
            Bi = 1
        End If
        Di = 1 / (Bi + Ai * Di)
        Ci = Bi + Ai / Ci
        Ei = Ci * Di
        Fi = Fi * Ei
    Loop While Ei <> 1 And i < MAX_ITERATIONS
    Debug.Print i
    erfcx = x / Fi / SQRPI
End If  End function

推荐答案

此处讨论了几种近似方法:

Several approximations are discuss here:

AMS期刊文章

确定适合的近似值后，我们可以帮助您在工作表函数或 VBA UDF()

Once you have determined which approximation is suitable, we can help you code it in either a worksheet function or a VBA UDF()

这篇关于比例互补误差函数，erfcx(x)，避免算术溢出的计算-VBA/Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！