It is a very interesting exercise to calculate some of the most important constants. The task is wonderfully pure:
the answer is completely useless and all the fun is in fiddling about with the code. You can check that your answer is
100% correct. You can time the calculation and test the efficiency of your code. This is something that clearly appeals
to a fair number of people judging by the variety of excellent sites on the internet on the subject. I have not seen anyone
else using Excel to calculate these constants, probably because it is too slow.
All three calculations are done by adding a series. Pi is the most complicated as it involves two series and the alternate terms are positive and negative. The time taken depends on the number of terms we have to calculate (ie how fast the series converges) and how difficult it is to calculate each term.
|Number of terms at 50,000 decimal places||46,288||10,513||13,528|
|Time in seconds for 50,000 decimals||1,401||356||288|
|Number of series||2||1||1|
|Divisions per term||2||2||1|
|Multiplications per term||0||1||0|
Pi takes far longer to calculate than root 2 or e because the series used converges more slowly. Although e needs more
terms to be calculated than root 2, each one is easier resulting in a faster overall time.
No patterns in the digits
Every term in the series is a fraction and there is a pattern in its decimals. The final result is a sequence of digits with no pattern. Although I can see why this is (ever more patterns get piled on top as you increase the number of decimals) there is something rather fascinating about it.
Here is a graph showing how long the calculations took on my laptop.
Probably the most interesting thing about the whole exercise is changing the code and looking at what effect it has on
the times. My first attempt at the pi code was very cumbersome and it involved moving numbers from one row of a working array
to another between calculating individual terms. When I managed to eliminate the need to move the numbers, it had only a small
effect on the overall times.
I started by having one digit per element of the array. Increasing this to two and then four digits per element gave a major reduction in the calculation times. The "skip factor" I introduced also resulted in a big improvement. The later terms in the series get smaller and smaller. The skip factor jumps over all the initial zeros and moves the calculation straight to the digits that matter.
The biggest improvement came when my brother, who is fluent in C, looked at my code. He was horrified at the very inelegant way my division code was picking up the next digit. The big number was stored in a working array called "works". The offending line of code was:
working = Val(Str(carry) & Str(works(0, col))) 'Read the next digit.
I had calculated pi to 10,000 decimal places this way with complete accuracy. But it was appallingly inefficient to handle numbers using a text function. I immediately changed it to:
working = (carry * base) + works(0, col) 'Read the next digit.
This one change made it do the same calculations in a seventh of the time.
Have a play with the code and see what effect it has on the running speed!
I am sure there is plenty of scope to get this code to run faster but if you want to enter the pi speed stakes, I don't think Excel is suitable.
|Download pi_root2_e.zip (38kb)|
|Xavier Gordon gives an excellent description of the mathematical constants and how to calculate them.|
|I also like this account of how to calculate pi by Ron Knott.|