Re: Lambert W function for Excel, work on real and complex number

Thank you also.

One minor quibble: there seems to be an issue with the results that come from input values between 0 and -0.0212. As soon as it hits -0.0212 it's like hitting a wall, beyond that the results are wildly off:

-0.0218 > -0.02229
-0.0217 > -0.02219
-0.0216 > -0.02208
-0.0215 > -0.02198
-0.0214 > -0.02187
-0.0213 > -0.02177
-0.0212 > -5.51714
-0.0211 > -5.57716
...and so on, all input values from there to zero are similarly off.

Can anyone see why? Unfortunately I just lack the coding skills to pick it apart - gave it a go, but just no experience there whatsoever :-(

Any help much appreciated.

Last edited by Chipmunker; Mar 27th, 2014 at 01:40 PM.

"If x is within the sub interval (-1/e, 0), the calculator returns two values."

And for x = -0.0211, the calculator returns -5.577162 and -0.02156, both values are valid.
In Excel, the function return only 1 of them, I will check if i can return the more suitable one.

Re: Lambert W function for Excel, work on real and complex number

I searched high and low for access top a vba routine for Lambert W.

This was the best - BUT it sometimes gives the -ve branch solutions instead of the +ve.

It is also complex which i do not need.

For others I will post my solution here.

It returns the +ve branch solution only, is accurate to 9dp for most of the useful space and is reasoanbly faast

Here it is and hope others find it useful.

Bob J.

Code:

Public Function myLambertW(x As Double) As Double
'
' Function provided on the web seems to have starting problems and uses complex Nos where I only need real
' This version solves for the upper branch of the solution only
'
' It uses standard Newton iteration
'
' The starting value is log(x+1) which is within 40% of target from -0.33 < x < 10^100
'
' Results are accurate to about 9dp from -0.3678 < x < 10^100
'
' Values for second Branch are not computed ie -0.36788... < x < 0, W(x) < -1
'
' There is some potential to reduce iterations for smaller +ve x values
' but may need to increase as x approaches -1
'
' RB Jordan 3/2/2015
'
Dim xTry As Double
Dim Iter As Integer
If x < -Exp(-1) Then
myLambert = CVErr(xlErrValue)
Exit Function
End If
xTry = Log(1 + x)
For Iter = 1 To 9
xTry = xTry - (xTry - x / Exp(xTry)) / (1 + xTry)
Next Iter
myLambertW = xTry
End Function

Re: Lambert W function for Excel, work on real and complex number

All:

There is a very nice presentation of the Lambert's W function (W0) and a method to calculate it based upon Halley's method in MATLAB at http://blogs.mathworks.com/cleve/201...e-1773193df571. (Google: The Lambert W Function Cleve Moler if link inactive).