DV-0006
hyperformula: function not implemented — AND, AVERAGE, AVERAGEIFS (+19 more)
Subjects (22)
Tests (53)
matches canonical diverges no fixture in this DV's cluster
| Test | Formula | Canonical | gsheets | excel | lattice | ironcalc | hyperformula | libreoffice | formulas | pycel |
|---|---|---|---|---|---|---|---|---|---|---|
lit:boolean/true-1 |
=TRUE+1 |
2 | gsheets2 | excel2 | lattice2 | ironcalc2 | hyperformula#NAME? | libreoffice— | formulas2 | pycel#NAME? |
INDIRECT/ref-error |
=INDIRECT("ZZZ99999") |
— | gsheets— | excel#REF! | lattice— | ironcalc#REF! | hyperformula#NAME? | libreoffice— | formulas0 | pycel#REF! |
VLOOKUP/n-a-error |
=VLOOKUP("missing", {1,2;3,4}, 2, FALSE) |
#N/A | gsheets#N/A | excel#N/A | lattice#N/A | ironcalc#N/A | hyperformula#NAME? | libreoffice— | formulas#N/A | pycel#N/A |
IF/error-in-if-true-branch-not-taken |
=IF(FALSE, #N/A, "ok") |
ok | gsheetsok | excelok | latticeok | ironcalcok | hyperformula#NAME? | libreoffice— | formulasok | pycelok |
IF/error-in-if-true-branch-taken |
=IF(TRUE, #N/A, "ok") |
#N/A | gsheets#N/A | excel#N/A | lattice#N/A | ironcalc#N/A | hyperformula#NAME? | libreoffice— | formulas#N/A | pycel#N/A |
ERROR.TYPE/error-type-of-div-0-error-handling |
=ERROR.TYPE(1/0) |
2 | gsheets2 | excel2 | lattice2 | ironcalc2 | hyperformula#NAME? | libreoffice— | formulas2 | pycel#NAME? |
ERROR.TYPE/error-type-of-n-a |
=ERROR.TYPE(#N/A) |
7 | gsheets7 | excel7 | lattice7 | ironcalc7 | hyperformula#NAME? | libreoffice— | formulas7 | pycel#NAME? |
ERROR.TYPE/error-type-of-value-error-handling |
=ERROR.TYPE("a"+1) |
3 | gsheets3 | excel3 | lattice3 | ironcalc3 | hyperformula#NAME? | libreoffice— | formulas3 | pycel#NAME? |
ERROR.TYPE/error-type-of-clean-value |
=ERROR.TYPE(42) |
#N/A | gsheets#N/A | excel#N/A | lattice#N/A | ironcalc#N/A | hyperformula#NAME? | libreoffice— | formulas#N/A | pycel#NAME? |
N/n-of-true-info |
=N(TRUE) |
1 | gsheets1 | excel1 | lattice1 | ironcalc1 | hyperformula#NAME? | libreoffice— | formulastrue | pycel1 |
N/n-of-false-info |
=N(FALSE) |
0 | gsheets0 | excel0 | lattice0 | ironcalc0 | hyperformula#NAME? | libreoffice— | formulasfalse | pycel0 |
IF/if-true-branch |
=IF(TRUE, 1, 2) |
1 | gsheets1 | excel1 | lattice1 | ironcalc1 | hyperformula#NAME? | libreoffice— | formulas1 | pycel1 |
IF/if-false-branch |
=IF(FALSE, 1, 2) |
2 | gsheets2 | excel2 | lattice2 | ironcalc2 | hyperformula#NAME? | libreoffice— | formulas2 | pycel2 |
IF/if-with-comparison |
=IF(2>3, TRUE, FALSE) |
false | gsheetsfalse | excelfalse | latticefalse | ironcalcfalse | hyperformula#NAME? | libreoffice— | formulasfalse | pycel#NAME? |
AND/and-all-true |
=AND(TRUE, TRUE, TRUE) |
true | gsheetstrue | exceltrue | latticetrue | ironcalctrue | hyperformula#NAME? | libreoffice— | formulastrue | pyceltrue |
AND/and-one-false |
=AND(TRUE, FALSE, TRUE) |
false | gsheetsfalse | excelfalse | latticefalse | ironcalcfalse | hyperformula#NAME? | libreoffice— | formulasfalse | pycelfalse |
OR/or-one-true |
=OR(FALSE, TRUE, FALSE) |
true | gsheetstrue | exceltrue | latticetrue | ironcalctrue | hyperformula#NAME? | libreoffice— | formulastrue | pyceltrue |
OR/or-all-false |
=OR(FALSE, FALSE, FALSE) |
false | gsheetsfalse | excelfalse | latticefalse | ironcalcfalse | hyperformula#NAME? | libreoffice— | formulasfalse | pycelfalse |
NOT/not-true |
=NOT(TRUE) |
false | gsheetsfalse | excelfalse | latticefalse | ironcalcfalse | hyperformula#NAME? | libreoffice— | formulasfalse | pycelfalse |
NOT/not-false |
=NOT(FALSE) |
true | gsheetstrue | exceltrue | latticetrue | ironcalctrue | hyperformula#NAME? | libreoffice— | formulastrue | pyceltrue |
INDIRECT/indirect-a1-to-value |
=INDIRECT("A1") |
42 | gsheets42 | excel42 | lattice42 | ironcalc42 | hyperformula#NAME? | libreoffice— | formulas42 | pycel42 |
INDIRECT/indirect-text-cell |
=INDIRECT("B3") |
hello | gsheetshello | excelhello | latticehello | ironcalchello | hyperformula#NAME? | libreoffice— | formulashello | pycelhello |
INDIRECT/indirect-sum-range |
=SUM(INDIRECT("A1:A3")) |
6 | gsheets6 | excel6 | lattice6 | ironcalc6 | hyperformula#NAME? | libreoffice— | formulas6 | pycel0 |
LOOKUP/lookup-exact-sorted |
=LOOKUP(2, A1:A3, B1:B3) |
banana | gsheetsbanana | excelbanana | latticebanana | ironcalcbanana | hyperformula#NAME? | libreoffice— | formulasbanana | pycelbanana |
LOOKUP/lookup-approximate |
=LOOKUP(4, A1:A3, B1:B3) |
mid | gsheetsmid | excelmid | latticemid | ironcalcmid | hyperformula#NAME? | libreoffice— | formulasmid | pycelmid |
LOOKUP/lookup-vector-form-no-result |
=LOOKUP(20, A1:A3) |
20 | gsheets20 | excel20 | lattice20 | ironcalc20 | hyperformula#NAME? | libreoffice— | formulas20 | pycel20 |
LOOKUP/lookup-below-range |
=LOOKUP(5, A1:A3, B1:B3) |
#N/A | gsheets#N/A | excel#N/A | lattice#N/A | ironcalc#N/A | hyperformula#NAME? | libreoffice— | formulas#N/A | pycel#N/A |
LOOKUP/lookup-above-range-picks-last |
=LOOKUP(99, A1:A3, B1:B3) |
c | gsheetsc | excelc | latticec | ironcalcc | hyperformula#NAME? | libreoffice— | formulasc | pycelc |
VLOOKUP/vlookup-exact |
=VLOOKUP(2, A1:B3, 2, FALSE) |
banana | gsheetsbanana | excelbanana | latticebanana | ironcalcbanana | hyperformula#NAME? | libreoffice— | formulasbanana | pycelbanana |
VLOOKUP/vlookup-not-found |
=VLOOKUP(99, A1:B2, 2, FALSE) |
#N/A | gsheets#N/A | excel#N/A | lattice#N/A | ironcalc#N/A | hyperformula#NAME? | libreoffice— | formulas#N/A | pycel#N/A |
HLOOKUP/hlookup-exact |
=HLOOKUP(2, A1:C2, 2, FALSE) |
banana | gsheetsbanana | excelbanana | latticebanana | ironcalcbanana | hyperformula#NAME? | libreoffice— | formulasbanana | pycelbanana |
RANDARRAY/randarray-smoke |
=RANDARRAY(2, 2) |
[object Object] | gsheets{0.880173, 0.731380; 0.502403, 0.639389} | excel{0.533529, 0.438999; 0.818163, 0.911050} | lattice{0.952195, 0.815616; 0.812852, 0.388461} | ironcalc#NAME? | hyperformula#NAME? | libreoffice— | formulas#NAME? | pycel#NAME? |
INTERCEPT/intercept-exact-linear |
=INTERCEPT(B1:B3, A1:A3) |
1 | gsheets1 | excel1 | lattice1 | ironcalc1 | hyperformula#NAME? | libreoffice— | formulas1 | pycel1.00000 |
AVERAGEIFS/averageifs-single-criterion |
=AVERAGEIFS(E1:E5, D1:D5, "A") |
30 | gsheets30 | excel30 | lattice30 | ironcalc30 | hyperformula#NAME? | libreoffice— | formulas30 | pycel30 |
AVERAGEIFS/averageifs-two-criteria |
=AVERAGEIFS(E1:E5, D1:D5, "A", E1:E5, ">15") |
40 | gsheets40 | excel40 | lattice40 | ironcalc40 | hyperformula#NAME? | libreoffice— | formulas40 | pycel40 |
AVERAGEIFS/averageifs-no-match |
=AVERAGEIFS(A1:A3, B1:B3, "q") |
#DIV/0! | gsheets#DIV/0! | excel#DIV/0! | lattice#DIV/0! | ironcalc#DIV/0! | hyperformula#NAME? | libreoffice— | formulas#DIV/0! | pycel#DIV/0! |
op:add/true-plus-zero |
=TRUE+0 |
1 | gsheets1 | excel1 | lattice1 | ironcalc1 | hyperformula#NAME? | libreoffice— | formulas1 | pycel#NAME? |
op:add/false-plus-zero |
=FALSE+0 |
0 | gsheets0 | excel0 | lattice0 | ironcalc0 | hyperformula#NAME? | libreoffice— | formulas0 | pycel#NAME? |
op:add/true-plus-one |
=TRUE+1 |
2 | gsheets2 | excel2 | lattice2 | ironcalc2 | hyperformula#NAME? | libreoffice— | formulas2 | pycel#NAME? |
op:multiply/true-times-true |
=TRUE*TRUE |
1 | gsheets1 | excel1 | lattice1 | ironcalc1 | hyperformula#NAME? | libreoffice— | formulas1 | pycel#NAME? |
op:subtract/true-minus-false |
=TRUE-FALSE |
1 | gsheets1 | excel1 | lattice1 | ironcalc1 | hyperformula#NAME? | libreoffice— | formulas1 | pycel#NAME? |
SUM/sum-with-booleans |
=SUM(TRUE, FALSE, TRUE) |
2 | gsheets2 | excel2 | lattice2 | ironcalc0 | hyperformula#NAME? | libreoffice— | formulas2 | pycel0 |
AVERAGE/average-with-booleans |
=AVERAGE(TRUE, FALSE, TRUE) |
0.666667 | gsheets0.666667 | excel0.666667 | lattice#DIV/0! | ironcalc0.666666667 | hyperformula#NAME? | libreoffice— | formulas0.666667 | pycel#DIV/0! |
op:concat/boolean-concat-string |
=TRUE&"x" |
TRUEx | gsheetsTRUEx | excelTRUEx | latticeTRUEx | ironcalcTRUEx | hyperformula#NAME? | libreoffice— | formulasTRUEx | pycel#NAME? |
op:concat/number-concat-boolean |
=1&TRUE |
1TRUE | gsheets1TRUE | excel1TRUE | lattice1TRUE | ironcalc1TRUE | hyperformula#NAME? | libreoffice— | formulas1TRUE | pycel#NAME? |
op:eq/boolean-equals-number |
=TRUE=1 |
false | gsheetsfalse | excelfalse | latticefalse | ironcalcfalse | hyperformula#NAME? | libreoffice— | formulasfalse | pycel#NAME? |
op:unary-/unary-minus-on-true |
=-TRUE |
-1 | gsheets-1 | excel-1 | lattice-1 | ironcalc-1 | hyperformula#NAME? | libreoffice— | formulas-1 | pycel#NAME? |
op:unary-/double-unary-on-true |
=--TRUE |
1 | gsheets1 | excel1 | lattice1 | ironcalctrue | hyperformula#NAME? | libreoffice— | formulastrue | pycel#NAME? |
op:unary-/double-unary-on-false |
=--FALSE |
0 | gsheets0 | excel0 | lattice0 | ironcalcfalse | hyperformula#NAME? | libreoffice— | formulasfalse | pycel#NAME? |
N/n-of-true-type-coercion |
=N(TRUE) |
1 | gsheets1 | excel1 | lattice1 | ironcalc1 | hyperformula#NAME? | libreoffice— | formulastrue | pycel1 |
N/n-of-false-type-coercion |
=N(FALSE) |
0 | gsheets0 | excel0 | lattice0 | ironcalc0 | hyperformula#NAME? | libreoffice— | formulasfalse | pycel0 |
N/n-over-boolean-array |
=N({TRUE,FALSE,TRUE}) |
{1, 0, 1} | gsheets{1, 0, 1} | excel{1, 0, 1} | lattice{1, 0, 1} | ironcalc#N/IMPL! | hyperformula#NAME? | libreoffice— | formulastrue | pycel1 |
RANDARRAY/randarray-2x3-shape |
=ROWS(RANDARRAY(2,3))&"x"&COLUMNS(RANDARRAY(2,3)) |
2x3 | gsheets2x3 | excel2x3 | lattice2x3 | ironcalc#NAME? | hyperformula#NAME? | libreoffice— | formulas#VALUE! | pycel#NAME? |