1use 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#[derive(Clone, Copy, PartialEq, Eq)]
15pub(crate) enum RoundMode {
16 Nearest,
18 Up,
20 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
35const MAX_DECIMAL_EXPONENT: i32 = 308;
37const MAX_ROUND_DIGITS: i32 = 400;
39const INTEGRAL_MAGNITUDE: f64 = 4_503_599_627_370_496.0;
41
42pub(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 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
89pub 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
111pub 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
149pub 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
157pub 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
165pub 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
180pub 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
195pub 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
209pub fn fn_rand(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
211 check_arg_count("RAND", args, 0, 0)?;
212 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
222pub 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
250pub 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
256pub 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
274pub 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
284pub 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
294pub 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
301pub 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
312pub 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
324pub 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 #[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 #[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 #[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 #[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 #[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 #[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 #[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 #[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 #[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 #[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 #[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 #[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 #[test]
615 fn product_basic() {
616 assert_eq!(eval("PRODUCT(2,3,4)"), CellValue::Number(24.0));
617 }
618
619 #[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 #[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 #[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 #[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}