Skip to main content

sheetkit_core/formula/functions/
statistical.rs

1//! Statistical formula functions: AVERAGEIF, AVERAGEIFS, COUNTBLANK, COUNTIF,
2//! COUNTIFS, MEDIAN, MODE, LARGE, SMALL, RANK.
3
4use crate::cell::CellValue;
5use crate::error::Result;
6use crate::formula::ast::Expr;
7use crate::formula::eval::{coerce_to_number, reference_number, Evaluator};
8use crate::formula::functions::conversion::{checked_trunc_count, checked_trunc_i64};
9use crate::formula::functions::{
10    check_arg_count, collect_criteria_range_values, matches_prepared_criteria, prepare_criteria,
11};
12
13/// AVERAGEIF(range, criteria, \[average_range\])
14pub fn fn_averageif(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
15    check_arg_count("AVERAGEIF", args, 2, 3)?;
16    let range_vals = collect_criteria_range_values(&args[0], ctx)?;
17    let criteria_val = ctx.eval_expr(&args[1])?;
18    let criteria = prepare_criteria(criteria_val, ctx)?;
19    let avg_vals = if args.len() == 3 {
20        collect_criteria_range_values(&args[2], ctx)?
21    } else {
22        range_vals.clone()
23    };
24    let mut sum = 0.0;
25    let mut count = 0u64;
26    for (i, rv) in range_vals.iter().enumerate() {
27        if matches_prepared_criteria(rv, &criteria, ctx)? {
28            if let Some(n) = avg_vals.get(i).and_then(reference_number) {
29                sum += n;
30                count += 1;
31            }
32        }
33    }
34    if count == 0 {
35        return Ok(CellValue::Error("#DIV/0!".to_string()));
36    }
37    Ok(CellValue::Number(sum / count as f64))
38}
39
40/// AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
41pub fn fn_averageifs(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
42    check_arg_count("AVERAGEIFS", args, 3, 255)?;
43    if !(args.len() - 1).is_multiple_of(2) {
44        return Ok(CellValue::Error("#VALUE!".to_string()));
45    }
46    let avg_vals = collect_criteria_range_values(&args[0], ctx)?;
47    let pair_count = (args.len() - 1) / 2;
48    let mut criteria_ranges: Vec<Vec<CellValue>> = Vec::with_capacity(pair_count);
49    let mut criteria_values = Vec::with_capacity(pair_count);
50    for i in 0..pair_count {
51        let range_vals = collect_criteria_range_values(&args[1 + i * 2], ctx)?;
52        let crit_val = ctx.eval_expr(&args[2 + i * 2])?;
53        criteria_ranges.push(range_vals);
54        criteria_values.push(prepare_criteria(crit_val, ctx)?);
55    }
56    let mut sum = 0.0;
57    let mut count = 0u64;
58    for (idx, sv) in avg_vals.iter().enumerate() {
59        let mut all_match = true;
60        for (range_vals, criteria) in criteria_ranges.iter().zip(criteria_values.iter()) {
61            let Some(value) = range_vals.get(idx) else {
62                all_match = false;
63                break;
64            };
65            if !matches_prepared_criteria(value, criteria, ctx)? {
66                all_match = false;
67                break;
68            }
69        }
70        if all_match {
71            if let Some(n) = reference_number(sv) {
72                sum += n;
73                count += 1;
74            }
75        }
76    }
77    if count == 0 {
78        return Ok(CellValue::Error("#DIV/0!".to_string()));
79    }
80    Ok(CellValue::Number(sum / count as f64))
81}
82
83/// COUNTBLANK(range) - count empty cells in a range
84pub fn fn_countblank(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
85    check_arg_count("COUNTBLANK", args, 1, 1)?;
86    let values = collect_criteria_range_values(&args[0], ctx)?;
87    let count = values
88        .iter()
89        .filter(|v| matches!(v, CellValue::Empty))
90        .count();
91    Ok(CellValue::Number(count as f64))
92}
93
94/// COUNTIF(range, criteria)
95pub fn fn_countif(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
96    check_arg_count("COUNTIF", args, 2, 2)?;
97    let range_vals = collect_criteria_range_values(&args[0], ctx)?;
98    let criteria_val = ctx.eval_expr(&args[1])?;
99    let criteria = prepare_criteria(criteria_val, ctx)?;
100    let mut count = 0usize;
101    for value in &range_vals {
102        if matches_prepared_criteria(value, &criteria, ctx)? {
103            count += 1;
104        }
105    }
106    Ok(CellValue::Number(count as f64))
107}
108
109/// COUNTIFS(range1, criteria1, range2, criteria2, ...)
110pub fn fn_countifs(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
111    check_arg_count("COUNTIFS", args, 2, 255)?;
112    if !args.len().is_multiple_of(2) {
113        return Ok(CellValue::Error("#VALUE!".to_string()));
114    }
115    let pair_count = args.len() / 2;
116    let mut criteria_ranges: Vec<Vec<CellValue>> = Vec::with_capacity(pair_count);
117    let mut criteria_values = Vec::with_capacity(pair_count);
118    for i in 0..pair_count {
119        let range_vals = collect_criteria_range_values(&args[i * 2], ctx)?;
120        let crit_val = ctx.eval_expr(&args[i * 2 + 1])?;
121        criteria_ranges.push(range_vals);
122        criteria_values.push(prepare_criteria(crit_val, ctx)?);
123    }
124    let len = criteria_ranges.first().map_or(0, |r| r.len());
125    let mut count = 0u64;
126    for idx in 0..len {
127        let mut all_match = true;
128        for (range_vals, criteria) in criteria_ranges.iter().zip(criteria_values.iter()) {
129            let Some(value) = range_vals.get(idx) else {
130                all_match = false;
131                break;
132            };
133            if !matches_prepared_criteria(value, criteria, ctx)? {
134                all_match = false;
135                break;
136            }
137        }
138        if all_match {
139            count += 1;
140        }
141    }
142    Ok(CellValue::Number(count as f64))
143}
144
145/// MEDIAN(args...) - median of numeric values
146pub fn fn_median(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
147    check_arg_count("MEDIAN", args, 1, 255)?;
148    let mut nums = ctx.collect_numbers(args)?;
149    if nums.is_empty() {
150        return Ok(CellValue::Error("#NUM!".to_string()));
151    }
152    nums.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
153    let n = nums.len();
154    let median = if n % 2 == 1 {
155        nums[n / 2]
156    } else {
157        (nums[n / 2 - 1] + nums[n / 2]) / 2.0
158    };
159    Ok(CellValue::Number(median))
160}
161
162/// MODE(args...) - most frequently occurring value
163pub fn fn_mode(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
164    check_arg_count("MODE", args, 1, 255)?;
165    let nums = ctx.collect_numbers(args)?;
166    if nums.is_empty() {
167        return Ok(CellValue::Error("#N/A".to_string()));
168    }
169    let mut counts: std::collections::HashMap<u64, (f64, usize)> = std::collections::HashMap::new();
170    for &n in &nums {
171        let key = n.to_bits();
172        let entry = counts.entry(key).or_insert((n, 0));
173        entry.1 += 1;
174    }
175    let max_count = counts.values().map(|(_, c)| *c).max().unwrap_or(0);
176    if max_count <= 1 {
177        return Ok(CellValue::Error("#N/A".to_string()));
178    }
179    // Return the first value that has max_count occurrences (in order of appearance)
180    for &n in &nums {
181        let key = n.to_bits();
182        if let Some((_, c)) = counts.get(&key) {
183            if *c == max_count {
184                return Ok(CellValue::Number(n));
185            }
186        }
187    }
188    Ok(CellValue::Error("#N/A".to_string()))
189}
190
191/// LARGE(array, k) - k-th largest value
192pub fn fn_large(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
193    check_arg_count("LARGE", args, 2, 2)?;
194    let mut nums = collect_criteria_range_values(&args[0], ctx)?
195        .iter()
196        .filter_map(reference_number)
197        .collect::<Vec<f64>>();
198    let k = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[1])?)?).unwrap_or(0);
199    if k == 0 || k > nums.len() {
200        return Ok(CellValue::Error("#NUM!".to_string()));
201    }
202    nums.sort_by(|a, b| b.partial_cmp(a).unwrap_or(std::cmp::Ordering::Equal));
203    Ok(CellValue::Number(nums[k - 1]))
204}
205
206/// SMALL(array, k) - k-th smallest value
207pub fn fn_small(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
208    check_arg_count("SMALL", args, 2, 2)?;
209    let mut nums = collect_criteria_range_values(&args[0], ctx)?
210        .iter()
211        .filter_map(reference_number)
212        .collect::<Vec<f64>>();
213    let k = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[1])?)?).unwrap_or(0);
214    if k == 0 || k > nums.len() {
215        return Ok(CellValue::Error("#NUM!".to_string()));
216    }
217    nums.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
218    Ok(CellValue::Number(nums[k - 1]))
219}
220
221/// RANK(number, ref, \[order\]) - rank of a number in a list.
222/// order=0 or omitted: descending (largest=1). order=nonzero: ascending (smallest=1).
223pub fn fn_rank(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
224    check_arg_count("RANK", args, 2, 3)?;
225    let number = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
226    let ref_vals = collect_criteria_range_values(&args[1], ctx)?;
227    let order = if args.len() > 2 {
228        let value = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
229        let Some(order) = checked_trunc_i64(value, i64::MIN, i64::MAX) else {
230            return Ok(CellValue::Error("#NUM!".to_string()));
231        };
232        order
233    } else {
234        0
235    };
236    let nums: Vec<f64> = ref_vals.iter().filter_map(reference_number).collect();
237    let rank = if order == 0 {
238        // Descending: count how many are greater than number, +1
239        nums.iter().filter(|&&n| n > number).count() + 1
240    } else {
241        // Ascending: count how many are less than number, +1
242        nums.iter().filter(|&&n| n < number).count() + 1
243    };
244    // If the number is not in the list, return #N/A
245    if !nums.iter().any(|&n| (n - number).abs() < f64::EPSILON) {
246        return Ok(CellValue::Error("#N/A".to_string()));
247    }
248    Ok(CellValue::Number(rank as f64))
249}
250
251#[cfg(test)]
252mod tests {
253    use crate::cell::CellValue;
254    use crate::formula::eval::{evaluate, CellSnapshot};
255    use crate::formula::parser::parse_formula;
256
257    fn eval_with_data(formula: &str, data: &[(&str, u32, u32, CellValue)]) -> CellValue {
258        let mut snap = CellSnapshot::new("Sheet1".to_string());
259        for (sheet, col, row, val) in data {
260            snap.set_cell(sheet, *col, *row, val.clone());
261        }
262        let expr = parse_formula(formula).unwrap();
263        evaluate(&expr, &snap).unwrap()
264    }
265
266    fn eval(formula: &str) -> CellValue {
267        eval_with_data(formula, &[])
268    }
269
270    // AVERAGEIF tests
271
272    #[test]
273    fn averageif_greater_than() {
274        let data = vec![
275            ("Sheet1", 1, 1, CellValue::Number(2.0)),
276            ("Sheet1", 1, 2, CellValue::Number(4.0)),
277            ("Sheet1", 1, 3, CellValue::Number(6.0)),
278        ];
279        let result = eval_with_data("AVERAGEIF(A1:A3,\">3\")", &data);
280        assert_eq!(result, CellValue::Number(5.0));
281    }
282
283    #[test]
284    fn averageif_with_avg_range() {
285        let data = vec![
286            ("Sheet1", 1, 1, CellValue::String("A".to_string())),
287            ("Sheet1", 2, 1, CellValue::Number(10.0)),
288            ("Sheet1", 1, 2, CellValue::String("B".to_string())),
289            ("Sheet1", 2, 2, CellValue::Number(20.0)),
290            ("Sheet1", 1, 3, CellValue::String("A".to_string())),
291            ("Sheet1", 2, 3, CellValue::Number(30.0)),
292        ];
293        let result = eval_with_data("AVERAGEIF(A1:A3,\"A\",B1:B3)", &data);
294        assert_eq!(result, CellValue::Number(20.0));
295    }
296
297    #[test]
298    fn averageif_no_match() {
299        let data = vec![
300            ("Sheet1", 1, 1, CellValue::Number(1.0)),
301            ("Sheet1", 1, 2, CellValue::Number(2.0)),
302        ];
303        let result = eval_with_data("AVERAGEIF(A1:A2,\">100\")", &data);
304        assert_eq!(result, CellValue::Error("#DIV/0!".to_string()));
305    }
306
307    // AVERAGEIFS test
308
309    #[test]
310    fn averageifs_multi_criteria() {
311        let data = vec![
312            ("Sheet1", 1, 1, CellValue::String("A".to_string())),
313            ("Sheet1", 2, 1, CellValue::Number(1.0)),
314            ("Sheet1", 3, 1, CellValue::Number(100.0)),
315            ("Sheet1", 1, 2, CellValue::String("A".to_string())),
316            ("Sheet1", 2, 2, CellValue::Number(5.0)),
317            ("Sheet1", 3, 2, CellValue::Number(200.0)),
318            ("Sheet1", 1, 3, CellValue::String("B".to_string())),
319            ("Sheet1", 2, 3, CellValue::Number(3.0)),
320            ("Sheet1", 3, 3, CellValue::Number(300.0)),
321        ];
322        let result = eval_with_data("AVERAGEIFS(C1:C3,A1:A3,\"A\",B1:B3,\">2\")", &data);
323        assert_eq!(result, CellValue::Number(200.0));
324    }
325
326    // COUNTBLANK tests
327
328    #[test]
329    fn countblank_basic() {
330        let data = vec![
331            ("Sheet1", 1, 1, CellValue::Number(1.0)),
332            ("Sheet1", 1, 2, CellValue::Empty),
333            ("Sheet1", 1, 3, CellValue::String("x".to_string())),
334            ("Sheet1", 1, 4, CellValue::Empty),
335        ];
336        let result = eval_with_data("COUNTBLANK(A1:A4)", &data);
337        assert_eq!(result, CellValue::Number(2.0));
338    }
339
340    // COUNTIF tests
341
342    #[test]
343    fn countif_greater_than() {
344        let data = vec![
345            ("Sheet1", 1, 1, CellValue::Number(1.0)),
346            ("Sheet1", 1, 2, CellValue::Number(5.0)),
347            ("Sheet1", 1, 3, CellValue::Number(10.0)),
348        ];
349        let result = eval_with_data("COUNTIF(A1:A3,\">3\")", &data);
350        assert_eq!(result, CellValue::Number(2.0));
351    }
352
353    #[test]
354    fn countif_text_match() {
355        let data = vec![
356            ("Sheet1", 1, 1, CellValue::String("Apple".to_string())),
357            ("Sheet1", 1, 2, CellValue::String("Banana".to_string())),
358            ("Sheet1", 1, 3, CellValue::String("apple".to_string())),
359        ];
360        let result = eval_with_data("COUNTIF(A1:A3,\"Apple\")", &data);
361        assert_eq!(result, CellValue::Number(2.0));
362    }
363
364    // COUNTIFS test
365
366    #[test]
367    fn countifs_multi_criteria() {
368        let data = vec![
369            ("Sheet1", 1, 1, CellValue::String("A".to_string())),
370            ("Sheet1", 2, 1, CellValue::Number(10.0)),
371            ("Sheet1", 1, 2, CellValue::String("A".to_string())),
372            ("Sheet1", 2, 2, CellValue::Number(20.0)),
373            ("Sheet1", 1, 3, CellValue::String("B".to_string())),
374            ("Sheet1", 2, 3, CellValue::Number(30.0)),
375        ];
376        let result = eval_with_data("COUNTIFS(A1:A3,\"A\",B1:B3,\">15\")", &data);
377        assert_eq!(result, CellValue::Number(1.0));
378    }
379
380    // MEDIAN tests
381
382    #[test]
383    fn median_odd_count() {
384        assert_eq!(eval("MEDIAN(1,3,2)"), CellValue::Number(2.0));
385    }
386
387    #[test]
388    fn median_even_count() {
389        assert_eq!(eval("MEDIAN(1,2,3,4)"), CellValue::Number(2.5));
390    }
391
392    #[test]
393    fn median_single() {
394        assert_eq!(eval("MEDIAN(42)"), CellValue::Number(42.0));
395    }
396
397    // MODE tests
398
399    #[test]
400    fn mode_basic() {
401        assert_eq!(eval("MODE(1,2,2,3,3,3)"), CellValue::Number(3.0));
402    }
403
404    #[test]
405    fn mode_no_repeat() {
406        assert_eq!(eval("MODE(1,2,3)"), CellValue::Error("#N/A".to_string()));
407    }
408
409    // LARGE tests
410
411    #[test]
412    fn large_basic() {
413        let data = vec![
414            ("Sheet1", 1, 1, CellValue::Number(5.0)),
415            ("Sheet1", 1, 2, CellValue::Number(3.0)),
416            ("Sheet1", 1, 3, CellValue::Number(8.0)),
417            ("Sheet1", 1, 4, CellValue::Number(1.0)),
418        ];
419        let result = eval_with_data("LARGE(A1:A4,2)", &data);
420        assert_eq!(result, CellValue::Number(5.0));
421    }
422
423    #[test]
424    fn large_k_out_of_range() {
425        let data = vec![
426            ("Sheet1", 1, 1, CellValue::Number(1.0)),
427            ("Sheet1", 1, 2, CellValue::Number(2.0)),
428        ];
429        let result = eval_with_data("LARGE(A1:A2,5)", &data);
430        assert_eq!(result, CellValue::Error("#NUM!".to_string()));
431    }
432
433    // SMALL tests
434
435    #[test]
436    fn small_basic() {
437        let data = vec![
438            ("Sheet1", 1, 1, CellValue::Number(5.0)),
439            ("Sheet1", 1, 2, CellValue::Number(3.0)),
440            ("Sheet1", 1, 3, CellValue::Number(8.0)),
441            ("Sheet1", 1, 4, CellValue::Number(1.0)),
442        ];
443        let result = eval_with_data("SMALL(A1:A4,2)", &data);
444        assert_eq!(result, CellValue::Number(3.0));
445    }
446
447    // RANK tests
448
449    #[test]
450    fn rank_descending() {
451        let data = vec![
452            ("Sheet1", 1, 1, CellValue::Number(5.0)),
453            ("Sheet1", 1, 2, CellValue::Number(3.0)),
454            ("Sheet1", 1, 3, CellValue::Number(8.0)),
455            ("Sheet1", 1, 4, CellValue::Number(1.0)),
456        ];
457        // 5 is 2nd largest in [5,3,8,1]
458        let result = eval_with_data("RANK(5,A1:A4)", &data);
459        assert_eq!(result, CellValue::Number(2.0));
460    }
461
462    #[test]
463    fn rank_ascending() {
464        let data = vec![
465            ("Sheet1", 1, 1, CellValue::Number(5.0)),
466            ("Sheet1", 1, 2, CellValue::Number(3.0)),
467            ("Sheet1", 1, 3, CellValue::Number(8.0)),
468            ("Sheet1", 1, 4, CellValue::Number(1.0)),
469        ];
470        // 5 is 3rd smallest in [5,3,8,1]
471        let result = eval_with_data("RANK(5,A1:A4,1)", &data);
472        assert_eq!(result, CellValue::Number(3.0));
473    }
474
475    #[test]
476    fn rank_not_found() {
477        let data = vec![
478            ("Sheet1", 1, 1, CellValue::Number(1.0)),
479            ("Sheet1", 1, 2, CellValue::Number(2.0)),
480        ];
481        let result = eval_with_data("RANK(99,A1:A2)", &data);
482        assert_eq!(result, CellValue::Error("#N/A".to_string()));
483    }
484}