diff --git a/crates/functions/src/date.rs b/crates/functions/src/date.rs --- a/crates/functions/src/date.rs +++ b/crates/functions/src/date.rs @@ -449,13 +449,15 @@ pub(crate) fn yearfrac_core(sys: DateSystem, s: i64, e: i64, basis: i64) -> R { let (mut a, mut b) = (d1.day as i64, d2.day as i64); + // Excel tests the end-31 rule against the original start day, before a February end moves to 30. + let start_30 = a >= 30; if last_of_feb(&d1) && last_of_feb(&d2) { b = 30; } if last_of_feb(&d1) || a == 31 { a = 30; } - if a == 30 && b == 31 { + if start_30 && b == 31 { b = 30; } ((d2.year as i64 - d1.year as i64) * 360 + (d2.month as i64 - d1.month as i64) * 30 + b - a) as f64 / 360.0 @@ -817,8 +819,13 @@ mod tests { #[test] fn yearfrac_basis0_feb_end_to_31() { - close(ev("YEARFRAC", vec![n(d(2021.0, 2.0, 28.0)), n(d(2021.0, 3.0, 31.0)), n(0.0)]), 30.0 / 360.0); - close(ev("YEARFRAC", vec![n(d(2020.0, 2.0, 29.0)), n(d(2020.0, 3.0, 31.0)), n(0.0)]), 30.0 / 360.0); + // Excel 16: a February-end start keeps its original day for the end-31 test, so 31 days. + close(ev("YEARFRAC", vec![n(d(2021.0, 2.0, 28.0)), n(d(2021.0, 3.0, 31.0)), n(0.0)]), 0.086_111_111_111_111_11); + close(ev("YEARFRAC", vec![n(d(2020.0, 2.0, 29.0)), n(d(2020.0, 3.0, 31.0)), n(0.0)]), 0.086_111_111_111_111_11); + close(ev("YEARFRAC", vec![n(d(2021.0, 3.0, 31.0)), n(d(2021.0, 2.0, 28.0)), n(0.0)]), 0.086_111_111_111_111_11); + close(ev("YEARFRAC", vec![n(d(2021.0, 1.0, 31.0)), n(d(2021.0, 2.0, 28.0)), n(0.0)]), 0.077_777_777_777_777_78); + close(ev("YEARFRAC", vec![n(d(2021.0, 1.0, 30.0)), n(d(2021.0, 3.0, 31.0)), n(0.0)]), 1.0 / 6.0); + close(ev("YEARFRAC", vec![n(d(2020.0, 2.0, 29.0)), n(d(2021.0, 2.0, 28.0)), n(0.0)]), 1.0); close(ev("YEARFRAC", vec![n(d(2021.0, 2.0, 28.0)), n(d(2021.0, 2.0, 28.0)), n(0.0)]), 0.0); } diff --git a/crates/functions/src/math.rs b/crates/functions/src/math.rs --- a/crates/functions/src/math.rs +++ b/crates/functions/src/math.rs @@ -314,6 +314,10 @@ fn modulo(x: f64, d: f64) -> R { if d == 0.0 { return Err(CellError::Div0); } + // Excel returns #NUM! once the quotient magnitude reaches 1.1259E12 (checked against Excel 16). + if (x / d).abs() >= 1.1259e12 { + return Err(CellError::Num); + } // `%` is an exact remainder (truncated), so |r| < |d| even for tiny divisors. let r = x % d; // Keep the sign of the divisor and avoid returning |d| from rounding. diff --git a/crates/functions/src/math/tests.rs b/crates/functions/src/math/tests.rs --- a/crates/functions/src/math/tests.rs +++ b/crates/functions/src/math/tests.rs @@ -135,16 +135,22 @@ fn rounding() { } #[test] -fn mod_tiny_divisor_stays_in_range() { - for (x, d) in [(3.0, 1e-300), (-1.0, 1e-300), (3.0, -1e-300), (-1.0, -1e-300)] { - match ev2("MOD", x, d) { - Value::Number(r) => { - assert!(r == 0.0 || (r < 0.0) == (d < 0.0), "MOD({x}, {d}) = {r}"); - assert!(r.abs() < d.abs(), "MOD({x}, {d}) = {r}"); - } - other => panic!("MOD({x}, {d}) = {other:?}"), - } +fn mod_quotient_limit() { + // Excel 16: #NUM! whenever |number / divisor| >= 1.1259E12. + for (x, d) in [ + (3.0, 1e-300), + (-1.0, 1e-300), + (3.0, -1e-300), + (-1.0, -1e-300), + (1e15, 7.0), + (1e10, 0.001), + (1_125_900_000_000.0, 1.0), + (-1_125_900_000_000.0, 1.0), + ] { + is_err(ev2("MOD", x, d), Num); } + close(ev2("MOD", 1e12 * 7.0 + 3.0, 7.0), 3.0); + close(ev2("MOD", 1_125_899_999_999.99, 1.0), 0.989_990_234_375); } #[test] diff --git a/crates/functions/src/stat/descriptive.rs b/crates/functions/src/stat/descriptive.rs --- a/crates/functions/src/stat/descriptive.rs +++ b/crates/functions/src/stat/descriptive.rs @@ -336,6 +336,10 @@ pub(super) fn percentrank_impl(a: &[Arg], exc: bool) -> R { if sig < 1.0 || s.is_empty() { return Err(CellError::Num); } + // Excel: 10^significance overflows from 309 up. + if sig >= 309.0 { + return Err(CellError::Div0); + } let (first, last) = (s.first().copied().unwrap_or(0.0), s.last().copied().unwrap_or(0.0)); if x < first || x > last { return Err(CellError::NA); @@ -359,6 +363,10 @@ pub(super) fn percentrank_impl(a: &[Arg], exc: bool) -> R { let (rl, rh) = (rank_of(lo), rank_of(hi)); rl + (x - lo) / (hi - lo) * (rh - rl) }; + // From significance 15 up Excel returns 15 significant digits, rounded. + if sig >= 15.0 { + return num_val(format!("{r:.14e}").parse().unwrap_or(r)); + } num_val(trunc_digits(r, sig)) } diff --git a/crates/functions/src/stat/tests.rs b/crates/functions/src/stat/tests.rs --- a/crates/functions/src/stat/tests.rs +++ b/crates/functions/src/stat/tests.rs @@ -214,10 +214,19 @@ fn percentiles() { close(ev("PERCENTRANK.EXC", vec![e9.clone(), n(5.43)]), 0.381); close(ev("PERCENTRANK.EXC", vec![e9, n(5.43), n(1.0)]), 0.3); let t = c(&[1.0, 2.0, 3.0]); - close(ev("PERCENTRANK", vec![t.clone(), n(2.0), n(400.0)]), 0.5); - close(ev("PERCENTRANK.INC", vec![t.clone(), n(2.0), n(400.0)]), 0.5); - close(ev("PERCENTRANK.INC", vec![t.clone(), n(1.0), n(400.0)]), 0.0); - close(ev("PERCENTRANK.EXC", vec![t, n(2.0), n(400.0)]), 0.5); + // Excel 16: significance 309 and up is #DIV/0!, 308 still works. + for name in ["PERCENTRANK", "PERCENTRANK.INC", "PERCENTRANK.EXC"] { + is_err(ev(name, vec![t.clone(), n(2.0), n(309.0)]), CellError::Div0); + is_err(ev(name, vec![t.clone(), n(1.0), n(400.0)]), CellError::Div0); + close(ev(name, vec![t.clone(), n(2.0), n(308.0)]), 0.5); + } + // From significance 15 up the result has 15 significant digits. + let k = c(&[1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0]); + for sig in [15.0, 16.0, 308.0] { + close_tol(ev("PERCENTRANK.INC", vec![k.clone(), n(2.123_456_789), n(sig)]), 0.187_242_798_166_667, 1e-16); + } + close(ev("PERCENTRANK.INC", vec![k.clone(), n(2.123_456_789), n(14.0)]), 0.187_242_798_166_66); + close(ev("PERCENTRANK.EXC", vec![k, n(2.123_456_789), n(16.0)]), 0.265_432_098_625); } #[test] diff --git a/crates/functions/src/text.rs b/crates/functions/src/text.rs --- a/crates/functions/src/text.rs +++ b/crates/functions/src/text.rs @@ -334,12 +334,14 @@ fn numbervalue(a: &[Arg], c: &mut dyn Ctx) -> R { if seen_dec { return Err(CellError::Value); } - } else if c == '.' || c == ',' { - return Err(CellError::Value); } else { out.push(c); } } + // A leftover `.` can be a decimal point but never a date separator: `1.2.3` is `#VALUE!` in Excel. + if out.matches('.').count() > 1 { + return Err(CellError::Value); + } let mut n = parse_number_text_in(&out, c.date_system()).ok_or(CellError::Value)?; for _ in 0..pct { n /= 100.0; @@ -1225,8 +1227,17 @@ mod tests { close(ev("NUMBERVALUE", vec![t(" 1 000 ")]), 1000.0); close(ev("NUMBERVALUE", vec![t("")]), 0.0); is_err(ev("NUMBERVALUE", vec![t("1.2.3")]), CellError::Value); - is_err(ev("NUMBERVALUE", vec![t("1.5"), t(","), t(" ")]), CellError::Value); - is_err(ev("NUMBERVALUE", vec![t("1,5"), t("."), t(" ")]), CellError::Value); + // Excel 16 (en-US) swaps the given separators, then parses what is left in the host locale. + close(ev("NUMBERVALUE", vec![t("1.5"), t(","), t(" ")]), 1.5); + close(ev("NUMBERVALUE", vec![t("1 234.5"), t(","), t(" ")]), 1234.5); + close(ev("NUMBERVALUE", vec![t("2.5%"), t(","), t(" ")]), 0.025); + close(ev("NUMBERVALUE", vec![t("1.5E3"), t(","), t(" ")]), 1500.0); + close(ev("NUMBERVALUE", vec![t("1.5"), t(";")]), 1.5); + close(ev("NUMBERVALUE", vec![t("1.5"), t("x")]), 1.5); + close(ev("NUMBERVALUE", vec![t("1,5"), t(".")]), 15.0); + is_err(ev("NUMBERVALUE", vec![t("12.345,6"), t(","), t(" ")]), CellError::Value); + is_err(ev("NUMBERVALUE", vec![t("1.2.3"), t(","), t(" ")]), CellError::Value); + is_err(ev("NUMBERVALUE", vec![t("1,5"), t("."), t(".")]), CellError::Value); is_text(ev("FIXED", vec![n(1234.567), n(1.0)]), "1,234.6"); is_text(ev("FIXED", vec![n(1234.567), n(-1.0)]), "1,230"); is_text(ev("FIXED", vec![n(-1234.567), n(1.0), b(true)]), "-1234.6");