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

formualizer_eval/builtins/stats/
mod.rs
