# List of differences with other spreadsheets
See a full list of differences between HyperFormula, Microsoft Excel, and Google Sheets.
Contents:
# 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 |
| Errors in array results | =DROP(Data!A1:C3, NA()) | The error fills the whole area reserved for the array (here 3x3), because the array's size is predicted before the formula is evaluated. | No such function. | A single N/A error. |
| Array size that depends on cell values | =DROP(Data!A1:C3, Counts!A1) in A1, where Counts!A1 = 1 and A3 holds a value | Returns SPILL: the whole source (3x3) is reserved, because the count is read after the array's size is set. | No such function. | Spills the two remaining rows into A1:C2. |
| 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 (opens new window) 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 (opens new window) 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 (opens new window) 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 (opens new window) 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 |
| 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. |
# 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 |
| TAKE | =TAKE(A1:A3, 0) | N/A | No such function. | CALC |
| TAKE | =TAKE(Data!A:A, , 1) | Spills to the source sheet's current height from row 1; returns SPILL below row 1. | No such function. | Spills all 1,048,576 rows from row 1, with blank source cells as 0; returns SPILL below row 1. |
| DROP | =DROP(A1:A3, 3) | N/A | No such function. | CALC |
| DROP | =DROP(Data!A:A, 1) | Spills to the source sheet's current height minus one row; returns SPILL below row 2. | No such function. | Spills all 1,048,575 remaining rows, with blank source cells as 0; returns SPILL below row 2. |
| DROP | =DROP(Data!A:A, B1) | Spills from row 1 only; returns SPILL below row 1, because the count is read after the result size is set. | No such function. | Spills 1,048,576 minus B1 rows when entered in row B1+1 or above; returns SPILL below. |
| DROP | =DROP(A1:C3, {1,2}) | Uses the first count and drops one row. | No such function. | Returns a 1x2 array of VALUE errors. |
| DROP | =ISREF(DROP(Data!A1:C3, 1)) | FALSE (returns values, not a reference) | No such function. | TRUE |
| TOCOL | =TOCOL(A1:A3, 1) where A1:A3 are empty | N/A | (measure) | CALC |
| TOCOL | =TOCOL(A1:A5, 1) where A1:A5 = 1, (empty), 3, (empty), 5 | 1, 3, 5, then two blank cells: the source size (5x1) is reserved, because the skipped elements are known only after the array's size is set. | (measure) | 1, 3, 5 |
| TOCOL | =TOCOL(A1:C3, 0, {0,1}) where A1:C3 = 1, 2, 3; 4, 5, 6; 7, 8, 9 | Uses the first value of the array: 1 to 9 in one column, read by row. | (measure) | Returns a 1x2 array: 1, 1. |
| TOCOL | =ROWS(TOCOL(Data!A:A)) | The source sheet's current height. =TOCOL(Data!A:A) spills from row 1 only and returns SPILL below row 1. | (measure) | 1048576 |
| TOCOL | =TOCOL(Data!A:B) | SPILL | (measure) | NUM |
| TOCOL | =TOCOL(A:A, 1) entered in D5, where A1:A3 = 1, 2, 3 | SPILL. On the formula's own sheet, a whole column spills from row 1 only, and two or more whole columns return SPILL in any row, whatever the ignore value, because the result would make the sheet taller. | (measure) | 1, 2, 3 |
| TOCOL | =TOCOL(A:A+0, 1) entered in D5, where A1:A3 = 1, 2, 3 | SPILL. A whole column computed by a formula, such as A:A+0, Data!A:A+0 or DROP(Data!A:A, 1), is sized by the maxRows setting. On any sheet, it spills from row 1 only, and two or more columns return SPILL in any row, whatever the ignore value. | (measure) | (measure) |
| TOROW | =TOROW(A1:A3, 1) where A1:A3 are empty | N/A | (measure) | CALC |
| TOROW | =TOROW(A1:A5, 1) where A1:A5 = 1, (empty), 3, (empty), 5 | 1, 3, 5, then two blank cells: the source size (1x5) is reserved, because the skipped elements are known only after the array's size is set. | (measure) | 1, 3, 5 |
| TOROW | =TOROW(A1:C3, 0, {0,1}) where A1:C3 = 1, 2, 3; 4, 5, 6; 7, 8, 9 | Uses the first value of the array: 1 to 9 in one row, read by row. | (measure) | Returns a 1x2 array: 1, 1. |
| TOROW | =COLUMNS(TOROW(Data!1:1)) | The source sheet's current width. =TOROW(Data!1:1) spills from column A only and returns SPILL right of column A. | (measure) | 16384 |
| TOROW | =COLUMNS(TOROW(Data!A:A)) | The source sheet's current height. | (measure) | 1048576 |
| TOROW | =COLUMNS(TOROW(Data!A:B)) | Twice the source sheet's current height. | (measure) | NUM |
| TOROW | =TOROW(1:1, 1) entered in E5, where A1:C1 = 1, 2, 3 | SPILL. On the formula's own sheet, a whole row spills from column A only, and two or more whole rows return SPILL in any column, whatever the ignore value, because the result would make the sheet wider. | (measure) | (measure) |
| TOROW | =TOROW(1:1+0, 1) entered in E5, where A1:C1 = 1, 2, 3 | SPILL. A whole row computed by a formula, such as 1:1+0, Data!1:1+0 or DROP(Data!1:1, , 1), is sized by the maxColumns setting. On any sheet, it spills from column A only, and two or more rows return SPILL in any column, whatever the ignore value. | (measure) | (measure) |
| 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.