# 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](named-expressions.md#name-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`](compatibility-with-microsoft-excel.md#date-and-time-formats) and [`stringifyCurrency`](currency-handling.md) 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](built-in-functions.md#text). | 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](https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html) 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](https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html) 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](https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html) 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](https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html) 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](custom-functions.md) 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 | | INDEX | =INDEX(A1:C3, 2, B1)
where B1 = 0 | VALUE | whole row | whole row | | INDEX | =INDEX(A1:C3, {1,2}, 1)
with array arithmetic enabled | 1 | 1, 4 | 1, 4 | | 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.** `INT` discards the fractional part rather than rounding toward negative infinity, so it differs from Excel and Google Sheets for negative input only. `ROUNDDOWN`/`ROUNDUP` are unaffected — they are defined in terms of zero in all three. - **`ISEVEN`/`ISODD` do not truncate.** They test the remainder of the value as given, so a value with a fractional part returns `FALSE` from *both*. Excel and Google Sheets truncate to an integer first, so exactly one of the two is always `TRUE`. - **`CEILING.MATH`/`FLOOR.MATH` honour only `mode` = 1.** Excel and Google Sheets switch the negative-number rounding direction for any non-zero `mode`. - **`INDEX` is not vectorized.** Excel and Google Sheets evaluate `=INDEX(A1:C3, {1,2}, 1)` once per element of the array and return `1, 4`. HyperFormula resolves the array to a single value instead, because a vectorized call cannot hold the array that a zero index returns. See [known limitations](known-limitations.md#index-function). - **`INDEX` spills a whole row or column only when its shape follows from the formula.** An index of `0` selects every row or every column, exactly as in Excel and Google Sheets, but the result is an array whose size HyperFormula has to know before evaluating the formula. That needs literal index arguments *and* a statically sized range. Otherwise the array can only be consumed by an enclosing function (`=SUM(INDEX(A1:C3, 2, B1))` works, `=INDEX(A1:C3, 2, B1)` does not). See [known limitations](known-limitations.md#index-function).