1# Formulas where 012 matches Google Sheets on purpose (docs/contributing/testing.md).2# Tab-separated: formula, 012's result, Sheets' result, note.3# Results read "text" (quoted), 12.5, TRUE, #N/A, or (blank) for an empty cell.4# Sheets' result is "unconfirmed" until checked in Sheets. A row where it5# differs from 012's needs a note starting "on purpose:" that says why.6# Every formula computes one value; array results are measured with ROWS,7# COLUMNS, INDEX or TEXTJOIN. Data! is the fixture sheet (sheetscorpus_test.go).8## Dates and times9=DATE(2026, 9, 26) 46291 4629110=DATE(2026, 14, 1) 46419 4641911=DATE(2026, 1, 0) 46022 4602212=DATE(26, 1, 1) 9498 949813=DATE(1899, 12, 30) 693961 69396114=DATE(1900, 1, 1) 2 215=DATE(1900, 2, 29) 61 6116=DATE(-1, 1, 1) #NUM! #NUM!17=DATE("2026", "9", "26") 46291 4629118=TIME(14, 30, 0) 0.604166666666667 0.60416666666666719=TIME(25, 0, 0) 0.0416666666666667 0.041666666666666720=TIME(0, -1, 0) #NUM! #NUM!21=YEAR(0) 1899 189922=DAY(Data!A5) 30 3023=YEAR(Data!A4) #VALUE! #VALUE!24=WEEKDAY(Data!F1) 7 725=WEEKDAY(Data!F1, 2) 6 626=WEEKDAY(Data!F1, 4) #NUM! #NUM!27=HOUR(0.99999999) 0 028=HOUR(1.75) 18 1829=MINUTE("14:30:59") 30 3030=SECOND(59.6/86400) 0 031=EDATE(DATE(2024, 2, 29), 12) 45716 4571632=EDATE(Data!F1, 1.9) 46321 4632133=EOMONTH(DATE(2026, 1, 31), 1) 46081 4608134=DATEDIF(Data!F4, Data!F1, "MD") 28 2835=DATEDIF(Data!F4, Data!F1, "YD") 210 21036=DATEDIF(DATE(2024, 1, 31), DATE(2024, 3, 1), "MD") -1 -137=DATEDIF(Data!F2, Data!F5, "D") #NUM! #NUM!38=DAYS(Data!F1, Data!F2) 238 23839=NETWORKDAYS(Data!F5, Data!F2, Data!F5) 22 2240=NETWORKDAYS(Data!F1, Data!F1) 0 041=DATEVALUE("9/26/2026") 46291 4629142=DATEVALUE("Sep 26, 2026") 46291 4629143=DATEVALUE("9/26/2026 18:00") 46291 4629144=DATEVALUE("2/30/2026") #VALUE! #VALUE!45=DATEVALUE(46291) #VALUE! #VALUE!46=TIMEVALUE("2:30 PM") 0.604166666666667 0.60416666666666747=TIMEVALUE("9/26/2026 6:00") 0.25 0.2548=TIMEVALUE("noon") #VALUE! #VALUE!49="2026-09-27"-Data!F1 1 150=Data!F1+Data!F3 46291.6041666667 46291.604166666751=Data!F1="2026-09-26" FALSE FALSE52## Text53=CONCATENATE("a", 1, TRUE) "a1TRUE" "a1TRUE"54=CONCATENATE(Data!A1:A3) "102030" "102030"55=TEXTJOIN(", ", TRUE, Data!A3:A5) "30, apple" "30, apple"56=TEXTJOIN(",", FALSE, Data!B5:B7) "north,,Ab*d" "north,,Ab*d"57=LEFT("hi", -1) #VALUE! #VALUE!58=LEFT(12345, 2) "12" "12"59=LEFT(TRUE, 1) "T" "T"60=MID("abc", 5, 2) "" ""61=MID("abc", 0, 2) #NUM! #NUM!62=LEN(Data!F1) 5 9 on purpose: F1 is a date, stored in the XLSX as its serial with a m/d/yyyy format; Sheets' text functions read a date cell as it shows (9/26/2026), 012 reads its serial, as Excel does63=LEN(1/4) 4 464=LEN("héllo") 5 565=LEN("😀") 1 2 on purpose: LEN counts characters (code points), where Sheets counts UTF-16 units66=LEFT("日本語", 2) "日本" "日本"67=UPPER("straße") "STRASSE" "STRASSE"68=UPPER(1.5) "1.5" "1.5"69=PROPER("hello wORLD it's") "Hello World It'S" "Hello World It'S"70=PROPER("o'neil mcdonald 3rd") "O'Neil Mcdonald 3Rd" "O'Neil Mcdonald 3Rd"71=TRIM(" a b ") "a b" "a b"72=SUBSTITUTE("aaa", "aa", "b") "ba" "ba"73=SUBSTITUTE("abc", "", "x") "abc" "abc"74=REPLACE("abc", 10, 1, "X") "abcX" "abcX"75=FIND("B", "abc") #VALUE! #VALUE!76=FIND("", "abc") 1 177=SEARCH("b?d", "abcbxd") 4 478=SEARCH("~*", "a*b") 2 279=REPT("ab", 2.9) "abab" "abab"80=REPT("ab", -1) #VALUE! #VALUE!81=EXACT(1, "1") TRUE TRUE82=EXACT(TRUE, "TRUE") TRUE TRUE83="a"&Data!A5 "a" "a"84=1&2 "12" "12"85=VALUE("1,234.5") 1234.5 1234.586=VALUE(" 42 ") 42 4287=VALUE("") 0 088=VALUE("abc") #VALUE! #VALUE!89=VALUE("9/26/2026") 46291 4629190=VALUE(TRUE) #VALUE! #VALUE!91## Regular expressions and SPLIT92=REGEXMATCH("ABC", "(?i)abc") TRUE unconfirmed93=REGEXMATCH(123, "1") #VALUE! unconfirmed94=REGEXEXTRACT("abc", "\d+") #N/A unconfirmed95=TEXTJOIN("|", TRUE, REGEXEXTRACT("a1b2", "(\d)b(\d)")) "1|2" unconfirmed96=REGEXREPLACE("john smith", "(\w+) (\w+)", "$2, $1") "smith, john" "smith, john"97=COLUMNS(SPLIT("a,b,,c", ",", TRUE, FALSE)) 4 unconfirmed98=SUM(SPLIT("1,2,3", ",")) 6 unconfirmed99=COLUMNS(SPLIT("a-b_c", "-_")) 3 unconfirmed100## TEXT and number formats101=TEXT(1234.567, "$#,##0.00") "$1,234.57" "$1,234.57"102=TEXT(1234567.891, "#,##0,") "1,235" "1,235"103=TEXT(1234.5, "0.00E+00") "1.23E+03" "1.23E+03"104=TEXT(0.0000000001, "0.00E+00") "1.00E-10" "1.00E-10"105=TEXT(-1234.5, "#,##0.00;(#,##0.00)") "(1,234.50)" "(1,234.50)"106=TEXT(0, "0;-0;\z\e\r\o") "zero" "zero"107=TEXT(3, "0.###") "3." "3."108=TEXT(2.5, "0") "3" "3"109=TEXT(-0.5, "0") "-1" "-1"110=TEXT(1.005, "0.00") "1.01" "1.01"111=TEXT(0.75, "# ?/?") "3/4" "3/4"112=TEXT("abc", "0") "abc" "abc"113=TEXT("12", "0.00") "12.00" "12.00"114=TEXT(TRUE, "0") "TRUE" "TRUE"115=TEXT(Data!F1, "dddd") "Saturday" "Saturday"116=TEXT(Data!F1, "mmm d, yyyy") "Sep 26, 2026" "Sep 26, 2026"117=TEXT(Data!F1, "dddd, mmmm d, yyyy") "Saturday, September 26, 2026" "Saturday, September 26, 2026"118=TEXT(Data!F3, "h:mm AM/PM") "2:30 PM" "2:30 PM"119=TEXT(0.75, "h AM/PM") "6 PM" "6 PM"120=TEXT(Data!F1+Data!F3, "yyyy-mm-dd hh:mm") "2026-09-26 14:30" "2026-09-26 14:30"121=TEXT(1.5, "[h]:mm") "36:00" "36:00"122=TEXT(90/1440, "[mm]:ss") "90:00" "90:00"123## Rounding and floating point124=ROUND(1.005, 2) 1.01 1.01125=ROUND(2.675, 2) 2.68 2.68126=ROUND(0.285, 2) 0.29 0.29127=ROUND(-2.5) -3 -3128=ROUND(1.5, 0.9) 2 2129=ROUND(1.23456789012345, 14) 1.23456789012345 1.23456789012346 on purpose: 012 rounds the shortest decimal half away from zero, so an exact input stays exact; Sheets' ...346 fits only a rule that also turns ROUND(1.5, 14) into 1.50000000000001130=ROUNDUP(0.1+0.2, 1) 0.3 0.3131=ROUNDDOWN(1-0.9, 1) 0.1 0.1132=TRUNC(1234, -2) 1200 1200133=INT(-1.5) -2 -2134=INT(1-0.9+0.9) 1 1135=CEILING(0.3, 0.1) 0.3 0.3136=CEILING(-2.5, 1) -2 -2137=CEILING(-2.5, -1) -3 -3138=CEILING(2.5, -1) #NUM! #NUM!139=CEILING(2.5, 0) 0 0140=FLOOR(-2.5, 1) -3 -3141=FLOOR(-2.5, -1) -2 -2142=MOD(-7, 3) 2 2143=MOD(10, 0) #DIV/0! #DIV/0!144=MOD(0.3, 0.1) 0.1 -5.55111512312578E-17 on purpose: 012 computes n - d*INT(n/d) as Excel does, keeping the divisor's sign; Sheets' -5.55E-17 is a floating artefact145=QUOTIENT(-7, 2) -3 -3146=0.1+0.2 0.3 0.3147=0.1+0.2=0.3 TRUE TRUE148=0.1+0.2-0.3 5.55111512312578E-17 5.55111512312578E-17149=1-0.9 0.1 0.1150=(1-0.9)=0.1 TRUE TRUE151=SUM(0.1, 0.2, 0.3)=0.6 TRUE TRUE152=1/3 0.333333333333333 0.333333333333333153=100*1.1 110 110154=1E+21 1E+21 1E+21155=123456789012345678 123456789012345000 123456789012345000156=9007199254740993 9007199254740990 9007199254740990157=0.000001 0.000001 0.000001158=-0 0 0159=-2^2 4 4160=0^0 1 1161=POWER(0, 0) 1 1162=POWER(-8, 1/3) -2 -2163=(-8)^(1/3) -2 -2164=1E+308*10 #NUM! #NUM!165=LOG(10, 1) #DIV/0! #DIV/0!166## Errors and their propagation167=0/0 #DIV/0! #DIV/0!168=Data!J2+1 #DIV/0! #DIV/0!169=SUM(Data!J2, 1) #DIV/0! #DIV/0!170=SUM(1, NA()) #N/A #N/A171=NA()+1/0 #N/A #N/A172=1/0+NA() #DIV/0! #DIV/0!173=IFERROR(1/0, "x") "x" "x"174=IFERROR(NA()) (blank) (blank)175=IFERROR(Data!J2, Data!J4) #N/A #N/A176=IFNA(1/0, 0) #DIV/0! #DIV/0!177=ISERR(NA()) FALSE FALSE178=IF(1/0, 1, 2) #DIV/0! #DIV/0!179=IF("abc", 1, 2) #VALUE! #VALUE!180=AND(TRUE, 1/0) #DIV/0! #DIV/0!181=OR(TRUE, NA()) #N/A #N/A182=Data!A1+nosuchname #NAME? #NAME?183=CHOOSE(0, "a") #NUM! #NUM!184=INDEX(Data!A1:A3, 5) #NUM! #NUM!185=AVERAGEIF(Data!A1:A7, ">100") #DIV/0! #DIV/0!186=SWITCH(Data!B4, "north", 1) #N/A #N/A187=PMT(0, 0, 1000) #NUM! #NUM!188=COUNT(Data!J1:J4) 0 0189=COUNTA(Data!J1:J4) 4 4190## Coercion191=" 3 "*1 3 3192="1,000"*1 1000 1000193="$5"*2 10 10194="50%"*2 1 1195="9/26/2026"*1 46291 46291196="TRUE"*1 #VALUE! #VALUE!197=-TRUE -1 -1198=Data!A5&"x" "x" "x"199=Data!A5="" TRUE TRUE200=Data!B6=0 FALSE FALSE201=ISBLANK(Data!B6) FALSE FALSE202=Data!J1+1 4 4203=SUM(Data!J1) 0 0204=SUM("3", 1) 4 4205=SUM(Data!E6, 1) 1 1206=AVERAGE(Data!E6, 3) 3 3207=COUNT("1", 2) 2 2208=COUNT(TRUE) 1 1209=COUNTBLANK(Data!B1:B7) 1 1210="10"<9 FALSE FALSE211=1<"a" TRUE TRUE212=TRUE>1 TRUE TRUE213=TRUE="TRUE" FALSE FALSE214=Data!A5<"a" TRUE TRUE215=""=0 FALSE FALSE216=IF("TRUE", 1, 2) 1 1217=IF(Data!A5, "y", "n") "n" "n"218=AND("abc") #VALUE! #VALUE!219=AND(Data!A4) #VALUE! #VALUE!220=NOT("") TRUE TRUE221=ROUND("2.5") 3 3222=LEFT(Data!F1, 3) "462" "9/2" on purpose: F1 is a date, stored in the XLSX as its serial with a m/d/yyyy format; Sheets' text functions read a date cell as it shows (9/26/2026), 012 reads its serial, as Excel does223## Lookups and criteria224=VLOOKUP("B*", Data!D1:E3, 2, FALSE) 2.5 2.5225=VLOOKUP("Blueberry", Data!D1:E3, 2) 2.5 2.5226=VLOOKUP("Aardvark", Data!D1:E3, 2, TRUE) #N/A #N/A227=VLOOKUP(2.5, Data!C1:D5, 2) "Banana" "Banana"228=VLOOKUP("1", Data!C1:D5, 2, FALSE) #N/A #N/A229=VLOOKUP("Apple", Data!D1:E3, 3, FALSE) #REF! #REF!230=VLOOKUP("Apple", Data!D1:E3, 0, FALSE) #VALUE! #VALUE!231=HLOOKUP("Apple", Data!D1:E2, 2, FALSE) "Banana" "Banana"232=MATCH("cherry", Data!D1:D3, 0) 3 3233=MATCH("B*", Data!D1:D3, 0) 2 2234=MATCH(3.2, Data!C1:C7) 3 3235=MATCH(0, Data!C1:C7) #N/A #N/A236=MATCH(35, Data!I1:I3, -1) #N/A #N/A237=MATCH(3, {1, 3, 5}, 0) 2 2238=INDEX(Data!C1:E1, 2) "Apple" "Apple"239=INDEX(Data!D1:E3, 4, 1) #NUM! #NUM!240=ROWS(INDEX(Data!A1:C3, 0, 1)) 3 3241=SUM(INDEX(Data!C1:E3, 0, 1)) 6 6242=XLOOKUP("kiwi", Data!D1:D3, Data!E1:E3) #N/A #N/A243=XLOOKUP("Apple", Data!D1:D5, Data!E1:E5, , 0, -1) 9 9244=XLOOKUP(3.2, Data!C1:C7, Data!D1:D7, , -1) "Cherry" "Cherry"245=XLOOKUP(3.2, Data!C1:C7, Data!D1:D7, , 1) "date" "date"246=XLOOKUP("Ch*", Data!D1:D3, Data!E1:E3, , 2) 3.5 3.5247=XLOOKUP(2, Data!G1:G4, Data!H1:H4, , 0, -1) 400 400248=CHOOSE(2.9, "a", "b", "c") "b" "b"249=SUMIF(Data!A1:A7, "<>10") 45 45250=SUMIF(Data!C1:C7, ">=" & Data!C5) 18 18251=SUMIFS(Data!C1:C5, Data!B1:B5, "north", Data!A1:A5, ">10") 3 3252=COUNTIF(Data!B1:B7, "?o*") 4 4253=COUNTIF(Data!B1:B7, "Ab~*d") 1 1254=COUNTIF(Data!B1:B7, "<>north") 4 4255=COUNTIF(Data!A1:A7, "") 2 2256=COUNTIF(Data!A1:A7, "10") 1 1257=COUNTIF(Data!J1:J4, 3) 1 1258=COUNTIF(Data!E1:E7, TRUE) 1 1259=COUNTIF(Data!F1:F5, ">" & DATE(2026, 1, 1)) 2 2260## Arrays and spills261=SUM(SEQUENCE(3, 3, 0, 2)) 72 72262=INDEX(SEQUENCE(3, 2, 10, 5), 3, 2) 35 35263=ROWS(SEQUENCE(0)) #NUM! #NUM!264=ROWS(FILTER(Data!A1:A7, Data!C1:C7>3)) 4 unconfirmed Sheets read _xlfn._xlws.FILTER and _xlfn._xlws.SORT, which is how Excel writes them, as #NAME?: confirm on the next import265=TEXTJOIN(",", TRUE, FILTER(Data!D1:D5, Data!B1:B5="north")) "Apple,Cherry,Apple" unconfirmed Sheets read _xlfn._xlws.FILTER and _xlfn._xlws.SORT, which is how Excel writes them, as #NAME?: confirm on the next import266=FILTER(Data!A1:A3, Data!A1:A3>100) #N/A unconfirmed Sheets read _xlfn._xlws.FILTER and _xlfn._xlws.SORT, which is how Excel writes them, as #NAME?: confirm on the next import267=TEXTJOIN(",", TRUE, SORT(Data!C1:C5, 1, FALSE)) "5,4,3,2,1" unconfirmed Sheets read _xlfn._xlws.FILTER and _xlfn._xlws.SORT, which is how Excel writes them, as #NAME?: confirm on the next import268=INDEX(SORT(Data!D1:E5, 2, FALSE), 1, 1) "date" unconfirmed Sheets read _xlfn._xlws.FILTER and _xlfn._xlws.SORT, which is how Excel writes them, as #NAME?: confirm on the next import269=TEXTJOIN(",", TRUE, SORT(Data!D1:D5)) "Apple,Apple,Banana,Cherry,date" unconfirmed Sheets read _xlfn._xlws.FILTER and _xlfn._xlws.SORT, which is how Excel writes them, as #NAME?: confirm on the next import270=TEXTJOIN(",", TRUE, UNIQUE(Data!B1:B5)) "north,south,east" "north,south,east"271=TEXTJOIN(",", TRUE, UNIQUE(Data!G1:G4, FALSE, TRUE)) "1,3" "1,3"272=TEXTJOIN(",", TRUE, FLATTEN(Data!C1:D2)) "1,Apple,2,Banana" unconfirmed273=ROWS(FLATTEN(Data!C1:D2, Data!G1:G2)) 6 unconfirmed274=TEXTJOIN(",", TRUE, SORTN(Data!C1:C7, 3, 0, 1, FALSE)) "7,6,5" unconfirmed275=ROWS(SORTN(Data!G1:G4, 2, 1, 1, TRUE)) 3 unconfirmed276=TEXTJOIN(",", TRUE, CHOOSECOLS(Data!C1:E1, -1, 1)) "1.5,1" "1.5,1"277=ROWS(CHOOSEROWS(Data!A1:A7, 1, 3)) 2 2278=SUM(ARRAYFORMULA(Data!C1:C3*Data!G1:G3)) 11 unconfirmed279=ARRAYFORMULA(SUM(Data!C1:C3^2)) 14 14280=SUM(Data!C1:C3*2) 12 #VALUE! on purpose: 012 computes the arguments of functions that take ranges as arrays, as Excel 365 does; Sheets needs ARRAYFORMULA281=SUMPRODUCT(Data!C1:C3>1) 2 2282=SUMPRODUCT(Data!A1:A4, Data!C1:C4) 140 140283=SUM({1, 2; 3, 4}) 10 10284=INDEX({1, 2; 3, 4}, 2, 1) 3 3285## LET and LAMBDA286=LET(x, 2, y, x+1, x*y) 6 6287=LET(x, SEQUENCE(3), SUM(x)) 6 6288=LET(x, 1/0, 5) 5 5289=LAMBDA(x, x+1)() #N/A #N/A290=LET(f, LAMBDA(x, x+1), f(f(1))) 3 3291=SUM(MAP({1, 2, 3}, LAMBDA(x, x*10))) 60 60292=SUM(MAP(Data!A1:A3, Data!C1:C3, LAMBDA(a, b, a+b))) 66 66293=REDUCE(0, Data!C1:C5, LAMBDA(a, b, a+b)) 15 15294=TEXTJOIN(",", TRUE, SCAN(0, {1, 2, 3}, LAMBDA(a, b, a+b))) "1,3,6" "1,3,6"295=SUM(BYROW(Data!C1:E3, LAMBDA(r, COUNT(r)))) 6 6296=TEXTJOIN(",", TRUE, BYCOL(Data!C1:E3, LAMBDA(c, COUNTA(c)))) "3,3,3" "3,3,3"297=SUM(MAKEARRAY(3, 3, LAMBDA(r, c, r*c))) 36 36298## Logic299=IFS(Data!A1>20, "big", Data!A1>5, "medium") "medium" "medium"300=SWITCH(Data!B2, "north", 1, "south", 2) 2 2301=SWITCH(Data!B4, "north", 1, "south", 2, 0) 0 0302=XOR(Data!A1:A3) TRUE TRUE303## Math, statistics and finance304=PRODUCT(2, "3") 6 6305=MEDIAN(1, 2, 3, 4) 2.5 2.5306=MODE(Data!G1:G4) 2 2307=MODE(Data!C1:C5) #N/A #N/A308=STDEV(Data!A1:A7) 14.9303940559741 14.9303940559741309=VAR(Data!A1:A7) 222.916666666667 222.916666666667310=RANK(2, Data!G1:G4) 2 2311=RANK(99, Data!A1:A7) #N/A #N/A312=ROUND(PMT(0.05/12, 360, 200000), 6) -1073.643246 -1073.643246313=PMT(0, 10, 1000) -100 -100314=ROUND(PV(0.05/12, 360, -1000), 6) 186281.617046 186281.617046315=ROUND(NPER(0.01, -1000, 10000), 9) 10.588644459 10.588644459316=NPER(0.01, 100, 10000) -69.6607168935748 -69.6607168935748317=ROUND(RATE(48, -200, 8000), 9) 0.007701472 0.007701472318=ROUND(NPV(0.1, Data!H1:H4), 6) -19.124377 -19.124377319=ROUND(IRR(Data!H1:H4), 9) 0.088963395 0.088963395320=IRR(Data!C1:C5) #NUM! #NUM!