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 ) ) ).