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)]
258#[allow(clippy::manual_range_contains)]
259mod tests {
260    use crate::cell::CellValue;
261    use crate::formula::eval::{evaluate, CellSnapshot};
262    use crate::formula::parser::parse_formula;
263
264    fn eval_with_data(formula: &str, data: &[(&str, u32, u32, CellValue)]) -> CellValue {
265        let mut snap = CellSnapshot::new("Sheet1".to_string());
266        for (sheet, col, row, val) in data {
267            snap.set_cell(sheet, *col, *row, val.clone());
268        }
269        let expr = parse_formula(formula).unwrap();
270        evaluate(&expr, &snap).unwrap()
271    }
272
273    fn eval(formula: &str) -> CellValue {
274        eval_with_data(formula, &[])
275    }
276
277    fn approx_eq(a: f64, b: f64) -> bool {
278        (a - b).abs() < 1e-9
279    }
280
281    // SUMIF tests
282
283    #[test]
284    fn sumif_greater_than() {
285        let data = vec![
286            ("Sheet1", 1, 1, CellValue::Number(1.0)),
287            ("Sheet1", 1, 2, CellValue::Number(5.0)),
288            ("Sheet1", 1, 3, CellValue::Number(10.0)),
289        ];
290        let result = eval_with_data("SUMIF(A1:A3,\">3\")", &data);
291        assert_eq!(result, CellValue::Number(15.0));
292    }
293
294    #[test]
295    fn sumif_less_than_or_equal() {
296        let data = vec![
297            ("Sheet1", 1, 1, CellValue::Number(1.0)),
298            ("Sheet1", 1, 2, CellValue::Number(5.0)),
299            ("Sheet1", 1, 3, CellValue::Number(10.0)),
300        ];
301        let result = eval_with_data("SUMIF(A1:A3,\"<=5\")", &data);
302        assert_eq!(result, CellValue::Number(6.0));
303    }
304
305    #[test]
306    fn sumif_exact_text_match() {
307        let data = vec![
308            ("Sheet1", 1, 1, CellValue::String("Apple".to_string())),
309            ("Sheet1", 2, 1, CellValue::Number(10.0)),
310            ("Sheet1", 1, 2, CellValue::String("Banana".to_string())),
311            ("Sheet1", 2, 2, CellValue::Number(20.0)),
312            ("Sheet1", 1, 3, CellValue::String("Apple".to_string())),
313            ("Sheet1", 2, 3, CellValue::Number(30.0)),
314        ];
315        let result = eval_with_data("SUMIF(A1:A3,\"Apple\",B1:B3)", &data);
316        assert_eq!(result, CellValue::Number(40.0));
317    }
318
319    #[test]
320    fn sumif_not_equal() {
321        let data = vec![
322            ("Sheet1", 1, 1, CellValue::Number(0.0)),
323            ("Sheet1", 1, 2, CellValue::Number(5.0)),
324            ("Sheet1", 1, 3, CellValue::Number(0.0)),
325        ];
326        let result = eval_with_data("SUMIF(A1:A3,\"<>0\")", &data);
327        assert_eq!(result, CellValue::Number(5.0));
328    }
329
330    #[test]
331    fn sumif_no_sum_range() {
332        let data = vec![
333            ("Sheet1", 1, 1, CellValue::Number(2.0)),
334            ("Sheet1", 1, 2, CellValue::Number(4.0)),
335            ("Sheet1", 1, 3, CellValue::Number(6.0)),
336        ];
337        let result = eval_with_data("SUMIF(A1:A3,\">3\")", &data);
338        assert_eq!(result, CellValue::Number(10.0));
339    }
340
341    // SUMIFS tests
342
343    #[test]
344    fn sumifs_multi_criteria() {
345        let data = vec![
346            ("Sheet1", 1, 1, CellValue::String("A".to_string())),
347            ("Sheet1", 2, 1, CellValue::Number(1.0)),
348            ("Sheet1", 3, 1, CellValue::Number(10.0)),
349            ("Sheet1", 1, 2, CellValue::String("B".to_string())),
350            ("Sheet1", 2, 2, CellValue::Number(2.0)),
351            ("Sheet1", 3, 2, CellValue::Number(20.0)),
352            ("Sheet1", 1, 3, CellValue::String("A".to_string())),
353            ("Sheet1", 2, 3, CellValue::Number(3.0)),
354            ("Sheet1", 3, 3, CellValue::Number(30.0)),
355        ];
356        let result = eval_with_data("SUMIFS(C1:C3,A1:A3,\"A\",B1:B3,\">1\")", &data);
357        assert_eq!(result, CellValue::Number(30.0));
358    }
359
360    // ROUNDUP tests
361
362    #[test]
363    fn roundup_positive() {
364        let result = eval("ROUNDUP(3.2,0)");
365        assert_eq!(result, CellValue::Number(4.0));
366    }
367
368    #[test]
369    fn roundup_negative() {
370        let result = eval("ROUNDUP(-3.2,0)");
371        assert_eq!(result, CellValue::Number(-4.0));
372    }
373
374    #[test]
375    fn roundup_with_digits() {
376        let result = eval("ROUNDUP(3.14159,2)");
377        assert_eq!(result, CellValue::Number(3.15));
378    }
379
380    // ROUNDDOWN tests
381
382    #[test]
383    fn rounddown_positive() {
384        let result = eval("ROUNDDOWN(3.9,0)");
385        assert_eq!(result, CellValue::Number(3.0));
386    }
387
388    #[test]
389    fn rounddown_negative() {
390        let result = eval("ROUNDDOWN(-3.9,0)");
391        assert_eq!(result, CellValue::Number(-3.0));
392    }
393
394    // CEILING tests
395
396    #[test]
397    fn ceiling_basic() {
398        let result = eval("CEILING(2.5,1)");
399        assert_eq!(result, CellValue::Number(3.0));
400    }
401
402    #[test]
403    fn ceiling_significance() {
404        let result = eval("CEILING(4.42,0.05)");
405        if let CellValue::Number(n) = result {
406            assert!(approx_eq(n, 4.45));
407        } else {
408            panic!("expected number");
409        }
410    }
411
412    // FLOOR tests
413
414    #[test]
415    fn floor_basic() {
416        let result = eval("FLOOR(2.5,1)");
417        assert_eq!(result, CellValue::Number(2.0));
418    }
419
420    #[test]
421    fn floor_zero_significance() {
422        let result = eval("FLOOR(2.5,0)");
423        assert_eq!(result, CellValue::Error("#DIV/0!".to_string()));
424    }
425
426    // SIGN tests
427
428    #[test]
429    fn sign_positive() {
430        assert_eq!(eval("SIGN(42)"), CellValue::Number(1.0));
431    }
432
433    #[test]
434    fn sign_negative() {
435        assert_eq!(eval("SIGN(-42)"), CellValue::Number(-1.0));
436    }
437
438    #[test]
439    fn sign_zero() {
440        assert_eq!(eval("SIGN(0)"), CellValue::Number(0.0));
441    }
442
443    // PI test
444
445    #[test]
446    fn pi_value() {
447        if let CellValue::Number(n) = eval("PI()") {
448            assert!(approx_eq(n, std::f64::consts::PI));
449        } else {
450            panic!("expected number");
451        }
452    }
453
454    // LOG tests
455
456    #[test]
457    fn log_base10_default() {
458        if let CellValue::Number(n) = eval("LOG(100)") {
459            assert!(approx_eq(n, 2.0));
460        } else {
461            panic!("expected number");
462        }
463    }
464
465    #[test]
466    fn log_base2() {
467        if let CellValue::Number(n) = eval("LOG(8,2)") {
468            assert!(approx_eq(n, 3.0));
469        } else {
470            panic!("expected number");
471        }
472    }
473
474    #[test]
475    fn log_negative_input() {
476        assert_eq!(eval("LOG(-1)"), CellValue::Error("#NUM!".to_string()));
477    }
478
479    // LOG10 test
480
481    #[test]
482    fn log10_basic() {
483        if let CellValue::Number(n) = eval("LOG10(1000)") {
484            assert!(approx_eq(n, 3.0));
485        } else {
486            panic!("expected number");
487        }
488    }
489
490    // LN test
491
492    #[test]
493    fn ln_basic() {
494        if let CellValue::Number(n) = eval("LN(1)") {
495            assert!(approx_eq(n, 0.0));
496        } else {
497            panic!("expected number");
498        }
499    }
500
501    // EXP test
502
503    #[test]
504    fn exp_basic() {
505        if let CellValue::Number(n) = eval("EXP(0)") {
506            assert!(approx_eq(n, 1.0));
507        } else {
508            panic!("expected number");
509        }
510    }
511
512    #[test]
513    fn exp_one() {
514        if let CellValue::Number(n) = eval("EXP(1)") {
515            assert!(approx_eq(n, std::f64::consts::E));
516        } else {
517            panic!("expected number");
518        }
519    }
520
521    // PRODUCT test
522
523    #[test]
524    fn product_basic() {
525        assert_eq!(eval("PRODUCT(2,3,4)"), CellValue::Number(24.0));
526    }
527
528    // QUOTIENT test
529
530    #[test]
531    fn quotient_basic() {
532        assert_eq!(eval("QUOTIENT(7,2)"), CellValue::Number(3.0));
533    }
534
535    #[test]
536    fn quotient_negative() {
537        assert_eq!(eval("QUOTIENT(-7,2)"), CellValue::Number(-3.0));
538    }
539
540    #[test]
541    fn quotient_div_zero() {
542        assert_eq!(
543            eval("QUOTIENT(7,0)"),
544            CellValue::Error("#DIV/0!".to_string())
545        );
546    }
547
548    // FACT test
549
550    #[test]
551    fn fact_basic() {
552        assert_eq!(eval("FACT(5)"), CellValue::Number(120.0));
553    }
554
555    #[test]
556    fn fact_zero() {
557        assert_eq!(eval("FACT(0)"), CellValue::Number(1.0));
558    }
559
560    #[test]
561    fn fact_negative() {
562        assert_eq!(eval("FACT(-1)"), CellValue::Error("#NUM!".to_string()));
563    }
564
565    // RAND test (just verify it returns a number in [0, 1))
566
567    #[test]
568    fn rand_returns_number() {
569        if let CellValue::Number(n) = eval("RAND()") {
570            assert!(n >= 0.0 && n < 1.0);
571        } else {
572            panic!("expected number");
573        }
574    }
575
576    // RANDBETWEEN test
577
578    #[test]
579    fn randbetween_returns_integer_in_range() {
580        if let CellValue::Number(n) = eval("RANDBETWEEN(1,10)") {
581            assert!(n >= 1.0 && n <= 10.0);
582            assert_eq!(n, n.floor());
583        } else {
584            panic!("expected number");
585        }
586    }
587
588    #[test]
589    fn randbetween_invalid_range() {
590        assert_eq!(
591            eval("RANDBETWEEN(10,1)"),
592            CellValue::Error("#NUM!".to_string())
593        );
594    }
595}
sheetkit_core/formula/functions/math.rs

sheetkit_core/formula/functions/
math.rs
