Created by Dr. Irv Bromberg, University of Toronto, Canada
[Click here to go back to the Symmetry454 / Kalendis home page]
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:
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
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 18.104.22.168 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.
I found that the CCUE algorithms are rather inefficient, re-using the listed bases many more times than is strictly necessary, and using recursion and passing of lists to implement the
to-radix( ) function.
In LISP it is natively simple to pass growing lists between functions, but I don’t know of way to do this in Excel VBA. In the VBA FromRadix( ) function, I found that if execution always processes the radix values from the radix point to the left and then from the radix point to the right then each base need only be accessed once, with the accumulating multiplier or divider carried forward to the next radix value. The CCUE lists of radix values always omit the radix point (semicolon), but I found that the correctly positioned radix point is mandatory for unambiguous conversion of a list of radix values back to a number, in particular if leading and/or trailing zeros were trimmed from the list of radix values.
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.
D) As Variant
sappended to the unit, but if that is inappropriate then you can specify the single and plural forms, for example
penny/pence, or you can designate individual units to be the same in single and plural, for example
x $100/x $100.
#(hash) then a numeric suffix will be prefixed to the unit (st, nd, rd, th, and so on), without plurals.
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).
Nthprefix is unfortunately required to avoid confusion with worksheet built-in functions, and for consistency.
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.
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.
Syntaxworksheet 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).
Syntax worksheet details the function parameters and how to invoke the functions.
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.
Mixed worksheet contains general examples of mixed radix conversions.
Single worksheet contains examples of interconversions using a single base.
Web worksheet contains titles and URLs of world wide web resources concerning mixed radix conversions.
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)