Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Wan Wan is offline
external usenet poster
 
Posts: 3
Default inverse error function

How can I find an inverse error function on Excel, using functions that are
available?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 146
Default inverse error function

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

"Wan" wrote in message
...
How can I find an inverse error function on Excel, using functions that
are
available?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 947
Default inverse error function

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)
--
HTH :)
Dana DeLouis


"Wan" wrote in message
...
How can I find an inverse error function on Excel, using functions that
are
available?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 947
Default inverse error function

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

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


"Wan" wrote in message
...
How can I find an inverse error function on Excel, using functions that
are
available?





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 947
Default inverse error function

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

--
HTH :)
Dana DeLouis




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default inverse error function

"Dana DeLouis" wrote...
....
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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 947
Default inverse error function

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


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

--
Dana DeLouis


"Harlan Grove" wrote in message
...
"Dana DeLouis" wrote...
...

.. 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



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default inverse error function

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

"Harlan Grove" wrote:

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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default inverse error function

"Jerry W. Lewis" wrote:

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
TAN INVERSE ? Ahsan Excel Worksheet Functions 3 June 19th 07 04:09 PM
FFT INVERSE ANALYSIS ilver belletti Excel Discussion (Misc queries) 5 June 5th 07 01:29 PM
What is the Function for Inverse sin Mike O Excel Worksheet Functions 1 September 4th 06 01:38 AM
log inverse Az Excel Discussion (Misc queries) 1 November 28th 05 10:12 AM
Inverse sin John Fitzpatrick Excel Worksheet Functions 3 March 25th 05 07:35 PM


All times are GMT +1. The time now is 04:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"