Created by Dr. Irv Bromberg, University of Toronto, Canada ## Introduction to Mixed Radix Conversions

In standard numeric positional notation the same base is used for all positions, for example in base 10 (decimal) notation the positions have these values:

106 105 104 103 102 101 100 . 10-1 10-2 10-3 10-4, and so on as necessary in both directions from the decimal point.

In hexadecimal, the positions have these values:

166 165 164 163 162 161 160 . 16-1 16-2 16-3 16-4, and so on as necessary in both directions from the hexadecimal point.

In binary, the positions have these values:

26 25 24 23 22 21 20 . 2-1 2-2 2-3 2-4, and so on as necessary in both directions from the binary point.

By contrast, in mixed radix number systems the bases used may vary between positions, but each base is a multiple of the next lower base, and each base is a positive integer equal to 2 or more. Here are some typical applications:

• Division of a moment into day and time units (bases), such as quarters (4), weeks (13), days (7), hours (24), minutes (60), seconds (60), and milliseconds (1000) — note that calendar years are excluded if they aren’t an integer multiple of equal quarters.
• Resolving the mean year of a solar cycle (or the mean year fraction in excess of 365 days) or the mean month of a lunar cycle (or the mean month fraction in excess of 29 days) into days, hours (24), minutes (60), seconds (60), and exact fraction of a second:
• Start by setting the least base equal to the number of years or months per cycle.
• Then convert the mean year or month to mixed radix values and reduce the least fraction if possible.
• If the least fraction reduced then use its reduced denominator as the final least base.
• Accounting for the number of prior elapsed leap days in a calendar, such as the Gregorian or Icelandic calendar, to use in converting a calendar date to an ordinal day number, such as the rata die for the purposes of calendrical calculations.
• Conversion of an ordinal date to or from the corresponding Mayan long count calendar date.
• The traditional calculations of the Tibetan Calendar and the movement of Sun, Moon, and visible planets, are all based on extensive use of mixed radix conversions and arithmetic operations involving mixed radices.
• Angles:
• Conversion from an angle to degrees (360), arcminutes (60), arcseconds (60), and milliarcseconds (1000), or the converse.
• For angles from historical sources such as Ptolemy or Mainmonides: conversion from an angle to degrees (360), arcminutes (60), arcseconds (60), arcthirds (60), and arcfourths (60), or the converse. This differs from sexigesimal (base 60 for all positions) because the degrees have base 360.
• Division of quantities into US Customary Units, for example Length, Fluid Volume, Dry Volume, Weight, Cooking Measures...
• Unusual number systems:
• Factorial number system, where each base is the next sequential integer value.
• Primorial number system, where each base is the next sequential prime number.
• Fibonorial number system, where each base is the next sequential Fibonacci number.
• Conversion of a monetary amount into a unique minimum number of currency bills and coins (recall that each base must be a multiple of the next lower position, which will probably exclude certain bills and coins, for example a \$50 bill is excluded if the next lower base is \$20, or a 10-cent coin is excluded if the next higher coin is 25 cents).

Chapter 1, page 27, section 1.10 of the book Calendrical Calculations: The Ultimate Edition (hereinafter abbreviated as CCUE), by Edward M. Reingold and Nachum Dershowitz, published by Cambridge University Press in 2018, introduced the arithmetic of mixed radix conversions, defining to-radix( ), from-radix( ), sigma( ) and round( ) functions in their corresponding LISP program code, and giving very limited examples of their use. Later in the book the authors applied mixed radix conversions to alternative Gregorian calendar arithmetic, as well as to limited use in the Icelandic, Hebrew, and Mayan calendars.

Because the CCUE description of mixed radix conversions was so terse and included few examples, I found it very hard to understand, and so set about learning it by implementing a set of functions to experiment with. I selected the Visual Basic for Applications (VBA) computer programming language (open source) in the Microsoft Office Excel developer environment.

Click here to download the Excel workbook Mixed-Radix-conversions.xlsm 239 KB

