formualizer_eval/builtins/stats/
mod.rs

1//! Statistical basic functions (Sprint 6)
2//!
3//! Implementations target Excel semantic parity for:
4//! LARGE, SMALL, RANK.EQ, RANK.AVG, MEDIAN, STDEV.S, STDEV.P, VAR.S, VAR.P,
5//! PERCENTILE.INC, PERCENTILE.EXC, QUARTILE.INC, QUARTILE.EXC.
6//!
7//! Notes:
8//! - We currently materialize numeric values into a Vec<f64>. For large ranges this could be
9//!   optimized with streaming selection algorithms (nth_element / partial sort). TODO(perf).
10//! - Text/boolean coercion nuance: For Excel statistical functions, values coming from range
11//!   references should ignore text and logical values (they are skipped), while direct scalar
12//!   arguments still coerce (e.g. =STDEV(1,TRUE) treats TRUE as 1). This file now implements that
13//!   distinction. TODO(excel-nuance): refine numeric text literal vs non‑numeric text handling.
14//! - Errors encountered in any argument propagate immediately.
15//! - Empty numeric sets produce Excel-specific errors (#NUM! for LARGE/SMALL, #N/A for rank target
16//!   out of range, #DIV/0! for STDEV/VAR sample with n < 2, etc.).
17
18use super::super::builtins::utils::{ARG_RANGE_NUM_LENIENT_ONE, coerce_num};
19use crate::args::ArgSchema;
20use crate::function::Function;
21use crate::traits::{ArgumentHandle, FunctionContext};
22use formualizer_common::{ExcelError, LiteralValue};
23// use std::collections::BTreeMap; // removed unused import
24use formualizer_macros::func_caps;
25
26/// Collect numeric inputs applying Excel statistical semantics:
27/// - Range references: include only numeric cells; skip text, logical, blank. Errors propagate.
28/// - Direct scalar arguments: attempt numeric coercion (so TRUE/FALSE, numeric text are included if
29///   coerce_num succeeds). Non-numeric text is ignored (Excel would treat a direct non-numeric text
30///   argument as #VALUE! in some contexts; covered by TODO for finer parity).
31fn collect_numeric_stats(args: &[ArgumentHandle]) -> Result<Vec<f64>, ExcelError> {
32    let mut out = Vec::new();
33    for a in args {
34        // Special-case: inline array literal argument should be treated like a list of direct scalar
35        // arguments (not a by-ref range). This allows boolean/text coercion per element akin to
36        // passing multiple scalars to the function.
37        match a.ast().node_type.clone() {
38            formualizer_parse::parser::ASTNodeType::Literal(LiteralValue::Array(arr)) => {
39                for row in arr.into_iter() {
40                    for cell in row.into_iter() {
41                        match cell {
42                            LiteralValue::Error(e) => return Err(e),
43                            other => {
44                                if let Ok(n) = coerce_num(&other) {
45                                    out.push(n);
46                                }
47                            }
48                        }
49                    }
50                }
51            }
52            _ => {
53                if let Ok(view) = a.range_view() {
54                    view.for_each_cell(&mut |v| {
55                        match v {
56                            LiteralValue::Error(e) => return Err(e.clone()),
57                            LiteralValue::Number(n) => out.push(*n),
58                            LiteralValue::Int(i) => out.push(*i as f64),
59                            _ => {}
60                        }
61                        Ok(())
62                    })?;
63                } else {
64                    match a.value()?.as_ref() {
65                        LiteralValue::Error(e) => return Err(e.clone()),
66                        other => {
67                            if let Ok(n) = coerce_num(other) {
68                                out.push(n);
69                            }
70                        }
71                    }
72                }
73            }
74        }
75    }
76    Ok(out)
77}
78
79fn percentile_inc(sorted: &[f64], p: f64) -> Result<f64, ExcelError> {
80    if sorted.is_empty() {
81        return Err(ExcelError::new_num());
82    }
83    if !(0.0..=1.0).contains(&p) {
84        return Err(ExcelError::new_num());
85    }
86    if sorted.len() == 1 {
87        return Ok(sorted[0]);
88    }
89    let n = sorted.len() as f64;
90    let rank = p * (n - 1.0); // 0-based rank
91    let lo = rank.floor() as usize;
92    let hi = rank.ceil() as usize;
93    if lo == hi {
94        return Ok(sorted[lo]);
95    }
96    let frac = rank - (lo as f64);
97    Ok(sorted[lo] + (sorted[hi] - sorted[lo]) * frac)
98}
99
100fn percentile_exc(sorted: &[f64], p: f64) -> Result<f64, ExcelError> {
101    // Excel PERCENTILE.EXC requires 0 < p < 1 and uses (n+1) basis; invalid if rank<1 or >n
102    if sorted.is_empty() {
103        return Err(ExcelError::new_num());
104    }
105    if !(0.0..=1.0).contains(&p) || p <= 0.0 || p >= 1.0 {
106        return Err(ExcelError::new_num());
107    }
108    let n = sorted.len() as f64;
109    let rank = p * (n + 1.0); // 1..n domain
110    if rank < 1.0 || rank > n {
111        return Err(ExcelError::new_num());
112    }
113    let lo = rank.floor();
114    let hi = rank.ceil();
115    if (lo - hi).abs() < f64::EPSILON {
116        return Ok(sorted[(lo as usize) - 1]);
117    }
118    let frac = rank - lo;
119    let lo_idx = (lo as usize) - 1;
120    let hi_idx = (hi as usize) - 1;
121    Ok(sorted[lo_idx] + (sorted[hi_idx] - sorted[lo_idx]) * frac)
122}
123
124/// RANK.EQ(number, ref, [order]) Excel semantics:
125/// - order omitted or 0 => descending (largest value rank 1)
126/// - order non-zero => ascending (smallest value rank 1)
127/// - ties return same rank (position of first in ordering)
128#[derive(Debug)]
129pub struct RankEqFn;
130impl Function for RankEqFn {
131    func_caps!(PURE, NUMERIC_ONLY);
132    fn name(&self) -> &'static str {
133        "RANK.EQ"
134    }
135    fn aliases(&self) -> &'static [&'static str] {
136        &["RANK"]
137    }
138    fn min_args(&self) -> usize {
139        2
140    }
141    fn variadic(&self) -> bool {
142        true
143    } // allow optional order
144    fn arg_schema(&self) -> &'static [ArgSchema] {
145        &ARG_RANGE_NUM_LENIENT_ONE[..]
146    }
147    fn eval_scalar<'a, 'b>(
148        &self,
149        args: &'a [ArgumentHandle<'a, 'b>],
150        _ctx: &dyn FunctionContext,
151    ) -> Result<LiteralValue, ExcelError> {
152        if args.len() < 2 {
153            return Ok(LiteralValue::Error(ExcelError::new_na()));
154        }
155        let target = match coerce_num(args[0].value()?.as_ref()) {
156            Ok(n) => n,
157            Err(_) => return Ok(LiteralValue::Error(ExcelError::new_na())),
158        };
159        // optional order arg at end if 3 args
160        let order = if args.len() >= 3 {
161            coerce_num(args[2].value()?.as_ref()).unwrap_or(0.0)
162        } else {
163            0.0
164        };
165        let nums = collect_numeric_stats(&args[1..2])?; // only one ref range per Excel spec
166        if nums.is_empty() {
167            return Ok(LiteralValue::Error(ExcelError::new_na()));
168        }
169        let mut sorted = nums; // copy
170        if order.abs() < 1e-12 {
171            // descending
172            sorted.sort_by(|a, b| b.partial_cmp(a).unwrap());
173        } else {
174            // ascending
175            sorted.sort_by(|a, b| a.partial_cmp(b).unwrap());
176        }
177        for (i, &v) in sorted.iter().enumerate() {
178            if (v - target).abs() < 1e-12 {
179                return Ok(LiteralValue::Number((i + 1) as f64));
180            }
181        }
182        Ok(LiteralValue::Error(ExcelError::new_na()))
183    }
184}
185
186/// RANK.AVG(number, ref, [order]) ties return average of ranks
187#[derive(Debug)]
188pub struct RankAvgFn;
189impl Function for RankAvgFn {
190    func_caps!(PURE, NUMERIC_ONLY);
191    fn name(&self) -> &'static str {
192        "RANK.AVG"
193    }
194    fn min_args(&self) -> usize {
195        2
196    }
197    fn variadic(&self) -> bool {
198        true
199    }
200    fn arg_schema(&self) -> &'static [ArgSchema] {
201        &ARG_RANGE_NUM_LENIENT_ONE[..]
202    }
203    fn eval_scalar<'a, 'b>(
204        &self,
205        args: &'a [ArgumentHandle<'a, 'b>],
206        _ctx: &dyn FunctionContext,
207    ) -> Result<LiteralValue, ExcelError> {
208        if args.len() < 2 {
209            return Ok(LiteralValue::Error(ExcelError::new_na()));
210        }
211        let target = match coerce_num(args[0].value()?.as_ref()) {
212            Ok(n) => n,
213            Err(_) => return Ok(LiteralValue::Error(ExcelError::new_na())),
214        };
215        let order = if args.len() >= 3 {
216            coerce_num(args[2].value()?.as_ref()).unwrap_or(0.0)
217        } else {
218            0.0
219        };
220        let nums = collect_numeric_stats(&args[1..2])?;
221        if nums.is_empty() {
222            return Ok(LiteralValue::Error(ExcelError::new_na()));
223        }
224        let mut sorted = nums;
225        if order.abs() < 1e-12 {
226            sorted.sort_by(|a, b| b.partial_cmp(a).unwrap());
227        } else {
228            sorted.sort_by(|a, b| a.partial_cmp(b).unwrap());
229        }
230        let mut positions = Vec::new();
231        for (i, &v) in sorted.iter().enumerate() {
232            if (v - target).abs() < 1e-12 {
233                positions.push(i + 1);
234            }
235        }
236        if positions.is_empty() {
237            return Ok(LiteralValue::Error(ExcelError::new_na()));
238        }
239        let avg = positions.iter().copied().sum::<usize>() as f64 / positions.len() as f64;
240        Ok(LiteralValue::Number(avg))
241    }
242}
243
244#[derive(Debug)]
245pub struct LARGE;
246impl Function for LARGE {
247    func_caps!(PURE, NUMERIC_ONLY, REDUCTION);
248    fn name(&self) -> &'static str {
249        "LARGE"
250    }
251    fn min_args(&self) -> usize {
252        2
253    }
254    fn variadic(&self) -> bool {
255        true
256    }
257    fn arg_schema(&self) -> &'static [ArgSchema] {
258        &ARG_RANGE_NUM_LENIENT_ONE[..]
259    }
260    fn eval_scalar<'a, 'b>(
261        &self,
262        args: &'a [ArgumentHandle<'a, 'b>],
263        _ctx: &dyn FunctionContext,
264    ) -> Result<LiteralValue, ExcelError> {
265        if args.len() < 2 {
266            return Ok(LiteralValue::Error(ExcelError::new_num()));
267        }
268        let k = match coerce_num(args.last().unwrap().value()?.as_ref()) {
269            Ok(n) => n,
270            Err(_) => return Ok(LiteralValue::Error(ExcelError::new_num())),
271        };
272        let k = k as i64;
273        if k < 1 {
274            return Ok(LiteralValue::Error(ExcelError::new_num()));
275        }
276        let mut nums = collect_numeric_stats(&args[..args.len() - 1])?;
277        if nums.is_empty() || k as usize > nums.len() {
278            return Ok(LiteralValue::Error(ExcelError::new_num()));
279        }
280        nums.sort_by(|a, b| b.partial_cmp(a).unwrap());
281        Ok(LiteralValue::Number(nums[(k as usize) - 1]))
282    }
283    fn eval_fold(
284        &self,
285        f: &mut dyn crate::function::FnFoldCtx,
286    ) -> Option<Result<LiteralValue, ExcelError>> {
287        let args = f.args();
288        if args.len() < 2 {
289            return Some(Ok(LiteralValue::Error(ExcelError::new_num())));
290        }
291        let k = match coerce_num(args.last().unwrap().value().ok()?.as_ref()) {
292            Ok(n) => n as i64,
293            Err(_) => {
294                return Some(Ok(LiteralValue::Error(ExcelError::from_error_string(
295                    "#NUM!",
296                ))));
297            }
298        };
299        if k < 1 {
300            return Some(Ok(LiteralValue::Error(ExcelError::new_num())));
301        }
302        let mut nums = match collect_numeric_stats(&args[..args.len() - 1]) {
303            Ok(v) => v,
304            Err(e) => return Some(Ok(LiteralValue::Error(e))),
305        };
306        if nums.is_empty() || k as usize > nums.len() {
307            return Some(Ok(LiteralValue::Error(ExcelError::new_num())));
308        }
309        // partial selection nth_element style
310        let idx = (k as usize) - 1;
311        // we want k-th largest; convert by selecting at idx in descending => nth on len-1-idx ascending
312        let target = nums.len() - 1 - idx;
313        let (left, nth, _right) =
314            nums.select_nth_unstable_by(target, |a, b| a.partial_cmp(b).unwrap());
315        let val = *nth;
316        f.write_result(LiteralValue::Number(val));
317        Some(Ok(LiteralValue::Number(val)))
318    }
319}
320
321#[derive(Debug)]
322pub struct SMALL;
323impl Function for SMALL {
324    func_caps!(PURE, NUMERIC_ONLY, REDUCTION);
325    fn name(&self) -> &'static str {
326        "SMALL"
327    }
328    fn min_args(&self) -> usize {
329        2
330    }
331    fn variadic(&self) -> bool {
332        true
333    }
334    fn arg_schema(&self) -> &'static [ArgSchema] {
335        &ARG_RANGE_NUM_LENIENT_ONE[..]
336    }
337    fn eval_scalar<'a, 'b>(
338        &self,
339        args: &'a [ArgumentHandle<'a, 'b>],
340        _ctx: &dyn FunctionContext,
341    ) -> Result<LiteralValue, ExcelError> {
342        if args.len() < 2 {
343            return Ok(LiteralValue::Error(ExcelError::new_num()));
344        }
345        let k = match coerce_num(args.last().unwrap().value()?.as_ref()) {
346            Ok(n) => n,
347            Err(_) => return Ok(LiteralValue::Error(ExcelError::new_num())),
348        };
349        let k = k as i64;
350        if k < 1 {
351            return Ok(LiteralValue::Error(ExcelError::new_num()));
352        }
353        let mut nums = collect_numeric_stats(&args[..args.len() - 1])?;
354        if nums.is_empty() || k as usize > nums.len() {
355            return Ok(LiteralValue::Error(ExcelError::new_num()));
356        }
357        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
358        Ok(LiteralValue::Number(nums[(k as usize) - 1]))
359    }
360    fn eval_fold(
361        &self,
362        f: &mut dyn crate::function::FnFoldCtx,
363    ) -> Option<Result<LiteralValue, ExcelError>> {
364        let args = f.args();
365        if args.len() < 2 {
366            return Some(Ok(LiteralValue::Error(ExcelError::new_num())));
367        }
368        let k = match coerce_num(args.last().unwrap().value().ok()?.as_ref()) {
369            Ok(n) => n as i64,
370            Err(_) => {
371                return Some(Ok(LiteralValue::Error(ExcelError::from_error_string(
372                    "#NUM!",
373                ))));
374            }
375        };
376        if k < 1 {
377            return Some(Ok(LiteralValue::Error(ExcelError::new_num())));
378        }
379        let mut nums = match collect_numeric_stats(&args[..args.len() - 1]) {
380            Ok(v) => v,
381            Err(e) => return Some(Ok(LiteralValue::Error(e))),
382        };
383        if nums.is_empty() || k as usize > nums.len() {
384            return Some(Ok(LiteralValue::Error(ExcelError::new_num())));
385        }
386        let idx = (k as usize) - 1;
387        let (left, nth, _right) =
388            nums.select_nth_unstable_by(idx, |a, b| a.partial_cmp(b).unwrap());
389        let val = *nth;
390        f.write_result(LiteralValue::Number(val));
391        Some(Ok(LiteralValue::Number(val)))
392    }
393}
394
395#[derive(Debug)]
396pub struct MEDIAN;
397impl Function for MEDIAN {
398    func_caps!(PURE, NUMERIC_ONLY, REDUCTION);
399    fn name(&self) -> &'static str {
400        "MEDIAN"
401    }
402    fn min_args(&self) -> usize {
403        1
404    }
405    fn variadic(&self) -> bool {
406        true
407    }
408    fn arg_schema(&self) -> &'static [ArgSchema] {
409        &ARG_RANGE_NUM_LENIENT_ONE[..]
410    }
411    fn eval_scalar<'a, 'b>(
412        &self,
413        args: &'a [ArgumentHandle<'a, 'b>],
414        _ctx: &dyn FunctionContext,
415    ) -> Result<LiteralValue, ExcelError> {
416        let mut nums = collect_numeric_stats(args)?;
417        if nums.is_empty() {
418            return Ok(LiteralValue::Error(ExcelError::new_num()));
419        }
420        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
421        let n = nums.len();
422        let mid = n / 2;
423        let med = if n % 2 == 1 {
424            nums[mid]
425        } else {
426            (nums[mid - 1] + nums[mid]) / 2.0
427        };
428        Ok(LiteralValue::Number(med))
429    }
430    fn eval_fold(
431        &self,
432        f: &mut dyn crate::function::FnFoldCtx,
433    ) -> Option<Result<LiteralValue, ExcelError>> {
434        let args = f.args();
435        let mut nums = match collect_numeric_stats(args) {
436            Ok(v) => v,
437            Err(e) => return Some(Ok(LiteralValue::Error(e))),
438        };
439        if nums.is_empty() {
440            return Some(Ok(LiteralValue::Error(ExcelError::new_num())));
441        }
442        let n = nums.len();
443        let mid = n / 2;
444        if n % 2 == 1 {
445            // odd
446            let (_left, nth, _right) =
447                nums.select_nth_unstable_by(mid, |a, b| a.partial_cmp(b).unwrap());
448            let val = *nth;
449            let out = LiteralValue::Number(val);
450            f.write_result(out.clone());
451            Some(Ok(out))
452        } else {
453            // even
454            let (_left, nth, _right) =
455                nums.select_nth_unstable_by(mid, |a, b| a.partial_cmp(b).unwrap());
456            let upper_mid = *nth;
457            // Now the first mid elements are the lower partition (unsorted), scan for its max.
458            let mut lower_max = f64::NEG_INFINITY;
459            for &v in &nums[..mid] {
460                if v > lower_max {
461                    lower_max = v;
462                }
463            }
464            let med = (lower_max + upper_mid) / 2.0;
465            let out = LiteralValue::Number(med);
466            f.write_result(out.clone());
467            Some(Ok(out))
468        }
469    }
470}
471
472#[derive(Debug)]
473pub struct StdevSample; // sample
474impl Function for StdevSample {
475    func_caps!(PURE, NUMERIC_ONLY, REDUCTION, STREAM_OK);
476    fn name(&self) -> &'static str {
477        "STDEV.S"
478    }
479    fn aliases(&self) -> &'static [&'static str] {
480        &["STDEV"]
481    }
482    fn min_args(&self) -> usize {
483        1
484    }
485    fn variadic(&self) -> bool {
486        true
487    }
488    fn arg_schema(&self) -> &'static [ArgSchema] {
489        &ARG_RANGE_NUM_LENIENT_ONE[..]
490    }
491    fn eval_scalar<'a, 'b>(
492        &self,
493        args: &'a [ArgumentHandle<'a, 'b>],
494        _ctx: &dyn FunctionContext,
495    ) -> Result<LiteralValue, ExcelError> {
496        let nums = collect_numeric_stats(args)?;
497        let n = nums.len();
498        if n < 2 {
499            return Ok(LiteralValue::Error(ExcelError::from_error_string(
500                "#DIV/0!",
501            )));
502        }
503        let mean = nums.iter().sum::<f64>() / (n as f64);
504        let mut ss = 0.0;
505        for &v in &nums {
506            let d = v - mean;
507            ss += d * d;
508        }
509        Ok(LiteralValue::Number((ss / ((n - 1) as f64)).sqrt()))
510    }
511    fn eval_fold(
512        &self,
513        f: &mut dyn crate::function::FnFoldCtx,
514    ) -> Option<Result<LiteralValue, ExcelError>> {
515        // Welford one-pass for sample stdev
516        let mut n: f64 = 0.0;
517        let mut mean: f64 = 0.0;
518        let mut m2: f64 = 0.0;
519        let mut err: Option<ExcelError> = None;
520        let mut cb = |chunk: crate::stripes::NumericChunk| -> Result<(), ExcelError> {
521            for &x in chunk.data {
522                n += 1.0;
523                let delta = x - mean;
524                mean += delta / n;
525                let delta2 = x - mean;
526                m2 += delta * delta2;
527            }
528            Ok(())
529        };
530        if let Err(e) = f.for_each_numeric_chunk(4096, &mut cb) {
531            err = Some(e);
532        }
533        let out = if let Some(e) = err {
534            LiteralValue::Error(e)
535        } else if n < 2.0 {
536            LiteralValue::Error(ExcelError::new_div())
537        } else {
538            LiteralValue::Number((m2 / (n - 1.0)).sqrt())
539        };
540        f.write_result(out.clone());
541        Some(Ok(out))
542    }
543}
544
545#[derive(Debug)]
546pub struct StdevPop; // population
547impl Function for StdevPop {
548    func_caps!(PURE, NUMERIC_ONLY, REDUCTION, STREAM_OK);
549    fn name(&self) -> &'static str {
550        "STDEV.P"
551    }
552    fn aliases(&self) -> &'static [&'static str] {
553        &["STDEVP"]
554    }
555    fn min_args(&self) -> usize {
556        1
557    }
558    fn variadic(&self) -> bool {
559        true
560    }
561    fn arg_schema(&self) -> &'static [ArgSchema] {
562        &ARG_RANGE_NUM_LENIENT_ONE[..]
563    }
564    fn eval_scalar<'a, 'b>(
565        &self,
566        args: &'a [ArgumentHandle<'a, 'b>],
567        _ctx: &dyn FunctionContext,
568    ) -> Result<LiteralValue, ExcelError> {
569        let nums = collect_numeric_stats(args)?;
570        let n = nums.len();
571        if n == 0 {
572            return Ok(LiteralValue::Error(ExcelError::from_error_string(
573                "#DIV/0!",
574            )));
575        }
576        let mean = nums.iter().sum::<f64>() / (n as f64);
577        let mut ss = 0.0;
578        for &v in &nums {
579            let d = v - mean;
580            ss += d * d;
581        }
582        Ok(LiteralValue::Number((ss / (n as f64)).sqrt()))
583    }
584    fn eval_fold(
585        &self,
586        f: &mut dyn crate::function::FnFoldCtx,
587    ) -> Option<Result<LiteralValue, ExcelError>> {
588        let mut n: f64 = 0.0;
589        let mut mean = 0.0;
590        let mut m2 = 0.0;
591        let mut err: Option<ExcelError> = None;
592        let mut cb = |chunk: crate::stripes::NumericChunk| -> Result<(), ExcelError> {
593            for &x in chunk.data {
594                n += 1.0;
595                let delta = x - mean;
596                mean += delta / n;
597                let delta2 = x - mean;
598                m2 += delta * delta2;
599            }
600            Ok(())
601        };
602        if let Err(e) = f.for_each_numeric_chunk(4096, &mut cb) {
603            err = Some(e);
604        }
605        let out = if let Some(e) = err {
606            LiteralValue::Error(e)
607        } else if n == 0.0 {
608            LiteralValue::Error(ExcelError::new_div())
609        } else {
610            LiteralValue::Number((m2 / n).sqrt())
611        };
612        f.write_result(out.clone());
613        Some(Ok(out))
614    }
615}
616
617#[derive(Debug)]
618pub struct VarSample; // sample variance
619impl Function for VarSample {
620    func_caps!(PURE, NUMERIC_ONLY, REDUCTION, STREAM_OK);
621    fn name(&self) -> &'static str {
622        "VAR.S"
623    }
624    fn aliases(&self) -> &'static [&'static str] {
625        &["VAR"]
626    }
627    fn min_args(&self) -> usize {
628        1
629    }
630    fn variadic(&self) -> bool {
631        true
632    }
633    fn arg_schema(&self) -> &'static [ArgSchema] {
634        &ARG_RANGE_NUM_LENIENT_ONE[..]
635    }
636    fn eval_scalar<'a, 'b>(
637        &self,
638        args: &'a [ArgumentHandle<'a, 'b>],
639        _ctx: &dyn FunctionContext,
640    ) -> Result<LiteralValue, ExcelError> {
641        let nums = collect_numeric_stats(args)?;
642        let n = nums.len();
643        if n < 2 {
644            return Ok(LiteralValue::Error(ExcelError::from_error_string(
645                "#DIV/0!",
646            )));
647        }
648        let mean = nums.iter().sum::<f64>() / (n as f64);
649        let mut ss = 0.0;
650        for &v in &nums {
651            let d = v - mean;
652            ss += d * d;
653        }
654        Ok(LiteralValue::Number(ss / ((n - 1) as f64)))
655    }
656    fn eval_fold(
657        &self,
658        f: &mut dyn crate::function::FnFoldCtx,
659    ) -> Option<Result<LiteralValue, ExcelError>> {
660        let mut n: f64 = 0.0;
661        let mut mean = 0.0;
662        let mut m2 = 0.0;
663        let mut err = None;
664        let mut cb = |chunk: crate::stripes::NumericChunk| -> Result<(), ExcelError> {
665            for &x in chunk.data {
666                n += 1.0;
667                let delta = x - mean;
668                mean += delta / n;
669                let delta2 = x - mean;
670                m2 += delta * delta2;
671            }
672            Ok(())
673        };
674        if let Err(e) = f.for_each_numeric_chunk(4096, &mut cb) {
675            err = Some(e);
676        }
677        let out = if let Some(e) = err {
678            LiteralValue::Error(e)
679        } else if n < 2.0 {
680            LiteralValue::Error(ExcelError::new_div())
681        } else {
682            LiteralValue::Number(m2 / (n - 1.0))
683        };
684        f.write_result(out.clone());
685        Some(Ok(out))
686    }
687}
688
689#[derive(Debug)]
690pub struct VarPop; // population variance
691impl Function for VarPop {
692    func_caps!(PURE, NUMERIC_ONLY, REDUCTION, STREAM_OK);
693    fn name(&self) -> &'static str {
694        "VAR.P"
695    }
696    fn aliases(&self) -> &'static [&'static str] {
697        &["VARP"]
698    }
699    fn min_args(&self) -> usize {
700        1
701    }
702    fn variadic(&self) -> bool {
703        true
704    }
705    fn arg_schema(&self) -> &'static [ArgSchema] {
706        &ARG_RANGE_NUM_LENIENT_ONE[..]
707    }
708    fn eval_scalar<'a, 'b>(
709        &self,
710        args: &'a [ArgumentHandle<'a, 'b>],
711        _ctx: &dyn FunctionContext,
712    ) -> Result<LiteralValue, ExcelError> {
713        let nums = collect_numeric_stats(args)?;
714        let n = nums.len();
715        if n == 0 {
716            return Ok(LiteralValue::Error(ExcelError::from_error_string(
717                "#DIV/0!",
718            )));
719        }
720        let mean = nums.iter().sum::<f64>() / (n as f64);
721        let mut ss = 0.0;
722        for &v in &nums {
723            let d = v - mean;
724            ss += d * d;
725        }
726        Ok(LiteralValue::Number(ss / (n as f64)))
727    }
728    fn eval_fold(
729        &self,
730        f: &mut dyn crate::function::FnFoldCtx,
731    ) -> Option<Result<LiteralValue, ExcelError>> {
732        let mut n: f64 = 0.0;
733        let mut mean = 0.0;
734        let mut m2 = 0.0;
735        let mut err = None;
736        let mut cb = |chunk: crate::stripes::NumericChunk| -> Result<(), ExcelError> {
737            for &x in chunk.data {
738                n += 1.0;
739                let delta = x - mean;
740                mean += delta / n;
741                let delta2 = x - mean;
742                m2 += delta * delta2;
743            }
744            Ok(())
745        };
746        if let Err(e) = f.for_each_numeric_chunk(4096, &mut cb) {
747            err = Some(e);
748        }
749        let out = if let Some(e) = err {
750            LiteralValue::Error(e)
751        } else if n == 0.0 {
752            LiteralValue::Error(ExcelError::new_div())
753        } else {
754            LiteralValue::Number(m2 / n)
755        };
756        f.write_result(out.clone());
757        Some(Ok(out))
758    }
759}
760
761// MODE.SNGL (alias MODE) and MODE.MULT
762#[derive(Debug)]
763pub struct ModeSingleFn;
764impl Function for ModeSingleFn {
765    func_caps!(PURE, NUMERIC_ONLY, REDUCTION);
766    fn name(&self) -> &'static str {
767        "MODE.SNGL"
768    }
769    fn aliases(&self) -> &'static [&'static str] {
770        &["MODE"]
771    }
772    fn min_args(&self) -> usize {
773        1
774    }
775    fn variadic(&self) -> bool {
776        true
777    }
778    fn arg_schema(&self) -> &'static [ArgSchema] {
779        &ARG_RANGE_NUM_LENIENT_ONE[..]
780    }
781    fn eval_scalar<'a, 'b>(
782        &self,
783        args: &'a [ArgumentHandle<'a, 'b>],
784        _ctx: &dyn FunctionContext,
785    ) -> Result<LiteralValue, ExcelError> {
786        let mut nums = collect_numeric_stats(args)?;
787        if nums.is_empty() {
788            return Ok(LiteralValue::Error(ExcelError::new_na()));
789        }
790        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
791        let mut best_val = nums[0];
792        let mut best_cnt = 1usize;
793        let mut cur_val = nums[0];
794        let mut cur_cnt = 1usize;
795        for &v in &nums[1..] {
796            if (v - cur_val).abs() < 1e-12 {
797                cur_cnt += 1;
798            } else {
799                if cur_cnt > best_cnt {
800                    best_cnt = cur_cnt;
801                    best_val = cur_val;
802                }
803                cur_val = v;
804                cur_cnt = 1;
805            }
806        }
807        if cur_cnt > best_cnt {
808            best_cnt = cur_cnt;
809            best_val = cur_val;
810        }
811        if best_cnt <= 1 {
812            Ok(LiteralValue::Error(ExcelError::new_na()))
813        } else {
814            Ok(LiteralValue::Number(best_val))
815        }
816    }
817    fn eval_fold(
818        &self,
819        f: &mut dyn crate::function::FnFoldCtx,
820    ) -> Option<Result<LiteralValue, ExcelError>> {
821        let args = f.args();
822        let mut nums = match collect_numeric_stats(args) {
823            Ok(v) => v,
824            Err(e) => return Some(Ok(LiteralValue::Error(e))),
825        };
826        if nums.is_empty() {
827            return Some(Ok(LiteralValue::Error(ExcelError::from_error_string(
828                "#N/A",
829            ))));
830        }
831        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
832        let mut best_val = nums[0];
833        let mut best_cnt = 1usize;
834        let mut cur_val = nums[0];
835        let mut cur_cnt = 1usize;
836        for &v in &nums[1..] {
837            if (v - cur_val).abs() < 1e-12 {
838                cur_cnt += 1;
839            } else {
840                if cur_cnt > best_cnt {
841                    best_cnt = cur_cnt;
842                    best_val = cur_val;
843                }
844                cur_val = v;
845                cur_cnt = 1;
846            }
847        }
848        if cur_cnt > best_cnt {
849            best_cnt = cur_cnt;
850            best_val = cur_val;
851        }
852        let out = if best_cnt <= 1 {
853            LiteralValue::Error(ExcelError::new_na())
854        } else {
855            LiteralValue::Number(best_val)
856        };
857        f.write_result(out.clone());
858        Some(Ok(out))
859    }
860}
861
862#[derive(Debug)]
863pub struct ModeMultiFn;
864impl Function for ModeMultiFn {
865    func_caps!(PURE, NUMERIC_ONLY, REDUCTION);
866    fn name(&self) -> &'static str {
867        "MODE.MULT"
868    }
869    fn min_args(&self) -> usize {
870        1
871    }
872    fn variadic(&self) -> bool {
873        true
874    }
875    fn arg_schema(&self) -> &'static [ArgSchema] {
876        &ARG_RANGE_NUM_LENIENT_ONE[..]
877    }
878    fn eval_scalar<'a, 'b>(
879        &self,
880        args: &'a [ArgumentHandle<'a, 'b>],
881        _ctx: &dyn FunctionContext,
882    ) -> Result<LiteralValue, ExcelError> {
883        let mut nums = collect_numeric_stats(args)?;
884        if nums.is_empty() {
885            return Ok(LiteralValue::Error(ExcelError::new_na()));
886        }
887        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
888        let mut runs: Vec<(f64, usize)> = Vec::new();
889        let mut cur_val = nums[0];
890        let mut cur_cnt = 1usize;
891        for &v in &nums[1..] {
892            if (v - cur_val).abs() < 1e-12 {
893                cur_cnt += 1;
894            } else {
895                runs.push((cur_val, cur_cnt));
896                cur_val = v;
897                cur_cnt = 1;
898            }
899        }
900        runs.push((cur_val, cur_cnt));
901        let max_freq = runs.iter().map(|r| r.1).max().unwrap_or(0);
902        if max_freq <= 1 {
903            return Ok(LiteralValue::Error(ExcelError::new_na()));
904        }
905        let rows: Vec<Vec<LiteralValue>> = runs
906            .into_iter()
907            .filter(|&(_, c)| c == max_freq)
908            .map(|(v, _)| vec![LiteralValue::Number(v)])
909            .collect();
910        Ok(LiteralValue::Array(rows))
911    }
912    fn eval_fold(
913        &self,
914        f: &mut dyn crate::function::FnFoldCtx,
915    ) -> Option<Result<LiteralValue, ExcelError>> {
916        let args = f.args();
917        let mut nums = match collect_numeric_stats(args) {
918            Ok(v) => v,
919            Err(e) => return Some(Ok(LiteralValue::Error(e))),
920        };
921        if nums.is_empty() {
922            return Some(Ok(LiteralValue::Error(ExcelError::from_error_string(
923                "#N/A",
924            ))));
925        }
926        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
927        let mut runs: Vec<(f64, usize)> = Vec::new();
928        let mut cur = nums[0];
929        let mut cnt = 1usize;
930        for &v in &nums[1..] {
931            if (v - cur).abs() < 1e-12 {
932                cnt += 1;
933            } else {
934                runs.push((cur, cnt));
935                cur = v;
936                cnt = 1;
937            }
938        }
939        runs.push((cur, cnt));
940        let max_freq = runs.iter().map(|r| r.1).max().unwrap_or(0);
941        if max_freq <= 1 {
942            let out = LiteralValue::Error(ExcelError::new_na());
943            f.write_result(out.clone());
944            return Some(Ok(out));
945        }
946        let rows: Vec<Vec<LiteralValue>> = runs
947            .into_iter()
948            .filter(|&(_, c)| c == max_freq)
949            .map(|(v, _)| vec![LiteralValue::Number(v)])
950            .collect();
951        let out = LiteralValue::Array(rows);
952        f.write_result(out.clone());
953        Some(Ok(out))
954    }
955}
956
957#[derive(Debug)]
958pub struct PercentileInc; // inclusive
959impl Function for PercentileInc {
960    func_caps!(PURE, NUMERIC_ONLY);
961    fn name(&self) -> &'static str {
962        "PERCENTILE.INC"
963    }
964    fn aliases(&self) -> &'static [&'static str] {
965        &["PERCENTILE"]
966    }
967    fn min_args(&self) -> usize {
968        2
969    }
970    fn variadic(&self) -> bool {
971        true
972    }
973    fn arg_schema(&self) -> &'static [ArgSchema] {
974        &ARG_RANGE_NUM_LENIENT_ONE[..]
975    }
976    fn eval_scalar<'a, 'b>(
977        &self,
978        args: &'a [ArgumentHandle<'a, 'b>],
979        _ctx: &dyn FunctionContext,
980    ) -> Result<LiteralValue, ExcelError> {
981        if args.len() < 2 {
982            return Ok(LiteralValue::Error(ExcelError::new_num()));
983        }
984        let p = match coerce_num(args.last().unwrap().value()?.as_ref()) {
985            Ok(n) => n,
986            Err(_) => return Ok(LiteralValue::Error(ExcelError::new_num())),
987        };
988        let mut nums = collect_numeric_stats(&args[..args.len() - 1])?;
989        if nums.is_empty() {
990            return Ok(LiteralValue::Error(ExcelError::new_num()));
991        }
992        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
993        match percentile_inc(&nums, p) {
994            Ok(v) => Ok(LiteralValue::Number(v)),
995            Err(e) => Ok(LiteralValue::Error(e)),
996        }
997    }
998}
999
1000#[derive(Debug)]
1001pub struct PercentileExc; // exclusive
1002impl Function for PercentileExc {
1003    func_caps!(PURE, NUMERIC_ONLY);
1004    fn name(&self) -> &'static str {
1005        "PERCENTILE.EXC"
1006    }
1007    fn min_args(&self) -> usize {
1008        2
1009    }
1010    fn variadic(&self) -> bool {
1011        true
1012    }
1013    fn arg_schema(&self) -> &'static [ArgSchema] {
1014        &ARG_RANGE_NUM_LENIENT_ONE[..]
1015    }
1016    fn eval_scalar<'a, 'b>(
1017        &self,
1018        args: &'a [ArgumentHandle<'a, 'b>],
1019        _ctx: &dyn FunctionContext,
1020    ) -> Result<LiteralValue, ExcelError> {
1021        if args.len() < 2 {
1022            return Ok(LiteralValue::Error(ExcelError::new_num()));
1023        }
1024        let p = match coerce_num(args.last().unwrap().value()?.as_ref()) {
1025            Ok(n) => n,
1026            Err(_) => return Ok(LiteralValue::Error(ExcelError::new_num())),
1027        };
1028        let mut nums = collect_numeric_stats(&args[..args.len() - 1])?;
1029        if nums.is_empty() {
1030            return Ok(LiteralValue::Error(ExcelError::new_num()));
1031        }
1032        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
1033        match percentile_exc(&nums, p) {
1034            Ok(v) => Ok(LiteralValue::Number(v)),
1035            Err(e) => Ok(LiteralValue::Error(e)),
1036        }
1037    }
1038}
1039
1040#[derive(Debug)]
1041pub struct QuartileInc; // quartile inclusive
1042impl Function for QuartileInc {
1043    func_caps!(PURE, NUMERIC_ONLY);
1044    fn name(&self) -> &'static str {
1045        "QUARTILE.INC"
1046    }
1047    fn aliases(&self) -> &'static [&'static str] {
1048        &["QUARTILE"]
1049    }
1050    fn min_args(&self) -> usize {
1051        2
1052    }
1053    fn variadic(&self) -> bool {
1054        true
1055    }
1056    fn arg_schema(&self) -> &'static [ArgSchema] {
1057        &ARG_RANGE_NUM_LENIENT_ONE[..]
1058    }
1059    fn eval_scalar<'a, 'b>(
1060        &self,
1061        args: &'a [ArgumentHandle<'a, 'b>],
1062        _ctx: &dyn FunctionContext,
1063    ) -> Result<LiteralValue, ExcelError> {
1064        if args.len() < 2 {
1065            return Ok(LiteralValue::Error(ExcelError::new_num()));
1066        }
1067        let q = match coerce_num(args.last().unwrap().value()?.as_ref()) {
1068            Ok(n) => n,
1069            Err(_) => return Ok(LiteralValue::Error(ExcelError::new_num())),
1070        };
1071        let q_i = q as i64;
1072        if !(0..=4).contains(&q_i) {
1073            return Ok(LiteralValue::Error(ExcelError::new_num()));
1074        }
1075        let mut nums = collect_numeric_stats(&args[..args.len() - 1])?;
1076        if nums.is_empty() {
1077            return Ok(LiteralValue::Error(ExcelError::new_num()));
1078        }
1079        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
1080        let p = match q_i {
1081            0 => return Ok(LiteralValue::Number(nums[0])),
1082            4 => return Ok(LiteralValue::Number(nums[nums.len() - 1])),
1083            1 => 0.25,
1084            2 => 0.5,
1085            3 => 0.75,
1086            _ => return Ok(LiteralValue::Error(ExcelError::new_num())),
1087        };
1088        match percentile_inc(&nums, p) {
1089            Ok(v) => Ok(LiteralValue::Number(v)),
1090            Err(e) => Ok(LiteralValue::Error(e)),
1091        }
1092    }
1093}
1094
1095#[derive(Debug)]
1096pub struct QuartileExc; // quartile exclusive
1097impl Function for QuartileExc {
1098    func_caps!(PURE, NUMERIC_ONLY);
1099    fn name(&self) -> &'static str {
1100        "QUARTILE.EXC"
1101    }
1102    fn min_args(&self) -> usize {
1103        2
1104    }
1105    fn variadic(&self) -> bool {
1106        true
1107    }
1108    fn arg_schema(&self) -> &'static [ArgSchema] {
1109        &ARG_RANGE_NUM_LENIENT_ONE[..]
1110    }
1111    fn eval_scalar<'a, 'b>(
1112        &self,
1113        args: &'a [ArgumentHandle<'a, 'b>],
1114        _ctx: &dyn FunctionContext,
1115    ) -> Result<LiteralValue, ExcelError> {
1116        if args.len() < 2 {
1117            return Ok(LiteralValue::Error(ExcelError::new_num()));
1118        }
1119        let q = match coerce_num(args.last().unwrap().value()?.as_ref()) {
1120            Ok(n) => n,
1121            Err(_) => return Ok(LiteralValue::Error(ExcelError::new_num())),
1122        };
1123        let q_i = q as i64;
1124        if !(1..=3).contains(&q_i) {
1125            return Ok(LiteralValue::Error(ExcelError::new_num()));
1126        }
1127        let mut nums = collect_numeric_stats(&args[..args.len() - 1])?;
1128        if nums.len() < 2 {
1129            return Ok(LiteralValue::Error(ExcelError::new_num()));
1130        }
1131        nums.sort_by(|a, b| a.partial_cmp(b).unwrap());
1132        let p = match q_i {
1133            1 => 0.25,
1134            2 => 0.5,
1135            3 => 0.75,
1136            _ => return Ok(LiteralValue::Error(ExcelError::new_num())),
1137        };
1138        match percentile_exc(&nums, p) {
1139            Ok(v) => Ok(LiteralValue::Number(v)),
1140            Err(e) => Ok(LiteralValue::Error(e)),
1141        }
1142    }
1143}
1144
1145pub fn register_builtins() {
1146    use std::sync::Arc;
1147    crate::function_registry::register_function(Arc::new(LARGE));
1148    crate::function_registry::register_function(Arc::new(SMALL));
1149    crate::function_registry::register_function(Arc::new(MEDIAN));
1150    crate::function_registry::register_function(Arc::new(StdevSample));
1151    crate::function_registry::register_function(Arc::new(StdevPop));
1152    crate::function_registry::register_function(Arc::new(VarSample));
1153    crate::function_registry::register_function(Arc::new(VarPop));
1154    crate::function_registry::register_function(Arc::new(PercentileInc));
1155    crate::function_registry::register_function(Arc::new(PercentileExc));
1156    crate::function_registry::register_function(Arc::new(QuartileInc));
1157    crate::function_registry::register_function(Arc::new(QuartileExc));
1158    crate::function_registry::register_function(Arc::new(RankEqFn));
1159    crate::function_registry::register_function(Arc::new(RankAvgFn));
1160    crate::function_registry::register_function(Arc::new(ModeSingleFn));
1161    crate::function_registry::register_function(Arc::new(ModeMultiFn));
1162}
1163
1164#[cfg(test)]
1165mod tests_basic_stats {
1166    use super::*;
1167    use crate::test_workbook::TestWorkbook;
1168    use crate::traits::ArgumentHandle;
1169    use formualizer_common::LiteralValue;
1170    use formualizer_parse::parser::{ASTNode, ASTNodeType};
1171    fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
1172        wb.interpreter()
1173    }
1174    fn arr(vals: Vec<f64>) -> ASTNode {
1175        ASTNode::new(
1176            ASTNodeType::Literal(LiteralValue::Array(vec![
1177                vals.into_iter().map(LiteralValue::Number).collect(),
1178            ])),
1179            None,
1180        )
1181    }
1182    #[test]
1183    fn median_even() {
1184        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(MEDIAN));
1185        let ctx = interp(&wb);
1186        let node = arr(vec![1.0, 3.0, 5.0, 7.0]);
1187        let f = ctx.context.get_function("", "MEDIAN").unwrap();
1188        let out = f
1189            .dispatch(
1190                &[ArgumentHandle::new(&node, &ctx)],
1191                &ctx.function_context(None),
1192            )
1193            .unwrap();
1194        assert_eq!(out, LiteralValue::Number(4.0));
1195    }
1196    #[test]
1197    fn median_odd() {
1198        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(MEDIAN));
1199        let ctx = interp(&wb);
1200        let node = arr(vec![1.0, 9.0, 5.0]);
1201        let f = ctx.context.get_function("", "MEDIAN").unwrap();
1202        let out = f
1203            .dispatch(
1204                &[ArgumentHandle::new(&node, &ctx)],
1205                &ctx.function_context(None),
1206            )
1207            .unwrap();
1208        assert_eq!(out, LiteralValue::Number(5.0));
1209    }
1210    #[test]
1211    fn large_basic() {
1212        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(LARGE));
1213        let ctx = interp(&wb);
1214        let nums = arr(vec![10.0, 20.0, 30.0]);
1215        let k = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(2.0)), None);
1216        let f = ctx.context.get_function("", "LARGE").unwrap();
1217        let out = f
1218            .dispatch(
1219                &[
1220                    ArgumentHandle::new(&nums, &ctx),
1221                    ArgumentHandle::new(&k, &ctx),
1222                ],
1223                &ctx.function_context(None),
1224            )
1225            .unwrap();
1226        assert_eq!(out, LiteralValue::Number(20.0));
1227    }
1228    #[test]
1229    fn small_basic() {
1230        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SMALL));
1231        let ctx = interp(&wb);
1232        let nums = arr(vec![10.0, 20.0, 30.0]);
1233        let k = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(2.0)), None);
1234        let f = ctx.context.get_function("", "SMALL").unwrap();
1235        let out = f
1236            .dispatch(
1237                &[
1238                    ArgumentHandle::new(&nums, &ctx),
1239                    ArgumentHandle::new(&k, &ctx),
1240                ],
1241                &ctx.function_context(None),
1242            )
1243            .unwrap();
1244        assert_eq!(out, LiteralValue::Number(20.0));
1245    }
1246    #[test]
1247    fn percentile_inc_quarter() {
1248        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(PercentileInc));
1249        let ctx = interp(&wb);
1250        let nums = arr(vec![1.0, 2.0, 3.0, 4.0]);
1251        let p = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(0.25)), None);
1252        let f = ctx.context.get_function("", "PERCENTILE.INC").unwrap();
1253        match f
1254            .dispatch(
1255                &[
1256                    ArgumentHandle::new(&nums, &ctx),
1257                    ArgumentHandle::new(&p, &ctx),
1258                ],
1259                &ctx.function_context(None),
1260            )
1261            .unwrap()
1262        {
1263            LiteralValue::Number(v) => assert!((v - 1.75).abs() < 1e-9),
1264            other => panic!("unexpected {other:?}"),
1265        }
1266    }
1267    #[test]
1268    fn rank_eq_descending() {
1269        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(RankEqFn));
1270        let ctx = interp(&wb);
1271        // target 5 among {10,5,1} descending => ranks 1,2,3 => expect 2
1272        let target = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(5.0)), None);
1273        let arr_node = arr(vec![10.0, 5.0, 1.0]);
1274        let f = ctx.context.get_function("", "RANK.EQ").unwrap();
1275        let out = f
1276            .dispatch(
1277                &[
1278                    ArgumentHandle::new(&target, &ctx),
1279                    ArgumentHandle::new(&arr_node, &ctx),
1280                ],
1281                &ctx.function_context(None),
1282            )
1283            .unwrap();
1284        assert_eq!(out, LiteralValue::Number(2.0));
1285    }
1286    #[test]
1287    fn rank_eq_ascending_order_arg() {
1288        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(RankEqFn));
1289        let ctx = interp(&wb);
1290        // ascending order=1: array {1,5,10}; target 5 => rank 2
1291        let target = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(5.0)), None);
1292        let arr_node = arr(vec![1.0, 5.0, 10.0]);
1293        let order = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(1.0)), None);
1294        let f = ctx.context.get_function("", "RANK.EQ").unwrap();
1295        let out = f
1296            .dispatch(
1297                &[
1298                    ArgumentHandle::new(&target, &ctx),
1299                    ArgumentHandle::new(&arr_node, &ctx),
1300                    ArgumentHandle::new(&order, &ctx),
1301                ],
1302                &ctx.function_context(None),
1303            )
1304            .unwrap();
1305        assert_eq!(out, LiteralValue::Number(2.0));
1306    }
1307    #[test]
1308    fn rank_avg_ties() {
1309        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(RankAvgFn));
1310        let ctx = interp(&wb);
1311        // descending array {5,5,1} target 5 positions 1 and 2 avg -> 1.5
1312        let target = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(5.0)), None);
1313        let arr_node = arr(vec![5.0, 5.0, 1.0]);
1314        let f = ctx.context.get_function("", "RANK.AVG").unwrap();
1315        let out = f
1316            .dispatch(
1317                &[
1318                    ArgumentHandle::new(&target, &ctx),
1319                    ArgumentHandle::new(&arr_node, &ctx),
1320                ],
1321                &ctx.function_context(None),
1322            )
1323            .unwrap();
1324        match out {
1325            LiteralValue::Number(v) => assert!((v - 1.5).abs() < 1e-12),
1326            other => panic!("expected number got {other:?}"),
1327        }
1328    }
1329    #[test]
1330    fn stdev_var_sample_population() {
1331        let wb = TestWorkbook::new()
1332            .with_function(std::sync::Arc::new(StdevSample))
1333            .with_function(std::sync::Arc::new(StdevPop))
1334            .with_function(std::sync::Arc::new(VarSample))
1335            .with_function(std::sync::Arc::new(VarPop));
1336        let ctx = interp(&wb);
1337        let arr_node = arr(vec![2.0, 4.0, 4.0, 4.0, 5.0, 5.0, 7.0, 9.0]); // variance population = 4, sample = 4.571428...
1338        let stdev_p = ctx.context.get_function("", "STDEV.P").unwrap();
1339        let stdev_s = ctx.context.get_function("", "STDEV.S").unwrap();
1340        let var_p = ctx.context.get_function("", "VAR.P").unwrap();
1341        let var_s = ctx.context.get_function("", "VAR.S").unwrap();
1342        let args = [ArgumentHandle::new(&arr_node, &ctx)];
1343        match var_p.dispatch(&args, &ctx.function_context(None)).unwrap() {
1344            LiteralValue::Number(v) => assert!((v - 4.0).abs() < 1e-12),
1345            other => panic!("unexpected {other:?}"),
1346        }
1347        match var_s.dispatch(&args, &ctx.function_context(None)).unwrap() {
1348            LiteralValue::Number(v) => assert!((v - 4.571428571428571).abs() < 1e-9),
1349            other => panic!("unexpected {other:?}"),
1350        }
1351        match stdev_p
1352            .dispatch(&args, &ctx.function_context(None))
1353            .unwrap()
1354        {
1355            LiteralValue::Number(v) => assert!((v - 2.0).abs() < 1e-12),
1356            other => panic!("unexpected {other:?}"),
1357        }
1358        match stdev_s
1359            .dispatch(&args, &ctx.function_context(None))
1360            .unwrap()
1361        {
1362            LiteralValue::Number(v) => assert!((v - 2.138089935).abs() < 1e-9),
1363            other => panic!("unexpected {other:?}"),
1364        }
1365    }
1366    #[test]
1367    fn quartile_inc_exc() {
1368        let wb = TestWorkbook::new()
1369            .with_function(std::sync::Arc::new(QuartileInc))
1370            .with_function(std::sync::Arc::new(QuartileExc));
1371        let ctx = interp(&wb);
1372        let arr_node = arr(vec![1.0, 2.0, 3.0, 4.0, 5.0]);
1373        let q1 = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(1.0)), None);
1374        let q2 = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(2.0)), None);
1375        let f_inc = ctx.context.get_function("", "QUARTILE.INC").unwrap();
1376        let f_exc = ctx.context.get_function("", "QUARTILE.EXC").unwrap();
1377        let arg_inc_q1 = [
1378            ArgumentHandle::new(&arr_node, &ctx),
1379            ArgumentHandle::new(&q1, &ctx),
1380        ];
1381        let arg_inc_q2 = [
1382            ArgumentHandle::new(&arr_node, &ctx),
1383            ArgumentHandle::new(&q2, &ctx),
1384        ];
1385        match f_inc
1386            .dispatch(&arg_inc_q1, &ctx.function_context(None))
1387            .unwrap()
1388        {
1389            LiteralValue::Number(v) => assert!((v - 2.0).abs() < 1e-12),
1390            other => panic!("unexpected {other:?}"),
1391        }
1392        match f_inc
1393            .dispatch(&arg_inc_q2, &ctx.function_context(None))
1394            .unwrap()
1395        {
1396            LiteralValue::Number(v) => assert!((v - 3.0).abs() < 1e-12),
1397            other => panic!("unexpected {other:?}"),
1398        }
1399        // QUARTILE.EXC Q1 for 5-point set uses exclusive percentile => 1.5
1400        match f_exc
1401            .dispatch(&arg_inc_q1, &ctx.function_context(None))
1402            .unwrap()
1403        {
1404            LiteralValue::Number(v) => assert!((v - 1.5).abs() < 1e-12),
1405            other => panic!("unexpected {other:?}"),
1406        }
1407        match f_exc
1408            .dispatch(&arg_inc_q2, &ctx.function_context(None))
1409            .unwrap()
1410        {
1411            LiteralValue::Number(v) => assert!((v - 3.0).abs() < 1e-12),
1412            other => panic!("unexpected {other:?}"),
1413        }
1414    }
1415
1416    // --- eval_fold equivalence tests for variance / stdev ---
1417    #[test]
1418    fn fold_equivalence_var_stdev() {
1419        use crate::function::Function as _; // trait import
1420        let wb = TestWorkbook::new()
1421            .with_function(std::sync::Arc::new(VarSample))
1422            .with_function(std::sync::Arc::new(VarPop))
1423            .with_function(std::sync::Arc::new(StdevSample))
1424            .with_function(std::sync::Arc::new(StdevPop));
1425        let ctx = interp(&wb);
1426        let arr_node = arr(vec![1.0, 2.0, 5.0, 5.0, 9.0]);
1427        let args = [ArgumentHandle::new(&arr_node, &ctx)];
1428
1429        let var_s_fn = VarSample; // concrete instance to call eval_scalar
1430        let var_p_fn = VarPop;
1431        let stdev_s_fn = StdevSample;
1432        let stdev_p_fn = StdevPop;
1433
1434        let fctx = ctx.function_context(None);
1435        // Dispatch results (will use fold path)
1436        let disp_var_s = ctx
1437            .context
1438            .get_function("", "VAR.S")
1439            .unwrap()
1440            .dispatch(&args, &fctx)
1441            .unwrap();
1442        let disp_var_p = ctx
1443            .context
1444            .get_function("", "VAR.P")
1445            .unwrap()
1446            .dispatch(&args, &fctx)
1447            .unwrap();
1448        let disp_stdev_s = ctx
1449            .context
1450            .get_function("", "STDEV.S")
1451            .unwrap()
1452            .dispatch(&args, &fctx)
1453            .unwrap();
1454        let disp_stdev_p = ctx
1455            .context
1456            .get_function("", "STDEV.P")
1457            .unwrap()
1458            .dispatch(&args, &fctx)
1459            .unwrap();
1460
1461        // Scalar path results
1462        let scalar_var_s = var_s_fn.eval_scalar(&args, &fctx).unwrap();
1463        let scalar_var_p = var_p_fn.eval_scalar(&args, &fctx).unwrap();
1464        let scalar_stdev_s = stdev_s_fn.eval_scalar(&args, &fctx).unwrap();
1465        let scalar_stdev_p = stdev_p_fn.eval_scalar(&args, &fctx).unwrap();
1466
1467        fn assert_close(a: &LiteralValue, b: &LiteralValue) {
1468            match (a, b) {
1469                (LiteralValue::Number(x), LiteralValue::Number(y)) => {
1470                    assert!((x - y).abs() < 1e-12, "mismatch {x} vs {y}")
1471                }
1472                _ => assert_eq!(a, b),
1473            }
1474        }
1475        assert_close(&disp_var_s, &scalar_var_s);
1476        assert_close(&disp_var_p, &scalar_var_p);
1477        assert_close(&disp_stdev_s, &scalar_stdev_s);
1478        assert_close(&disp_stdev_p, &scalar_stdev_p);
1479    }
1480
1481    #[test]
1482    fn fold_equivalence_edge_cases() {
1483        use crate::function::Function as _;
1484        let wb = TestWorkbook::new()
1485            .with_function(std::sync::Arc::new(VarSample))
1486            .with_function(std::sync::Arc::new(VarPop))
1487            .with_function(std::sync::Arc::new(StdevSample))
1488            .with_function(std::sync::Arc::new(StdevPop));
1489        let ctx = interp(&wb);
1490        // Single numeric element -> sample variance/div0, population variance 0
1491        let single = arr(vec![42.0]);
1492        let args_single = [ArgumentHandle::new(&single, &ctx)];
1493        let fctx = ctx.function_context(None);
1494        let disp_var_s = ctx
1495            .context
1496            .get_function("", "VAR.S")
1497            .unwrap()
1498            .dispatch(&args_single, &fctx)
1499            .unwrap();
1500        let scalar_var_s = VarSample.eval_scalar(&args_single, &fctx).unwrap();
1501        assert_eq!(disp_var_s, scalar_var_s);
1502        let disp_var_p = ctx
1503            .context
1504            .get_function("", "VAR.P")
1505            .unwrap()
1506            .dispatch(&args_single, &fctx)
1507            .unwrap();
1508        let scalar_var_p = VarPop.eval_scalar(&args_single, &fctx).unwrap();
1509        assert_eq!(disp_var_p, scalar_var_p);
1510        let disp_stdev_p = ctx
1511            .context
1512            .get_function("", "STDEV.P")
1513            .unwrap()
1514            .dispatch(&args_single, &fctx)
1515            .unwrap();
1516        let scalar_stdev_p = StdevPop.eval_scalar(&args_single, &fctx).unwrap();
1517        assert_eq!(disp_stdev_p, scalar_stdev_p);
1518        let disp_stdev_s = ctx
1519            .context
1520            .get_function("", "STDEV.S")
1521            .unwrap()
1522            .dispatch(&args_single, &fctx)
1523            .unwrap();
1524        let scalar_stdev_s = StdevSample.eval_scalar(&args_single, &fctx).unwrap();
1525        assert_eq!(disp_stdev_s, scalar_stdev_s);
1526    }
1527
1528    #[test]
1529    fn legacy_aliases_match_modern() {
1530        let wb = TestWorkbook::new()
1531            .with_function(std::sync::Arc::new(PercentileInc))
1532            .with_function(std::sync::Arc::new(QuartileInc))
1533            .with_function(std::sync::Arc::new(RankEqFn));
1534        let ctx = interp(&wb);
1535        let arr_node = arr(vec![1.0, 2.0, 3.0, 4.0, 5.0]);
1536        let p = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(0.4)), None);
1537        let q2 = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(2.0)), None);
1538        let target = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(4.0)), None);
1539        let args_p = [
1540            ArgumentHandle::new(&arr_node, &ctx),
1541            ArgumentHandle::new(&p, &ctx),
1542        ];
1543        let args_q = [
1544            ArgumentHandle::new(&arr_node, &ctx),
1545            ArgumentHandle::new(&q2, &ctx),
1546        ];
1547        let args_rank = [
1548            ArgumentHandle::new(&target, &ctx),
1549            ArgumentHandle::new(&arr_node, &ctx),
1550        ];
1551        let modern_p = ctx
1552            .context
1553            .get_function("", "PERCENTILE.INC")
1554            .unwrap()
1555            .dispatch(&args_p, &ctx.function_context(None))
1556            .unwrap();
1557        let legacy_p = ctx
1558            .context
1559            .get_function("", "PERCENTILE")
1560            .unwrap()
1561            .dispatch(&args_p, &ctx.function_context(None))
1562            .unwrap();
1563        assert_eq!(modern_p, legacy_p);
1564        let modern_q = ctx
1565            .context
1566            .get_function("", "QUARTILE.INC")
1567            .unwrap()
1568            .dispatch(&args_q, &ctx.function_context(None))
1569            .unwrap();
1570        let legacy_q = ctx
1571            .context
1572            .get_function("", "QUARTILE")
1573            .unwrap()
1574            .dispatch(&args_q, &ctx.function_context(None))
1575            .unwrap();
1576        assert_eq!(modern_q, legacy_q);
1577        let modern_rank = ctx
1578            .context
1579            .get_function("", "RANK.EQ")
1580            .unwrap()
1581            .dispatch(&args_rank, &ctx.function_context(None))
1582            .unwrap();
1583        let legacy_rank = ctx
1584            .context
1585            .get_function("", "RANK")
1586            .unwrap()
1587            .dispatch(&args_rank, &ctx.function_context(None))
1588            .unwrap();
1589        assert_eq!(modern_rank, legacy_rank);
1590    }
1591
1592    #[test]
1593    fn mode_single_basic_and_alias() {
1594        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ModeSingleFn));
1595        let ctx = interp(&wb);
1596        let arr_node = arr(vec![5.0, 2.0, 2.0, 3.0, 3.0, 3.0]);
1597        let args = [ArgumentHandle::new(&arr_node, &ctx)];
1598        let mode_sngl = ctx
1599            .context
1600            .get_function("", "MODE.SNGL")
1601            .unwrap()
1602            .dispatch(&args, &ctx.function_context(None))
1603            .unwrap();
1604        assert_eq!(mode_sngl, LiteralValue::Number(3.0));
1605        let mode_alias = ctx
1606            .context
1607            .get_function("", "MODE")
1608            .unwrap()
1609            .dispatch(&args, &ctx.function_context(None))
1610            .unwrap();
1611        assert_eq!(mode_alias, mode_sngl);
1612    }
1613
1614    #[test]
1615    fn mode_single_no_duplicates() {
1616        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ModeSingleFn));
1617        let ctx = interp(&wb);
1618        let arr_node = arr(vec![1.0, 2.0, 3.0]);
1619        let args = [ArgumentHandle::new(&arr_node, &ctx)];
1620        let out = ctx
1621            .context
1622            .get_function("", "MODE.SNGL")
1623            .unwrap()
1624            .dispatch(&args, &ctx.function_context(None))
1625            .unwrap();
1626        match out {
1627            LiteralValue::Error(e) => assert!(e.to_string().contains("#N/A")),
1628            _ => panic!("expected #N/A"),
1629        }
1630    }
1631
1632    #[test]
1633    fn mode_multi_basic() {
1634        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ModeMultiFn));
1635        let ctx = interp(&wb);
1636        let arr_node = arr(vec![2.0, 3.0, 2.0, 4.0, 3.0, 5.0, 2.0, 3.0]);
1637        let args = [ArgumentHandle::new(&arr_node, &ctx)];
1638        let out = ctx
1639            .context
1640            .get_function("", "MODE.MULT")
1641            .unwrap()
1642            .dispatch(&args, &ctx.function_context(None))
1643            .unwrap();
1644        let expected = LiteralValue::Array(vec![
1645            vec![LiteralValue::Number(2.0)],
1646            vec![LiteralValue::Number(3.0)],
1647        ]);
1648        assert_eq!(out, expected);
1649    }
1650
1651    #[test]
1652    fn large_small_fold_vs_scalar() {
1653        let wb = TestWorkbook::new()
1654            .with_function(std::sync::Arc::new(LARGE))
1655            .with_function(std::sync::Arc::new(SMALL));
1656        let ctx = interp(&wb);
1657        let arr_node = arr(vec![10.0, 5.0, 7.0, 12.0, 9.0]);
1658        let k_node = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(2.0)), None);
1659        let args = [
1660            ArgumentHandle::new(&arr_node, &ctx),
1661            ArgumentHandle::new(&k_node, &ctx),
1662        ];
1663        let f_large = ctx.context.get_function("", "LARGE").unwrap();
1664        let disp_large = f_large
1665            .dispatch(&args, &ctx.function_context(None))
1666            .unwrap();
1667        let scalar_large = LARGE
1668            .eval_scalar(&args, &ctx.function_context(None))
1669            .unwrap();
1670        assert_eq!(disp_large, scalar_large);
1671        let f_small = ctx.context.get_function("", "SMALL").unwrap();
1672        let disp_small = f_small
1673            .dispatch(&args, &ctx.function_context(None))
1674            .unwrap();
1675        let scalar_small = SMALL
1676            .eval_scalar(&args, &ctx.function_context(None))
1677            .unwrap();
1678        assert_eq!(disp_small, scalar_small);
1679    }
1680
1681    #[test]
1682    fn mode_fold_vs_scalar() {
1683        let wb = TestWorkbook::new()
1684            .with_function(std::sync::Arc::new(ModeSingleFn))
1685            .with_function(std::sync::Arc::new(ModeMultiFn));
1686        let ctx = interp(&wb);
1687        let arr_node = arr(vec![2.0, 3.0, 2.0, 4.0, 3.0, 3.0, 2.0]);
1688        let args = [ArgumentHandle::new(&arr_node, &ctx)];
1689        let f_single = ctx.context.get_function("", "MODE.SNGL").unwrap();
1690        let disp_single = f_single
1691            .dispatch(&args, &ctx.function_context(None))
1692            .unwrap();
1693        let scalar_single = ModeSingleFn
1694            .eval_scalar(&args, &ctx.function_context(None))
1695            .unwrap();
1696        assert_eq!(disp_single, scalar_single);
1697        let f_multi = ctx.context.get_function("", "MODE.MULT").unwrap();
1698        let disp_multi = f_multi
1699            .dispatch(&args, &ctx.function_context(None))
1700            .unwrap();
1701        let scalar_multi = ModeMultiFn
1702            .eval_scalar(&args, &ctx.function_context(None))
1703            .unwrap();
1704        assert_eq!(disp_multi, scalar_multi);
1705    }
1706
1707    #[test]
1708    fn median_fold_vs_scalar_even() {
1709        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(MEDIAN));
1710        let ctx = interp(&wb);
1711        let arr_node = arr(vec![7.0, 1.0, 9.0, 5.0]); // sorted: 1,5,7,9 median=(5+7)/2=6
1712        let args = [ArgumentHandle::new(&arr_node, &ctx)];
1713        let f_med = ctx.context.get_function("", "MEDIAN").unwrap();
1714        let disp = f_med.dispatch(&args, &ctx.function_context(None)).unwrap();
1715        let scalar = MEDIAN
1716            .eval_scalar(&args, &ctx.function_context(None))
1717            .unwrap();
1718        assert_eq!(disp, scalar);
1719        assert_eq!(disp, LiteralValue::Number(6.0));
1720    }
1721
1722    #[test]
1723    fn median_fold_vs_scalar_odd() {
1724        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(MEDIAN));
1725        let ctx = interp(&wb);
1726        let arr_node = arr(vec![9.0, 2.0, 5.0]); // sorted 2,5,9 median=5
1727        let args = [ArgumentHandle::new(&arr_node, &ctx)];
1728        let f_med = ctx.context.get_function("", "MEDIAN").unwrap();
1729        let disp = f_med.dispatch(&args, &ctx.function_context(None)).unwrap();
1730        let scalar = MEDIAN
1731            .eval_scalar(&args, &ctx.function_context(None))
1732            .unwrap();
1733        assert_eq!(disp, scalar);
1734        assert_eq!(disp, LiteralValue::Number(5.0));
1735    }
1736
1737    // Newly added edge case tests for statistical semantics.
1738    #[test]
1739    fn percentile_inc_edges() {
1740        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(PercentileInc));
1741        let ctx = interp(&wb);
1742        let arr_node = arr(vec![10.0, 20.0, 30.0, 40.0]);
1743        let p0 = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(0.0)), None);
1744        let p1 = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(1.0)), None);
1745        let f = ctx.context.get_function("", "PERCENTILE.INC").unwrap();
1746        let args0 = [
1747            ArgumentHandle::new(&arr_node, &ctx),
1748            ArgumentHandle::new(&p0, &ctx),
1749        ];
1750        let args1 = [
1751            ArgumentHandle::new(&arr_node, &ctx),
1752            ArgumentHandle::new(&p1, &ctx),
1753        ];
1754        assert_eq!(
1755            f.dispatch(&args0, &ctx.function_context(None)).unwrap(),
1756            LiteralValue::Number(10.0)
1757        );
1758        assert_eq!(
1759            f.dispatch(&args1, &ctx.function_context(None)).unwrap(),
1760            LiteralValue::Number(40.0)
1761        );
1762    }
1763
1764    #[test]
1765    fn percentile_exc_invalid() {
1766        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(PercentileExc));
1767        let ctx = interp(&wb);
1768        let arr_node = arr(vec![1.0, 2.0, 3.0, 4.0, 5.0]);
1769        let p_bad0 = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(0.0)), None);
1770        let p_bad1 = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(1.0)), None);
1771        let f = ctx.context.get_function("", "PERCENTILE.EXC").unwrap();
1772        for bad in [&p_bad0, &p_bad1] {
1773            let args = [
1774                ArgumentHandle::new(&arr_node, &ctx),
1775                ArgumentHandle::new(bad, &ctx),
1776            ];
1777            match f.dispatch(&args, &ctx.function_context(None)).unwrap() {
1778                LiteralValue::Error(e) => assert!(e.to_string().contains("#NUM!")),
1779                other => panic!("expected #NUM! got {other:?}"),
1780            }
1781        }
1782    }
1783
1784    #[test]
1785    fn quartile_invalids() {
1786        let wb = TestWorkbook::new()
1787            .with_function(std::sync::Arc::new(QuartileInc))
1788            .with_function(std::sync::Arc::new(QuartileExc));
1789        let ctx = interp(&wb);
1790        let arr_node = arr(vec![1.0, 2.0, 3.0]);
1791        // QUARTILE.INC invalid q=5
1792        let q5 = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(5.0)), None);
1793        let args_bad_inc = [
1794            ArgumentHandle::new(&arr_node, &ctx),
1795            ArgumentHandle::new(&q5, &ctx),
1796        ];
1797        match ctx
1798            .context
1799            .get_function("", "QUARTILE.INC")
1800            .unwrap()
1801            .dispatch(&args_bad_inc, &ctx.function_context(None))
1802            .unwrap()
1803        {
1804            LiteralValue::Error(e) => assert!(e.to_string().contains("#NUM!")),
1805            other => panic!("expected #NUM! {other:?}"),
1806        }
1807        // QUARTILE.EXC invalid q=0
1808        let q0 = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(0.0)), None);
1809        let args_bad_exc = [
1810            ArgumentHandle::new(&arr_node, &ctx),
1811            ArgumentHandle::new(&q0, &ctx),
1812        ];
1813        match ctx
1814            .context
1815            .get_function("", "QUARTILE.EXC")
1816            .unwrap()
1817            .dispatch(&args_bad_exc, &ctx.function_context(None))
1818            .unwrap()
1819        {
1820            LiteralValue::Error(e) => assert!(e.to_string().contains("#NUM!")),
1821            other => panic!("expected #NUM! {other:?}"),
1822        }
1823    }
1824
1825    #[test]
1826    fn rank_target_not_found() {
1827        let wb = TestWorkbook::new()
1828            .with_function(std::sync::Arc::new(RankEqFn))
1829            .with_function(std::sync::Arc::new(RankAvgFn));
1830        let ctx = interp(&wb);
1831        let arr_node = arr(vec![1.0, 2.0, 3.0]);
1832        let target = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(4.0)), None);
1833        let args = [
1834            ArgumentHandle::new(&target, &ctx),
1835            ArgumentHandle::new(&arr_node, &ctx),
1836        ];
1837        for name in ["RANK.EQ", "RANK.AVG"] {
1838            match ctx
1839                .context
1840                .get_function("", name)
1841                .unwrap()
1842                .dispatch(&args, &ctx.function_context(None))
1843                .unwrap()
1844            {
1845                LiteralValue::Error(e) => assert!(e.to_string().contains("#N/A")),
1846                other => panic!("expected #N/A {other:?}"),
1847            }
1848        }
1849    }
1850
1851    #[test]
1852    fn mode_mult_ordering() {
1853        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ModeMultiFn));
1854        let ctx = interp(&wb);
1855        // Two modes with same frequency; ensure ascending ordering in array result
1856        let arr_node = arr(vec![5.0, 2.0, 2.0, 5.0, 3.0, 7.0, 5.0, 2.0]); // 2 and 5 appear 4 times each
1857        let args = [ArgumentHandle::new(&arr_node, &ctx)];
1858        let out = ctx
1859            .context
1860            .get_function("", "MODE.MULT")
1861            .unwrap()
1862            .dispatch(&args, &ctx.function_context(None))
1863            .unwrap();
1864        match out {
1865            LiteralValue::Array(rows) => {
1866                let vals: Vec<f64> = rows
1867                    .into_iter()
1868                    .map(|r| {
1869                        if let LiteralValue::Number(n) = r[0] {
1870                            n
1871                        } else {
1872                            panic!("expected number")
1873                        }
1874                    })
1875                    .collect();
1876                assert_eq!(vals, vec![2.0, 5.0]);
1877            }
1878            other => panic!("expected array {other:?}"),
1879        }
1880    }
1881
1882    #[test]
1883    fn boolean_and_text_in_range_are_ignored() {
1884        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(StdevPop));
1885        let ctx = interp(&wb);
1886        let mixed = ASTNode::new(
1887            ASTNodeType::Literal(LiteralValue::Array(vec![vec![
1888                LiteralValue::Number(1.0),
1889                LiteralValue::Text("ABC".into()),
1890                LiteralValue::Boolean(true),
1891                LiteralValue::Number(4.0),
1892            ]])),
1893            None,
1894        );
1895        let f = ctx.context.get_function("", "STDEV.P").unwrap();
1896        let out = f
1897            .dispatch(
1898                &[ArgumentHandle::new(&mixed, &ctx)],
1899                &ctx.function_context(None),
1900            )
1901            .unwrap();
1902        // NOTE: Inline array literal is treated as a direct scalar argument (not a range reference),
1903        // so boolean TRUE is coerced to 1. Dataset becomes {1,1,4}; population stdev = sqrt(6/3)=sqrt(2).
1904        match out {
1905            LiteralValue::Number(v) => {
1906                assert!((v - 2f64.sqrt()).abs() < 1e-12, "expected sqrt(2) got {v}")
1907            }
1908            other => panic!("unexpected {other:?}"),
1909        }
1910    }
1911
1912    #[test]
1913    fn boolean_direct_arg_coerces() {
1914        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(StdevPop));
1915        let ctx = interp(&wb);
1916        let one = ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(1.0)), None);
1917        let t = ASTNode::new(ASTNodeType::Literal(LiteralValue::Boolean(true)), None);
1918        let f = ctx.context.get_function("", "STDEV.P").unwrap();
1919        let args = [
1920            ArgumentHandle::new(&one, &ctx),
1921            ArgumentHandle::new(&t, &ctx),
1922        ];
1923        let out = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1924        assert_eq!(out, LiteralValue::Number(0.0));
1925    }
1926}
formualizer_eval/builtins/stats/mod.rs

formualizer_eval/builtins/stats/
mod.rs
