Demonstrating Kaprekar's constant with Excelsior

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.

The idea

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

Program structure

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.

The tables


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