The workbook file is in Microsoft Excel’s macro-enabled XML file format (.xlsm extension). A macro is computer code written in Excel’s Visual Basic for Applications (VBA) programming language. Depending on your security settings, Excel may prompt you to allow enabling of macro execution, which is necessary for the examples to work. XML is the native file format for Excel 2007 and later. I tried saving the workbook in the older XLS format, but the file size was about 3 times larger. I have tested this workbook in Excel 2003 and 2007 under 32-bit Windows XP, in Excel 2013 and 2016 under 64-bit Windows 10, Excel for macOS 2011 version 14.7.7 under the last version of macOS High Sierra, Excel for Mac 16.57 under macOS Monterey 12.2, LibreOffice Calc 6.0.7.3 under Linux Mint Uma, and x64 LibreOffice Calc 7.2.5 under Windows 10. To open the file in versions of Excel prior to 2007 you need the XML file converter plug-in from Microsoft — failing that, contact me to make the older XLS version available. The steps required to enable VBA macro execution differs in these various environments — search the internet for instructions.

LibreOffice Calc natively opens the .xlsm file, but for the examples to work you need to enable execution of macros once only as follows: from the Tools menu choose Options..., then under LibreOffice...Security click on the Macro Security... button, then choose the Medium option (in which case upon each file open Calc will ask the user for permission to enable macro execution) — the default is probably High or more, which won’t allow execution of macros, and won’t ask. If you make changes in Calc then use the Save As command to save the file in Open Document Spreadsheet format (.ods), and there will be no going back to Excel. Although I’ve only tested LibreOffice Calc under Linux and Windows, I expect it to work with this spreadsheet on any supported platform. An annoyance is that Calc shows all VBA function names in UPPERCASE in worksheet cell formulas. The similar program, Apache OpenOffice Calc, might work as well — if you have OpenOffice then please test it and let me know. If CALC asks This file contains links to other files. Should they be updated? then click the No button, because there aren’t any external links.

The workbook include a few examples where Hebrew units are applied to radix values. Excel for Windows or Mac displays these properly, but LibreOffice Calc puts the highest radix value leftmost (instead of rightmost) and the highest radix unit rightmost. The lesser radix values and units do display properly in Calc.

The first time that you open the workbook in Excel for Mac, you might see #NAME! in the VBA-calculated cells for several minutes (during this time you may notice the Visual Basic editor window flickering), then they will all display properly. After that, save the workbook, and from then on when you open the workbook all cells will display promptly. There is an anomaly in Excel for Mac: the ApplyUnits( ) function uses a Windows non-breaking space = Chr\$(160) as delimiter between each radix value and its unit, but Excel for Mac displays this as an annoying dagger symbol (†). The beginning of the VBA code offers the mid-dot or raised dot as an alternative = Chr\$(183), but Excel for Mac displays that as a Greek uppercase sigma character (Σ). Until a better alternative is found [a normal space = Chr\$(32) won’t work properly], you might prefer to set `Const DelimiterCode As Integer = 95` (underscore _) near the top of the VBA module — the first recalculations after changing that may take several minutes, during which Excel may be unresponsive and its windows may flicker annoyingly.

### How do these VBA functions differ from the CCUE functions?

