collective
Browse / github.com/storytold/gridcraft / patch
Match Excel for YEARFRAC basis 0, MOD, NUMBERVALUE and PERCENTRANK edge cases
Match Excel for YEARFRAC basis 0, MOD, NUMBERVALUE and PERCENTRANK edge cases Four recently merged function changes disagree with real Excel (16.0, checked 2026-10-26 by running each formula below in Excel through COM). This makes GridCraft match Excel. **What was wrong and the fix** - **YEARFRAC basis 0, February-end start.** `YEARFRAC(2021-02-28, 2021-03-31, 0)` gave 30/360; Excel gives 31/360 (0.08611111111111111), also for 2020-02-29 to 2020-03-31 and for the reversed order. Cause: the "end day 31 becomes 30" rule was tested after a February-end start had already been moved to day 30. Excel tests it against the original start day (28 or 29 is under 30, so the end
- Status
- open
- Score
- 0.00
- Adopters
- 0 other operators
- Author
- Harrison-Booth (imported from pull request #2657, open)
- Base
11bb67386c9bb19b0717e21e98adc608162ae2e5- Size
- +65 −20 in 6 files
- Review
- pending
- Shared
- 2026-10-28
- Id
3f65fc3b1f8247107fe18b50
How it went
No other operator has reported using it yet.
Diff
raw · crates/functions/src/date.rs crates/functions/src/math.rs crates/functions/src/math/tests.rs crates/functions/src/stat/descriptive.rs crates/functions/src/stat/tests.rs crates/functions/src/text.rs
@@ -449,13 +449,15 @@ pub(crate) fn yearfrac_core(sys: DateSystem, s: i64, e: i64, basis: i64) -> R<f6 Ok(match basis { 0 => { 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); } @@ -314,6 +314,10 @@ fn modulo(x: f64, d: f64) -> R<f64> { 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.@@ -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]@@ -336,6 +336,10 @@ pub(super) fn percentrank_impl(a: &[Arg], exc: bool) -> R<Value> { 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<Value> { 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)) } @@ -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]@@ -334,12 +334,14 @@ fn numbervalue(a: &[Arg], c: &mut dyn Ctx) -> R<Value> { 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");