use super::{Arg, aggregate_numbers, first_error, one};
use crate::error::CellError;
use crate::formula::eval::{Engine, Origin};
use crate::formula::parser::Expr;
use crate::formula::value::Value;
pub fn sum(args: &[Arg]) -> Value {
match aggregate_numbers(args) {
Ok(ns) => Value::Number(ns.iter().fold(0.0, |acc, n| acc + n)),
Err(e) => Value::Error(e),
}
}
pub fn product(args: &[Arg]) -> Value {
match aggregate_numbers(args) {
Ok(ns) if ns.is_empty() => Value::Number(0.0),
Ok(ns) => Value::Number(ns.iter().product()),
Err(e) => Value::Error(e),
}
}
pub fn abs(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.abs()))
}
pub fn sign(args: &[Arg]) -> Value {
one(args, |n| {
Value::Number(if n > 0.0 {
1.0
} else if n < 0.0 {
-1.0
} else {
0.0
})
})
}
pub fn int(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.floor()))
}
pub fn sqrt(args: &[Arg]) -> Value {
one(args, |n| {
if n < 0.0 {
Value::Error(CellError::Num)
} else {
Value::Number(n.sqrt())
}
})
}
pub fn exp(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.exp()))
}
pub fn ln(args: &[Arg]) -> Value {
one(args, |n| {
if n <= 0.0 {
Value::Error(CellError::Num)
} else {
Value::Number(n.ln())
}
})
}
pub fn log10(args: &[Arg]) -> Value {
one(args, |n| {
if n <= 0.0 {
Value::Error(CellError::Num)
} else {
Value::Number(n.log10())
}
})
}
pub fn pi(args: &[Arg]) -> Value {
if args.is_empty() {
Value::Number(std::f64::consts::PI)
} else {
Value::Error(CellError::Value)
}
}
pub fn log(args: &[Arg]) -> Value {
let (n, base) = match args {
[n] => (n.number(), Ok(10.0)),
[n, b] => (n.number(), b.number()),
_ => return Value::Error(CellError::Value),
};
match (n, base) {
(Ok(n), Ok(b)) if n > 0.0 && b > 0.0 && (b - 1.0).abs() > f64::EPSILON => {
Value::Number(n.log(b))
}
(Err(e), _) | (_, Err(e)) => Value::Error(e),
_ => Value::Error(CellError::Num),
}
}
pub fn power(args: &[Arg]) -> Value {
let [x, y] = args else {
return Value::Error(CellError::Value);
};
match (x.number(), y.number()) {
(Ok(x), Ok(y)) => {
let r = x.powf(y);
if r.is_finite() {
Value::Number(r)
} else {
Value::Error(CellError::Num)
}
}
(Err(e), _) | (_, Err(e)) => Value::Error(e),
}
}
pub fn mod_(args: &[Arg]) -> Value {
let [x, y] = args else {
return Value::Error(CellError::Value);
};
match (x.number(), y.number()) {
(Err(e), _) | (_, Err(e)) => Value::Error(e),
(Ok(n), Ok(d)) => {
if d == 0.0 {
Value::Error(CellError::Div0)
} else {
Value::Number(n - d * (n / d).floor())
}
}
}
}
fn round_to(n: f64, digits: f64) -> f64 {
let factor = 10f64.powf(digits);
let scaled = n * factor;
if !scaled.is_finite() {
return n;
}
let r = if scaled < 0.0 {
-(-scaled).round()
} else {
scaled.round()
};
r / factor
}
pub fn round(args: &[Arg]) -> Value {
two_numbers(args, |n, d| Value::Number(round_to(n, d)))
}
pub fn roundup(args: &[Arg]) -> Value {
two_numbers(args, |n, d| {
let f = 10f64.powf(d);
Value::Number((n * f).abs().ceil().copysign(n) / f)
})
}
pub fn rounddown(args: &[Arg]) -> Value {
two_numbers(args, |n, d| {
let f = 10f64.powf(d);
Value::Number((n * f).abs().floor().copysign(n) / f)
})
}
pub fn trunc(args: &[Arg]) -> Value {
match args {
[n] => match n.number() {
Ok(n) => Value::Number(n.trunc()),
Err(e) => Value::Error(e),
},
[_, _] => rounddown(args),
_ => Value::Error(CellError::Value),
}
}
pub fn ceiling(args: &[Arg]) -> Value {
multiple_of(args, f64::ceil)
}
pub fn floor(args: &[Arg]) -> Value {
multiple_of(args, f64::floor)
}
fn multiple_of(args: &[Arg], round: fn(f64) -> f64) -> Value {
two_numbers(args, |n, step| {
if step == 0.0 {
return Value::Number(0.0);
}
if n > 0.0 && step < 0.0 {
return Value::Error(CellError::Num);
}
Value::Number(round(n / step) * step)
})
}
fn two_numbers(args: &[Arg], body: impl Fn(f64, f64) -> Value) -> Value {
if let Some(err) = first_error(args) {
return Value::Error(err);
}
let [first, second] = args else {
return Value::Error(CellError::Value);
};
match (first.number(), second.number()) {
(Ok(x), Ok(y)) => body(x, y),
(Err(err), _) | (_, Err(err)) => Value::Error(err),
}
}
pub fn sumproduct(args: &[Arg]) -> Value {
let Some((first, rest)) = args.split_first() else {
return Value::Error(CellError::Value);
};
let mut totals: Vec<f64> = numbers_of(first);
for arg in rest {
let next = numbers_of(arg);
if next.len() != totals.len() {
return Value::Error(CellError::Value);
}
for (slot, n) in totals.iter_mut().zip(next) {
*slot *= n;
}
}
if let Some(e) = first_error(args) {
return Value::Error(e);
}
Value::Number(totals.iter().fold(0.0, |acc, n| acc + n))
}
fn numbers_of(arg: &Arg) -> Vec<f64> {
super::cells(arg)
.into_iter()
.map(|v| match v {
Value::Number(n) => *n,
Value::Bool(b) => f64::from(u8::from(*b)),
_ => 0.0,
})
.collect()
}
pub fn sin(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.sin()))
}
pub fn cos(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.cos()))
}
pub fn tan(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.tan()))
}
pub fn cot(args: &[Arg]) -> Value {
one(args, |n| {
if n == 0.0 {
Value::Error(CellError::Div0)
} else {
Value::Number(1.0 / n.tan())
}
})
}
pub fn sec(args: &[Arg]) -> Value {
one(args, |n| Value::Number(1.0 / n.cos()))
}
pub fn csc(args: &[Arg]) -> Value {
one(args, |n| {
if n == 0.0 {
Value::Error(CellError::Div0)
} else {
Value::Number(1.0 / n.sin())
}
})
}
pub fn asin(args: &[Arg]) -> Value {
one(args, |n| bounded(n, f64::asin))
}
pub fn acos(args: &[Arg]) -> Value {
one(args, |n| bounded(n, f64::acos))
}
pub fn atan(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.atan()))
}
pub fn acot(args: &[Arg]) -> Value {
one(args, |n| {
Value::Number(core::f64::consts::FRAC_PI_2 - n.atan())
})
}
pub fn atan2(args: &[Arg]) -> Value {
two_numbers(args, |x, y| {
if x == 0.0 && y == 0.0 {
return Value::Error(CellError::Div0);
}
Value::Number(y.atan2(x))
})
}
pub fn sinh(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.sinh()))
}
pub fn cosh(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.cosh()))
}
pub fn tanh(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.tanh()))
}
pub fn coth(args: &[Arg]) -> Value {
one(args, |n| {
if n == 0.0 {
Value::Error(CellError::Div0)
} else {
Value::Number(1.0 / n.tanh())
}
})
}
pub fn sech(args: &[Arg]) -> Value {
one(args, |n| Value::Number(1.0 / n.cosh()))
}
pub fn csch(args: &[Arg]) -> Value {
one(args, |n| {
if n == 0.0 {
Value::Error(CellError::Div0)
} else {
Value::Number(1.0 / n.sinh())
}
})
}
pub fn asinh(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.asinh()))
}
pub fn acosh(args: &[Arg]) -> Value {
one(args, |n| {
if n < 1.0 {
Value::Error(CellError::Num)
} else {
Value::Number(n.acosh())
}
})
}
pub fn atanh(args: &[Arg]) -> Value {
one(args, |n| {
if n <= -1.0 || n >= 1.0 {
Value::Error(CellError::Num)
} else {
Value::Number(n.atanh())
}
})
}
pub fn acoth(args: &[Arg]) -> Value {
one(args, |n| {
if n > -1.0 && n < 1.0 {
Value::Error(CellError::Num)
} else {
Value::Number((1.0 / n).atanh())
}
})
}
fn bounded(n: f64, f: fn(f64) -> f64) -> Value {
if (-1.0..=1.0).contains(&n) {
Value::Number(f(n))
} else {
Value::Error(CellError::Num)
}
}
pub fn degrees(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.to_degrees()))
}
pub fn radians(args: &[Arg]) -> Value {
one(args, |n| Value::Number(n.to_radians()))
}
pub fn even(args: &[Arg]) -> Value {
one(args, |n| Value::Number(away_to_step(n, 2.0)))
}
pub fn odd(args: &[Arg]) -> Value {
one(args, |n| {
if n == 0.0 {
return Value::Number(1.0);
}
let sign = n.signum();
Value::Number(sign * (away_to_step(n.abs() - 1.0, 2.0) + 1.0))
})
}
fn away_to_step(n: f64, step: f64) -> f64 {
if n == 0.0 {
return 0.0;
}
let sign = n.signum();
sign * (n.abs() / step).ceil() * step
}
pub fn mround(args: &[Arg]) -> Value {
two_numbers(args, |n, step| {
if step == 0.0 {
return Value::Number(0.0);
}
if n.signum() != step.signum() && n != 0.0 {
return Value::Error(CellError::Num);
}
Value::Number((n / step).round() * step)
})
}
pub fn quotient(args: &[Arg]) -> Value {
two_numbers(args, |a, b| {
if b == 0.0 {
Value::Error(CellError::Div0)
} else {
Value::Number((a / b).trunc())
}
})
}
pub fn gcd(args: &[Arg]) -> Value {
whole_numbers(args, |ns| ns.into_iter().fold(0u64, binary_gcd))
}
pub fn lcm(args: &[Arg]) -> Value {
whole_numbers(args, |ns| {
ns.into_iter().fold(1u64, |acc, n| {
if n == 0 || acc == 0 {
return 0;
}
acc / binary_gcd(acc, n) * n
})
})
}
fn whole_numbers(args: &[Arg], body: impl Fn(Vec<u64>) -> u64) -> Value {
let numbers = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
let mut whole = Vec::with_capacity(numbers.len());
for n in numbers {
if n < 0.0 || !n.is_finite() {
return Value::Error(CellError::Num);
}
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "checked non-negative and finite; a number past u64 is \
past what Excel's own 15 digits can say anyway"
)]
let truncated = n.trunc() as u64;
whole.push(truncated);
}
#[expect(
clippy::cast_precision_loss,
reason = "Excel stores every number as f64 regardless"
)]
let result = body(whole) as f64;
Value::Number(result)
}
fn binary_gcd(a: u64, b: u64) -> u64 {
let (mut a, mut b) = (a, b);
while b != 0 {
(a, b) = (b, a % b);
}
a
}
pub fn fact(args: &[Arg]) -> Value {
one(args, |n| {
if n < 0.0 || n.trunc() > 170.0 {
return Value::Error(CellError::Num);
}
let mut total = 1.0f64;
let mut i = 2.0;
while i <= n.trunc() {
total *= i;
i += 1.0;
}
Value::Number(total)
})
}
pub fn factdouble(args: &[Arg]) -> Value {
one(args, |n| {
if n < -1.0 {
return Value::Error(CellError::Num);
}
let n = n.trunc();
let mut total = 1.0f64;
let mut i = n;
while i > 1.0 {
total *= i;
i -= 2.0;
}
if total.is_finite() {
Value::Number(total)
} else {
Value::Error(CellError::Num)
}
})
}
pub fn combin(args: &[Arg]) -> Value {
two_numbers(args, |n, k| {
let (n, k) = (n.trunc(), k.trunc());
if n < 0.0 || k < 0.0 || k > n {
return Value::Error(CellError::Num);
}
Value::Number(choose(n, k))
})
}
pub fn combina(args: &[Arg]) -> Value {
two_numbers(args, |n, k| {
let (n, k) = (n.trunc(), k.trunc());
if n < 0.0 || k < 0.0 || (n == 0.0 && k > 0.0) {
return Value::Error(CellError::Num);
}
if k == 0.0 {
return Value::Number(1.0);
}
Value::Number(choose(n + k - 1.0, k))
})
}
pub fn permut(args: &[Arg]) -> Value {
two_numbers(args, |n, k| {
let (n, k) = (n.trunc(), k.trunc());
if n < 0.0 || k < 0.0 || k > n {
return Value::Error(CellError::Num);
}
let mut total = 1.0f64;
let mut i = 0.0;
while i < k {
total *= n - i;
i += 1.0;
}
Value::Number(total)
})
}
fn choose(n: f64, k: f64) -> f64 {
let k = k.min(n - k);
let mut total = 1.0f64;
let mut i = 0.0;
while i < k {
total = total * (n - i) / (i + 1.0);
i += 1.0;
}
total.round()
}
pub fn sumsq(args: &[Arg]) -> Value {
match aggregate_numbers(args) {
Ok(ns) => Value::Number(ns.iter().fold(0.0, |acc, n| acc + n * n)),
Err(e) => Value::Error(e),
}
}
pub fn sumx2my2(args: &[Arg]) -> Value {
paired(args, |x, y| x * x - y * y)
}
pub fn sumx2py2(args: &[Arg]) -> Value {
paired(args, |x, y| x * x + y * y)
}
pub fn sumxmy2(args: &[Arg]) -> Value {
paired(args, |x, y| (x - y) * (x - y))
}
fn paired(args: &[Arg], body: impl Fn(f64, f64) -> f64) -> Value {
if let Some(e) = first_error(args) {
return Value::Error(e);
}
let [x, y] = args else {
return Value::Error(CellError::Value);
};
let (xs, ys) = (super::cells(x), super::cells(y));
if xs.len() != ys.len() {
return Value::Error(CellError::Na);
}
let mut total = 0.0;
for (a, b) in xs.iter().zip(ys) {
if let (Value::Number(a), Value::Number(b)) = (a, b) {
total += body(*a, *b);
}
}
Value::Number(total)
}
pub fn seriessum(args: &[Arg]) -> Value {
if let Some(e) = first_error(args) {
return Value::Error(e);
}
let [x, n, m, coefficients] = args else {
return Value::Error(CellError::Value);
};
let (Ok(x), Ok(n), Ok(m)) = (x.number(), n.number(), m.number()) else {
return Value::Error(CellError::Value);
};
let mut total = 0.0;
for (i, c) in super::cells(coefficients).iter().enumerate() {
let Value::Number(c) = c else {
return Value::Error(CellError::Value);
};
#[expect(
clippy::cast_precision_loss,
reason = "an index into one argument's cells is far inside f64"
)]
let step = i as f64;
total += c * x.powf(n + step * m);
}
Value::Number(total)
}
pub fn sqrtpi(args: &[Arg]) -> Value {
one(args, |n| {
if n < 0.0 {
Value::Error(CellError::Num)
} else {
Value::Number((n * core::f64::consts::PI).sqrt())
}
})
}
pub fn multinomial(args: &[Arg]) -> Value {
let numbers = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
let mut total = 1.0f64;
let mut running = 0.0f64;
for n in numbers {
if n < 0.0 {
return Value::Error(CellError::Num);
}
let n = n.trunc();
running += n;
total *= choose(running, n);
}
Value::Number(total)
}
pub fn munit(args: &[Arg]) -> Value {
one(args, |n| {
let n = n.trunc();
if !(1.0..=1000.0).contains(&n) {
return Value::Error(CellError::Value);
}
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "bounded to 1..=1000 on the line above"
)]
let size = n as usize;
Value::array(
(0..size)
.map(|r| {
(0..size)
.map(|c| Value::Number(f64::from(u8::from(r == c))))
.collect()
})
.collect(),
)
})
}
pub fn mmult(args: &[Arg]) -> Value {
let [a, b] = args else {
return Value::Error(CellError::Value);
};
let (Some(a), Some(b)) = (matrix(a), matrix(b)) else {
return Value::Error(CellError::Value);
};
let inner = a.first().map_or(0, Vec::len);
if inner != b.len() || inner == 0 {
return Value::Error(CellError::Value);
}
let width = b.first().map_or(0, Vec::len);
let product = a
.iter()
.map(|row| {
(0..width)
.map(|c| {
let total: f64 = (0..inner).map(|k| row[k] * b[k][c]).sum();
Value::Number(total)
})
.collect()
})
.collect();
Value::array(product)
}
pub fn mdeterm(args: &[Arg]) -> Value {
let [arg] = args else {
return Value::Error(CellError::Value);
};
let Some(square) = square_matrix(arg) else {
return Value::Error(CellError::Value);
};
Value::Number(tidy(determinant(square)))
}
pub fn minverse(args: &[Arg]) -> Value {
let [arg] = args else {
return Value::Error(CellError::Value);
};
let Some(square) = square_matrix(arg) else {
return Value::Error(CellError::Value);
};
match crate::formula::functions::regression::invert_matrix(&square) {
Some(inverse) => Value::array(
inverse
.into_iter()
.map(|row| row.into_iter().map(|n| Value::Number(tidy(n))).collect())
.collect(),
),
None => Value::Error(CellError::Num),
}
}
fn tidy(n: f64) -> f64 {
if !n.is_finite() {
return n;
}
format!("{n:.14e}").parse().unwrap_or(n)
}
fn determinant(mut work: Vec<Vec<f64>>) -> f64 {
let n = work.len();
let mut result = 1.0;
for column in 0..n {
let Some(pivot) =
(column..n).max_by(|a, b| work[*a][column].abs().total_cmp(&work[*b][column].abs()))
else {
return 0.0;
};
if work[pivot][column].abs() < 1.0e-300 {
return 0.0;
}
if pivot != column {
work.swap(column, pivot);
result = -result;
}
result *= work[column][column];
let scale = work[column][column];
for row in column + 1..n {
let factor = work[row][column] / scale;
if factor == 0.0 {
continue;
}
let (before, after) = work.split_at_mut(row);
let (target, pivot) = (&mut after[0], &before[column]);
for (value, above) in target[column..].iter_mut().zip(&pivot[column..]) {
*value -= factor * above;
}
}
}
result
}
fn matrix(arg: &Arg) -> Option<Vec<Vec<f64>>> {
let Value::Array(rows) = &arg.value else {
return arg.number().ok().map(|n| vec![vec![n]]);
};
let width = rows.first()?.len();
rows.iter()
.map(|row| {
(row.len() == width)
.then(|| {
row.iter()
.map(|v| match v.scalar() {
Value::Number(n) => Some(*n),
_ => None,
})
.collect::<Option<Vec<f64>>>()
})
.flatten()
})
.collect()
}
fn square_matrix(arg: &Arg) -> Option<Vec<Vec<f64>>> {
let rows = matrix(arg)?;
let width = rows.first()?.len();
(width == rows.len() && width > 0).then_some(rows)
}
pub fn sequence(args: &[Arg]) -> Value {
if let Some(e) = first_error(args) {
return Value::Error(e);
}
let number = |at: usize, default: f64| {
args.get(at)
.filter(|a| !a.missing())
.map_or(Ok(default), Arg::number)
};
if args.is_empty() || args.len() > 4 {
return Value::Error(CellError::Value);
}
let (Ok(rows), Ok(columns), Ok(start), Ok(step)) = (
number(0, 1.0),
number(1, 1.0),
number(2, 1.0),
number(3, 1.0),
) else {
return Value::Error(CellError::Value);
};
let size = |n: f64| -> Option<usize> {
let n = n.trunc();
(1.0..=1.0e6).contains(&n).then(|| {
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "bounded to 1..=1e6 on the line above"
)]
let size = n as usize;
size
})
};
let (Some(rows), Some(columns)) = (size(rows), size(columns)) else {
return Value::Error(CellError::Value);
};
if rows.saturating_mul(columns) > 1_000_000 {
return Value::Error(CellError::Num);
}
Value::array(
(0..rows)
.map(|r| {
(0..columns)
.map(|c| {
#[expect(
clippy::cast_precision_loss,
reason = "a count bounded by a million"
)]
let step_count = (r * columns + c) as f64;
Value::Number(start + step_count * step)
})
.collect()
})
.collect(),
)
}
pub fn roman(args: &[Arg]) -> Value {
const NUMERALS: [(u32, &str); 13] = [
(1000, "M"),
(900, "CM"),
(500, "D"),
(400, "CD"),
(100, "C"),
(90, "XC"),
(50, "L"),
(40, "XL"),
(10, "X"),
(9, "IX"),
(5, "V"),
(4, "IV"),
(1, "I"),
];
let number = match args {
[n] | [n, _] => n.number(),
_ => return Value::Error(CellError::Value),
};
let Ok(number) = number else {
return Value::Error(CellError::Value);
};
let number = number.trunc();
if !(0.0..=3999.0).contains(&number) {
return Value::Error(CellError::Value);
}
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "bounded to 0..=3999 on the line above"
)]
let mut left = number as u32;
let mut out = String::new();
for (value, numeral) in NUMERALS {
while left >= value {
out.push_str(numeral);
left -= value;
}
}
Value::Text(out)
}
pub fn arabic(args: &[Arg]) -> Value {
let [arg] = args else {
return Value::Error(CellError::Value);
};
let text = match arg.text() {
Ok(text) => text.trim().to_uppercase(),
Err(e) => return Value::Error(e),
};
let (sign, digits) = match text.strip_prefix('-') {
Some(rest) => (-1.0, rest),
None => (1.0, text.as_str()),
};
let value = |c: char| match c {
'I' => Some(1),
'V' => Some(5),
'X' => Some(10),
'L' => Some(50),
'C' => Some(100),
'D' => Some(500),
'M' => Some(1000),
_ => None,
};
let mut total = 0i32;
let mut previous = 0i32;
for c in digits.chars().rev() {
let Some(n) = value(c) else {
return Value::Error(CellError::Value);
};
if n < previous {
total -= n;
} else {
total += n;
previous = n;
}
}
Value::Number(sign * f64::from(total))
}
pub fn base(args: &[Arg]) -> Value {
if let Some(e) = first_error(args) {
return Value::Error(e);
}
let (number, radix, length) = match args {
[n, r] => (n.number(), r.number(), Ok(0.0)),
[n, r, l] => (n.number(), r.number(), l.number()),
_ => return Value::Error(CellError::Value),
};
let (Ok(number), Ok(radix), Ok(length)) = (number, radix, length) else {
return Value::Error(CellError::Value);
};
let radix = radix.trunc();
if !(2.0..=36.0).contains(&radix) || number < 0.0 || number >= 2f64.powi(53) {
return Value::Error(CellError::Num);
}
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "range-checked on the line above"
)]
let (mut left, radix) = (number.trunc() as u64, radix as u64);
let mut digits = Vec::new();
while left > 0 {
let digit = usize::try_from(left % radix).unwrap_or(0);
digits.push(b"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"[digit]);
left /= radix;
}
if digits.is_empty() {
digits.push(b'0');
}
digits.reverse();
let text = String::from_utf8(digits).unwrap_or_default();
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "a padding width Excel caps at 255"
)]
let width = length.trunc().clamp(0.0, 255.0) as usize;
Value::Text(format!("{text:0>width$}"))
}
pub fn decimal(args: &[Arg]) -> Value {
if let Some(e) = first_error(args) {
return Value::Error(e);
}
let [text, radix] = args else {
return Value::Error(CellError::Value);
};
let (Ok(text), Ok(radix)) = (text.text(), radix.number()) else {
return Value::Error(CellError::Value);
};
let radix = radix.trunc();
if !(2.0..=36.0).contains(&radix) {
return Value::Error(CellError::Num);
}
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "bounded to 2..=36 on the line above"
)]
let radix = radix as u32;
match u64::from_str_radix(text.trim(), radix) {
#[expect(
clippy::cast_precision_loss,
reason = "Excel stores every number as f64 regardless"
)]
Ok(n) => Value::Number(n as f64),
Err(_) => Value::Error(CellError::Num),
}
}
pub fn ceiling_math(args: &[Arg]) -> Value {
directed(args, true)
}
pub fn floor_math(args: &[Arg]) -> Value {
directed(args, false)
}
pub fn ceiling_precise(args: &[Arg]) -> Value {
toward_infinity(args, true)
}
pub fn floor_precise(args: &[Arg]) -> Value {
toward_infinity(args, false)
}
fn directed(args: &[Arg], up: bool) -> Value {
if let Some(e) = first_error(args) {
return Value::Error(e);
}
let number = |at: usize, default: f64| {
args.get(at)
.filter(|a| !a.missing())
.map_or(Ok(default), Arg::number)
};
if args.is_empty() || args.len() > 3 {
return Value::Error(CellError::Value);
}
let (Ok(n), Ok(step), Ok(mode)) = (number(0, 0.0), number(1, 1.0), number(2, 0.0)) else {
return Value::Error(CellError::Value);
};
let step = step.abs();
if step == 0.0 {
return Value::Number(0.0);
}
let away = n < 0.0 && mode != 0.0;
let toward_larger = up ^ away;
let rounded = if toward_larger {
(n / step).ceil()
} else {
(n / step).floor()
};
Value::Number(rounded * step)
}
fn toward_infinity(args: &[Arg], up: bool) -> Value {
if let Some(e) = first_error(args) {
return Value::Error(e);
}
let (n, step) = match args {
[n] => (n.number(), Ok(1.0)),
[n, s] => (n.number(), s.number()),
_ => return Value::Error(CellError::Value),
};
let (Ok(n), Ok(step)) = (n, step) else {
return Value::Error(CellError::Value);
};
let step = step.abs();
if step == 0.0 {
return Value::Number(0.0);
}
let rounded = if up {
(n / step).ceil()
} else {
(n / step).floor()
};
Value::Number(rounded * step)
}
pub fn subtotal(engine: &mut Engine<'_>, origin: Origin, args: &[Expr]) -> Value {
let Some((code, rest)) = args.split_first() else {
return Value::Error(CellError::Value);
};
let Ok(code) = engine.eval_expr(origin, code).number() else {
return Value::Error(CellError::Value);
};
let code = code.trunc();
let code = if code > 100.0 { code - 100.0 } else { code };
aggregate_by_code(engine, origin, code, rest, 0.0)
}
pub fn aggregate(engine: &mut Engine<'_>, origin: Origin, args: &[Expr]) -> Value {
let [code, options, rest @ ..] = args else {
return Value::Error(CellError::Value);
};
let (Ok(code), Ok(options)) = (
engine.eval_expr(origin, code).number(),
engine.eval_expr(origin, options).number(),
) else {
return Value::Error(CellError::Value);
};
aggregate_by_code(engine, origin, code.trunc(), rest, options.trunc())
}
fn aggregate_by_code(
engine: &mut Engine<'_>,
origin: Origin,
code: f64,
args: &[Expr],
options: f64,
) -> Value {
let code_of = |n: f64| -> i64 {
#[expect(
clippy::cast_possible_truncation,
reason = "clamped to a range far inside i64 on this line"
)]
let code = n.trunc().clamp(-1000.0, 1000.0) as i64;
code
};
let skip_errors = matches!(code_of(options), 2 | 3 | 6 | 7);
let mut values: Vec<Arg> = Vec::with_capacity(args.len());
for arg in args {
let value = engine.eval_expr(origin, arg);
if skip_errors {
values.push(Arg {
value: without_errors(value),
reference: true,
});
} else {
values.push(Arg {
value,
reference: matches!(arg, Expr::Range { .. }),
});
}
}
let body: fn(&[Arg]) -> Value = match code_of(code) {
1 => super::stats::average,
2 => super::stats::count,
3 => super::stats::counta,
4 => super::stats::max,
5 => super::stats::min,
6 => product,
7 => super::stats::stdev,
8 => super::stats::stdevp,
9 => sum,
10 => super::stats::var,
11 => super::stats::varp,
12 => super::stats::median,
13 => super::stats::mode,
14 => super::stats::large,
15 => super::stats::small,
16 => super::stats::percentile,
17 => super::stats::quartile,
_ => return Value::Error(CellError::Value),
};
body(&values)
}
fn without_errors(value: Value) -> Value {
match value {
Value::Array(rows) => Value::array(
std::sync::Arc::unwrap_or_clone(rows)
.into_iter()
.map(|row| {
row.into_iter()
.map(|v| {
if matches!(v, Value::Error(_)) {
Value::Blank
} else {
v
}
})
.collect()
})
.collect(),
),
Value::Error(_) => Value::Blank,
other => other,
}
}
pub fn rand(args: &[Arg]) -> Value {
if !args.is_empty() {
return Value::Error(CellError::Value);
}
Value::Number(next_random())
}
pub fn randbetween(args: &[Arg]) -> Value {
let [low, high] = args else {
return Value::Error(CellError::Value);
};
let (Ok(low), Ok(high)) = (low.number(), high.number()) else {
return Value::Error(CellError::Value);
};
let (low, high) = (low.ceil(), high.floor());
if low > high {
return Value::Error(CellError::Num);
}
Value::Number(low + (next_random() * (high - low + 1.0)).floor().min(high - low))
}
pub fn randarray(args: &[Arg]) -> Value {
if let Some(e) = first_error(args) {
return Value::Error(e);
}
if args.len() > 5 {
return Value::Error(CellError::Value);
}
let number = |at: usize, default: f64| {
args.get(at)
.filter(|a| !a.missing())
.map_or(Ok(default), Arg::number)
};
let (Ok(rows), Ok(columns), Ok(low), Ok(high)) = (
number(0, 1.0),
number(1, 1.0),
number(2, 0.0),
number(3, 1.0),
) else {
return Value::Error(CellError::Value);
};
let whole = args
.get(4)
.and_then(|a| a.value.boolean().ok())
.unwrap_or(false);
if low > high {
return Value::Error(CellError::Value);
}
let size = |n: f64| -> Option<usize> {
let n = n.trunc();
(1.0..=1.0e6).contains(&n).then(|| {
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "bounded to 1..=1e6 on the line above"
)]
let size = n as usize;
size
})
};
let (Some(rows), Some(columns)) = (size(rows), size(columns)) else {
return Value::Error(CellError::Value);
};
if rows.saturating_mul(columns) > crate::formula::eval::MAX_RANGE_CELLS {
return Value::Error(CellError::Num);
}
Value::array(
(0..rows)
.map(|_| {
(0..columns)
.map(|_| {
let raw = low + next_random() * (high - low);
Value::Number(if whole { raw.floor() } else { raw })
})
.collect()
})
.collect(),
)
}
fn next_random() -> f64 {
use std::sync::atomic::{AtomicU64, Ordering};
static COUNTER: AtomicU64 = AtomicU64::new(0);
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "any bits of the clock will do as a seed"
)]
let seed = (crate::shared::unix_seconds().fract() * 1e9) as u64;
let mut z = COUNTER
.fetch_add(0x9E37_79B9_7F4A_7C15, Ordering::Relaxed)
.wrapping_add(seed);
z = (z ^ (z >> 30)).wrapping_mul(0xBF58_476D_1CE4_E5B9);
z = (z ^ (z >> 27)).wrapping_mul(0x94D0_49BB_1331_11EB);
z ^= z >> 31;
#[expect(
clippy::cast_precision_loss,
reason = "53 bits is exactly what f64's mantissa holds"
)]
let fraction = (z >> 11) as f64 / (1u64 << 53) as f64;
fraction
}