1package sheet23import "testing"45// Where Sheets and Excel differ, 012 follows Sheets (the Sheets corpus in6// internal/fileio checks these against Sheets itself).7func TestSheetsSemantics(t *testing.T) {8 s := fixture(t)9 for a, in := range map[string]string{"J1": "'3", "J2": "=1/0", "J3": "FALSE", "J4": "=NA()"} {10 if err := s.Set(at(a), in); err != nil {11 t.Fatal(err)12 }13 }14 checkFormulas(t, s, []fnCase{15 // Text.16 {`=UPPER("straße")`, txt("STRASSE")},17 {`=UPPER("dž")`, txt("DŽ")},18 {`=TEXT(TRUE, "0")`, txt("TRUE")},19 {`=TEXT(FALSE, "0.00")`, txt("FALSE")},20 {`=TEXT(0.75, "# ?/?")`, txt("3/4")},21 {`=TEXT(2.5, "# ?/?")`, txt("2 1/2")},22 // Numbers compare at the 15 digits a cell shows.23 {"=0.1+0.2=0.3", boolean(true)},24 {"=(1-0.9)=0.1", boolean(true)},25 {"=SUM(0.1, 0.2, 0.3)=0.6", boolean(true)},26 {"=0.1+0.2<0.3", boolean(false)},27 {"=0.1+0.2>=0.3", boolean(true)},28 {"=0.1+0.2-0.3=0", boolean(false)},29 {"=1.00000000000001=1", boolean(false)},30 {`=COUNTIF(E1:E3, 0.1+0.2+1.2)`, num(1)},31 {"=123456789012345678", num(123456789012345000)},32 {"=123456789012345678=123456789012345000", boolean(true)},33 // ROUND is half away from zero on the shortest decimal, as in34 // Sheets and Excel; exact inputs stay exact.35 {"=ROUND(2.675, 2)=2.68", boolean(true)},36 {"=ROUND(-1.005, 2)=-1.01", boolean(true)},37 {"=ROUND(1.5, 14)-1.5=0", boolean(true)},38 {"=ROUND(0.1+0.2, 15)-0.3=0", boolean(true)},39 {"=ROUND(-2.5)", num(-3)},40 // Errors and conditions.41 {"=IFERROR(NA())", Value{}},42 {`=ISBLANK(IFERROR(NA()))`, boolean(true)},43 {`=IF("TRUE", 1, 2)`, num(1)},44 {`=IF("false", 1, 2)`, num(2)},45 {`=IF("", 1, 2)`, num(2)},46 {`=IF("abc", 1, 2)`, ErrValue},47 {`=NOT("")`, boolean(true)},48 {`=NOT("TRUE")`, boolean(false)},49 {`=AND("TRUE", 1)`, boolean(true)},50 {"=COUNT(J1:J4)", num(0)},51 {"=COUNTA(J1:J4)", num(4)},52 {"=COUNT(1/0, 2)", num(1)},53 {"=SUM(J1:J4)", ErrDiv0},54 {"=COUNTIF(J1:J4, 3)", num(1)},55 {`=COUNTIF(J1:J4, "<>3")`, num(3)},56 {`=COUNTIF(J1:J4, ">2")`, num(0)},57 // Positions out of range, and whole rows and columns.58 {`=CHOOSE(0, "a")`, ErrNum},59 {"=INDEX(A1:A3, 5)", ErrNum},60 {"=ROWS(INDEX(A1:C3, 0, 1))", num(3)},61 {"=COLUMNS(INDEX(A1:C3, 2, 0))", num(3)},62 {"=SUM(INDEX(C1:E3, 0, 1))", num(6)},63 {"=ROWS(SEQUENCE(0))", ErrNum},64 {"=ROWS(1/0)", ErrDiv0},65 // Arrays and names.66 {"=SUMPRODUCT(C1:C3>1)", num(2)},67 {"=SUMPRODUCT(C1:C3>1, C1:C3)", num(5)},68 {"=LET(x, 1/0, 5)", num(5)},69 {"=LET(x, 1/0, x+1)", ErrDiv0},70 })71}