Numbers

Numbers

Doubles

Cells may contain decimal numbers with up to fifteen significant digits and a signed exponent up to three digits. All values are stored in double precision format which requires up to seventeen significant digits to specify uniquely.

The largest positive number that can be returned from a formula is equal to 2^1023*(2-2^-52), although numeric entries are restricted to less than 10^308 when entered as a value.

The smallest positive number is 2^-1022, values lower than this minimum underflow to zero.

In general, all binary floating point numbers have the form:

(-1) ^ S * 2 ^ (E - b) * (i + F * 2 ^ -52)

Standard doubles define the constants

b = 1023 i = 1 S = {0,1} E = {1,..,2046} F = {0, ... , 2 ^ 52 - 1}.

Zero is the case E = 0 i = 1

Special Numbers

Subnormal numbers are defined with b = 1022 i = 0 E = 0 giving a minimum positive value of 2 ^ -1074. These are supported in vba, but inside formulas values are displayed as 0.

Other exceptional values may be returned from a custom function including Infinity 2^1024 and NaN 3*2^1023 which are ordinal values in the sense that comparison operations are defined but arithmetic operations throw an error.

These exceptional values are defined with E = 2047 and have a range up to twice the normalized double. Inf F = 0 and NaN F = 2 ^ 52 - 1 can be returned via 1 / 0 and -(0 / 0) respectively via custom functions.

A negative sign may prefix any value including zero -0 although is not displayed for Infinity or NaN.

Integers

When converting a double to an integer several different methods are applied. Some number arguments in the lookup, date and text category apply a fixed binary format with precision 2^-22 that entails rounding decimals to roughly seven digits and then truncating the result.

Several rounding functions in the Math and Trig category truncate the fifteen significant figure in input arguments. Other functions either truncate the underlying double or else return an error if the input is noninteger.

Dates & Times

Dates are treated as integers, nominally the number of days from 0/1/1900 to a maximum of 31/12/9999 with the year 1900 treated as a leap year for Lotus compatibility or alternatively there is an option to set 1/1/1904 as the first date.

Times are stored as decimal fractions of days to the nearest millisecond. In terms of normalised doubles the scaled serial numbers occupy an integer range F = {0, ...,255613462399999} E - b = 0.

For accurate timing calculations serial numbers should first be scaled by the number of milliseconds per day (note the actual number of seconds per day may be subject to UTC correction).

Fractions

Fractions may be set as number formats or entered as cell inputs, a zero space combination can prefix a simple fraction to distinguish it from a formatted date. If the fraction format consists of a fixed denominator, the displayed fraction is valid provided both denominator and numerator are less than 32767.

Fraction formats may also be specified by a given number of digits in the denominator, and then the formatted value may contain up to fifteen digits in integer and fractional parts combined and is derived from a continued fraction.

For example the continued fraction coefficients of PI() are [3;7,15,1,292,1,1,1,2,1,3,1,14,...] and the following approximations are displayed as fraction formats:

[#/#] 22/7 = 3+1/7 [#/###] 355/113 = 3+1/(7+1/(15+1)) [#/#####] 312689/99532 = 3+1/(7+1/(15+1/(292+1/(1+1/(1+1)))))

The continued fraction coefficients are easily derived. The integer part of PI() is 3 and the reciprocal of the decimal part is 7. Then repeat this process as shown:

7 [1/0.14159...=7.0625...]. 15 [1/0.0625... =15.996…], 1 [1/0.996…=1.0034…], 292 [1/0.0034 = 292.6…], ...