code for inverse erf function

[pete6982]

I am doing work with confidence intervals for normally distributed data and I want to be able to calculate specific intervals (such as .92, etc). If x is the mean and y is the std dev I know that CI=(x-ny,x+ny), where n=sqrt(2)*inverseERF(CI).

I tried to make a vba function to calculate the invERF, but I'm off by a bit.

Does anyone know of an existing invERF funcion? I can't find actual code for one anywhere.

Thanks,
Pete

[Andrew Poulsom]

There are ERF and ERFC functions in the Analysis ToolPak, but I don't know if those are what you want. There is also a CONFIDENCE function.

[pgc01]

Hi Pete

Look at the function CONFIDENCE() as Andrew said. If, however, you really need to compute the value of the invERF there is a small code (posted by JJ 05-18-06 09:20). It is in Pascal and so should be very easy to port to vba. The name of the function is invErf

http://les-mathematiques.u-strasbg.f...86277&t=286277

Hope this helps
PGC

[pete6982]

thanks guys. i was able to recode the pascal invERF function into vba. My worksheet works like a charm now.

in case anyone else ever wants a vba version, here's what I ended up with:

Function invERF(y As Double) As Double
Dim pi As Double, x As Double, d As Double
pi = 3.14159265358979
If y < 0 Then
    invERF = 0 'interval includes the mean only
    Exit Function
ElseIf y >= 1 Then
    invERF = 10 'makes the interval include everything
    Exit Function
'for my purposes, I only want the function to process input from 0 to 1
ElseIf y < 0.596 Then
    x = sqr(pi) / 2 * y * (1 + (pi / 12) * y * y)
Else
    x = sqr(-Log((1 - y) * sqr(pi)))
End If
d = (y - ERF(x)) / (2 * Exp(-x * x) / sqr(pi))
x = x + d
Do While Abs(d) >= 0.00000001
    d = (y - ERF(x)) / (2 * Exp(-x * x) / sqr(pi))
    x = x + d
Loop
invERF = x
End Function

Function ERF(x As Double) As Double

Dim f As Double, c As Double, pi As Double
Dim j As Integer
c = 0
pi = 3.14159265358979
If 1.5 < x Then
    c = 2 - c
    j = 3 + Int(32 / x)
    f = 0
    Do While j <> 0
        f = 1 / (f * j + x * sqr(2))
        j = j - 1
    Loop
    f = f * c * (3 - c * c) * Exp(-x * x) / sqr(2 * pi) + (c - 1) * (3 * c * c + c - 8) / 6
Else
    j = 3 + Int(9 * x)
    f = 1
    Do While j <> 0
        f = 1 + f * x * x * (0.5 - j) / j / (0.5 + j)
        j = j - 1
    Loop
    f = c + f * x * (2 - 4 * c) / sqr(pi)
End If
    ERF = f
End Function

thanks for the help!

[Norie]

Pete

A 'sqrt' function?

Why not just use VBA's Sqr function?

[pete6982]

hehe, didn't even know it was there

[ikkle]

thanks guys. i was able to recode the pascal invERF function into vba. My worksheet works like a charm now.

in case anyone else ever wants a vba version, here's what I ended up with:

Function invERF(y As Double) As Double
Dim pi As Double, x As Double, d As Double
pi = 3.14159265358979
If y < 0 Then
    invERF = 0 'interval includes the mean only
    Exit Function
ElseIf y >= 1 Then
    invERF = 10 'makes the interval include everything
    Exit Function
'for my purposes, I only want the function to process input from 0 to 1
ElseIf y < 0.596 Then
    x = sqr(pi) / 2 * y * (1 + (pi / 12) * y * y)
Else
    x = sqr(-Log((1 - y) * sqr(pi)))
End If
d = (y - ERF(x)) / (2 * Exp(-x * x) / sqr(pi))
x = x + d
Do While Abs(d) >= 0.00000001
    d = (y - ERF(x)) / (2 * Exp(-x * x) / sqr(pi))
    x = x + d
Loop
invERF = x
End Function

Function ERF(x As Double) As Double

Dim f As Double, c As Double, pi As Double
Dim j As Integer
c = 0
pi = 3.14159265358979
If 1.5 < x Then
    c = 2 - c
    j = 3 + Int(32 / x)
    f = 0
    Do While j <> 0
        f = 1 / (f * j + x * sqr(2))
        j = j - 1
    Loop
    f = f * c * (3 - c * c) * Exp(-x * x) / sqr(2 * pi) + (c - 1) * (3 * c * c + c - 8) / 6
Else
    j = 3 + Int(9 * x)
    f = 1
    Do While j <> 0
        f = 1 + f * x * x * (0.5 - j) / j / (0.5 + j)
        j = j - 1
    Loop
    f = c + f * x * (2 - 4 * c) / sqr(pi)
End If
    ERF = f
End Function

thanks for the help!

SO basically i am a complete idiot where it comes to this but does this still work as i am trying to do it for some work i have to do and it keeps coming up with the error #NAME?

If needed can give more info

Thanks