select_calculation_modes/
select_calculation_modes.rs

1//! The two numeric compatibility axes, shown by calculating the same cells under both settings.
2//!
3//! Both default to matching Excel rather than to what releases up to 0.1.2 did, so upgrading
4//! changes calculated numbers unless the 0.1.2 policies are selected explicitly. This example
5//! builds its own workbook so the difference is visible without an input file.
6
7use std::error::Error;
8
9use cellrune::{
10    ArithmeticSemantics, CalculationCellId, CalculationCellResult, CalculationOptions,
11    CalculationSnapshot, CellAddress, CellValue, FinancialSolverSemantics, FormulaText,
12    WorkbookDraft, calculate_workbook,
13};
14
15/// Formulas that expose Excel's narrow correction boundary, plus one real difference.
16///
17/// The first group is corrected, the sixth row cancels exactly but lies outside Excel's observed
18/// binary boundary, and the last row is a real difference that must not be swallowed.
19const ARITHMETIC: &[(&str, &str)] = &[
20    ("A1", "=0.1+0.2-0.3"),
21    ("A2", "=SUM(0.1,0.2,-0.3)"),
22    ("A3", "=SUMPRODUCT({0.1,0.2,-0.3})"),
23    ("A4", "=NPV(0.1,11,-12.1)"),
24    ("A5", "=(0.1+0.2-0.3)=0"),
25    ("A6", "=100.1-100-0.1"),
26    ("A7", "=100.1-100-0.099999999999999"),
27];
28
29/// An `IRR` whose answer depends on how long the solver is allowed to search.
30const SOLVER: &[(&str, &str)] = &[("B1", "=IRR({-100,30,35,40,45})")];
31
32fn main() -> Result<(), Box<dyn Error>> {
33    let mut draft = WorkbookDraft::new();
34    let sheet_id = draft.workbook().sheets()[0].id();
35    for (address, formula) in ARITHMETIC.iter().chain(SOLVER) {
36        draft.set_cell_formula(
37            sheet_id,
38            CellAddress::from_a1(address)?,
39            FormulaText::from_user_input(*formula)?,
40        )?;
41    }
42
43    // Defaults on both axes. Nothing has to be selected to match Excel.
44    let excel = CalculationOptions::default();
45    // What 0.1.2 did, and what a caller who depended on those numbers should select.
46    let legacy = CalculationOptions::default()
47        .with_arithmetic_semantics(ArithmeticSemantics::Ieee754)
48        .with_financial_solver_semantics(FinancialSolverSemantics::ExtendedSearch);
49
50    let excel_results = calculate_workbook(draft.workbook(), excel);
51    let legacy_results = calculate_workbook(draft.workbook(), legacy);
52
53    println!("{:<34} {:<26} 0.1.2 opt-in", "formula", "default (Excel)");
54    for (address, formula) in ARITHMETIC.iter().chain(SOLVER) {
55        let cell = CalculationCellId::new(sheet_id, CellAddress::from_a1(address)?);
56        println!(
57            "{formula:<34} {:<26} {}",
58            render(&excel_results, cell),
59            render(&legacy_results, cell)
60        );
61    }
62
63    println!();
64    println!(
65        "The correction requires both exact cancellation and Excel's observed relative binary \
66         boundary. The final two arithmetic rows are therefore identical under both policies. \
67         Compare calculated numbers with a tolerance rather than for equality under either one; \
68         see docs/NUMERICS.md."
69    );
70    Ok(())
71}
72
73fn render(calculation: &CalculationSnapshot, cell: CalculationCellId) -> String {
74    match calculation.cell(cell) {
75        Some(CalculationCellResult::Value(CellValue::Number(number))) => {
76            render_number(number.get())
77        }
78        Some(CalculationCellResult::Value(CellValue::Logical(logical))) => logical.to_string(),
79        Some(CalculationCellResult::Value(CellValue::Error(error))) => error.as_str().to_owned(),
80        Some(CalculationCellResult::Unavailable(issue)) => {
81            format!("unavailable: {}", issue.code().as_str())
82        }
83        // `CellValue` and `CalculationCellResult` are `#[non_exhaustive]`, so a wildcard arm is
84        // required even when every variant this example can produce is handled above.
85        Some(_) => "<other value>".to_owned(),
86        None => "<not calculated>".to_owned(),
87    }
88}
89
90/// Renders enough digits that a residue is visible rather than rounded away in the output.
91fn render_number(value: f64) -> String {
92    if value != 0.0 && value.abs() < 1e-6 {
93        format!("{value:e}")
94    } else {
95        format!("{value}")
96    }
97}
select_calculation_modes/select_calculation_modes.rs

select_calculation_modes/
select_calculation_modes.rs
