1package sheet23import (4 "errors"5 "math"6 "strconv"7 "strings"8)910// Rules that compare a cell with the other cells of the rule's ranges,11// as Excel's top 10, above average and duplicate values: the numbers in12// the top or bottom N (or N percent), those above or below their13// average, and values that appear more than once, or once. What they14// compare with is worked out once per recalculation, reading the values15// the ranges hold, as a color scale does.1617// rankStats is what a ranking rule compares a cell's value with.18type rankStats struct {19 cut float64 // top and bottom: the Nth value; average: the mean20 ok bool // the ranges hold a number to compare with21 count map[string]int // duplicates and uniques: how often each value appears22}2324func (c *looksCache) rank(s *Sheet, i int) *rankStats {25 if st, ok := c.ranks[i]; ok {26 return st27 }28 f := s.rules.formats[i]29 st := &rankStats{}30 switch f.Op {31 case RuleDuplicate, RuleUnique:32 st.count = map[string]int{}33 for _, r := range f.Ranges {34 for _, v := range s.cells.valuesIn(r) {35 if k, ok := dupKey(v); ok {36 st.count[k]++37 }38 }39 }40 default:41 var vals []float6442 for _, r := range f.Ranges {43 for _, v := range s.cells.valuesIn(r) {44 if v.Kind == Number {45 vals = append(vals, v.Num)46 }47 }48 }49 st.cut, st.ok = rankCut(f.Op, f.Args[0], vals)50 }51 c.ranks[i] = st52 return st53}5455// rankCut is what op compares with among vals: the Nth largest or56// smallest value, or the mean. vals is reordered.57func rankCut(op RuleOp, arg string, vals []float64) (float64, bool) {58 if len(vals) == 0 {59 return 0, false60 }61 if op == RuleAboveAverage || op == RuleBelowAverage {62 sum := 0.063 for _, v := range vals {64 sum += v65 }66 return sum / float64(len(vals)), true67 }68 n, _ := strconv.ParseFloat(strings.TrimSpace(arg), 64)69 if op == RuleTopPercent || op == RuleBottomPercent {70 n = math.Floor(float64(len(vals)) * n / 100) // as Excel: at least one71 }72 k := min(max(int(n), 1), len(vals))73 if op == RuleTop || op == RuleTopPercent {74 return nth(vals, len(vals)-k), true75 }76 return nth(vals, k-1), true77}7879// dupKey is what makes two values the same for duplicates: a number's80// value, or text ignoring case, as COUNTIF compares them. Blanks and81// errors have none.82func dupKey(v Value) (string, bool) {83 switch v.Kind {84 case Number:85 return "n" + strconv.FormatFloat(v.Num, 'g', -1, 64), true86 case Bool:87 return "b" + strconv.FormatFloat(v.Num, 'g', -1, 64), true88 case Text:89 if v.Str == "" {90 return "", false91 }92 return "t" + strings.ToLower(v.Str), true93 }94 return "", false95}9697// match reports whether a cell's value v meets ranking rule op.98func (st *rankStats) match(op RuleOp, v Value) bool {99 switch op {100 case RuleDuplicate, RuleUnique:101 k, ok := dupKey(v)102 return ok && (st.count[k] > 1) == (op == RuleDuplicate)103 }104 if !st.ok || v.Kind != Number {105 return false106 }107 switch op {108 case RuleTop, RuleTopPercent:109 return v.Num >= st.cut110 case RuleBottom, RuleBottomPercent:111 return v.Num <= st.cut112 case RuleAboveAverage:113 return v.Num > st.cut114 }115 return v.Num < st.cut116}117118// checkRank checks the N of a top or bottom rule: a count from 1 to119// 1000, or a percent from 1 to 100, as Excel takes them.120func checkRank(op RuleOp, arg string) error {121 n, err := strconv.Atoi(strings.TrimSpace(arg))122 switch {123 case (op == RuleTopPercent || op == RuleBottomPercent) && (err != nil || n < 1 || n > 100):124 return errors.New("Enter a percent from 1 to 100")125 case err != nil || n < 1 || n > 1000:126 return errors.New("Enter how many values, from 1 to 1000")127 }128 return nil129}130131// rankSummary describes a ranking rule: "Top 10", "Bottom 5%".132func rankSummary(op RuleOp, arg string) string {133 arg = strings.TrimSpace(arg)134 switch op {135 case RuleTop:136 return "Top " + arg137 case RuleBottom:138 return "Bottom " + arg139 case RuleTopPercent:140 return "Top " + arg + "%"141 case RuleBottomPercent:142 return "Bottom " + arg + "%"143 }144 return op.Title()145}