Subscribe via feed.

Solution in Excel

Posted by ragknot on June 25, 2015 – 2:12 pm

Equation is … X=10+Log(20*X)
Loop 1 is =10+LOG(20*D4) the D4 is the guessed number above it. (Cell D4 where I guess 100)
Eash loop uses X for the number above it and before 20 loops it stops changing.
Guess a X 100 Changed =
Loop 1 13.30102999566400000000 -86.698970004336000
Loop 2 12.42491526851930000000 -0.876114727144710
Loop 3 12.39532343141620000000 -0.029591837103039
Loop 4 12.39428785897010000000 -0.001035572446172
Loop 5 12.39425157414140000000 -0.000036284828671
Loop 6 12.39425030272310000000 -0.000001271418261
Loop 7 12.39425025817260000000 -0.000000044550490
Loop 8 12.39425025661160000000 -0.000000001561048
Loop 9 12.39425025655690000000 -0.000000000054699
Loop 10 12.39425025655500000000 -0.000000000001917
Loop 11 12.39425025655490000000 -0.000000000000068
Loop 12 12.39425025655490000000 0.000000000000000
Loop 13 12.39425025655490000000 0.000000000000000
Loop 14 12.39425025655490000000 0.000000000000000
Loop 15 12.39425025655490000000 0.000000000000000
Loop 16 12.39425025655490000000 0.000000000000000
Loop 17 12.39425025655490000000 0.000000000000000
Loop 18 12.39425025655490000000 0.000000000000000
Loop 19 12.39425025655490000000 0.000000000000000
Loop 20 12.39425025655490000000 0.000000000000000


This post is under “Tom” and has 2 respond so far.
If you enjoy this article, make sure you subscribe to my RSS Feed.

2 Responds so far- Add one»

  1. 1. Zorglub Said:

    The iterative method that you propose is called ‘The Fixed point iteration’

    https://en.wikipedia.org/wiki/Fixed-point_iteration

    But beware, there are situations where it fails miserably. For example, the equation that you consider is X=10+Log(20*X). If you solve this equation for the X inside the Log you will get X = (1/20) * 10^(X-10). Now, take your Excel spreadsheet and pick an initial guess of 12.39425. You will see that the method fails…

  2. 2. Chris Said:

    Hi Zorglub. For that inverted equation you get:
    X = 5.0000000000575646273258452479962*10^-12

    The initialiser X = 0 does the trick.

    I showed Ragknot the algorithm (in a private email) several years ago. I wish that I hadn’t. At the time he really did want to know how to find X (for the related Colebrook-White equation). He is/was some sort of computational support man to civil engineers.

    Since then I have given him a fantastic fourth order algorithm. But he quibbles because it doesn’t get the very last available digit that Excel supports. The fact that it needs a maximum of three call to Log doesn’t seem to register on him at all. He doesn’t seem to understand rounding errors either.

Post a reply




PHP Warning: PHP Startup: Unable to load dynamic library 'C:\Program Files (x86)\Parallels\Plesk\Additional\PleskPHP5\ext\php_mssql.dll' - The specified module could not be found. in Unknown on line 0 PHP Warning: PHP Startup: Unable to load dynamic library 'C:\Program Files (x86)\Parallels\Plesk\Additional\PleskPHP5\ext\php_pdo_mssql.dll' - The specified module could not be found. in Unknown on line 0