Fractional Arithmetic

It is not an uncommon task to use numbers whose fractional value (the value to the right of the decimal point) represents something other than a decimal value. For example, you may want to enter 5.03 to mean 5 feet, 3 inches rather than 5.03 feet. If you are to use this value in calculations, you must convert the number to an ordinary decimal number; that is, you need to convert 5.03 (representing 5 feet 3 inches) to the value 5.25 (representing 5.25 feet). After the calculations have been made, you will need to convert it from decimal back to fractional. For example, 5.03 (feet and inches) times 2 equals 10.5 feet, or 10.06 feet and inches. There are two ways to perform the required conversions. The first is to use the DOLLARDE and DOLLARFR functions in the Analysis ToolPak, and the second method is to write conversion functions using built in functions. Using DOLLARDE and DOLLARFR functions result is shorter, clearer, and more easily maintained formulas, but these function have the drawback that they do not support array formulas. The formulas built using built in functions are longer and more complicated (and thus harder to maintain), but they do support array formulas. (See this page for more information about array formulas.)

You can download a workbook containing the formulas on this page.

The DOLLARDE and DOLLARFR functions take their names from their original purpose, converting stock prices expressed as 1/8 of a dollar to values that can be used in calculations. The functions, however, are not restricted to conversion of stock prices. They can be used for any non-decimal value, such as feet and inches, cases and bottles, or dozens and doughnuts. The basic formula syntax is as follows:

=DOLLARDE(Value,Base)

Where Value is the value to be converted (e.g., 5.03) and Base is the whole-number base for the conversion (e.g., 12 for inches). The DOLLARDE function converts the value to a decimal number. If Value is 5.03, DOLLARDE returns 5.25. Now, 5.25 can be used in any normal calculation. Note that it is very important to include the '0' in the fractional part of the value. Using the same example, if you used 5.3 rather than 5.03, the DOLLARDE function would treat that as 5 feet, 30 inches rather than 5 feet, 3 inches. The number of digits to the right of the decimal place in the Value parameter must be equal to =TRUNC(LOG10(Value),0)+1. Once you have converted the fractional number to decimal values, you can use them in normal calcuations such as a SUM function. As noted earlier, the DOLLAR functions cannot be used in array formulas. That means that you cannot convert a series of fractional values in one formula. Put another way, the following formula will not work.

=SUM(DOLLARDE(A1:A3,12))

Instead, you must string together a number of separate DOLLARDE functions, as shown below:

=SUM(DOLLARDE(A1,12),DOLLARDE(A2,12),DOLLARDE(A3,12))

This is cumbersome and difficult to maintain. If you must use array formulas, you can use the formulas described in the next section.

The DOLLARFR function is the inverse of DOLLARDE function. While DOLLARDE converts fractional values to decimals (e.g., 5.03 to 5.25), the DOLLARFR function takes a decimal value back to its fractional value (e.g., 5.25 to 5.03). The syntax of the DOLLARFR function is the same as the DOLLARDE function. For example, the formula =DOLLARFR(5.25,12) returns 5.03, meaning 5 feet, 3 inches.

As noted earlier, the DOLLAR functions do not support array formulas. If you pass in an array or range as the Value parameter, the functions will return a #VALUE error. The functions described in this section do support array formulas. These examples are written to illustrate calculations of feet and inches so the base conversion factor is 12. In your own formulas, change the occurrences of 12 to the appropriate base value.

The following formula performs the same calculation as =DOLLARDE(A1,12)

=TRUNC(A1,0)+((A1-TRUNC(A1,0))*100)/12

In a similar manner, the formula below performs the same calculation as =DOLLARFR(A1,12):

=TRUNC(A1,0)+(A1-TRUNC(A1,0))*12/100

Since array formulas are supported you can use a formula like the following formula to convert the fractional numbers in A1:A3 to their decimal equivalents, sum those decimal values, and convert the result back to a fractional number:

=TRUNC(SUM(TRUNC(A1:A3,0)+((A1:A3-TRUNC(A1:A3,0))*100)/12),0)+
((SUM(TRUNC(A1:A3,0)+((A1:A3-TRUNC(A1:A3,0))*100)/12)-TRUNC(SUM(TRUNC(A1:A3,0)+ ((A1:A3-TRUNC(A1:A3,0))*100)/12),0))*12/100)

If A1:A3 contains the values 5.03, 5.06, and 5.01, all representing feet and inches, the previous formula would return 15.10, representing 15 feet 10 inches. If you do not need to convert the SUM back to a fractional number, you can use the simpler formula

=SUM(TRUNC(A1:A3,0)+((A1:A3-TRUNC(A1:A3,0))*100)/12)

With the same values in A1:A3 as above, this formula will return the value 15.83, the decimal equivalent of 15 feet, 10 inches. Remember that when you use the array formulas, you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. If you do not array-enter the formula, it will return either an incorrect result or a #VALUE/span> error.

The DOLLARDE function can prove useful when entering a list of times. It is much easier to enter 9.2, for example, than entering 9:20. You can convert the times entered with the period separator to real Excel times with the formula

=DOLLARDE(A1,60)/24

where A1 contains the pseudo-time (with the period rather than the colon). You'll have to manually format the cell for Time rather than a Number.