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

sheetkit_core/formula/functions/
math.rs
