Numbers

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. Numbers may be truncated to fifteen digits in formula results, additional parentheses can be inserted to change default precedence and precision. For example =(PI()-3.14159265358979) returns 7*2^-51 (3.1E-15) but without parentheses the result is zero.

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 be less than 10^308 when entered as a value. The smallest positive number is 2^-1022, values lower than this minimum underflow to zero.

Subnormal numbers in the interval (2^-1022,2^-1074] are supported in vba but inside formulas values are displayed as zero. Other exceptional values may be returned from a custom function including Infinity and NaN via 1 / 0 and -(0 / 0) with floating point representations 2^1024 and 3*2^1023 respectively. Infinity and NaN may be returned via custom functions and are defined for comparison operations but not arithmetic operations. A negative sign may prefix any value including zero -0 although is not displayed for Infinity or NaN.

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 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 from 0 to 255613462399999. For accurate timing calculations serial numbers should be scaled by the number of milliseconds per day (note the actual number of seconds per day may be subject to UTC correction).

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 =PI() formatted as a fraction up to 1,3,5 digits displays as: 22/7, 355/113, 312689/99532.