Change the base of a number

Add-in to convert a number to another base

The download on this page contains three files. The first is an add-in that contains five worksheet functions.

baseChange changes the base of a number. It uses letters eg A = 10, B = 11 etc
Excel provides a number of standard worksheet functions that can change numbers between certain bases:
eg Bin2Dec – converts from binary to decimal

My function can deal with any base (up to 36) and it can handle "decimals" ie digits representing a fraction. (I realise that, strictly, the terms "decimal" and "decimal point" should only apply to base 10, but I am going to use them here for any base.)

When the numbers are not integers, there are some pesky but quite interesting rounding problems. Excel does its calculations in binary and tends to round down a fraction such as 0.5 to 0.49999999999 in base 10. In higher bases, you might want to leave recurring 9s as they are; for example 9/10 in base 11 is 0.999999999. Similarly in base 12, 9/11 should produce the same pattern of recurring 9s. I have had a go at adjusting for some rounding errors but it is not possible to eliminate them all. Chip Pearson describes rounding errors in Excel in more detail.

The baseChange function also makes a modest effort to format the result consistently with the other numbers on the worksheet.

If you convert 8 to binary you get 100. The worksheet treats it as a hundred rather than eight. So you can't add the results in another base (or apply most other functions for that matter). However, the following two functions will work on the results of baseChange.

sumDigits finds the sum of the digits of an integer. It can handle the results of the baseChange function that include letters.

areDigitsAllDifferent says whether the digits of an integer are all different. Again it can cope with numbers that include letters.

decimalRepeatCycle calculates the repeat cycle of decimals for a given integer in a given base. I have included a ceiling in this function after which it stops. You can increase the ceiling in order to find larger repeat cycles but the function will be slower.

sumRepeatingDigits is similar to the previous function but it adds the repeating digits rather than counting them.

Convert a big number to another base

The second file contains two macros. One converts a big number to another base: the other does division in any base. You can set them to handle letters, in which case the maximum base is 36. If you use just numbers, the base can go much higher.

Interesting things to do
In base 10, numbers divisible by 3 have a sum of digits that is divisible by 3. (eg 252 is divisible by 3 and its digits sum to 9). What similar patterns apply in other bases? contains three files:

 a) baseFunction.xla – the add-in described above
 b) baseChange.xls – converts big numbers from one base to another
 c) base examples.xls – gives a few examples of the functions in the add-in
* Download (81kb)