• ToRadix( Bases As String, TheNumber As Variant, Optional ShowBases As Boolean = False, Optional Rounding As Variant = 0 ) As String:
• The Bases list is comma delimited, optionally enclosed in [ ] or ( ) or { } brackets. Spaces are ignored.
• The Bases list can include one radix point, indicated by a semicolon instead of a comma delimiter. Bases to the left of the radix point refer to the integer part of TheNumber and bases to the right of the radix point refer to the fractional part or decimal points of TheNumber.
• If only a single base is specified then a radix conversion will be returned, and if the base is <=36 then numbers and, if necessary, letters will be employed to represent the digits without any delimiters or brackets in the result. For example, {2} will convert the given number to a binary string, or {16} will convert it to a hexadecimal string. With a single base, if the given number has a fractional part then the radix converted result will have a radix point and an appropriate number of digits to its right.
• TheNumber is declared as Variant because it is allowed to contain a Double precision number or a String representing a mixed, proper, or improper fraction (spaces ignored).
• If TheNumber is negative then each of the radix values will be negative, or, in the case of a single base with symbolic rendering a single minus sign leftmost.
• This function will always return { } brackets around the result list (except in single base radix conversion mode), so it's a good idea to use different brackets for the list of bases.
• The optional ShowBases = True switches on the showing of base values alongside the radix values. I realize that traditionally these are rendered as subscripted numbers, for example for a mixed radix conversion of a moment to separated time values the bases would be shown like { 132441603760 }, but VBA can’t return subscripted values between functions or back to worksheet cells, so I implemented the showing of plain text /bases like { 13/24, 41/60, 37/60 }. See also the ApplyUnits( ) function described below. In single base mode ShowBases = True will cause this function to return the converted radix values as comma-delimited base 10 number groups.
• The optional Rounding variant parameter controls rounding of the least fractional radix value, with choices of U = Unrounded, T = Truncated (floored), R = rounds the least fractional radix value to an integer and then reduces it (if possible, using its base as denominator) returning a fraction, or the digit zero (the default) or higher digit specifying the number of decimal points to round the least fractional radix value to. The CCUE to-radix( ) function allows the fractional part of the given number to be separately passed as an optional parameter — I tried that in the VBA ToRadix( ) function and it did very slightly improve deep decimal point precision, but I abandoned that feature because it’s easy to obtain the desired performance by controlling the type of rounding or truncation (or neither) to be applied to the least fractional radix value.
• Instead of using the Rounding option in the ToRadix( ) function, you can first apply the RoundToLeast( Bases as String, x As Double, Optional Decimals As Integer ) As Double function to the number, which rounds to the integer value of the least base or optionally a specified number of decimal points for the least radix value.
• If desired, use the TrimZeros( ) function to remove leading and trailing zeros from the list of radix values returned by the ToRadix( ) function.
• After a single base conversion, you can use the SpaceEvery( Interval As Integer, TheString As String) As String function to insert spaces at specified intervals left and right of the radix point, for example to group nybbles and bytes in binary strings, or bytes and words in hexadecimal strings. TheString will be returned unchanged if it contains one or more comma delimiter(s).
• FromRadix( Bases As String, Radices As String, Optional FractType As String = D ) As Variant
• Converts the given radix values to a base 10 number using the given radix bases.
• The Radices string may contain one radix point (semicolon), which can be anywhere in the string including the beginning or end, provided that it correlates with the position of the radix point in the given Bases list.
• If leading zeros were suppressed by ToRadix( ) and if the Radices string contains values that correspond to any fractional bases then they must be preceded by a radix point ( ; ), as would have been originally returned by the ToRadix( ) function call.
• By default the fractional part, if any, is returned as decimal points, but the user can specify other fraction types (as used for many of the Tibetan calendar examples in the workbook) by passing FractType as one of these codes: P = Proper fraction, I = Improper fraction (will return as Proper if the fraction’s Numerator is less than its Denominator), M = Mixed fraction (will return as Proper if Whole part is zero), D = (default) Decimal fraction.
• When returning a Whole number with or without a Decimal fraction the return type is Double, otherwise the return type for fractions is String.
• The ApplyUnits( List As String, Units As String, Optional Plurals As Boolean = True, Optional SkipZeros As Boolean = False ) As String function is convenient for applying units alongside radix values.
• Each unit may change to appropriate singular or plural forms unless Plurals = False. The default plural form simply has an s appended to the unit, but if that is inappropriate then you can specify the single and plural forms, for example foot/feet or penny/pence, or you can designate individual units to be the same in single and plural, for example x \$100/x \$100.
• If the unit starts with the special symbol # (hash) then a numeric suffix will be prefixed to the unit (st, nd, rd, th, and so on), without plurals.
• If TrimZeros( ) was used to remove leading and/or trailing zeros then ApplyUnits( ) will skip the unused units.
• If SkipZeros = True is passed then all radix values of zero will be omitted from the returned result, but because the positions are undefined for the remaining radix values after skipping zeros it won't be possible to use FromRadix( ) on the radices with units to recover the correct number — in such a case use the original radices including zeros to recover the correct number, if required.
• Passing bases as the List is convenient for confirming their sequence, meanings, and maximum counts.
• The nth( ) function employed by CCUE is natively built into LISP, counts items from zero, and is non-destructive (it doesn’t change the original list). I wrote a non-destructive VBA Nth( List As String, Optional ItemNo As Integer, Optional RemoveSlashBase As Boolean = True ) As Variant function, which allows passing a positive item number to return that value counting from the left of the list of radix values or a negative item number to return the value found by counting from the right (ItemNo = -1 is particularly useful for fetching the least radix value without needing to know the number of items in the list), or zero or omit the item number to return the count of items in the list. If the specified item number is out-of-range (doesn’t exist) then Nth( ) returns zero. By default, /bases are removed, returning a Double number, but if RemoveSlashBase = False and there is a /base then it returns the fraction as a String (text).
• A set of LISP-like functions indirectly invoke the Nth( ) function:
• NthFirst( ), NthSecond( ), NthThird( ), and so on up to NthTenth( ) to return the first through tenth item in the specified List, respectively.
• To return the least (rightmost) value in a list there is NthLeast( ) and simply Least( ).
• The Nth prefix is unfortunately required to avoid confusion with worksheet built-in functions, and for consistency.
• All of these functions that indirectly call the Nth( ) function also accept and pass on the optional RemoveSlashBase parameter.
• Given an integer numerator and denominator, the ReduceFraction( Numerator As Double, Denominator As Double, Optional ImproperFraction As Boolean = False, Optional compact As Boolean = True ) function will form a string expressing the fraction, if possible reducing Numerator and Denominator (which must be integers, but are declared as Double to extend their numeric ranges). When called programmatically, Numerator and Denominator are passed by reference, so reductions are also numerically returned to the calling program. There is also the similar function ReduceFractStr( Fraction As String, Optional ImproperFraction As Boolean = False, Optional compact As Boolean = True) As String, which accepts a text fraction (mixed, proper, or improper) instead of a numerator and denominator, and returns a reduced fraction string (text).
• The parts of a fraction or mixed fraction can be extracted from the string returned from ReduceFraction(), ignoring all spaces:
• Whole( ) returns the whole part that is in front of the fraction, or zero if there is none.
• Numerator( ) returns the numerator, or zero if there is none.
• Denominator( ) returns the denominator, or one if there is none.
• The SigmaX( List1 As String, List2 As String) As Double function is like the CCUE sigma( ) function: given two comma-delimited numeric lists containing the same number of elements, they return the sum of the products of the corresponding elements. If one list has fewer elements than the other then it will first be padded on the left with an appropriate number of zeros. I didn’t like the CCUE function name because it only implied addition.
• The function ContinuedFraction( TargetValue As Double, Optional MinConvergent As Integer = 2, Optional MaxConvergent As Integer = 12, Optional ErrorLimit As Double = 0.01) As String uses the continued fraction method to find a series of progressively better fractional approximations (called convergents) to the given TargetValue. The search terminates when the a convergent’s parts-per-trillion error is less than the ErrorLimit or when it reaches the specified maximum convergent. The final mixed or proper fraction is returned as a text string. If it returns a mixed fraction that you don't want then pass the inverse of the TargetValue or pass the fractional result to ReduceFractStr( ) with the ImproperFraction flag = True. If the fractional result has an absurd number of digits then try specifying a lower MaxConvergent or a greater ErrorLimit.
• Errors: When there is something wrong with the passed parameters, Excel VBA returns #VALUE! to the cell, which doesn’t inform the user what went wrong nor help with troubleshooting. LibreOffice Calc, however, displays a helpful error message dialog box that the user has to dismiss — usually you can continue using the workbook without apparent problems.
• I found it useful or necessary to implement several additional support functions that are documented on the Syntax worksheet of the Excel workbook, as well as several other CCUE functions, including: amod(x,y), modulus(x,y), floor(x), ceiling(x), sign(x), IntervalModulus(x,a,b), quotient(x,y), GCD(x,y), and LCM(x,y).

