1package sheet23import (4 "fmt"5 "math/rand/v2"6 "testing"7)89// Formulas read ranges sparsely (only the cells that hold something),10// clip positional reads to the data and share running aggregates across a11// recalculation. Each formula here gives exactly what reading every12// address of its ranges gives, on sheets with blanks, text, errors and13// empty strings scattered through the data.14func TestRangeReadsMatchDense(t *testing.T) {15 formulas := []string{16 "=SUM(A1:A2000)", "=SUM(A1:E2000)", "=AVERAGE(B3:B1500)", "=COUNT(A:A)", "=COUNTA(A1:E3000)",17 "=MIN(C1:C2000)", "=MAX(A1:C2000)", "=PRODUCT(D1:D40)", "=SUM(A1:A5, 1.5, A6:A900)",18 `=SUMIF(A1:A2000, "")`, `=SUMIF(A1:A2000, ">5", C1:C2000)`, `=SUMIF(B1:B2000, "<>x", C1)`,19 `=COUNTIF(A1:A2000, "")`, `=COUNTIF(B1:B2000, "<>b")`, `=COUNTIF(A1:C2000, ">3")`,20 "=COUNTBLANK(A1:E2000)", `=COUNTIFS(A1:A2000, "", B1:B2000, "")`, `=SUMIFS(C1:C2000, A1:A2000, "<>")`,21 `=AVERAGEIF(A1:A2000, ">0")`, `=AVERAGEIFS(C1:C2000, B1:B2000, "")`,22 "=SUMPRODUCT(A1:A2000, C1:C2000)", "=SUMPRODUCT(A1:B2000, C1:D2000)",23 "=MATCH(7, A1:A2000, 0)", "=MATCH(Z99, A1:A2000, 0)", "=MATCH(Z99, A1:A2000)", "=MATCH(1E9, C1:C2000)",24 "=MATCH(Z99, A1:A2000, -1)", "=MATCH(-1, C1:C2000, -1)",25 "=VLOOKUP(Z99, A1:C2000, 3, FALSE)", "=VLOOKUP(5, A1:C2000, 2)", "=HLOOKUP(Z99, A1:E2000, 2, FALSE)",26 "=XLOOKUP(Z99, A1:A2000, C1:C2000)", "=XLOOKUP(Z99, A1:A2000, C1:C2000, , 0, -1)",27 "=XLOOKUP(3, A1:A2000, C1:C2000, , 1)", "=XLOOKUP(3, A1:A2000, C1:C2000, , -1, -1)",28 `=TEXTJOIN(",", FALSE, B1:B60)`, `=LEN(TEXTJOIN("-", FALSE, B1:C2000))`, `=TEXTJOIN(",", TRUE, B1:B2000)`,29 "=CONCATENATE(B1:B80)", "=MEDIAN(A1:A2000)", "=RANK(C5, C1:C2000)", "=ROWS(A:A)", "=COLUMNS(1:1)",30 "=INDEX(A:A, 70000)", "=MATCH(Z99, F1:F2000, 0)", "=MATCH(Z99, F1:F2000)", "=MATCH(Z99, F1:F2000, -1)",31 "=XLOOKUP(Z99, F1:F2000, C1:C2000)", "=XLOOKUP(Z99, F1:F2000, C1:C2000, , 0, -1)", "=XLOOKUP(Z99, F1:F2000, C1:C2000, , 1)",32 "=VLOOKUP(Z99, F1:G2000, 2, FALSE)", "=VLOOKUP(Z99, F1:G2000, 2)", `=COUNTIF(F1:F2000, "")`, "=COUNTBLANK(F1:F2000)",33 `=LEN(TEXTJOIN(",", FALSE, F1:F2000))`, `=SUMIF(F1:F2000, "", G1:G2000)`, `=SUMIF(F1:F2000, "<>1", A1:A2000)`, "=SUM(Nope!A1:A2000)", "=COUNTBLANK(Nope!A1:A20)", `=COUNTIF(Nope!A1:A20, "")`,34 }35 for seed := range 6 {36 rng := rand.New(rand.NewPCG(uint64(seed), 7))37 s := New()38 fill := []string{"", "", "", "1", "2.5", "-3", "7", "x", "b", "TRUE", `=""`, "=1/0", "'5"}39 if seed%2 == 1 {40 fill = fill[:10] // no errors: aggregates get past them41 }42 for row := range 300 {43 for col := range 5 {44 if in := fill[rng.IntN(len(fill))]; in != "" {45 s.Set(Addr{Col: col, Row: row * (1 + seed%3)}, in)46 }47 }48 }49 for row := range 60 {50 s.Set(Addr{Col: 5, Row: row}, fill[3+rng.IntN(len(fill)-3)])51 s.Set(Addr{Col: 6, Row: row + 1}, "4")52 }53 for i, f := range formulas {54 s.Set(Addr{Col: 7 + i/200, Row: i % 200}, f)55 }56 // Running totals, down a column and over itself.57 for row := range 120 {58 s.Set(Addr{Col: 11, Row: row}, fmt.Sprintf("=SUM($A$1:A%d)", row+1))59 s.Set(Addr{Col: 12, Row: row}, fmt.Sprintf("=SUM($M$1:M%d)+1", row))60 s.Set(Addr{Col: 13, Row: row}, fmt.Sprintf("=COUNTA($A$1:$C%d)+MAX($C$3:C%d)", row+20, row+20))61 }62 s.Set(Addr{Col: 12, Row: 0}, "1")63 s.RecalcAll()64 sparse := snapshotValues(s)65 denseReads = true66 s.RecalcAll()67 dense := snapshotValues(s)68 denseReads = false69 for a, v := range dense {70 if sparse[a] != v {71 t.Errorf("seed %d: %s %q = %+v, reading every cell gives %+v", seed, a, s.Cell(a).Input, sparse[a], v)72 }73 }74 }75}7677func snapshotValues(s *Sheet) map[Addr]Value {78 out := map[Addr]Value{}79 for a, c := range s.cells.all() {80 out[a] = c.Value81 }82 return out83}8485// A range whose running aggregate is being read by the formulas it86// contains still gives each formula its own value.87func TestRunningAggregateReentry(t *testing.T) {88 s := New()89 s.Set(at("A1"), "1")90 for row := 2; row <= 40; row++ {91 s.Set(Addr{Col: 0, Row: row - 1}, fmt.Sprintf("=SUM($A$1:A%d)", row-1))92 }93 // A(n) = 2^(n-2) for n >= 2.94 if got := s.Value(at("A40")).Num; got != 1<<38 {95 t.Errorf("A40 = %v, want %v", got, 1<<38)96 }97 s.Set(at("A1"), "2")98 if got := s.Value(at("A40")).Num; got != 1<<39 {99 t.Errorf("after the edit A40 = %v, want %v", got, 1<<39)100 }101}