Skip to main content

sheetkit_core/formula/functions/
math.rs

1//! Math formula functions: SUMIF, SUMIFS, ROUNDUP, ROUNDDOWN, CEILING, FLOOR,
2//! SIGN, RAND, RANDBETWEEN, PI, LOG, LOG10, LN, EXP, PRODUCT, QUOTIENT, FACT.
3
4use crate::cell::CellValue;
5use crate::error::Result;
6use crate::formula::ast::Expr;
7use crate::formula::eval::{coerce_to_number, reference_number, Evaluator};
8use crate::formula::functions::conversion::{checked_trunc_i32_in, checked_trunc_i64};
9use crate::formula::functions::{
10    check_arg_count, collect_criteria_range_values, matches_prepared_criteria, prepare_criteria,
11};
12
13/// Rounding direction shared by ROUND, ROUNDUP, and ROUNDDOWN.
14#[derive(Clone, Copy, PartialEq, Eq)]
15pub(crate) enum RoundMode {
16    /// Nearest, with halves rounded away from zero (ROUND).
17    Nearest,
18    /// Away from zero (ROUNDUP).
19    Up,
20    /// Toward zero (ROUNDDOWN).
21    Down,
22}
23
24impl RoundMode {
25    fn apply(self, value: f64) -> f64 {
26        match self {
27            Self::Nearest => value.round(),
28            Self::Up if value >= 0.0 => value.ceil(),
29            Self::Up => value.floor(),
30            Self::Down => value.trunc(),
31        }
32    }
33}
34
35/// Largest power of ten representable as a finite `f64`.
36const MAX_DECIMAL_EXPONENT: i32 = 308;
37/// Digit counts are clamped to this magnitude; beyond it no `f64` changes.
38const MAX_ROUND_DIGITS: i32 = 400;
39/// Every `f64` at or above this magnitude is already an integer.
40const INTEGRAL_MAGNITUDE: f64 = 4_503_599_627_370_496.0;
41
42/// Round `n` to `digits` decimal places (negative digits round to the left
43/// of the decimal point), truncating `digits` toward zero.
44///
45/// Digit counts beyond what an `f64` can resolve leave the value unchanged
46/// (positive digits) or collapse it to zero (negative digits). A non-finite
47/// input, non-finite digit count, or non-finite result yields `#NUM!`; the
48/// function never returns NaN or infinity.
49pub(crate) fn round_to_digits(n: f64, digits: f64, mode: RoundMode) -> CellValue {
50    let num_error = || CellValue::Error("#NUM!".to_string());
51    if !n.is_finite() {
52        return num_error();
53    }
54    let bound = f64::from(MAX_ROUND_DIGITS);
55    let Some(digits) = checked_trunc_i32_in(
56        digits.clamp(-bound, bound),
57        -MAX_ROUND_DIGITS,
58        MAX_ROUND_DIGITS,
59    ) else {
60        return num_error();
61    };
62    let result = if digits > 0 {
63        let factor = 10f64.powi(digits.min(MAX_DECIMAL_EXPONENT));
64        let scaled = n * factor;
65        if !scaled.is_finite() || scaled.abs() >= INTEGRAL_MAGNITUDE {
66            n
67        } else {
68            mode.apply(scaled) / factor
69        }
70    } else if digits == 0 {
71        mode.apply(n)
72    } else if -digits > MAX_DECIMAL_EXPONENT {
73        // |n| < 10^309 / 2, so only rounding away from zero leaves zero.
74        if mode == RoundMode::Up && n != 0.0 {
75            return num_error();
76        }
77        0.0
78    } else {
79        let factor = 10f64.powi(-digits);
80        mode.apply(n / factor) * factor
81    };
82    if result.is_finite() {
83        CellValue::Number(result)
84    } else {
85        num_error()
86    }
87}
88
89/// SUMIF(range, criteria, \[sum_range\])
90pub fn fn_sumif(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
91    check_arg_count("SUMIF", args, 2, 3)?;
92    let range_vals = collect_criteria_range_values(&args[0], ctx)?;
93    let criteria_val = ctx.eval_expr(&args[1])?;
94    let criteria = prepare_criteria(criteria_val, ctx)?;
95    let sum_vals = if args.len() == 3 {
96        collect_criteria_range_values(&args[2], ctx)?
97    } else {
98        range_vals.clone()
99    };
100    let mut total = 0.0;
101    for (i, rv) in range_vals.iter().enumerate() {
102        if matches_prepared_criteria(rv, &criteria, ctx)? {
103            if let Some(n) = sum_vals.get(i).and_then(reference_number) {
104                total += n;
105            }
106        }
107    }
108    Ok(CellValue::Number(total))
109}
110
111/// SUMIFS(sum_range, criteria_range1, criteria1, ...)
112pub fn fn_sumifs(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
113    check_arg_count("SUMIFS", args, 3, 255)?;
114    if !(args.len() - 1).is_multiple_of(2) {
115        return Ok(CellValue::Error("#VALUE!".to_string()));
116    }
117    let sum_vals = collect_criteria_range_values(&args[0], ctx)?;
118    let pair_count = (args.len() - 1) / 2;
119    let mut criteria_ranges: Vec<Vec<CellValue>> = Vec::with_capacity(pair_count);
120    let mut criteria_values = Vec::with_capacity(pair_count);
121    for i in 0..pair_count {
122        let range_vals = collect_criteria_range_values(&args[1 + i * 2], ctx)?;
123        let crit_val = ctx.eval_expr(&args[2 + i * 2])?;
124        criteria_ranges.push(range_vals);
125        criteria_values.push(prepare_criteria(crit_val, ctx)?);
126    }
127    let mut total = 0.0;
128    for (idx, sv) in sum_vals.iter().enumerate() {
129        let mut all_match = true;
130        for (range_vals, criteria) in criteria_ranges.iter().zip(criteria_values.iter()) {
131            let Some(value) = range_vals.get(idx) else {
132                all_match = false;
133                break;
134            };
135            if !matches_prepared_criteria(value, criteria, ctx)? {
136                all_match = false;
137                break;
138            }
139        }
140        if all_match {
141            if let Some(n) = reference_number(sv) {
142                total += n;
143            }
144        }
145    }
146    Ok(CellValue::Number(total))
147}
148
149/// ROUNDUP(number, digits) - round away from zero
150pub fn fn_roundup(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
151    check_arg_count("ROUNDUP", args, 2, 2)?;
152    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
153    let digits = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
154    Ok(round_to_digits(n, digits, RoundMode::Up))
155}
156
157/// ROUNDDOWN(number, digits) - round toward zero (truncate)
158pub fn fn_rounddown(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
159    check_arg_count("ROUNDDOWN", args, 2, 2)?;
160    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
161    let digits = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
162    Ok(round_to_digits(n, digits, RoundMode::Down))
163}
164
165/// CEILING(number, significance) - round up to nearest multiple of significance
166pub fn fn_ceiling(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
167    check_arg_count("CEILING", args, 2, 2)?;
168    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
169    let sig = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
170    if sig == 0.0 {
171        return Ok(CellValue::Number(0.0));
172    }
173    if n > 0.0 && sig < 0.0 {
174        return Ok(CellValue::Error("#NUM!".to_string()));
175    }
176    let result = (n / sig).ceil() * sig;
177    Ok(CellValue::Number(result))
178}
179
180/// FLOOR(number, significance) - round down to nearest multiple of significance
181pub fn fn_floor(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
182    check_arg_count("FLOOR", args, 2, 2)?;
183    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
184    let sig = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
185    if sig == 0.0 {
186        return Ok(CellValue::Error("#DIV/0!".to_string()));
187    }
188    if n > 0.0 && sig < 0.0 {
189        return Ok(CellValue::Error("#NUM!".to_string()));
190    }
191    let result = (n / sig).floor() * sig;
192    Ok(CellValue::Number(result))
193}
194
195/// SIGN(number) - returns -1, 0, or 1
196pub fn fn_sign(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
197    check_arg_count("SIGN", args, 1, 1)?;
198    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
199    let result = if n > 0.0 {
200        1.0
201    } else if n < 0.0 {
202        -1.0
203    } else {
204        0.0
205    };
206    Ok(CellValue::Number(result))
207}
208
209/// RAND() - random number between 0 (inclusive) and 1 (exclusive)
210pub fn fn_rand(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
211    check_arg_count("RAND", args, 0, 0)?;
212    // Simple deterministic-ish random using system time for non-crypto randomness.
213    // In a real spreadsheet this would use a proper RNG.
214    let t = std::time::SystemTime::now()
215        .duration_since(std::time::UNIX_EPOCH)
216        .unwrap_or_default()
217        .subsec_nanos();
218    let r = (t as f64 % 1_000_000.0) / 1_000_000.0;
219    Ok(CellValue::Number(r))
220}
221
222/// RANDBETWEEN(bottom, top) - random integer in [bottom, top]
223pub fn fn_randbetween(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
224    check_arg_count("RANDBETWEEN", args, 2, 2)?;
225    let bottom = coerce_to_number(&ctx.eval_expr(&args[0])?)?.ceil();
226    let top = coerce_to_number(&ctx.eval_expr(&args[1])?)?.floor();
227    let (Some(bottom), Some(top)) = (
228        checked_trunc_i64(bottom, i64::MIN, i64::MAX),
229        checked_trunc_i64(top, i64::MIN, i64::MAX),
230    ) else {
231        return Ok(CellValue::Error("#NUM!".to_string()));
232    };
233    if bottom > top {
234        return Ok(CellValue::Error("#NUM!".to_string()));
235    }
236    let t = std::time::SystemTime::now()
237        .duration_since(std::time::UNIX_EPOCH)
238        .unwrap_or_default()
239        .subsec_nanos() as i64;
240    let Some(range) = top
241        .checked_sub(bottom)
242        .and_then(|width| width.checked_add(1))
243    else {
244        return Ok(CellValue::Error("#NUM!".to_string()));
245    };
246    let result = bottom + (t % range);
247    Ok(CellValue::Number(result as f64))
248}
249
250/// PI() - returns the value of pi
251pub fn fn_pi(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
252    check_arg_count("PI", args, 0, 0)?;
253    Ok(CellValue::Number(std::f64::consts::PI))
254}
255
256/// LOG(number, \[base\]) - logarithm with optional base (default 10)
257pub fn fn_log(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
258    check_arg_count("LOG", args, 1, 2)?;
259    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
260    if n <= 0.0 {
261        return Ok(CellValue::Error("#NUM!".to_string()));
262    }
263    let base = if args.len() > 1 {
264        coerce_to_number(&ctx.eval_expr(&args[1])?)?
265    } else {
266        10.0
267    };
268    if base <= 0.0 || base == 1.0 {
269        return Ok(CellValue::Error("#NUM!".to_string()));
270    }
271    Ok(CellValue::Number(n.log(base)))
272}
273
274/// LOG10(number) - base-10 logarithm
275pub fn fn_log10(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
276    check_arg_count("LOG10", args, 1, 1)?;
277    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
278    if n <= 0.0 {
279        return Ok(CellValue::Error("#NUM!".to_string()));
280    }
281    Ok(CellValue::Number(n.log10()))
282}
283
284/// LN(number) - natural logarithm
285pub fn fn_ln(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
286    check_arg_count("LN", args, 1, 1)?;
287    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
288    if n <= 0.0 {
289        return Ok(CellValue::Error("#NUM!".to_string()));
290    }
291    Ok(CellValue::Number(n.ln()))
292}
293
294/// EXP(number) - e raised to the power of number
295pub fn fn_exp(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
296    check_arg_count("EXP", args, 1, 1)?;
297    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
298    Ok(CellValue::Number(n.exp()))
299}
300
301/// PRODUCT(args...) - product of all numbers
302pub fn fn_product(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
303    check_arg_count("PRODUCT", args, 1, 255)?;
304    let nums = ctx.collect_numbers(args)?;
305    if nums.is_empty() {
306        return Ok(CellValue::Number(0.0));
307    }
308    let result: f64 = nums.iter().product();
309    Ok(CellValue::Number(result))
310}
311
312/// QUOTIENT(numerator, denominator) - integer part of a division
313pub fn fn_quotient(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
314    check_arg_count("QUOTIENT", args, 2, 2)?;
315    let num = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
316    let den = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
317    if den == 0.0 {
318        return Ok(CellValue::Error("#DIV/0!".to_string()));
319    }
320    let result = (num / den).trunc();
321    Ok(CellValue::Number(result))
322}
323
324/// FACT(number) - factorial of a non-negative integer
325pub fn fn_fact(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
326    check_arg_count("FACT", args, 1, 1)?;
327    let n = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
328    if !n.is_finite() || n < 0.0 {
329        return Ok(CellValue::Error("#NUM!".to_string()));
330    }
331    let truncated = n.floor();
332    if truncated > u64::MAX as f64 {
333        ctx.charge_evaluation_work(u64::MAX)?;
334        unreachable!("an unrepresentable factorial must exceed the formula budget");
335    }
336    let n_int = truncated as u64;
337    ctx.charge_evaluation_work(n_int.saturating_sub(1))?;
338    let mut result: f64 = 1.0;
339    for i in 2..=n_int {
340        result *= i as f64;
341        if !result.is_finite() {
342            return Ok(CellValue::Error("#NUM!".to_string()));
343        }
344    }
345    Ok(CellValue::Number(result))
346}
347
348#[cfg(test)]
349#[allow(clippy::manual_range_contains)]
350mod tests {
351    use crate::cell::CellValue;
352    use crate::formula::eval::{evaluate, CellSnapshot};
353    use crate::formula::parser::parse_formula;
354
355    fn eval_with_data(formula: &str, data: &[(&str, u32, u32, CellValue)]) -> CellValue {
356        let mut snap = CellSnapshot::new("Sheet1".to_string());
357        for (sheet, col, row, val) in data {
358            snap.set_cell(sheet, *col, *row, val.clone());
359        }
360        let expr = parse_formula(formula).unwrap();
361        evaluate(&expr, &snap).unwrap()
362    }
363
364    fn eval(formula: &str) -> CellValue {
365        eval_with_data(formula, &[])
366    }
367
368    fn approx_eq(a: f64, b: f64) -> bool {
369        (a - b).abs() < 1e-9
370    }
371
372    // SUMIF tests
373
374    #[test]
375    fn sumif_greater_than() {
376        let data = vec![
377            ("Sheet1", 1, 1, CellValue::Number(1.0)),
378            ("Sheet1", 1, 2, CellValue::Number(5.0)),
379            ("Sheet1", 1, 3, CellValue::Number(10.0)),
380        ];
381        let result = eval_with_data("SUMIF(A1:A3,\">3\")", &data);
382        assert_eq!(result, CellValue::Number(15.0));
383    }
384
385    #[test]
386    fn sumif_less_than_or_equal() {
387        let data = vec![
388            ("Sheet1", 1, 1, CellValue::Number(1.0)),
389            ("Sheet1", 1, 2, CellValue::Number(5.0)),
390            ("Sheet1", 1, 3, CellValue::Number(10.0)),
391        ];
392        let result = eval_with_data("SUMIF(A1:A3,\"<=5\")", &data);
393        assert_eq!(result, CellValue::Number(6.0));
394    }
395
396    #[test]
397    fn sumif_exact_text_match() {
398        let data = vec![
399            ("Sheet1", 1, 1, CellValue::String("Apple".to_string())),
400            ("Sheet1", 2, 1, CellValue::Number(10.0)),
401            ("Sheet1", 1, 2, CellValue::String("Banana".to_string())),
402            ("Sheet1", 2, 2, CellValue::Number(20.0)),
403            ("Sheet1", 1, 3, CellValue::String("Apple".to_string())),
404            ("Sheet1", 2, 3, CellValue::Number(30.0)),
405        ];
406        let result = eval_with_data("SUMIF(A1:A3,\"Apple\",B1:B3)", &data);
407        assert_eq!(result, CellValue::Number(40.0));
408    }
409
410    #[test]
411    fn sumif_not_equal() {
412        let data = vec![
413            ("Sheet1", 1, 1, CellValue::Number(0.0)),
414            ("Sheet1", 1, 2, CellValue::Number(5.0)),
415            ("Sheet1", 1, 3, CellValue::Number(0.0)),
416        ];
417        let result = eval_with_data("SUMIF(A1:A3,\"<>0\")", &data);
418        assert_eq!(result, CellValue::Number(5.0));
419    }
420
421    #[test]
422    fn sumif_no_sum_range() {
423        let data = vec![
424            ("Sheet1", 1, 1, CellValue::Number(2.0)),
425            ("Sheet1", 1, 2, CellValue::Number(4.0)),
426            ("Sheet1", 1, 3, CellValue::Number(6.0)),
427        ];
428        let result = eval_with_data("SUMIF(A1:A3,\">3\")", &data);
429        assert_eq!(result, CellValue::Number(10.0));
430    }
431
432    // SUMIFS tests
433
434    #[test]
435    fn sumifs_multi_criteria() {
436        let data = vec![
437            ("Sheet1", 1, 1, CellValue::String("A".to_string())),
438            ("Sheet1", 2, 1, CellValue::Number(1.0)),
439            ("Sheet1", 3, 1, CellValue::Number(10.0)),
440            ("Sheet1", 1, 2, CellValue::String("B".to_string())),
441            ("Sheet1", 2, 2, CellValue::Number(2.0)),
442            ("Sheet1", 3, 2, CellValue::Number(20.0)),
443            ("Sheet1", 1, 3, CellValue::String("A".to_string())),
444            ("Sheet1", 2, 3, CellValue::Number(3.0)),
445            ("Sheet1", 3, 3, CellValue::Number(30.0)),
446        ];
447        let result = eval_with_data("SUMIFS(C1:C3,A1:A3,\"A\",B1:B3,\">1\")", &data);
448        assert_eq!(result, CellValue::Number(30.0));
449    }
450
451    // ROUNDUP tests
452
453    #[test]
454    fn roundup_positive() {
455        let result = eval("ROUNDUP(3.2,0)");
456        assert_eq!(result, CellValue::Number(4.0));
457    }
458
459    #[test]
460    fn roundup_negative() {
461        let result = eval("ROUNDUP(-3.2,0)");
462        assert_eq!(result, CellValue::Number(-4.0));
463    }
464
465    #[test]
466    fn roundup_with_digits() {
467        let result = eval("ROUNDUP(3.14159,2)");
468        assert_eq!(result, CellValue::Number(3.15));
469    }
470
471    // ROUNDDOWN tests
472
473    #[test]
474    fn rounddown_positive() {
475        let result = eval("ROUNDDOWN(3.9,0)");
476        assert_eq!(result, CellValue::Number(3.0));
477    }
478
479    #[test]
480    fn rounddown_negative() {
481        let result = eval("ROUNDDOWN(-3.9,0)");
482        assert_eq!(result, CellValue::Number(-3.0));
483    }
484
485    // CEILING tests
486
487    #[test]
488    fn ceiling_basic() {
489        let result = eval("CEILING(2.5,1)");
490        assert_eq!(result, CellValue::Number(3.0));
491    }
492
493    #[test]
494    fn ceiling_significance() {
495        let result = eval("CEILING(4.42,0.05)");
496        if let CellValue::Number(n) = result {
497            assert!(approx_eq(n, 4.45));
498        } else {
499            panic!("expected number");
500        }
501    }
502
503    // FLOOR tests
504
505    #[test]
506    fn floor_basic() {
507        let result = eval("FLOOR(2.5,1)");
508        assert_eq!(result, CellValue::Number(2.0));
509    }
510
511    #[test]
512    fn floor_zero_significance() {
513        let result = eval("FLOOR(2.5,0)");
514        assert_eq!(result, CellValue::Error("#DIV/0!".to_string()));
515    }
516
517    // SIGN tests
518
519    #[test]
520    fn sign_positive() {
521        assert_eq!(eval("SIGN(42)"), CellValue::Number(1.0));
522    }
523
524    #[test]
525    fn sign_negative() {
526        assert_eq!(eval("SIGN(-42)"), CellValue::Number(-1.0));
527    }
528
529    #[test]
530    fn sign_zero() {
531        assert_eq!(eval("SIGN(0)"), CellValue::Number(0.0));
532    }
533
534    // PI test
535
536    #[test]
537    fn pi_value() {
538        if let CellValue::Number(n) = eval("PI()") {
539            assert!(approx_eq(n, std::f64::consts::PI));
540        } else {
541            panic!("expected number");
542        }
543    }
544
545    // LOG tests
546
547    #[test]
548    fn log_base10_default() {
549        if let CellValue::Number(n) = eval("LOG(100)") {
550            assert!(approx_eq(n, 2.0));
551        } else {
552            panic!("expected number");
553        }
554    }
555
556    #[test]
557    fn log_base2() {
558        if let CellValue::Number(n) = eval("LOG(8,2)") {
559            assert!(approx_eq(n, 3.0));
560        } else {
561            panic!("expected number");
562        }
563    }
564
565    #[test]
566    fn log_negative_input() {
567        assert_eq!(eval("LOG(-1)"), CellValue::Error("#NUM!".to_string()));
568    }
569
570    // LOG10 test
571
572    #[test]
573    fn log10_basic() {
574        if let CellValue::Number(n) = eval("LOG10(1000)") {
575            assert!(approx_eq(n, 3.0));
576        } else {
577            panic!("expected number");
578        }
579    }
580
581    // LN test
582
583    #[test]
584    fn ln_basic() {
585        if let CellValue::Number(n) = eval("LN(1)") {
586            assert!(approx_eq(n, 0.0));
587        } else {
588            panic!("expected number");
589        }
590    }
591
592    // EXP test
593
594    #[test]
595    fn exp_basic() {
596        if let CellValue::Number(n) = eval("EXP(0)") {
597            assert!(approx_eq(n, 1.0));
598        } else {
599            panic!("expected number");
600        }
601    }
602
603    #[test]
604    fn exp_one() {
605        if let CellValue::Number(n) = eval("EXP(1)") {
606            assert!(approx_eq(n, std::f64::consts::E));
607        } else {
608            panic!("expected number");
609        }
610    }
611
612    // PRODUCT test
613
614    #[test]
615    fn product_basic() {
616        assert_eq!(eval("PRODUCT(2,3,4)"), CellValue::Number(24.0));
617    }
618
619    // QUOTIENT test
620
621    #[test]
622    fn quotient_basic() {
623        assert_eq!(eval("QUOTIENT(7,2)"), CellValue::Number(3.0));
624    }
625
626    #[test]
627    fn quotient_negative() {
628        assert_eq!(eval("QUOTIENT(-7,2)"), CellValue::Number(-3.0));
629    }
630
631    #[test]
632    fn quotient_div_zero() {
633        assert_eq!(
634            eval("QUOTIENT(7,0)"),
635            CellValue::Error("#DIV/0!".to_string())
636        );
637    }
638
639    // FACT test
640
641    #[test]
642    fn fact_basic() {
643        assert_eq!(eval("FACT(5)"), CellValue::Number(120.0));
644    }
645
646    #[test]
647    fn fact_zero() {
648        assert_eq!(eval("FACT(0)"), CellValue::Number(1.0));
649    }
650
651    #[test]
652    fn fact_negative() {
653        assert_eq!(eval("FACT(-1)"), CellValue::Error("#NUM!".to_string()));
654    }
655
656    // RAND test (just verify it returns a number in [0, 1))
657
658    #[test]
659    fn rand_returns_number() {
660        if let CellValue::Number(n) = eval("RAND()") {
661            assert!(n >= 0.0 && n < 1.0);
662        } else {
663            panic!("expected number");
664        }
665    }
666
667    // RANDBETWEEN test
668
669    #[test]
670    fn randbetween_returns_integer_in_range() {
671        if let CellValue::Number(n) = eval("RANDBETWEEN(1,10)") {
672            assert!(n >= 1.0 && n <= 10.0);
673            assert_eq!(n, n.floor());
674        } else {
675            panic!("expected number");
676        }
677    }
678
679    #[test]
680    fn randbetween_invalid_range() {
681        assert_eq!(
682            eval("RANDBETWEEN(10,1)"),
683            CellValue::Error("#NUM!".to_string())
684        );
685    }
686
687    #[test]
688    fn randbetween_rejects_ranges_that_overflow_i64() {
689        assert_eq!(
690            eval("RANDBETWEEN(-9223372036854775808,9223372036854775807)"),
691            CellValue::Error("#NUM!".to_string())
692        );
693    }
694}