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:

10^{6} 10^{5} 10^{4} 10^{3} 10^{2} 10^{1} 10^{0} **.** 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:

16^{6} 16^{5} 16^{4} 16^{3} 16^{2} 16^{1} 16^{0} **.** 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:

2^{6} 2^{5} 2^{4} 2^{3} 2^{2} 2^{1} 2^{0} **.** 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.

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.

**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 { 13_{24}41_{60}37_{60}}, 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 ofU

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

- The
**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 ans

appended to the unit, but if that is inappropriate then you can specify the single and plural forms, for examplefoot/feet

orpenny/pence

, or you can designate individual units to be the same in single and plural, for examplex $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.

- Each unit may change to appropriate singular or plural forms unless
- 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 CCUEsigma( )

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 (calledconvergents

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

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.

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