inverse error function

[Wan]

How can I find an inverse error function on Excel, using functions that are
available?

 

[Dave Thomas]

I don't know what you mean by inverse error. Please give an example.

 

[Dana DeLouis]

How can I find an inverse error function on Excel, using functions that

are
available?

I could have sworn there was a better way, but I don't see it at the moment.
If you don't get a better reply, here's something I put together.
We note that it's only accurate to 7-8 digits because Excel's Erf function
is not very accurate either.
I'm using Excel 2007, so Erf is a worksheet function. If using an earlier
version, set a vba reference to the ATP.

Sub TestIt()
Debug.Print InverseErf(0.1)
Debug.Print InverseErf(0.5)
Debug.Print InverseErf(0.9)
End Sub

Function InverseErf(n)
If n < 0 Or n >= 1 Then Exit Function

Dim P As Double
Dim g As Double
Dim J As Long 'Counter
With WorksheetFunction
P = .SqrtPi(1)
g = 0
For J = 1 To 15
g = g + (Exp(g * g) * P * (n - .Erf(g))) / 2
' Debug.Print J; g
Next J
End With
InverseErf = g
End Function


Three known results from Math Program:
Note the slight error in Excel.

'InverseErf [0.1]
'0.0888559904942577
'
'InverseErf [0.5]
'0.4769362762044698
'
'InverseErf [0.9]
'1.163087153676674


Note that Excel can not take the Erf between -1 & 0. (Bug!)
therefore, just note that InverseErf(-x) = - InverseErf(+x)

 

[Dana DeLouis]

How about we get rid of Excel's erf, and use our own?
This is much more accurate.
I've eliminated WorksheetFunction also.
Not much Error checking, but this should give you some ideas.
HTH. :>)

Option Explicit
Const SqrPi As Double = 1.77245385090552

Sub TestIt()
Debug.Print InverseErf(0.1)
Debug.Print InverseErf(0.5)
Debug.Print InverseErf(0.9)
End Sub

Function InverseErf(n)
If n < 0 Or n >= 1 Then Exit Function

Dim g As Double
Dim j As Long
g = 0
For j = 1 To 15
g = g + (Exp(g * g) * SqrPi * (n - Erf(g))) / 2
Next j
InverseErf = g
End Function

Function Erf(z)
Dim k As Long
Dim F As Double '(F)actorial
Dim n As Double
Dim d As Double
Dim Ans As Double

If z = 0 Then Exit Function
F = 1
With WorksheetFunction
For k = 0 To 25
n = (-1) ^ k * z ^ (2 * k + 1)
d = F * (2 * k + 1)
Ans = Ans + n / d
F = F * (k + 1)
Next k
Ans = Ans * 2 / SqrPi
End With
Erf = Ans
End Function

 

[Dana DeLouis]

OOps! Forgot to remove Worksheetfunction.

Option Explicit
Const SqrPi As Double = 1.77245385090552

Sub TestIt()
Debug.Print InverseErf(0.1)
Debug.Print InverseErf(0.5)
Debug.Print InverseErf(0.9)
End Sub

Function InverseErf(n)
If n < 0 Or n >= 1 Then Exit Function

Dim g As Double
Dim j As Long
g = 0
For j = 1 To 15
g = g + (Exp(g * g) * SqrPi * (n - Erf(g))) / 2
Next j
InverseErf = g
End Function

Function Erf(z)
Dim k As Long
Dim F As Double '(F)actorial
Dim n As Double
Dim d As Double
Dim Ans As Double

If z = 0 Then Exit Function
F = 1
For k = 0 To 25
n = (-1) ^ k * z ^ (2 * k + 1)
d = F * (2 * k + 1)
Ans = Ans + n / d
F = F * (k + 1)
Next k
Erf = Ans * 2 / SqrPi
End Function

 

[Harlan Grove]

Function InverseErf(n)
If n < 0 Or n >= 1 Then Exit Function

Dim g As Double
Dim j As Long
g = 0
For j = 1 To 15
g = g + (Exp(g * g) * SqrPi * (n - Erf(g))) / 2
Next j
InverseErf = g
End Function

Function Erf(z)
Dim k As Long
Dim F As Double '(F)actorial
Dim n As Double
Dim d As Double
Dim Ans As Double

If z = 0 Then Exit Function
F = 1
For k = 0 To 25
n = (-1) ^ k * z ^ (2 * k + 1)
d = F * (2 * k + 1)
Ans = Ans + n / d
F = F * (k + 1)
Next k
Erf = Ans * 2 / SqrPi
End Function

....

Someone's gotta ask, since ERF(x) = 2*NORMSDIST(x*SQRT(2))-1, then why not
use NORMSINV? That is, why not estimate InverseERF(y) as

=NORMSINV((y+1)/2)/SQRT(2)

..>'InverseErf [0.1]
..>'0.0888559904942577

=NORMSINV((0.1+1)/2)/SQRT(2) -> 0.0888559904942577

..>'InverseErf [0.5]
..>'0.4769362762044698

=NORMSINV((0.5+1)/2)/SQRT(2) -> 0.47693627620447

..>'InverseErf [0.9]
..>'1.163087153676674

=NORMSINV((0.9+1)/2)/SQRT(2) -> 1.16308715367667

 

[Dana DeLouis]

InverseERF(y) as

=NORMSINV((y+1)/2)/SQRT(2)

Well, this is embarrassing! Thank you very much. :>)

--
Dana DeLouis

...

.. since ERF(x) = 2*NORMSDIST(x*SQRT(2))-1, then why not

use NORMSINV? That is, why not estimate InverseERF(y) as

=NORMSINV((y+1)/2)/SQRT(2)

..>'InverseErf [0.1]
..>'0.0888559904942577

=NORMSINV((0.1+1)/2)/SQRT(2) -> 0.0888559904942577

 

[Jerry W. Lewis]

You could avoid loss of precision for small erf values by using
SQRT(inv_gamma(erf_value,0.5,1))
where inv_gamma is the highly accurate equivalent of GAMMAINV from Ian
Smith's VBA library
http://members.aol.com/iandjmsmith/Examples.xls

Unfortunately, the native GAMMAINV function is very crude for the purpose.

Sorry to come so late to the party, but Google's indexing of newsgroups was
down for nearly half a month, so I just stumbled onto this thread.

Jerry

Someone's gotta ask, since ERF(x) = 2*NORMSDIST(x*SQRT(2))-1, then why not
use NORMSINV? That is, why not estimate InverseERF(y) as

=NORMSINV((y+1)/2)/SQRT(2)

..>'InverseErf [0.1]
..>'0.0888559904942577

=NORMSINV((0.1+1)/2)/SQRT(2) -> 0.0888559904942577

..>'InverseErf [0.5]
..>'0.4769362762044698

=NORMSINV((0.5+1)/2)/SQRT(2) -> 0.47693627620447

..>'InverseErf [0.9]
..>'1.163087153676674

=NORMSINV((0.9+1)/2)/SQRT(2) -> 1.16308715367667

 

[Jerry W. Lewis]

You could avoid loss of precision for small erf values by using
SQRT(inv_gamma(erf_value,0.5,1))
where inv_gamma is the highly accurate equivalent of GAMMAINV from Ian
Smith's VBA library
http://members.aol.com/iandjmsmith/Examples.xls

Unfortunately, the native GAMMAINV function is very crude for the purpose.

The improvment in the inversion algorithm introduced in Excel 2003 means
that GAMMAIN for Excel 2003 or later appears to give at least 6-figure
accuracy, which may be adequate.

Jerry