1package sheet23import "testing"45func TestStatsFunctions(t *testing.T) {6 checkFormulas(t, fixture(t), []fnCase{7 {`=AVERAGEIF(B1:B5, "north", C1:C5)`, num(3)},8 {`=AVERAGEIF(A1:A7, ">0")`, num(20)},9 {`=AVERAGEIF(A1:A7, ">100")`, ErrDiv0},10 {`=AVERAGEIFS(C1:C5, B1:B5, "north", C1:C5, ">1")`, num(4)},11 {`=COUNTIF(B1:B7, "north")`, num(3)},12 {`=COUNTIF(B1:B7, "NORTH")`, num(3)}, // case-insensitive13 {`=COUNTIF(B1:B7, "?o*")`, num(4)},14 {`=COUNTIF(B1:B7, "Ab~*d")`, num(1)},15 {`=COUNTIF(A1:A7, "<>10")`, num(6)}, // blanks and text count as not equal16 {`=COUNTIF(A1:A7, "")`, num(1)},17 {`=COUNTIF(B1:B7, "")`, num(1)}, // "" from a formula counts as blank18 {`=COUNTIF(A1:A7, "<>")`, num(6)},19 {`=COUNTIF(A1:A7, TRUE)`, num(1)},20 {`=COUNTIF(A1:A7, ">=10")`, num(3)},21 {`=COUNTIF(F1:F5, ">1/1/2026")`, num(2)}, // date criteria compare as dates22 {`=COUNTIF(B1:B7, ">m")`, num(4)},23 {`=COUNTIFS(B1:B5, "north", C1:C5, "<5")`, num(2)},24 {"=COUNTBLANK(A1:A7)", num(1)},25 {"=COUNTBLANK(B1:B7)", num(1)},26 {"=MEDIAN(A1:A7)", num(15)},27 {"=MEDIAN(C1:C5)", num(3)},28 {"=MEDIAN(A4)", ErrNum},29 {`=MEDIAN("a")`, ErrValue},30 {"=MODE(G1:G4)", num(2)},31 {"=MODE(C1:C5)", ErrNA},32 {"=STDEV(A1:A7)", num(14.930394055974098)},33 {"=STDEVP(A1:A7)", num(12.93010054098575)},34 {"=VAR(A1:A7)", num(222.91666666666666)},35 {"=VARP(A1:A7)", num(167.1875)},36 {"=STDEV(5)", ErrDiv0},37 {"=LARGE(A1:A7, 1)", num(30)},38 {"=LARGE(A1:A7, 4)", num(-5)},39 {"=LARGE(A1:A7, 5)", ErrNum},40 {"=SMALL(C1:C7, 2)", num(2)},41 {"=RANK(20, A1:A7)", num(2)},42 {"=RANK(20, A1:A7, 1)", num(3)},43 {"=RANK(2, G1:G4)", num(2)},44 {"=RANK(99, A1:A7)", ErrNA},45 {"=RANK.EQ(20, A1:A7)", num(2)},46 {"=RANK(1/0, A1:A7)", ErrDiv0},47 {"=MODE.SNGL(G1:G4)", num(2)},48 {"=MODE(3, 2, 2, 3, 1)", num(3)}, // the first read of the most common49 {"=PERCENTILE(A1:A7, 0.5)", num(15)},50 {"=PERCENTILE(A1:A7, 0.25)", num(6.25)},51 {"=PERCENTILE.INC(A1:A7, 1)", num(30)},52 {"=PERCENTILE(A1:A7, 1.5)", ErrNum},53 {"=PERCENTILE(A4, 0.5)", ErrNum},54 {"=PERCENTILE.EXC(C1:C5, 0.25)", num(1.5)},55 {"=PERCENTILE.EXC(C1:C5, 0.1)", ErrNum},56 {"=PERCENTILE.EXC(C1:C5, 0)", ErrNum},57 {"=QUARTILE(C1:C5, 1)", num(2)},58 {"=QUARTILE(C1:C5, 0)", num(1)},59 {"=QUARTILE(C1:C5, 4.9)", num(5)},60 {"=QUARTILE(C1:C5, 5)", ErrNum},61 {"=QUARTILE.INC(C1:C5, 3)", num(4)},62 {"=QUARTILE.EXC(C1:C5, 1)", num(1.5)},63 {"=QUARTILE.EXC(C1:C5, 0)", ErrNum},64 })65}