This page, and the spreadsheet generated from it, demonstrates Kaprekar's constant, and was inspired by Jeff Lutes's posting to the Microsoft Excel Developers List on 10th July 2012. I wrote it using my Literate Excelsior spreadsheet generator. This also generated the HTML page which you are reading now, and which documents the program. Program text is on a grey background: the rest is commentary. To use the spreadsheet, type a four-digit number into cell A2.
In Jeff's words, the process is this:
Choose a 4-digit number that does not contain only 1 digit (i.e.,
5555, 6666, etc. are out).
Write the number down in descending value.
Write the number down in ascending value.
Subtract the two.
Repeat steps 2-4.
At some point you will get the number 6174. If you repeat steps 2-4 you
will again get 6174.
1000 - 0001 = 0999
9990 - 0999 = 8991
9981 - 1899 = 8082
8820 - 0288 = 8532
8532 - 2358 = 6174
7641 - 1467 = 6174
I'm laying the spreadsheet out in columns. Each row corresponds to one iteration, and I shall define a constant called max_iterations to give the depth of each column:
type column = 1:max_iterations. constant max_iterations = 20.I hope this will be big enough.
Briefly stated, the columns hold the following
tables:
the original number;
its digits, calculated by converting it to
text and extracting a substring;
the digits in ascending order, calculated by
using the function SMALL, which returns the k-th smallest value in
an array or range;
the number in ascending order, calculated by
concatenating these;
the number in descending order;
their difference. I feed this back
into the first column.
number[1] holds the original number. number[n], where n>1, contains the absolute value of difference calculated in the n-1'th iteration. I made it absolute because I hit problems with the minus sign otherwise.
table number : column -> general. number[ 1 ] = 4973. number[ n>1 ] = abs( difference[ n-1 ] ).
number_as_text[n] is number[n] converted to text.
table number_as_text : column -> text. number_as_text[ n ] = text( number[n], "0000" ).
digits is a four-column table. The d'th column holds the d'th digit of number.
type digit_range = 1:4. table digits : digit_range column -> general. digits[ d, n ] = value( mid( number_as_text[n], d, 1 ) ).
digits_ascending is also a four-column table. The d'th column holds the d smallest digit.
table digits_ascending : digit_range column -> general. digits_ascending[ d, n ] = small( digits[all,n], d ).
number_ascending[n] concatenates the digits from digits_ascending[n], giving us them as a single number.
table number_ascending : column -> general.
number_ascending[ n ] =
value( digits_ascending[ 1, n ] &
digits_ascending[ 2, n ] &
digits_ascending[ 3, n ] &
digits_ascending[ 4, n ]
).
Similarly, number_descending[n] is a single number with the digits in descending order.
table number_descending : column -> general.
number_descending[ n ] =
value( digits_ascending[ 4, n ] &
digits_ascending[ 3, n ] &
digits_ascending[ 2, n ] &
digits_ascending[ 1, n ]
).
difference[n] is the difference between number_ascending[n] and number_descending[n].
table difference : column -> general. difference[ n ] = number_ascending[ n ] - number_descending[ n ].
This arranges the tables from left to right as named below, and puts an automatically-generated heading above each giving its name.
layout( 'Sheet 1'
, rows( heading
, row( number as y
, number_as_text as y
, digits as xy
, digits_ascending as xy
, number_ascending as y
, number_descending as y
, difference as y
)
)
).