Results 1 to 8 of 8

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    8

    Thumbs up Lambert W function for Excel, work on real and complex number

    The Lambert W function (very efficient, fast)

    See, LambertW.bas to get the VB module (attach)

    Input: real or complex values from -infinity to +infinity
    You can verify the results with

    =IMSUM(IMPRODUCT(LambertW(COMPLEX(-100,0)),IMEXP(LambertWc(-100,0))),-1)
    return:
    -100.999999999999+8.5265128291212E-14i

    OR

    =IMPRODUCT(LambertW(-0.1),IMEXP(LambertW(-0.1)))
    return:
    -0.1

    Lots of testing, no error found
    Attached Files Attached Files

  2. #2
    New Member
    Join Date
    Feb 2013
    Posts
    1

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

    Thank you! That has saved me a lot of work!



    Martin/

  3. #3
    New Member
    Join Date
    Mar 2014
    Posts
    2

    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 12:40 PM.

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    8

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

    See: http://www.had2know.com/academics/la...alculator.html

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

  5. #5
    New Member
    Join Date
    Mar 2014
    Posts
    2

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

    Aha, that all makes sense. Thank you.

    Please don't spend any more time on my account - now the dual correct answers are apparent I can work with that.

    Thanks again.

  6. #6
    Registered User
    Join Date
    Feb 2015
    Posts
    1

    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

  7. #7
    New Member
    Join Date
    Dec 2015
    Posts
    1

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

    Adapted VBA Code is attached
    Attached Files Attached Files

  8. #8
    New Member
    Join Date
    Oct 2022
    Posts
    1

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

    This is a very old thread, but there's some great code in it! However, all these formulae apparently work on the principal branch. I need a formula that will work on the -1 branch (as well as the principal branch). Is there such a thing?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width