- Write down the numbers 1-30
- For each number do the following:
- If the number is 1 then STOP
- If the the number is even then divide it by 2
- If the number is odd then multiply it by 3 and add 1
For example:
1 - STOP
2 - even so divide by 2; 1 - STOP
3 - odd so x 3 +1 = 10; even so /2 = 5; odd so x3 + 1 = 16; even so /2 = 8; even so /2 = 4; even so /2 = 2; even so /2 = 1 - STOP
and so on.
This kind of repeated operation is called an iteration. The operation is repeated until a certain value is reached.
Most numbers divide out to 1 fairly quickly, though 27 does go on growing for some time. The problem for mathematicians is whether all numbers divide out to 1: are there 1 or more numbers which go on growing and never divide out to 1? A spreadsheet cannot prove whether this happens or not because it can work out answers for only a finite number of values - up to 16384 in the case of Excel assuming one value per row. However the spreadsheet does take the drudgery out of repeated calculations and it may provide some insight into the way numbers behave and the patterns they may create.
To create the sequences of numbers from the calculations described we do the following in Excel:
- Create a sequence of numbers 1..30 in the A column
- Insert an IF function in cell B1 as follows: IF(A1>1,IF((MOD(A1,2)=0),A1/2,A1*3+1),0)
We need to break this down to understand it. There are two nested IF functions; the first tests whether the number in the cell is greater than 1; the second tests whether the number in the cell is even or odd. The first IF function is as follows:
=IF (A1>1, … ,0)
That is, if the number in the cell is greater than 1 then go on to the first action; if the number in cell A1 is <1 then take the second action which is to output zero to the cell (we could leave it at 1- 0 is easier to read).
The second IF function, embedded into the first, is followed if the contents of the test cell are greater than 1. This IF function is as follows:
IF((MOD(A1,2)=0),A1/2,A1*3+1)
The condition for this IF function is: (MOD(A1,2)=0). The MOD function finds the remainder when the first of its values (A1) is divided by the second (2). This effectively tests whether a number is odd or even as even numbers divide by 2 with remainder zero while odd numbers do not. For example MOD(5, 2) returns 1 while MOD(8, 2) returns 0. The test is whether the result of MOD(A1, 2) is zero. If it is zero the number is even and A1/2 is output to the cell; if it is not zero then the number is odd and (A1 * 3 + 1) is returned to the cell.
Having typed this formula into one cell (B1) we complete the spreadsheet as follows:
- drag the copy marker of cell B1 down to row 30;
- drag the copy marker of the range thus selected into the columns to the right, up to column DH.
 |
|
There are 256 columns and 65,536 rows in Excel so you can generate a lot of numbers if you drag-copy to the edges of the worksheet - in this case up to 16,711,680 numbers (that is 255 columns x 65,536 rows - discounting column A). You will, of course, have to extend the sequence of numbers in the A column to the same row as the IF function. For now be moderate - extend the series into row 30 and column DH27 - where 27 divides out to 1.
To show the maximum number of values on screen click the select all panel (Ctrl/A on the keyboard) and double click any cell border to set the width of all columns to the best fit.
Once you have found the numbers you can select any group of them and produce a chart (see below). This does not provide any further insight into the pattern produced though in other cases producing a chart may well reveal patterns which lie hidden in the raw values. The X axis represents the columns on the spreadsheet while the Y axis represents the rows.
The selection of cells here is A1 to M10 and the type of chart chosen is Line. Excel initially produces coloured lines to distinguish between each data series but you can change this, especially if you have to output to a black and white printer. To format a line proceed as follows:
- make sure the chart is selected
- point at the line and click the right mouse button.
- select Format Data Series from the pop-up list
- Change the style, weight and colour of the line and the symbol as you require.
It is possible to distinguish between only a limited number of lines in a chart before you run out of options. Try not to overload a chart with too much data otherwise it will become difficult to interpret and not worth the bother.
The second chart includes the sequence for the number 27 which does not divide out to 1 until the 111th iteration. (To find this value create a sequence 1, 2, 3… starting at cell B28; this provides a counter to the iterations which is easier to use than DF, DG, DH, etc. the row column headers). This chart is dominated by the iterations for 27 because there are so many of them and they reach much higher values than any of the other numbers. You cannot see any of the other series in detail due to the scale.
While the chart is active in Excel you can read off the values of each marker on a data series by pointing at it and holding the mouse steady for a moment. The highest value reached while iterating on 27 is 9232.
Use the Back Button to Return to the previous page
