Function notes
Where Excel's answer is inaccurate
Recorded calls with the mathematical value beside Excel's answer. Each note states the capture build and machine, the input route, and the evidence; missing capture information stays explicit.
71 notes, attached to 106 distinct entries in the 554-entry catalogue. 554 entries = 531 functions + 23 operators = 534 published Excel rows + 7 split byte-variant rows + 13 added functions.
| Note / call | Excel | Correct value |
|---|---|---|
FN-ABS-001=ABS("0e-309") | #VALUE! | 00x0000000000000000 |
FN-ABS-001=ABS("0e309") | #VALUE! | 00x0000000000000000 |
FN-ASINH-001ASINH(-1.0126103340069186e-11) | -1.0126122162949859e-110xbda6447fffff840a | -1.0126103340069186e-110xbda6447d498e6f26 |
FN-ASINH-001ASINH(-1.4901631254755754e-16) | -2.2204460492503128e-160xbcafffffffffffff | -1.4901631254755754e-160xbca579bb4790a498 |
FN-ASINH-001ASINH(1.0501151062119094e-16) | 00x0000000000000000 | 1.0501151062119094e-160x3c9e447b5a2fd072 |
FN-ASINH-001ASINH(5e-324) | 00x0000000000000000 | 5e-3240x0000000000000001 |
FN-ASINH-001ASINH(7.193439987815484e-08) | 7.19343998474134e-080x3e734f4b58591206 | 7.193439987815477e-080x3e734f4b587c8346 |
FN-ASINH-001ASINH(2.3842260672008416e+154) | #NUM! | 356.16012607319630x4076428fe05b79d7 |
FN-BESSELI-002BESSELI(-1, 0) | 1.26606584803426010x3ff441ce433e3c05 | 1.26606587775200840x3ff441ce4b386c2d |
FN-BESSELI-002BESSELI(12.514410892637624, 3.987394155038235) | 21360.0007873445070x40d4dc000ce65cba | 21360.001501123020x40d4dc0018982a92 |
FN-BESSELI-002BESSELK(1.8447401994787955, 0.15119840195885237) | 0.13800290495734090x3fc1aa1445c5bb34 | 0.138002895235260380x3fc1aa1430e4f602 |
FN-BESSELI-003BESSELI(709.7, 3) | 1.1560227990465618e+3060x7f7a56f66ecd12a0 | 2.4631300544524576e+3060x7f8c0f97bbe572f6 |
FN-BESSELI-003BESSELI(709.7, 2) | 2.4767103124440567e+3060x7f8c3732e70ce6f9 | 2.47182813908566e+3060x7f8c28f5d197b1dd |
FN-BESSELI-003BESSELI(709.7, 0) | 2.4788083687166675e+3060x7f8c3d51558ee541 | 2.4788087273156452e+3060x7f8c3d519a191ac9 |
FN-BESSELJ-001=BESSELJ(0,0) | 1.000000002831410x3ff0000000c292b1 | 10x3ff0000000000000 |
FN-ERFC-001ERFC.PRECISE(26.543000000000003) | 2.2558350220536164e-3080x001038a055f6d0d3 | 2.2558350220535116e-3080x001038a055f6cfff |
FN-ERFC-001ERFC.PRECISE(5.927083333333333) | 5.1964407001614455e-170x3c8df497ca9a0a49 | 5.1964407001614313e-170x3c8df497ca9a0a32 |
FN-ERFC-001ERFC.PRECISE(2.0) | 0.00467773498104726450x3f7328f5ec350e65 | 0.0046777349810472660x3f7328f5ec350e67 |
FN-FLOOR-002FLOOR(0.9999999999999999, 0.1) | 10x3ff0000000000000 | 0.90x3feccccccccccccd |
FN-FLOOR-002FLOOR(4503599627370496, 2) | 45035996273705000x4330000000000004 | 45035996273704960x4330000000000000 |
FN-GAMMA-002GAMMA(10.649360706449173) | 1600316.67082694360x41386b3cabbb5088 | 1600316.67082694380x41386b3cabbb5089 |
FN-GAMMA-002GAMMA(160.48267465838674) | 3.410909690032054e+2830x7acd5c4d6f1f4edc | 3.4109096900320908e+2830x7acd5c4d6f1f4f35 |
FN-GAMMA.DIST-001GAMMA.DIST(0.010000000000003386, 2, 1, TRUE) | 4.9667913340299445e-050x3f0a0a5081f5d243 | 4.966791334029942e-050x3f0a0a5081f5d23f |
FN-GAMMA.DIST-001GAMMA.DIST(0.5000000000001851, 3, 1, TRUE) | 0.014387677966984710x3f8d774972ab2b85 | 0.0143876779669847180x3f8d774972ab2b89 |
FN-GEOMEAN-001GEOMEAN(1,2) | 1.4142135623730950x3ff6a09e667f3bcc | 1.41421356237309510x3ff6a09e667f3bcd |
FN-MOD-001MOD(-1.223247290044572e-296, 1.223247290044539e-296) | 1.223247290044572e-2960x0280000000000079 | 1.2232472900445062e-2960x027fffffffffff0e |
FN-MOD-001MOD(1.223247290044539e-296, -1.2232472900445716e-296) | #NUM! | -3.25938553492266e-3100x80003c0000000000 |
FN-MROUND-001MROUND(0.499999999999995, 1) | 10x3ff0000000000000 | 00x0000000000000000 |
FN-MROUND-001MROUND(0.49999999999999994, 1) | 10x3ff0000000000000 | 00x0000000000000000 |
FN-PPMT-001=PPMT(0.05/12,1,360,200000) | -240.309912690944740xc06e09eace050723 | -240.309912690944630xc06e09eace05071f |
FN-ROUND-001ROUND(-112500000000000, 2147483635) | 00x0000000000000000 | -1125000000000000xc2d9945ca2620000 |
FN-ROUND-002ROUND(-1594628480062905, 14) | -15946284800629000xc316a939806956d0 | -15946284800629050xc316a939806956e4 |
FN-ROUND-002ROUNDDOWN(283025097725723.5, 0) | 2830250977257240x42f0168ea69971c0 | 2830250977257230x42f0168ea69971b0 |
FN-ROUND-003ROUND(1.7976931348623157E+308, 0) | NaN bit pattern, shown as 1.8E+3080x7ff000000000000a | 1.7976931348623157e+3080x7fefffffffffffff |
FN-ROUND-003ROUND(1.7976931348623157E+308, -307) | NaN bit pattern, shown as 1.8E+3080x7ff005419221015d | #NUM! |
FN-ROUND-003ROUND(1.7976931348623157E+308, -308) | NaN bit pattern, shown as 2E+3080x7ff1ccf385ebc8a0 | #NUM! |
FN-ROUND-004=ROUND(2.675,2) | 2.680x400570a3d70a3d71 | 2.670x40055c28f5c28f5c |
FN-ROUND-004=ROUND(1.005,2) | 1.010x3ff028f5c28f5c29 | 10x3ff0000000000000 |
FN-ROUNDUP-001ROUNDUP(1.234567890123456e-200, 65536) | 10x3ff0000000000000 | 1.234567890123456e-2000x166e3d71f622be8f |
FN-ROUNDUP-001ROUNDUP(1.234567890123456e-200, 65537) | 0.10x3fb999999999999a | 1.234567890123456e-2000x166e3d71f622be8f |
FN-ROUNDUP-001ROUNDUP(1.234567890123456e-200, 32768) | 00x0000000000000000 | 1.234567890123456e-2000x166e3d71f622be8f |
FN-ROUNDUP-001TRUNC(1.234567890123456e-200, 65536) | 00x0000000000000000 | 1.234567890123456e-2000x166e3d71f622be8f |
FN-ROUNDUP-002ROUNDUP(-1.125, 2) | -1.13000000000000010xbff2147ae147ae15 | -1.130xbff2147ae147ae14 |
FN-ROUNDUP-002ROUNDUP(3.535, 2) | 3.53999999999999960x400c51eb851eb851 | 3.540x400c51eb851eb852 |
FN-ROUNDUP-003ROUNDUP(1.0001086461844975e-307, 307) | 1e-3070x0031fa182c40c60d | 2e-3070x0041fa182c40c60d |
FN-ROUNDUP-003ROUNDUP(-1.2225073858507202e-307, 308) | -1.2e-3070x80359283684dba77 | -1.3e-3070x80375eb9065434ab |
FN-SQRT-001SQRT(0.9999999999999999) | 10x3ff0000000000000 | 0.99999999999999990x3fefffffffffffff |
FN-SQRT-001SQRT(1.7976931348623157e+308) | 1.3407807929942597e+1540x5ff0000000000000 | 1.3407807929942596e+1540x5fefffffffffffff |
FN-TRUNC-001TRUNC(2.2250738585072014e-308, 0) | 2.2250738585071984e-3080x000ffffffffffffa | 00x0000000000000000 |
FN-TRUNC-001TRUNC(2.2250738585072014e-308, 1) | 2.2250738585071984e-3080x000ffffffffffffa | 00x0000000000000000 |
FN-VAR.S-001VAR.S(0.8769085090206679, 1, 1.1230914909793321) | 0.0151515151515151380x3f8f07c1f07c1f00 | 0.0151515151515150060x3f8f07c1f07c1eb4 |
FN-VAR.S-001VAR.S(1.2976169487144796e-09, 0.0013620167657056779) | 9.275430676564799e-070x3eaf1f88c6bbc519 | 9.2754306765648e-070x3eaf1f88c6bbc51a |
FN-VAR.S-001=VAR.S({157,376.82,189.207,12.38,12.473,483,306.24,324.1852,482.235,78}) | 31907.4804533817950x40df28debfbf8a84 | 31907.4804533817770x40df28debfbf8a7f |
All function notes
Measured means read from recorded answers over the note's stated scope. Inferred marks an explanation that goes beyond those readings. Documentation differences show both accounts.
Excel differs from the mathematical answer
- FN-ABS-001: Text to number checks the exponent range even when the mantissa is zeromeasured · FUNC.ABS, FUNC.VALUE
- FN-ASINH-001: ASINH is LN(|x|+SQRT(x*x+1)) with every step stored, so small inputs lose their digitsmeasured · FUNC.ASINH
- FN-BESSELI-002: BESSELI and BESSELK are low-order approximations, rarely correctly roundedmeasured · FUNC.BESSELI, FUNC.BESSELK
- FN-BESSELI-003: At large x the fixed recurrence start makes BESSELI badly wrongmeasured · FUNC.BESSELI
- FN-BESSELJ-001: BESSELJ at zero and order zero is slightly above onemeasured · FUNC.BESSELJ
- FN-ERFC-001: Above 0.5, ERFC rounds x*x before the exponential, so the tail drifts by hundreds of unitsmeasured · FUNC.ERFC, FUNC.ERFC.PRECISE
- FN-FLOOR-002: FLOOR rounds its quotient to 15 digits first, so the result can pass the numbermeasured · FUNC.FLOOR
- FN-GAMMA-002: From 10 up, GAMMA is a Stirling product of stored stages, and its error grows with xinferred · FUNC.GAMMA
- FN-GAMMA.DIST-001: GAMMA.DIST's prefactor carries GAMMALN(a+1) in the exponent, with its errorinferred · FUNC.GAMMA.DIST
- FN-GEOMEAN-001: GEOMEAN(1,2) and SQRT(2) return adjacent values in retained build 20430 controlsmeasured · FUNC.GEOMEAN, FUNC.SQRT
- FN-MOD-001: MOD with tiny operands gives wrong signs and #NUM!, by a rule that fits every recorded rowmeasured · FUNC.MOD
- FN-MROUND-001: MROUND counts a quotient within 5E-15 below a half upmeasured · FUNC.MROUND
- FN-PPMT-001: PPMT is not PMT minus IPMT, and sits further from the exact value than PMTmeasured · FUNC.PMT, FUNC.PPMT
- FN-ROUND-001: ROUND counts digits in 32 bits, and the count wrapsmeasured · FUNC.ROUND
- FN-ROUND-002: The rounding functions read 15 digits first, so ROUNDDOWN can round upmeasured · FUNC.ROUND, FUNC.ROUNDDOWN, FUNC.ROUNDUP, FUNC.TRUNC
- FN-ROUND-003: Near DBL_MAX, ROUND returns NaN bit patterns that Excel treats as numbersmeasured · FUNC.ROUND, FUNC.ROUNDDOWN, FUNC.ROUNDUP, FUNC.TRUNC
- FN-ROUND-004: ROUND takes ties away from zero on the 15-digit reading, so 1.005 rounds upmeasured · FUNC.ROUND
- FN-ROUNDUP-001: ROUNDUP, ROUNDDOWN and TRUNC read num_digits from its low 16 bitsmeasured · FUNC.ROUNDDOWN, FUNC.ROUNDUP, FUNC.TRUNC
- FN-ROUNDUP-002: ROUNDUP adds its unit in binary64, one bit from the decimal answermeasured · FUNC.ROUNDUP
- FN-ROUNDUP-003: At tiny magnitudes ROUNDUP may add no unit, and the two signs differmeasured · FUNC.ROUNDUP
- FN-SQRT-001: SQRT is rounded twice, once to 64 bits and once to 53measured · FUNC.SQRT
- FN-TRUNC-001: TRUNC of DBL_MIN returns a subnormal, not 0measured · FUNC.ROUNDDOWN, FUNC.ROUNDUP, FUNC.TRUNC
- FN-VAR.S-001: The variance family uses raw moments with stored steps, and two tests switch it to a centered summeasured · FUNC.STDEV, FUNC.STDEV.P, FUNC.STDEV.S, FUNC.STDEVA, FUNC.STDEVP, FUNC.STDEVPA, FUNC.VAR, FUNC.VAR.P, FUNC.VAR.S, FUNC.VARA, FUNC.VARP, FUNC.VARPA
Documentation differs from behaviour
- FN-GCD-001: GCD and LCM accept 2^53 itself, not only values below itmeasured · FUNC.GCD, FUNC.LCM
- FN-HYPGEOM.DIST-001: Recorded hypergeometric calls admit zero counts and counts above the supportmeasured · FUNC.HYPGEOM.DIST
- FN-MODE-001: MODE ignores direct text and logicals instead of converting them or giving an errormeasured · FUNC.MODE, FUNC.MODE.MULT, FUNC.MODE.SNGL
- FN-PERCENTRANK-001: Two percent-rank controls retain decimal places rather than significant digitsmeasured · FUNC.PERCENTRANK, FUNC.PERCENTRANK.INC
- FN-TBILLEQ-001: For bills longer than 182 days TBILLEQ is a quadratic, not the documented formulameasured · FUNC.TBILLEQ
- FN-VDB-001: An empty factor slot in VDB is 0, not the default 2measured · FUNC.VDB
Quirks
- FN-BAHTTEXT-001: A tiny negative amount keeps the word for minus before zero bahtmeasured · FUNC.BAHTTEXT
- FN-BESSELI-004: BESSELI gives #NUM! just above 709.7, where the function is still finitemeasured · FUNC.BESSELI
- FN-CHOOSE-001: An array index in CHOOSE turns selected blank cells into 0measured · FUNC.CHOOSE
- FN-COUNTBLANK-001: COUNTBLANK of an omitted optional LAMBDA parameter is #VALUE!measured · FUNC.COUNTBLANK
- FN-COUPDAYS-001: On basis 1 COUPDAYS walks back from maturity by its own rule, not COUPPCD to COUPNCDmeasured · FUNC.COUPDAYS, FUNC.COUPNCD, FUNC.COUPPCD
- FN-COUPDAYSNC-001: On basis 0 COUPDAYSNC is not always COUPDAYS minus COUPDAYBSmeasured · FUNC.COUPDAYBS, FUNC.COUPDAYS, FUNC.COUPDAYSNC
- FN-COUPNUM-001: COUPNUM is #NUM! when the coupon before settlement falls before 1900measured · FUNC.COUPNCD, FUNC.COUPNUM, FUNC.COUPPCD
- FN-DELTA-001: DELTA and GESTEP read their second argument firstmeasured · FUNC.DELTA, FUNC.ERF, FUNC.GESTEP
- FN-DOLLAR-001: DOLLAR keeps the minus sign on an amount that rounds to zero; FIXED and TEXT drop itmeasured · FUNC.DOLLAR, FUNC.FIXED, FUNC.TEXT
- FN-ERROR.TYPE-001: ERROR.TYPE of #BUSY! is 0measured · FUNC.ERROR.TYPE
- FN-FLOOR-001: FLOOR of zero is +0, even with significance 0measured · FUNC.FLOOR
- FN-HSTACK-001: A direct error stops these array functions; the same error in an array or cell is an elementmeasured · FUNC.BYCOL, FUNC.BYROW, FUNC.EXPAND, FUNC.HSTACK, FUNC.MAP, FUNC.TAKE, FUNC.TOCOL, FUNC.TOROW, FUNC.VSTACK
- FN-IFS-001: IFS and SWITCH pass on the selected value whole, and later arrays do not lift the callmeasured · FUNC.IFS, FUNC.SWITCH
- FN-IRR-001: IRR with a direct non-number gives #NUM!, and the guess is still checkedmeasured · FUNC.IRR
- FN-LINEST-002: LINEST with no residual degrees of freedom returns zeros and one #NUM!measured · FUNC.LINEST
- FN-LOWER-001: LOWER leaves letters above U+FFFF alone, and a combining mark stops the final sigmameasured · FUNC.LOWER, FUNC.PROPER
- FN-NETWORKDAYS.INTL-001: NETWORKDAYS.INTL checks weekend before the dates, WORKDAY.INTL after themmeasured · FUNC.NETWORKDAYS.INTL, FUNC.WORKDAY.INTL
- FN-OP_EQUAL-001: Conjoining Hangul jamo do not compare equal to the precomposed syllablemeasured · FUNC.OP_EQUAL, FUNC.OP_LESS_THAN
- FN-OP_LESS_THAN-001: Punctuation before a Japanese repeat mark sorts one way after a Latin letter, the other after Hanmeasured · FUNC.OP_GREATER_THAN, FUNC.OP_LESS_THAN
- FN-PRICEMAT-002: PRICEMAT basis 0 applies the 31st rule before the February rulemeasured · FUNC.PRICEMAT
- FN-REGEXTEST-001: In REGEXTEST, \w takes combining marks, dot refuses CR, and case folding reaches into rangesmeasured · FUNC.REGEXTEST
- FN-SORT-001: A Han iteration mark is not read as a repeat of the charactermeasured · FUNC.OP_LESS_THAN, FUNC.SORT
- FN-TEXT-001: Locale-tagged month, weekday and era names in TEXT follow their own tablemeasured · FUNC.TEXT
- FN-TEXT-002: In TEXT, an @ applied to a number gives the General display, not the full numbermeasured · FUNC.TEXT
- FN-TEXTSPLIT-001: Empty text gives #N/A from TEXTBEFORE and TEXTAFTER, #VALUE! from TEXTSPLITmeasured · FUNC.TEXTAFTER, FUNC.TEXTBEFORE, FUNC.TEXTSPLIT
- FN-TEXTSPLIT-002: Over an array, TEXTSPLIT and REGEXEXTRACT keep only the first piece of each elementmeasured · FUNC.REGEXEXTRACT, FUNC.TEXTSPLIT
- FN-VALUE-001: Text to number reads Arabic, Persian, Devanagari and Thai digits, but not fullwidth digitsmeasured · FUNC.DATEVALUE, FUNC.SQRT, FUNC.VALUE
- FN-VALUE-002: A date as text takes at most two digits for month and daymeasured · FUNC.DATEVALUE, FUNC.VALUE
- FN-VALUE-003: VALUE takes one percent sign anywhere around parentheses, and a euro sign but not a dollarmeasured · FUNC.VALUE
- FN-WEEKNUM-001: WEEKNUM with an invalid return_type can answer differently each time it is calledmeasured · FUNC.WEEKNUM
- FN-WORKDAY-001: An empty cell as a first date is serial 0, but a logical in that cell is #VALUE!measured · FUNC.NETWORKDAYS, FUNC.NETWORKDAYS.INTL, FUNC.TBILLEQ, FUNC.TBILLPRICE, FUNC.TBILLYIELD, FUNC.WORKDAY, FUNC.WORKDAY.INTL, FUNC.YEARFRAC
- FN-YEARFRAC-001: On basis 1 the fictitious 1900-02-29 is not a leap day, and YEARFRAC takes serial 0 as a datemeasured · FUNC.ACCRINTM, FUNC.DISC, FUNC.INTRATE, FUNC.PRICEDISC, FUNC.RECEIVED, FUNC.YEARFRAC
Details for matching Excel's bits
- FN-BESSELI-001: BESSELI is ITK's published polynomial and recurrence, on the x87 unitmeasured · FUNC.BESSELI
- FN-CUMPRINC-001: CUMPRINC follows PPMT on one loan and not in general; its own route is openmeasured · FUNC.CUMIPMT, FUNC.CUMPRINC
- FN-DISC-001: The discount-security functions run a fixed formula order on the x87 unitmeasured · FUNC.DISC, FUNC.INTRATE, FUNC.PRICEDISC, FUNC.RECEIVED, FUNC.TBILLYIELD
- FN-ERF.PRECISE-001: Below 0.5, ERF is a stored (4, 5) rational from a table not yet identifiedmeasured · FUNC.ERF, FUNC.ERF.PRECISE, FUNC.ERFC, FUNC.ERFC.PRECISE, FUNC.GAUSS
- FN-GAMMA-001: Below 10, GAMMA is a stored product down to a seed on (1,2)measured · FUNC.GAMMA
- FN-GAMMALN-001: GAMMALN from 3 to 8 is a stored recurrence, and GAMMALN(3) is one unit above ln 2measured · FUNC.GAMMALN, FUNC.GAMMALN.PRECISE
- FN-IPMT-001: IPMT and PPMT reuse PMT's quotient, one through expm1 and the other through expmeasured · FUNC.IPMT, FUNC.PPMT
- FN-LINEST-001: LINEST's Householder reflector stores each step from the x87 unitmeasured · FUNC.LINEST, FUNC.LOGEST
- FN-PMT-001: PMT takes ln(1+rate) from a series, and stores its quotient before the ratemeasured · FUNC.PMT
- FN-PRICEMAT-001: PRICEMAT formula order on the x87 unitmeasured · FUNC.PRICEMAT