Finding square roots …

… to 100 decimal places.

Why would anyone want to do that?

To solve Project Euler Problem 80:

It is well known that if the square root of a natural number is not an integer, then it is irrational. The decimal expansion of such square roots is infinite without any repeating pattern at all.

The square root of two is 1.41421356237309504880…, and the digital sum of the first one hundred decimal digits is 475.

For the first one hundred natural numbers, find the total of the digital sums of the first one hundred decimal digits for all the irrational square roots.

Michael at Daily Dose of Excel tracked down a method of finding square roots that requires only addition and subtraction of integers:

Square Roots by Subtraction, by Frazer Jarvis

Initial step
Let a = 5n (this multiplication by 5 is the only time when an operation other
than addition and subtraction is involved!), and put b = 5.

Repeated steps
(R1) If a is greater than or equal to b, replace a with a − b, and add 10 to b.
(R2) If a is less than b, add two zeroes to the end of a, and add a zero to b just before
the final digit (which will always be ‘5’).

Then the digits of b approach more and more closely the digits of the square
root of n.

Implementing this very simple algorithm in VBA was complicated by the lack of a very long integer, so instead I set up two arrays a little bigger than the number of required digits, to store a and b, with one digit in each array element.  The algorithm above was then iterated until all the required decimal places had been filled, then the array was converted to a string for display in the spreadsheet (or for the Project Euler problem the sum of the digits was found with a simple loop).

A spreadsheet with the VBA code for the SqRtI() function may be downloaded from here

The screen shot below shows the function returning the first 100 digits of the square root of 3, together with a spreadsheet implementation of the algorithm (which is limited to the 15 digit precision available in Excel).

100 digits of root 3

100 digits of root 3

This entry was posted in Arrays, Excel, Maths, Newton, UDFs, VBA. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s