List of differences with other spreadsheets
See a full list of differences between HyperFormula, Microsoft Excel, and Google Sheets.
General functionalities
Section titled “General functionalities”| Functionality | Examples | HyperFormula | Google Sheets | Microsoft Excel |
|---|---|---|---|---|
| Dependency collection | A1:=IF(FALSE(), A1, 0) ISREF(A1) |
Dependencies are collected during the parsing phase, which finds cycles that wouldn’t appear in the evaluation.CYCLE error for both examples. |
Dependencies are collected during evaluation.0 for both examples. |
Same as Google Sheets. |
| Named expressions and named ranges | SALARY:=$A$10 COST:=10*$B$5+100 PROFIT:=SALARY-COST A1:=SALARY-COST |
Only absolute addresses are allowed (e.g., SALARY:= $A$10). Named expressions can be global or scoped to one sheet only. They can contain other named expressions. |
Named expressions are not available. Named ranges can be used to create aliases for addresses and ranges. |
Named ranges and scoped named expressions are available. |
| Named expression names | ProductPrice1:=42 | A name must be distinctive from a cell reference (case-insensitive), so ProductPrice1 is invalid. See complete naming rules. |
A name that is a valid cell reference is allowed if the column address is at least 4-letter long, so ProductPrice1 is valid. |
A name that is a valid cell reference is allowed if the column address is at least 4-letter long, so ProductPrice1 is valid. |
| Applying a scalar value to a function taking range | COLUMNS(A1) | CellRangeExpected error. |
Treats the element as length-1 range. Returns 1 for the example. | Same as Google Sheets. |
| Coercion of explicit arguments | VARP(2, 3, 4, TRUE(), FALSE(), “1”,) | 1.9592, based on the behavior of Microsoft Excel. | GoogleSheets implementation is not consistent with the standard (see also VAR.S, STDEV.P, and STDEV.S function.) |
1.9592 |
Ranges created with : |
A1:A2 A$1:$A$2 A:C 1:2 Sheet1!A1:A2 |
Allowed ranges consist of two addresses (A1:B5), columns (A:C) or rows (3:5). They cannot be mixed or contain named expressions. |
Everything allowed. | Same as Google Sheets. |
| Formatting inside the TEXT function | TEXT(A1,“dd-mm-yy”) TEXT(A1,“###.###”) |
To support all date, time and currency formats, set stringifyDateTime and stringifyCurrency configuration options. |
A wide variety of options for string formatting is supported. | Same as Google Sheets. |
| Cell references inside inline arrays | ={A1, A2} | The array’s value is calculated but not updated when the cells’ values change. | The array’s value is calculated and updated when the cells’ values change. | ERROR: invalid array |
| SPLIT function | =SPLIT(“Lorem ipsum dolor”, 0) | This function works differently from Google Sheets version but should be sufficient to achieve the same functionality in most scenarios. Read SPLIT function description on the Built-in Functions page. | Different syntax and return value. | No such function. |
| DATEVALUE function | =DATEVALUE(“25/02/1991”) | Type of the returned value: CellValueDetailedType.NUMBER_DATE (compliant with the OpenDocument standard) |
Cell auto-formatted as regular number | Cell auto-formatted as regular number |
| TIMEVALUE function | =TIMEVALUE(“14:31”) | Type of the returned value: CellValueDetailedType.NUMBER_TIME (compliant with the OpenDocument standard) |
Cell auto-formatted as regular number | Cell auto-formatted as regular number |
| EDATE function | =EDATE(DATE(2019, 7, 31), 1) | Type of the returned value: CellValueDetailedType.NUMBER_DATE. This is non-compliant with the OpenDocument standard, which defines the return type as a Number, while describing it as a Date serial number through the function summary. |
Cell auto-formatted as date | Cell auto-formatted as regular number |
| EOMONTH function | =EOMONTH(DATE(2019, 7, 31), 1) | Type of the returned value: CellValueDetailedType.NUMBER_DATE. This is non-compliant with the OpenDocument standard, which defines the return type as a Number, while describing it as a Date serial number through the function summary. |
Cell auto-formatted as date | Cell auto-formatted as regular number |
| EDATE and EOMONTH results in February 1900 | =EDATE(DATE(1900, 1, 31), 1) =EOMONTH(DATE(1900, 1, 31), 1) |
With the leapYear1900 option set to true, February 1900 has 29 days, so both examples return 29 February 1900 (serial 60 with nullDate set to 1899-12-31). With the default false, both return 28 February 1900. |
28 February 1900 for both examples. Google Sheets does not treat 1900 as a leap year. | 28 February 1900 (serial 59) for both examples. Excel keeps the 1900 leap day only in the serial-to-date mapping (DAY(60) is 29); its month arithmetic treats February 1900 as having 28 days, so no EDATE or EOMONTH result is ever serial 60. |
| Empty cells in lookup search | =XLOOKUP(3, A1:A4, A1:A4, “NF”, 0, 2) where A1:A4 = 1, 2, (empty), 3 |
Empty cells are skipped during the search. A value that is present is found even when it sits past an interspersed empty cell (exact match is gap-independent), and approximate MATCH/VLOOKUP/HLOOKUP/XLOOKUP skip empty cells — but not empty strings — when finding the lower/upper bound. Returns 3. On an all-empty range in a binary search mode, HyperFormula returns the if_not_found result (never row 1). |
Skips empty cells in approximate search (parity with HyperFormula). | With binary search modes (search_mode ±2), a range with interspersed empty cells is not strictly sorted; per Excel’s documentation the result may be invalid, so a value past an empty cell is not reliably found. On an all-empty range in a binary mode, Excel returns the first row’s value. |
| TYPE of a computed array | =TYPE(ROW(A1:A2)+0) =TYPE(A1:A2+1) =TYPE(IF(TRUE(), A1:A2)) |
ROW and COLUMN over a multi-cell range count as arrays only when passed to TYPE directly, so TYPE(ROW(A1:A2)+0) gives 1. With useArrayArithmetic set to false (the default), A1:A2+1 and IF(TRUE(), A1:A2) are reduced to the cell in the formula’s row, so TYPE classifies that cell’s value (1 for a number) in rows 1 and 2, and gives 16 (#VALUE!) in any other row. With useArrayArithmetic set to true, both give 64. |
Not checked. | Returns 64 for all three examples. |
| TYPE of a one-by-one array | =TYPE({1}+0) =TYPE(IF(TRUE(), {1})) =TYPE(VSTACK(A1)) where A1 holds an error |
An array that holds a single value counts as an array (64) only when it is the argument itself: an array constant, a call to FILTER, HSTACK, MMULT, SEQUENCE, SORT, UNIQUE or VSTACK, or TRANSPOSE of such an array. Passed through an operator or another function first, it is reduced to its value, so the first two examples give 1. When such a function call has an error as its single value, TYPE gives 16, as in the third example. |
Not checked. | Returns 64 for all three examples. |
| ERROR.TYPE function | =ERROR.TYPE(#NULL!) =ERROR.TYPE(#GETTING_DATA) |
HyperFormula has no #NULL! or #GETTING_DATA error, so the codes 1 and 8 are never returned and the literals do not parse. #CYCLE!, which Excel does not have, has no code and gives #N/A. |
Not checked. | Returns 1 for #NULL! and 8 for #GETTING_DATA. |
Built-in functions
Section titled “Built-in functions”Some built-in functions are implemented differently than in Google Sheets or Microsoft Excel.
To remove the differences, create custom implementations of those functions.
| Function | Example | HyperFormula | Google Sheets | Microsoft Excel |
|---|---|---|---|---|
| TBILLEQ | =TBILLEQ(0, 180, 1.9) | 38.5278 | NUM | NUM |
| TBILLEQ | =TBILLEQ(0, 180, 2) | 0.0000 | NUM | 0.0000 |
| TBILLEQ | =TBILLEQ(“1/2/2000”, “31/1/2001”, 0.1) | 0.1128 | VALUE | VALUE |
| TBILLEQ | =TBILLEQ(0, 360, 0.1) | 0.1127 | 0.1097 | 0.1097 |
| TBILLEQ | =TBILLEQ(0, 365, 0.1) | 0.1128 | 0.1098 | 0.1098 |
| GCD | =GCD(1000000000000000000.0) | NUM | 1E+18 | NUM |
| COMBIN | =COMBIN(1030, 0) | NUM | NUM | 1.0000 |
| RRI | =RRI(1, -1, -1) | 0.0000 | NUM | 0.0000 |
| DAYS | =DAYS(-1, 0) | NUM | -1.0000 | NUM |
| DAYS | =DAYS(0, -1) | NUM | 1.0000 | NUM |
| DATEDIF | =DATEDIF(-1, 0, “Y”) | NUM | 0.0000 | NUM |
| RATE | =RATE(12, -100, 400, 0, 1) | -1.0000 | NUM | NUM |
| PV | =PV(-1, 0, 100, 400) | NUM | -400 | NUM |
| LCMP | =LCM(1000000, 1000001, 1000002, 1000003) | NUM | 5.00003E+23 | NUM |
| TBILLPRICE | =TBILLPRICE(0, 180, 1.9) | 5.0000 | NUM | 5.0000 |
| TBILLPRICE | =TBILLPRICE(0, 180, 2) | 0.0000 | NUM | 0.0000 |
| NPV | =NPV(1, TRUE(), 1) | 0.7500 | 0.5000 | 0.7500 |
| NPV | =NPV(1,B1) where B1 = true | 0.5000 | 0.0000 | 0.0000 |
| POISSON.DIST | =POISSON.DIST(-0.01, 0, FALSE()) | NUM | 1.0000 | NUM |
| POISSON.DIST | =POISSON.DIST(0, -0.01, FALSE()) | NUM | NUM | 1.0101 |
| DB | =DB(1000000, 100000, 6, 7, 7) | 15845.1000 | NUM | 15845.0985 |
| BETA.DIST | =BETA.DIST(1, 2, 3) | N/A | 1.0000 | NUM |
| BETA.DIST | =BETA.DIST(0, 1, 1, FALSE()) | NUM | 0.0000 | NUM |
| BETA.DIST | =BETA.DIST(0.6, 1, 1, FALSE(), 0.6, 0.7) | NUM | 0.0000 | 0.0000 |
| BETA.DIST | =BETA.DIST(0.7, 1, 1, FALSE(), 0.6, 0.7) | NUM | 0.0000 | 0.0000 |
| GAMMA | =GAMMA(-2.5) | -0.9453 | NUM | -0.9453 |
| BINOM.DIST | =BINOM.DIST(0.5, 0.4, 1, FALSE()) | N/A | NUM | 1.0000 |
| NEGBINOM.DIST | =NEGBINOM.DIST(0, 1, 0, FALSE()) | 0.0000 | N/A | NUM |
| NEGBINOM.DIST | =NEGBINOM.DIST(0, 1, 1, FALSE()) | 1.0000 | N/A | NUM |
| T.INV | =T.INV(0, 1) | NUM | NUM | DIV/0 |
| BETA.INV | =BETA.INV(1, 1, 1) | 1.0000 | 1.0000 | NUM |
| WEIBULL.DIST | =WEIBULL.DIST(0, 1, 1, FALSE()) | 1.0000 | 1.0000 | 0.0000 |
| HYPGEOM.DIST | =HYPGEOM.DIST(12.1, 12, 20, 40, TRUE()) | NUM | N/A | 1.0000 |
| HYPGEOM.DIST | =HYPGEOM.DIST(12.1, 20, 12, 40, TRUE()) | NUM | N/A | 1.0000 |
| HYPGEOM.DIST | =HYPGEOM.DIST(1, 2, 3, 4) | N/A | 0.5000 | NUM |
| HYPGEOM.DIST | =HYPGEOM.DIST(4, 12, 20, 40, TRUE()) | 0.1504 | N/A | 0.1504 |
| TDIST | =TDIST(0, 1, 1.5) | NUM | 0.5000 | 0.5000 |
| T.INV.2T | =T.INV.2T(0, 1) | NUM | NUM | DIV/0 |
| T.DIST | =T.DIST(1, 0.9, FALSE()) | NUM | NUM | DIV/0 |
| AVEDEV | =AVEDEV(TRUE(), FALSE()) | 0.4444 | 0.0000 | 0.4444 |
| LARGE | =LARGE(TRUE(), 1) | NUM | NUM | 1.0000 |
| COUNTA | =COUNTA(1,) | 2.0000 | 1.0000 | 2.0000 |
| XNPV | =XNPV(-0.9, A2:D2, A3:D3) where 2nd and 3rd row: 1, 2, 3, 4 |
10.1272 | 10.12716959 | NUM |
| SKEW | =SKEW(TRUE(), FALSE()) | 1.7321 | DIV/0 | 1.7321 |
| HARMEAN | =HARMEAN(TRUE(), “4”) | 1.6000 | 4.0000 | 1.6000 |
| GEOMEAN | =GEOMEAN(TRUE(), “4”) | 2.0000 | 4.0000 | 2.0000 |
| CHISQ.TEST | =CHISQ.TEST(A1:C2, A1:F1) | N/A | N/A | DIV/0 |
| BINOM.INV | =BINOM.INV(1, 0.8, 0.2) | 0.0000 | 1.0000 | 1.0000 |
| BINOM.INV | =BINOM.INV(-0.001, 0.5, 0.5) | NUM | 0.0000 | NUM |
| BINOM.INV | =BINOM.INV(10, 0, 0.5) | 0.0000 | NUM | NUM |
| BINOM.INV | =BINOM.INV(10, 1, 0.5) | 10.0000 | NUM | NUM |
| DEVSQ | =DEVSQ(A2, A3) | 0.0000 | 0.0000 | NUM |
| NORMSDIST | =NORMSDIST(0, TRUE()) | 0.5 | Wrong number | Wrong number |
| ADDRESS | =ADDRESS(1,1,4, TRUE(), “”) | !A1 | ‘’!A1 | !A1 |
| SEQUENCE | =SEQUENCE(0) | VALUE | N/A | CALC |
| INT | =INT(-8.9) | -8 | -9 | -9 |
| ISEVEN | =ISEVEN(2.5) | FALSE | TRUE | TRUE |
| ISODD | =ISODD(3.5) | FALSE | TRUE | TRUE |
| CEILING.MATH | =CEILING.MATH(-4.3, 2, 2) | -4 | -6 | -6 |
| FLOOR.MATH | =FLOOR.MATH(-4.7, 2, 2) | -6 | -4 | -4 |
A few of the rows above share a root cause worth stating once:
- Rounding toward zero, not down.
INTdiscards the fractional part rather than rounding toward negative infinity, so it differs from Excel and Google Sheets for negative input only.ROUNDDOWN/ROUNDUPare unaffected — they are defined in terms of zero in all three. ISEVEN/ISODDdo not truncate. They test the remainder of the value as given, so a value with a fractional part returnsFALSEfrom both. Excel and Google Sheets truncate to an integer first, so exactly one of the two is alwaysTRUE.CEILING.MATH/FLOOR.MATHhonour onlymode= 1. Excel and Google Sheets switch the negative-number rounding direction for any non-zeromode.
Microsoft Excel Google Sheets compatibility OpenDocument negative numbers rounding