Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
related to error function
How to find inverse of complementary error and error function
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
related to error function
Hummm? Please refrase or expand your question.
Argy "amit saraf" wrote: How to find inverse of complementary error and error function |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
related to error function
amit saraf wrote:
How to find inverse of complementary error and error function Hi. If you don't get a better solution, here is a Newton version of the Inverse Error function. The normal Series equation converges too slowly in my opinion. Note that Excel's ERF() function can't work with Negative values (it's a known bug). This tries to work around it. As a simple test, we try to get back the same values... Sub TestIt() Debug.Print InverseERF([Erf(.1)]) Debug.Print InverseERF([Erf(.9)]) Debug.Print InverseERF([Erf(2)]) Debug.Print InverseERF([-Erf(2)]) End Sub Function InverseERF(N) Dim J As Long 'Counter Dim G As Double 'Guess Dim Ng As Double 'New Guess Dim K As Double 'Constant Dim M As Double 'Hold Positive value of n Select Case N Case -1 InverseERF = "-Infinity" Case 1 InverseERF = "+Infinity" Case -1 To 1 'Valid With WorksheetFunction K = [SqrtPi(1)/2] Ng = 0.5 M = Abs(N) Do G = Ng Ng = G + K * Exp(G * G) * (.ErfC(G) + M - 1) J = J + 1 Loop While G < Ng And J < 30 'It's a Odd function, so ok. InverseERF = Sgn(N) * Ng End With Case Else InverseERF = "Invalid" End Select End Function - - - HTH Dana DeLouis |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
related to error function
You can use these, with a reference to atpvbaen. Code: -------------------- ' The worksheet ERF and ERFC functions only allow non-negative ' arguments less than about 27.334, although the mathematical functions are valid ' across the domain of Reals. The ERF function reaches its asymptotic limit of 1 ' (to double precision accuracy) by 5.8 ' The functions myERF and myERFC are identical in interface to the worksheet ' functions, but work for any value of the arguments. Function myERF(a As Double, Optional b As Variant) myERF = myERF1(a) If Not IsMissing(b) Then myERF = myERF1(CDbl(b)) - myERF End Function Function myERFC(x As Double) As Double myERFC = 1# - myERF(x) End Function Private Function myERF1(x As Double) As Double If Abs(x) 5.8 Then myERF1 = Sgn(x) ElseIf x < 0 Then myERF1 = Sgn(x) * Application.Run("ATPVBAEN.XLA!ERF", Abs(x)) End If End Function -------------------- -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=26920 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
related to error function
Use the relationship between these functions and functions for which Excel
does have inverses. ERF(x) = GAMMADIST(x^2,0.5,1,TRUE) ERFC(x) = 2*NORMSDIST(-x*SQRT(2)) = CHIDIST(2*x^2,1) so invERF(p) = SQRT(GAMMAINV(p,0.5,1)) invERFC(p) = -NORMSINV(p/2)/SQRT(2) = SQRT(CHIINV(p,1)/2) For Excel 2003 or later, NORMSDIST and NORMSINV are much more accurate than ERFC for large values of x. For earlier versions of Excel, CHIDIST is more accurate than ERFC, but neither inverse function performs well for large x (small p). Jerry "amit saraf" wrote: How to find inverse of complementary error and error function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel related | Charts and Charting in Excel | |||
two related workfiles | Excel Discussion (Misc queries) | |||
2 questions! Related to combobox and time function. HELP!! | Excel Discussion (Misc queries) | |||
macro related | Excel Discussion (Misc queries) | |||
Function Related Question | Excel Discussion (Misc queries) |