### Worksheets in the Workbook

The Syntax worksheet details the function parameters and how to invoke the functions.

The Calendar worksheet focuses on examples related to calendars, cycles, mean year or month, time, angles, and so on. Despite the easily encountered limitations of double precision floating point calculations in VBA, I found that mixed radix conversions are especially useful for converting a number with decimal points to the exact time duration of a solar or lunisolar calendar mean year, or lunar cycle mean month. The version of Excel that I used for some of this work was the 64-bit 2016 release in Windows 10, which supports the new 64-bit LongLong integer type (compare with the 32-bit Long integer type) that might have been useful in cases where floating point imprecision let me down, but then the least radix value couldn’t contain decimal points and users of the 32-bit versions of Excel would be unable to use this work.

The Mixed worksheet contains general examples of mixed radix conversions.

The Single worksheet contains examples of interconversions using a single base.

The Web worksheet contains titles and URLs of world wide web resources concerning mixed radix conversions.

### Please Provide Feedback for this Project

I would really appreciate receiving comments, bug reports, and suggestions about this work, as well as further examples to add to the demo collection and useful URLs to add to the Web worksheet in the Excel workbook.

Updated 5 Adar Sheini (Traditional) = 5 Adar Sheini (Rectified) = Mar 2, 2022 (Symmetry454) = Mar 4, 2022 (Symmetry010) = Mar 8, 2022 (Gregorian)