use super::{Arg, aggregate_numbers, cells, selected};
use crate::error::CellError;
use crate::formula::value::Value;
pub fn average(args: &[Arg]) -> Value {
match aggregate_numbers(args) {
Ok(ns) if ns.is_empty() => Value::Error(CellError::Div0),
#[expect(
clippy::cast_precision_loss,
reason = "a count that large cannot be reached"
)]
Ok(ns) => Value::Number(ns.iter().sum::<f64>() / ns.len() as f64),
Err(e) => Value::Error(e),
}
}
pub fn min(args: &[Arg]) -> Value {
extreme(args, f64::min)
}
pub fn max(args: &[Arg]) -> Value {
extreme(args, f64::max)
}
fn extreme(args: &[Arg], pick: fn(f64, f64) -> f64) -> Value {
match aggregate_numbers(args) {
Ok(ns) => Value::Number(ns.into_iter().reduce(pick).unwrap_or(0.0)),
Err(e) => Value::Error(e),
}
}
pub fn median(args: &[Arg]) -> Value {
match aggregate_numbers(args) {
Ok(ns) if ns.is_empty() => Value::Error(CellError::Num),
Ok(mut ns) => {
ns.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
let mid = ns.len() / 2;
Value::Number(if ns.len() % 2 == 0 {
f64::midpoint(ns[mid - 1], ns[mid])
} else {
ns[mid]
})
}
Err(e) => Value::Error(e),
}
}
pub fn count(args: &[Arg]) -> Value {
let mut n = 0usize;
for arg in args {
let mut flat = Vec::new();
arg.value.flatten(&mut flat);
for v in flat {
let counts = match v {
Value::Number(_) => true,
Value::Blank | Value::Error(_) | Value::Array(_) | Value::Lambda(_) => false,
Value::Bool(_) | Value::Text(_) => {
!arg.reference && !matches!(arg.value, Value::Array(_)) && v.number().is_ok()
}
};
n += usize::from(counts);
}
}
count_value(n)
}
pub fn counta(args: &[Arg]) -> Value {
let mut n = 0usize;
for arg in args {
let mut flat = Vec::new();
arg.value.flatten(&mut flat);
n += flat.iter().filter(|v| !matches!(v, Value::Blank)).count();
}
count_value(n)
}
pub fn countblank(args: &[Arg]) -> Value {
let mut n = 0usize;
for arg in args {
let mut flat = Vec::new();
arg.value.flatten(&mut flat);
n += flat
.iter()
.filter(|v| matches!(v, Value::Blank) || matches!(v, Value::Text(t) if t.is_empty()))
.count();
}
count_value(n)
}
#[expect(
clippy::cast_precision_loss,
reason = "a sheet cannot hold enough cells to lose a digit here"
)]
fn count_value(n: usize) -> Value {
Value::Number(n as f64)
}
pub fn countif(args: &[Arg]) -> Value {
let [range, criterion] = args else {
return Value::Error(CellError::Value);
};
conditional(&[range, criterion], None, |kept, _| count_value(kept.len()))
}
pub fn countifs(args: &[Arg]) -> Value {
if args.is_empty() || !args.len().is_multiple_of(2) {
return Value::Error(CellError::Value);
}
let pairs: Vec<&Arg> = args.iter().collect();
conditional(&pairs, None, |kept, _| count_value(kept.len()))
}
pub fn sumif(args: &[Arg]) -> Value {
one_condition(args, |ns| {
Value::Number(ns.iter().fold(0.0, |acc, n| acc + n))
})
}
pub fn sumifs(args: &[Arg]) -> Value {
many_conditions(args, |ns| {
Value::Number(ns.iter().fold(0.0, |acc, n| acc + n))
})
}
pub fn averageif(args: &[Arg]) -> Value {
one_condition(args, mean)
}
pub fn averageifs(args: &[Arg]) -> Value {
many_conditions(args, mean)
}
pub fn medianif(args: &[Arg]) -> Value {
one_condition(args, middle)
}
fn middle(ns: &[f64]) -> Value {
if ns.is_empty() {
return Value::Error(CellError::Num);
}
let mut ns = ns.to_vec();
ns.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
let mid = ns.len() / 2;
Value::Number(if ns.len().is_multiple_of(2) {
f64::midpoint(ns[mid - 1], ns[mid])
} else {
ns[mid]
})
}
fn mean(ns: &[f64]) -> Value {
if ns.is_empty() {
return Value::Error(CellError::Div0);
}
#[expect(
clippy::cast_precision_loss,
reason = "a count that large cannot be reached"
)]
let count = ns.len() as f64;
Value::Number(ns.iter().sum::<f64>() / count)
}
fn one_condition(args: &[Arg], body: fn(&[f64]) -> Value) -> Value {
let (range, criterion, values) = match args {
[r, c] => (r, c, r),
[r, c, v] => (r, c, v),
_ => return Value::Error(CellError::Value),
};
conditional(&[range, criterion], Some(values), |kept, values| {
body(&numbers_at(&kept, values))
})
}
fn many_conditions(args: &[Arg], body: fn(&[f64]) -> Value) -> Value {
let Some((values, rest)) = args.split_first() else {
return Value::Error(CellError::Value);
};
if rest.is_empty() || !rest.len().is_multiple_of(2) {
return Value::Error(CellError::Value);
}
let pairs: Vec<&Arg> = rest.iter().collect();
conditional(&pairs, Some(values), |kept, values| {
body(&numbers_at(&kept, values))
})
}
fn conditional(
pairs: &[&Arg],
values: Option<&Arg>,
body: impl Fn(Vec<usize>, &[&Value]) -> Value,
) -> Value {
let Some(first) = pairs.first() else {
return Value::Error(CellError::Value);
};
let ranges = pairs.iter().step_by(2).copied().chain(values);
for range in ranges {
if let Value::Error(e) = range.value {
return Value::Error(e);
}
}
let len = cells(first).len();
let keep = match selected(pairs, len) {
Ok(keep) => keep,
Err(e) => return Value::Error(e),
};
let positions: Vec<usize> = keep
.iter()
.enumerate()
.filter_map(|(i, on)| on.then_some(i))
.collect();
let values = values.map(cells).unwrap_or_default();
body(positions, &values)
}
fn numbers_at(positions: &[usize], values: &[&Value]) -> Vec<f64> {
positions
.iter()
.filter_map(|i| match values.get(*i) {
Some(Value::Number(n)) => Some(*n),
_ => None,
})
.collect()
}
pub fn large(args: &[Arg]) -> Value {
nth(args, true)
}
pub fn small(args: &[Arg]) -> Value {
nth(args, false)
}
fn nth(args: &[Arg], from_top: bool) -> Value {
let [array, k] = args else {
return Value::Error(CellError::Value);
};
let (Ok(mut ns), Ok(k)) = (aggregate_numbers(std::slice::from_ref(array)), k.number()) else {
return super::read_error(&[k.number()]);
};
if ns.is_empty() || k < 1.0 {
return Value::Error(CellError::Num);
}
ns.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "checked at least 1; a larger k is caught as #NUM! below"
)]
let index = k.trunc() as usize - 1;
let index = if from_top {
match ns.len().checked_sub(index + 1) {
Some(i) => i,
None => return Value::Error(CellError::Num),
}
} else {
index
};
ns.get(index)
.map_or(Value::Error(CellError::Num), |n| Value::Number(*n))
}
pub fn rank(args: &[Arg]) -> Value {
let (number, range, ascending) = match args {
[n, r] => (n.number(), r, Ok(0.0)),
[n, r, o] => (n.number(), r, o.number()),
_ => return Value::Error(CellError::Value),
};
let (Ok(number), Ok(order)) = (number, ascending) else {
return super::read_error(&[number, ascending]);
};
let Ok(ns) = aggregate_numbers(std::slice::from_ref(range)) else {
return Value::Error(CellError::Value);
};
if !ns.iter().any(|n| (n - number).abs() < f64::EPSILON) {
return Value::Error(CellError::Na);
}
let ahead = ns
.iter()
.filter(|n| {
if order == 0.0 {
**n > number
} else {
**n < number
}
})
.count();
count_value(ahead + 1)
}
pub fn stdev(args: &[Arg]) -> Value {
spread(args, true, true)
}
pub fn stdevp(args: &[Arg]) -> Value {
spread(args, false, true)
}
pub fn var(args: &[Arg]) -> Value {
spread(args, true, false)
}
pub fn varp(args: &[Arg]) -> Value {
spread(args, false, false)
}
fn spread(args: &[Arg], sample: bool, root: bool) -> Value {
let ns = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
let wanted = if sample { 2 } else { 1 };
if ns.len() < wanted {
return Value::Error(CellError::Div0);
}
#[expect(
clippy::cast_precision_loss,
reason = "a count that large cannot be reached"
)]
let count = ns.len() as f64;
let mean = ns.iter().sum::<f64>() / count;
let sum_squares: f64 = ns.iter().map(|n| (n - mean) * (n - mean)).sum();
let divisor = if sample { count - 1.0 } else { count };
let variance = sum_squares / divisor;
Value::Number(if root { variance.sqrt() } else { variance })
}
pub fn avedev(args: &[Arg]) -> Value {
around_mean(args, |ns, mean, count| {
ns.iter().map(|n| (n - mean).abs()).sum::<f64>() / count
})
}
pub fn devsq(args: &[Arg]) -> Value {
around_mean(args, |ns, mean, _| {
ns.iter().map(|n| (n - mean) * (n - mean)).sum()
})
}
fn around_mean(args: &[Arg], body: impl Fn(&[f64], f64, f64) -> f64) -> Value {
let ns = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
if ns.is_empty() {
return Value::Error(CellError::Num);
}
let count = count_of(&ns);
let mean = ns.iter().sum::<f64>() / count;
Value::Number(body(&ns, mean, count))
}
pub fn geomean(args: &[Arg]) -> Value {
let ns = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
if ns.is_empty() || ns.iter().any(|n| *n <= 0.0) {
return Value::Error(CellError::Num);
}
let logs: f64 = ns.iter().map(|n| n.ln()).sum();
Value::Number((logs / count_of(&ns)).exp())
}
pub fn harmean(args: &[Arg]) -> Value {
let ns = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
if ns.is_empty() || ns.iter().any(|n| *n <= 0.0) {
return Value::Error(CellError::Num);
}
let sum: f64 = ns.iter().map(|n| 1.0 / n).sum();
Value::Number(count_of(&ns) / sum)
}
pub fn skew(args: &[Arg]) -> Value {
let ns = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
let count = count_of(&ns);
if ns.len() < 3 {
return Value::Error(CellError::Div0);
}
let mean = ns.iter().sum::<f64>() / count;
let deviation = sample_deviation(&ns, mean, count);
if deviation == 0.0 {
return Value::Error(CellError::Div0);
}
let sum: f64 = ns.iter().map(|n| ((n - mean) / deviation).powi(3)).sum();
Value::Number(sum * count / ((count - 1.0) * (count - 2.0)))
}
pub fn skewp(args: &[Arg]) -> Value {
let ns = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
if ns.is_empty() {
return Value::Error(CellError::Div0);
}
let count = count_of(&ns);
let mean = ns.iter().sum::<f64>() / count;
let variance = ns.iter().map(|n| (n - mean) * (n - mean)).sum::<f64>() / count;
if variance == 0.0 {
return Value::Error(CellError::Div0);
}
let deviation = variance.sqrt();
let sum: f64 = ns.iter().map(|n| ((n - mean) / deviation).powi(3)).sum();
Value::Number(sum / count)
}
pub fn kurt(args: &[Arg]) -> Value {
let ns = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
if ns.len() < 4 {
return Value::Error(CellError::Div0);
}
let count = count_of(&ns);
let mean = ns.iter().sum::<f64>() / count;
let deviation = sample_deviation(&ns, mean, count);
if deviation == 0.0 {
return Value::Error(CellError::Div0);
}
let sum: f64 = ns.iter().map(|n| ((n - mean) / deviation).powi(4)).sum();
let scale = count * (count + 1.0) / ((count - 1.0) * (count - 2.0) * (count - 3.0));
let correction = 3.0 * (count - 1.0).powi(2) / ((count - 2.0) * (count - 3.0));
Value::Number(sum * scale - correction)
}
fn sample_deviation(ns: &[f64], mean: f64, count: f64) -> f64 {
(ns.iter().map(|n| (n - mean) * (n - mean)).sum::<f64>() / (count - 1.0)).sqrt()
}
pub fn mode(args: &[Arg]) -> Value {
let ns = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
let mut best: Option<(f64, usize)> = None;
for candidate in &ns {
let seen = ns
.iter()
.filter(|n| n.total_cmp(candidate) == core::cmp::Ordering::Equal)
.count();
if seen > 1 && best.is_none_or(|(_, most)| seen > most) {
best = Some((*candidate, seen));
}
}
best.map_or(Value::Error(CellError::Na), |(n, _)| Value::Number(n))
}
pub fn trimmean(args: &[Arg]) -> Value {
let Some((percent, rest)) = args.split_last() else {
return Value::Error(CellError::Value);
};
let Ok(percent) = percent.number() else {
return Value::Error(CellError::Value);
};
if !(0.0..=1.0).contains(&percent) {
return Value::Error(CellError::Num);
}
let mut ns = match aggregate_numbers(rest) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
if ns.is_empty() {
return Value::Error(CellError::Num);
}
ns.sort_by(f64::total_cmp);
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "a fraction of a count, floored, is a small non-negative number"
)]
let discard = (count_of(&ns) * percent / 2.0).floor() as usize;
if discard * 2 >= ns.len() {
return Value::Error(CellError::Num);
}
mean(&ns[discard..ns.len() - discard])
}
pub fn standardize(args: &[Arg]) -> Value {
let [x, mean, deviation] = args else {
return Value::Error(CellError::Value);
};
let (Ok(x), Ok(mean), Ok(deviation)) = (x.number(), mean.number(), deviation.number()) else {
return Value::Error(CellError::Value);
};
if deviation <= 0.0 {
return Value::Error(CellError::Num);
}
Value::Number((x - mean) / deviation)
}
pub fn permutationa(args: &[Arg]) -> Value {
let [n, k] = args else {
return Value::Error(CellError::Value);
};
let (Ok(n), Ok(k)) = (n.number(), k.number()) else {
return Value::Error(CellError::Value);
};
let (n, k) = (n.trunc(), k.trunc());
if n < 0.0 || k < 0.0 {
return Value::Error(CellError::Num);
}
Value::Number(n.powf(k))
}
pub fn percentile(args: &[Arg]) -> Value {
let Some((k, rest)) = args.split_last() else {
return Value::Error(CellError::Value);
};
let Ok(k) = k.number() else {
return Value::Error(CellError::Value);
};
if !(0.0..=1.0).contains(&k) {
return Value::Error(CellError::Num);
}
let Some(sorted) = sorted_numbers(rest) else {
return Value::Error(CellError::Num);
};
Value::Number(at_position(&sorted, k * (count_of(&sorted) - 1.0)))
}
pub fn percentile_exc(args: &[Arg]) -> Value {
let Some((k, rest)) = args.split_last() else {
return Value::Error(CellError::Value);
};
let Ok(k) = k.number() else {
return Value::Error(CellError::Value);
};
let Some(sorted) = sorted_numbers(rest) else {
return Value::Error(CellError::Num);
};
let count = count_of(&sorted);
let position = k * (count + 1.0) - 1.0;
if k <= 0.0 || k >= 1.0 || position < 0.0 || position > count - 1.0 {
return Value::Error(CellError::Num);
}
Value::Number(at_position(&sorted, position))
}
pub fn quartile(args: &[Arg]) -> Value {
quarter_of(args, percentile)
}
pub fn quartile_exc(args: &[Arg]) -> Value {
quarter_of(args, percentile_exc)
}
fn quarter_of(args: &[Arg], body: fn(&[Arg]) -> Value) -> Value {
let Some((quarter, rest)) = args.split_last() else {
return Value::Error(CellError::Value);
};
let Ok(quarter) = quarter.number() else {
return Value::Error(CellError::Value);
};
let k = quarter.floor() / 4.0;
if !(0.0..=1.0).contains(&k) {
return Value::Error(CellError::Num);
}
let mut with_k: Vec<Arg> = rest.to_vec();
with_k.push(Arg {
value: Value::Number(k),
reference: false,
});
body(&with_k)
}
pub fn percentrank(args: &[Arg]) -> Value {
rank_within(args, false)
}
pub fn percentrank_exc(args: &[Arg]) -> Value {
rank_within(args, true)
}
fn rank_within(args: &[Arg], exclusive: bool) -> Value {
let (array, value, digits) = match args {
[a, v] => (a, v, Ok(3.0)),
[a, v, d] => (a, v, d.number()),
_ => return Value::Error(CellError::Value),
};
let (Ok(value), Ok(digits)) = (value.number(), digits) else {
return Value::Error(CellError::Value);
};
if digits < 1.0 {
return Value::Error(CellError::Num);
}
let Some(sorted) = sorted_numbers(core::slice::from_ref(array)) else {
return Value::Error(CellError::Num);
};
let count = count_of(&sorted);
if value < sorted[0] || value > sorted[sorted.len() - 1] {
return Value::Error(CellError::Na);
}
let exact = sorted
.iter()
.position(|n| n.total_cmp(&value) == core::cmp::Ordering::Equal);
let position = if let Some(at) = exact {
count_of(&sorted[..at])
} else {
let above = sorted.iter().position(|n| *n > value).unwrap_or(0);
let below = above - 1;
count_of(&sorted[..below]) + (value - sorted[below]) / (sorted[above] - sorted[below])
};
let rank = if exclusive {
(position + 1.0) / (count + 1.0)
} else {
position / (count - 1.0)
};
#[expect(
clippy::cast_possible_truncation,
reason = "a digit count Excel caps well below what i32 holds"
)]
let scale = 10f64.powi(digits.trunc() as i32);
Value::Number((rank * scale).trunc() / scale)
}
pub fn correl(args: &[Arg]) -> Value {
paired_stat(args, |xs, ys| {
let (sxx, syy, sxy) = moments(xs, ys);
let denominator = (sxx * syy).sqrt();
(denominator != 0.0).then(|| sxy / denominator)
})
}
pub fn rsq(args: &[Arg]) -> Value {
paired_stat(args, |xs, ys| {
let (sxx, syy, sxy) = moments(xs, ys);
let denominator = sxx * syy;
(denominator != 0.0).then(|| sxy * sxy / denominator)
})
}
pub fn covar(args: &[Arg]) -> Value {
paired_stat(args, |xs, ys| {
let (_, _, sxy) = moments(xs, ys);
Some(sxy / count_of(xs))
})
}
pub fn covariance_s(args: &[Arg]) -> Value {
paired_stat(args, |xs, ys| {
let (_, _, sxy) = moments(xs, ys);
let count = count_of(xs);
(count > 1.0).then(|| sxy / (count - 1.0))
})
}
pub fn slope(args: &[Arg]) -> Value {
paired_stat(args, |xs, ys| {
let (sxx, _, sxy) = moments(xs, ys);
(sxx != 0.0).then(|| sxy / sxx)
})
}
pub fn intercept(args: &[Arg]) -> Value {
paired_stat(args, |xs, ys| {
let (sxx, _, sxy) = moments(xs, ys);
if sxx == 0.0 {
return None;
}
let count = count_of(xs);
let (mx, my) = (
xs.iter().sum::<f64>() / count,
ys.iter().sum::<f64>() / count,
);
Some(my - (sxy / sxx) * mx)
})
}
pub fn steyx(args: &[Arg]) -> Value {
paired_stat(args, |xs, ys| {
let count = count_of(xs);
if count < 3.0 {
return None;
}
let (sxx, syy, sxy) = moments(xs, ys);
if sxx == 0.0 {
return None;
}
Some(((syy - sxy * sxy / sxx) / (count - 2.0)).sqrt())
})
}
pub fn forecast(args: &[Arg]) -> Value {
let [x, ys, xs] = args else {
return Value::Error(CellError::Value);
};
let Ok(x) = x.number() else {
return Value::Error(CellError::Value);
};
paired_stat(&[ys.clone(), xs.clone()], |xs, ys| {
let (sxx, _, sxy) = moments(xs, ys);
if sxx == 0.0 {
return None;
}
let count = count_of(xs);
let (mx, my) = (
xs.iter().sum::<f64>() / count,
ys.iter().sum::<f64>() / count,
);
let gradient = sxy / sxx;
Some(my - gradient * mx + gradient * x)
})
}
pub fn fisher(args: &[Arg]) -> Value {
super::one(args, |x| {
if x <= -1.0 || x >= 1.0 {
return Value::Error(CellError::Num);
}
Value::Number(0.5 * ((1.0 + x) / (1.0 - x)).ln())
})
}
pub fn fisherinv(args: &[Arg]) -> Value {
super::one(args, |y| {
let e = (2.0 * y).exp();
Value::Number((e - 1.0) / (e + 1.0))
})
}
pub fn phi(args: &[Arg]) -> Value {
super::one(args, |x| {
Value::Number((-x * x / 2.0).exp() / (2.0 * core::f64::consts::PI).sqrt())
})
}
fn moments(xs: &[f64], ys: &[f64]) -> (f64, f64, f64) {
let count = count_of(xs);
let (mx, my) = (
xs.iter().sum::<f64>() / count,
ys.iter().sum::<f64>() / count,
);
let (mut sxx, mut syy, mut sxy) = (0.0, 0.0, 0.0);
for (x, y) in xs.iter().zip(ys) {
sxx += (x - mx) * (x - mx);
syy += (y - my) * (y - my);
sxy += (x - mx) * (y - my);
}
(sxx, syy, sxy)
}
fn paired_stat(args: &[Arg], body: impl Fn(&[f64], &[f64]) -> Option<f64>) -> Value {
let [ys, xs] = args else {
return Value::Error(CellError::Value);
};
if let Some(e) = super::first_error(args) {
return Value::Error(e);
}
let (ys, xs) = (cells(ys), cells(xs));
if ys.len() != xs.len() {
return Value::Error(CellError::Na);
}
let (mut x_kept, mut y_kept) = (Vec::new(), Vec::new());
for (y, x) in ys.iter().zip(&xs) {
if let (Value::Number(y), Value::Number(x)) = (y.scalar(), x.scalar()) {
y_kept.push(*y);
x_kept.push(*x);
}
}
if x_kept.is_empty() {
return Value::Error(CellError::Div0);
}
body(&x_kept, &y_kept).map_or(Value::Error(CellError::Div0), Value::Number)
}
fn sorted_numbers(args: &[Arg]) -> Option<Vec<f64>> {
let mut ns = aggregate_numbers(args).ok()?;
if ns.is_empty() {
return None;
}
ns.sort_by(f64::total_cmp);
Some(ns)
}
fn at_position(sorted: &[f64], position: f64) -> f64 {
let floor = position.floor();
#[expect(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
reason = "a position inside a list this crate could build"
)]
let base = floor as usize;
let next = sorted.get(base + 1).copied().unwrap_or(sorted[base]);
sorted[base] + (next - sorted[base]) * (position - floor)
}
#[expect(
clippy::cast_precision_loss,
reason = "a count that large cannot be reached"
)]
fn count_of<T>(items: &[T]) -> f64 {
items.len() as f64
}
pub fn averagea(args: &[Arg]) -> Value {
with_text_as_zero(args, mean)
}
pub fn maxa(args: &[Arg]) -> Value {
with_text_as_zero(args, |ns| extreme_of(ns, f64::max))
}
pub fn mina(args: &[Arg]) -> Value {
with_text_as_zero(args, |ns| extreme_of(ns, f64::min))
}
pub fn stdeva(args: &[Arg]) -> Value {
with_text_as_zero(args, |ns| spread_of(ns, true, true))
}
pub fn stdevpa(args: &[Arg]) -> Value {
with_text_as_zero(args, |ns| spread_of(ns, false, true))
}
pub fn vara(args: &[Arg]) -> Value {
with_text_as_zero(args, |ns| spread_of(ns, true, false))
}
pub fn varpa(args: &[Arg]) -> Value {
with_text_as_zero(args, |ns| spread_of(ns, false, false))
}
fn with_text_as_zero(args: &[Arg], body: impl Fn(&[f64]) -> Value) -> Value {
let mut ns = Vec::new();
for arg in args {
let written = !arg.reference && !matches!(arg.value, Value::Array(_));
let mut flat = Vec::new();
arg.value.flatten(&mut flat);
for v in flat {
match v {
Value::Error(e) => return Value::Error(*e),
Value::Number(n) => ns.push(*n),
Value::Bool(b) => ns.push(f64::from(u8::from(*b))),
Value::Text(_) if written => match v.number() {
Ok(n) => ns.push(n),
Err(e) => return Value::Error(e),
},
Value::Text(_) => ns.push(0.0),
Value::Blank | Value::Array(_) | Value::Lambda(_) => {}
}
}
}
body(&ns)
}
fn extreme_of(ns: &[f64], pick: fn(f64, f64) -> f64) -> Value {
if ns.is_empty() {
return Value::Number(0.0);
}
Value::Number(ns.iter().copied().fold(ns[0], pick))
}
fn spread_of(ns: &[f64], sample: bool, root: bool) -> Value {
if ns.len() < usize::from(sample) + 1 {
return Value::Error(CellError::Div0);
}
let count = count_of(ns);
let mean = ns.iter().sum::<f64>() / count;
let squares: f64 = ns.iter().map(|n| (n - mean) * (n - mean)).sum();
let variance = squares / if sample { count - 1.0 } else { count };
Value::Number(if root { variance.sqrt() } else { variance })
}
pub fn maxifs(args: &[Arg]) -> Value {
extreme_ifs(args, f64::max)
}
pub fn minifs(args: &[Arg]) -> Value {
extreme_ifs(args, f64::min)
}
fn extreme_ifs(args: &[Arg], pick: fn(f64, f64) -> f64) -> Value {
let Some((values, rest)) = args.split_first() else {
return Value::Error(CellError::Value);
};
if rest.is_empty() || !rest.len().is_multiple_of(2) {
return Value::Error(CellError::Value);
}
let pairs: Vec<&Arg> = rest.iter().collect();
conditional(&pairs, Some(values), |kept, values| {
let ns = numbers_at(&kept, values);
extreme_of(&ns, pick)
})
}
pub fn rank_avg(args: &[Arg]) -> Value {
let (needle, list, ascending) = match args {
[n, r] => (n.number(), r, Ok(0.0)),
[n, r, o] => (n.number(), r, o.number()),
_ => return Value::Error(CellError::Value),
};
let (Ok(needle), Ok(ascending)) = (needle, ascending) else {
return Value::Error(CellError::Value);
};
let ns: Vec<f64> = cells(list)
.into_iter()
.filter_map(|v| match v.scalar() {
Value::Number(n) => Some(*n),
_ => None,
})
.collect();
let ahead = ns
.iter()
.filter(|n| {
if ascending == 0.0 {
**n > needle
} else {
**n < needle
}
})
.count();
let tied = ns
.iter()
.filter(|n| n.total_cmp(&needle) == core::cmp::Ordering::Equal)
.count();
if tied == 0 {
return Value::Error(CellError::Na);
}
let first = count_of(&ns[..ahead]) + 1.0;
Value::Number(first + (count_of(&ns[..tied]) - 1.0) / 2.0)
}
pub fn mode_mult(args: &[Arg]) -> Value {
let ns = match aggregate_numbers(args) {
Ok(ns) => ns,
Err(e) => return Value::Error(e),
};
let count_same = |candidate: &f64| {
ns.iter()
.filter(|n| n.total_cmp(candidate) == core::cmp::Ordering::Equal)
.count()
};
let most = ns.iter().map(count_same).max().unwrap_or(0);
if most < 2 {
return Value::Error(CellError::Na);
}
let mut out: Vec<Vec<Value>> = Vec::new();
for (i, n) in ns.iter().enumerate() {
let first = ns[..i]
.iter()
.all(|earlier| earlier.total_cmp(n) != core::cmp::Ordering::Equal);
if first && count_same(n) == most {
out.push(vec![Value::Number(*n)]);
}
}
Value::array(out)
}
pub fn frequency(args: &[Arg]) -> Value {
let [data, bins] = args else {
return Value::Error(CellError::Value);
};
if let Some(e) = super::first_error(args) {
return Value::Error(e);
}
let numbers = |arg: &Arg| -> Vec<f64> {
cells(arg)
.into_iter()
.filter_map(|v| match v.scalar() {
Value::Number(n) => Some(*n),
_ => None,
})
.collect()
};
let data = numbers(data);
let mut edges = numbers(bins);
edges.sort_by(f64::total_cmp);
let mut counts = vec![0.0; edges.len() + 1];
for value in data {
let bin = edges.iter().position(|edge| value <= *edge);
counts[bin.unwrap_or(edges.len())] += 1.0;
}
Value::array(counts.into_iter().map(|n| vec![Value::Number(n)]).collect())
}
pub fn prob(args: &[Arg]) -> Value {
if let Some(e) = super::first_error(args) {
return Value::Error(e);
}
let (values, chances, low, high) = match args {
[v, c, l] => (v, c, l.number(), None),
[v, c, l, h] => (v, c, l.number(), Some(h.number())),
_ => return Value::Error(CellError::Value),
};
let Ok(low) = low else {
return Value::Error(CellError::Value);
};
let high = match high {
Some(Ok(h)) => h,
Some(Err(_)) => return Value::Error(CellError::Value),
None => low,
};
let (values, chances) = (cells(values), cells(chances));
if values.len() != chances.len() {
return Value::Error(CellError::Na);
}
let mut total = 0.0;
let mut all = 0.0;
for (value, chance) in values.iter().zip(&chances) {
let (Value::Number(value), Value::Number(chance)) = (value.scalar(), chance.scalar())
else {
continue;
};
if *chance < 0.0 || *chance > 1.0 {
return Value::Error(CellError::Num);
}
all += chance;
if (low..=high).contains(value) {
total += chance;
}
}
if (all - 1.0).abs() > 1.0e-9 {
return Value::Error(CellError::Num);
}
Value::Number(total)
}