Skip to main content

sheetkit_core/formula/functions/
mod.rs

1//! Built-in Excel function dispatch.
2//!
3//! Provides [`lookup_function`] to resolve a function name to its implementation,
4//! and helper utilities used by individual function implementations.
5
6pub(crate) mod conversion;
7pub mod date_time;
8pub mod engineering;
9pub mod financial;
10pub mod information;
11pub mod logical;
12pub mod lookup;
13pub mod math;
14pub mod statistical;
15pub mod text;
16
17use std::borrow::Cow;
18
19use crate::cell::CellValue;
20use crate::error::{Error, Result};
21use crate::formula::ast::Expr;
22use crate::formula::eval::{coerce_to_number, coerce_to_string, Evaluator};
23
24/// Signature for a built-in function implementation.
25///
26/// Functions receive unevaluated argument expressions and a mutable evaluator,
27/// allowing short-circuit evaluation (e.g., IF) and range expansion (e.g., SUM).
28pub type FunctionFn = fn(&[Expr], &mut Evaluator) -> Result<CellValue>;
29
30/// Resolve a function name (case-insensitive) to its implementation.
31pub fn lookup_function(name: &str) -> Option<FunctionFn> {
32    match name.to_ascii_uppercase().as_str() {
33        "SUM" => Some(fn_sum),
34        "AVERAGE" => Some(fn_average),
35        "COUNT" => Some(fn_count),
36        "COUNTA" => Some(fn_counta),
37        "MIN" => Some(fn_min),
38        "MAX" => Some(fn_max),
39        "IF" => Some(fn_if),
40        "ABS" => Some(fn_abs),
41        "INT" => Some(fn_int),
42        "ROUND" => Some(fn_round),
43        "MOD" => Some(fn_mod),
44        "POWER" => Some(fn_power),
45        "SQRT" => Some(fn_sqrt),
46        "LEN" => Some(fn_len),
47        "LOWER" => Some(fn_lower),
48        "UPPER" => Some(fn_upper),
49        "TRIM" => Some(fn_trim),
50        "LEFT" => Some(fn_left),
51        "RIGHT" => Some(fn_right),
52        "MID" => Some(fn_mid),
53        "CONCATENATE" => Some(fn_concatenate),
54        "AND" => Some(fn_and),
55        "OR" => Some(fn_or),
56        "NOT" => Some(fn_not),
57        "ISNUMBER" => Some(fn_isnumber),
58        "ISTEXT" => Some(fn_istext),
59        "ISBLANK" => Some(fn_isblank),
60        "ISERROR" => Some(fn_iserror),
61        "VALUE" => Some(fn_value),
62        "TEXT" => Some(fn_text),
63        "SUMIF" => Some(math::fn_sumif),
64        "SUMIFS" => Some(math::fn_sumifs),
65        "ROUNDUP" => Some(math::fn_roundup),
66        "ROUNDDOWN" => Some(math::fn_rounddown),
67        "CEILING" => Some(math::fn_ceiling),
68        "FLOOR" => Some(math::fn_floor),
69        "SIGN" => Some(math::fn_sign),
70        "RAND" => Some(math::fn_rand),
71        "RANDBETWEEN" => Some(math::fn_randbetween),
72        "PI" => Some(math::fn_pi),
73        "LOG" => Some(math::fn_log),
74        "LOG10" => Some(math::fn_log10),
75        "LN" => Some(math::fn_ln),
76        "EXP" => Some(math::fn_exp),
77        "PRODUCT" => Some(math::fn_product),
78        "QUOTIENT" => Some(math::fn_quotient),
79        "FACT" => Some(math::fn_fact),
80        "AVERAGEIF" => Some(statistical::fn_averageif),
81        "AVERAGEIFS" => Some(statistical::fn_averageifs),
82        "COUNTBLANK" => Some(statistical::fn_countblank),
83        "COUNTIF" => Some(statistical::fn_countif),
84        "COUNTIFS" => Some(statistical::fn_countifs),
85        "MEDIAN" => Some(statistical::fn_median),
86        "MODE" => Some(statistical::fn_mode),
87        "LARGE" => Some(statistical::fn_large),
88        "SMALL" => Some(statistical::fn_small),
89        "RANK" => Some(statistical::fn_rank),
90        "ISERR" => Some(information::fn_iserr),
91        "ISNA" => Some(information::fn_isna),
92        "ISLOGICAL" => Some(information::fn_islogical),
93        "ISEVEN" => Some(information::fn_iseven),
94        "ISODD" => Some(information::fn_isodd),
95        "TYPE" => Some(information::fn_type),
96        "N" => Some(information::fn_n),
97        "NA" => Some(information::fn_na),
98        "ERROR.TYPE" => Some(information::fn_error_type),
99        "CONCAT" => Some(text::fn_concat),
100        "FIND" => Some(text::fn_find),
101        "SEARCH" => Some(text::fn_search),
102        "SUBSTITUTE" => Some(text::fn_substitute),
103        "REPLACE" => Some(text::fn_replace),
104        "REPT" => Some(text::fn_rept),
105        "EXACT" => Some(text::fn_exact),
106        "T" => Some(text::fn_t),
107        "PROPER" => Some(text::fn_proper),
108        "TRUE" => Some(logical::fn_true),
109        "FALSE" => Some(logical::fn_false),
110        "IFERROR" => Some(logical::fn_iferror),
111        "IFNA" => Some(logical::fn_ifna),
112        "IFS" => Some(logical::fn_ifs),
113        "SWITCH" => Some(logical::fn_switch),
114        "XOR" => Some(logical::fn_xor),
115        "DATE" => Some(date_time::fn_date),
116        "TODAY" => Some(date_time::fn_today),
117        "NOW" => Some(date_time::fn_now),
118        "YEAR" => Some(date_time::fn_year),
119        "MONTH" => Some(date_time::fn_month),
120        "DAY" => Some(date_time::fn_day),
121        "HOUR" => Some(date_time::fn_hour),
122        "MINUTE" => Some(date_time::fn_minute),
123        "SECOND" => Some(date_time::fn_second),
124        "DATEDIF" => Some(date_time::fn_datedif),
125        "EDATE" => Some(date_time::fn_edate),
126        "EOMONTH" => Some(date_time::fn_eomonth),
127        "DATEVALUE" => Some(date_time::fn_datevalue),
128        "WEEKDAY" => Some(date_time::fn_weekday),
129        "WEEKNUM" => Some(date_time::fn_weeknum),
130        "NETWORKDAYS" => Some(date_time::fn_networkdays),
131        "WORKDAY" => Some(date_time::fn_workday),
132        "VLOOKUP" => Some(lookup::fn_vlookup),
133        "HLOOKUP" => Some(lookup::fn_hlookup),
134        "INDEX" => Some(lookup::fn_index),
135        "MATCH" => Some(lookup::fn_match),
136        "LOOKUP" => Some(lookup::fn_lookup),
137        "ROW" => Some(lookup::fn_row),
138        "COLUMN" => Some(lookup::fn_column),
139        "ROWS" => Some(lookup::fn_rows),
140        "COLUMNS" => Some(lookup::fn_columns),
141        "CHOOSE" => Some(lookup::fn_choose),
142        "ADDRESS" => Some(lookup::fn_address),
143        "FV" => Some(financial::fn_fv),
144        "PV" => Some(financial::fn_pv),
145        "NPV" => Some(financial::fn_npv),
146        "IRR" => Some(financial::fn_irr),
147        "PMT" => Some(financial::fn_pmt),
148        "IPMT" => Some(financial::fn_ipmt),
149        "PPMT" => Some(financial::fn_ppmt),
150        "RATE" => Some(financial::fn_rate),
151        "NPER" => Some(financial::fn_nper),
152        "DB" => Some(financial::fn_db),
153        "DDB" => Some(financial::fn_ddb),
154        "SLN" => Some(financial::fn_sln),
155        "SYD" => Some(financial::fn_syd),
156        "EFFECT" => Some(financial::fn_effect),
157        "NOMINAL" => Some(financial::fn_nominal),
158        "DOLLARDE" => Some(financial::fn_dollarde),
159        "DOLLARFR" => Some(financial::fn_dollarfr),
160        "CUMIPMT" => Some(financial::fn_cumipmt),
161        "CUMPRINC" => Some(financial::fn_cumprinc),
162        "XNPV" => Some(financial::fn_xnpv),
163        "XIRR" => Some(financial::fn_xirr),
164        "BIN2DEC" => Some(engineering::fn_bin2dec),
165        "BIN2HEX" => Some(engineering::fn_bin2hex),
166        "BIN2OCT" => Some(engineering::fn_bin2oct),
167        "DEC2BIN" => Some(engineering::fn_dec2bin),
168        "DEC2HEX" => Some(engineering::fn_dec2hex),
169        "DEC2OCT" => Some(engineering::fn_dec2oct),
170        "HEX2BIN" => Some(engineering::fn_hex2bin),
171        "HEX2DEC" => Some(engineering::fn_hex2dec),
172        "HEX2OCT" => Some(engineering::fn_hex2oct),
173        "OCT2BIN" => Some(engineering::fn_oct2bin),
174        "OCT2DEC" => Some(engineering::fn_oct2dec),
175        "OCT2HEX" => Some(engineering::fn_oct2hex),
176        "DELTA" => Some(engineering::fn_delta),
177        "GESTEP" => Some(engineering::fn_gestep),
178        "ERF" => Some(engineering::fn_erf),
179        "ERFC" => Some(engineering::fn_erfc),
180        "COMPLEX" => Some(engineering::fn_complex),
181        "IMREAL" => Some(engineering::fn_imreal),
182        "IMAGINARY" => Some(engineering::fn_imaginary),
183        "IMABS" => Some(engineering::fn_imabs),
184        "IMARGUMENT" => Some(engineering::fn_imargument),
185        "IMCONJUGATE" => Some(engineering::fn_imconjugate),
186        "IMSUM" => Some(engineering::fn_imsum),
187        "IMSUB" => Some(engineering::fn_imsub),
188        "IMPRODUCT" => Some(engineering::fn_improduct),
189        "IMDIV" => Some(engineering::fn_imdiv),
190        "IMPOWER" => Some(engineering::fn_impower),
191        "IMSQRT" => Some(engineering::fn_imsqrt),
192        "CONVERT" => Some(engineering::fn_convert),
193        "BESSELI" => Some(engineering::fn_besseli),
194        "BESSELJ" => Some(engineering::fn_besselj),
195        "BESSELK" => Some(engineering::fn_besselk),
196        "BESSELY" => Some(engineering::fn_bessely),
197        _ => None,
198    }
199}
200
201/// Verify that `args` has between `min` and `max` entries (inclusive).
202pub fn check_arg_count(name: &str, args: &[Expr], min: usize, max: usize) -> Result<()> {
203    if args.len() < min || args.len() > max {
204        let expected = if min == max {
205            format!("{min}")
206        } else {
207            format!("{min}..{max}")
208        };
209        return Err(Error::WrongArgCount {
210            name: name.to_string(),
211            expected,
212            got: args.len(),
213        });
214    }
215    Ok(())
216}
217
218fn finite_number_or_num_error(value: f64) -> Result<CellValue> {
219    if value.is_finite() {
220        Ok(CellValue::Number(value))
221    } else {
222        Ok(CellValue::Error("#NUM!".to_string()))
223    }
224}
225
226/// Check whether a cell value matches a criteria string.
227///
228/// Criteria format: ">5", "<=10", "<>text", "=exact", plain "text".
229/// Numeric comparisons are used when both sides parse as numbers;
230/// otherwise strings are compared case-insensitively. Wildcards use Excel's
231/// `*` and `?` behavior, with `~` escaping `*`, `?`, or `~`.
232pub fn matches_criteria(cell_value: &CellValue, criteria: &str, ctx: &Evaluator) -> Result<bool> {
233    matches_criteria_with_prepaid(cell_value, criteria, 0, ctx)
234}
235
236fn matches_criteria_with_prepaid(
237    cell_value: &CellValue,
238    criteria: &str,
239    prepaid_pattern_bytes: u64,
240    ctx: &Evaluator,
241) -> Result<bool> {
242    if criteria.is_empty() {
243        return Ok(matches!(cell_value, CellValue::Empty));
244    }
245    let (op, val_str) = if let Some(rest) = criteria.strip_prefix("<=") {
246        ("<=", rest)
247    } else if let Some(rest) = criteria.strip_prefix(">=") {
248        (">=", rest)
249    } else if let Some(rest) = criteria.strip_prefix("<>") {
250        ("<>", rest)
251    } else if let Some(rest) = criteria.strip_prefix('<') {
252        ("<", rest)
253    } else if let Some(rest) = criteria.strip_prefix('>') {
254        (">", rest)
255    } else if let Some(rest) = criteria.strip_prefix('=') {
256        ("=", rest)
257    } else {
258        ("=", criteria)
259    };
260
261    let cell_num = coerce_to_number(cell_value).ok();
262    let crit_num: Option<f64> = val_str.parse().ok();
263
264    if crit_num.is_some() && matches!(cell_value, CellValue::Empty) {
265        return Ok(false);
266    }
267
268    if let (Some(cn), Some(crn)) = (cell_num, crit_num) {
269        return Ok(match op {
270            "<=" => cn <= crn,
271            ">=" => cn >= crn,
272            "<>" => (cn - crn).abs() > f64::EPSILON,
273            "<" => cn < crn,
274            ">" => cn > crn,
275            "=" => (cn - crn).abs() < f64::EPSILON,
276            _ => false,
277        });
278    }
279
280    let uses_wildcards = val_str.contains(['*', '?', '~']);
281    if matches!(op, "=" | "<>") && uses_wildcards {
282        let (cell_text, prepaid_text_bytes) = cell_value_as_wildcard_text(cell_value, ctx)?;
283        let prepaid_decode_bytes = prepaid_pattern_bytes.saturating_add(prepaid_text_bytes);
284        let matched =
285            wildcard_match_with_prepaid(cell_text.as_ref(), val_str, prepaid_decode_bytes, ctx)?;
286        return Ok(if op == "=" { matched } else { !matched });
287    }
288    let cell_text = cell_value_as_text(cell_value);
289    let cell_lower = cell_text.to_lowercase();
290    let crit_lower = val_str.to_lowercase();
291
292    Ok(match op {
293        "=" => cell_lower == crit_lower,
294        "<>" => cell_lower != crit_lower,
295        "<" => cell_lower < crit_lower,
296        ">" => cell_lower > crit_lower,
297        "<=" => cell_lower <= crit_lower,
298        ">=" => cell_lower >= crit_lower,
299        _ => false,
300    })
301}
302
303pub(crate) struct PreparedCriteria {
304    text: String,
305    prepaid_match_bytes: u64,
306}
307
308pub(crate) fn prepare_criteria(
309    criteria_value: CellValue,
310    ctx: &Evaluator,
311) -> Result<PreparedCriteria> {
312    let (text, prepaid_match_bytes) = cell_value_into_criteria_text(criteria_value, ctx)?;
313    Ok(PreparedCriteria {
314        text,
315        prepaid_match_bytes,
316    })
317}
318
319pub(crate) fn matches_prepared_criteria(
320    cell_value: &CellValue,
321    criteria: &PreparedCriteria,
322    ctx: &Evaluator,
323) -> Result<bool> {
324    matches_criteria_with_prepaid(
325        cell_value,
326        &criteria.text,
327        criteria.prepaid_match_bytes,
328        ctx,
329    )
330}
331
332fn cell_value_as_text(value: &CellValue) -> Cow<'_, str> {
333    match value {
334        CellValue::String(text) | CellValue::Error(text) => Cow::Borrowed(text),
335        CellValue::Formula {
336            result: Some(result),
337            ..
338        } => cell_value_as_text(result),
339        CellValue::Empty | CellValue::Formula { result: None, .. } => Cow::Borrowed(""),
340        _ => Cow::Owned(coerce_to_string(value)),
341    }
342}
343
344fn cell_value_into_criteria_text(value: CellValue, ctx: &Evaluator) -> Result<(String, u64)> {
345    match value {
346        CellValue::String(text) | CellValue::Error(text) => Ok((text, 0)),
347        CellValue::Formula {
348            result: Some(result),
349            ..
350        } => cell_value_into_criteria_text(*result, ctx),
351        CellValue::Empty | CellValue::Formula { result: None, .. } => Ok((String::new(), 0)),
352        CellValue::RichString(runs) => flatten_rich_text_with_budget(&runs, ctx),
353        other => Ok((coerce_to_string(&other), 0)),
354    }
355}
356
357fn cell_value_as_wildcard_text<'a>(
358    value: &'a CellValue,
359    ctx: &Evaluator,
360) -> Result<(Cow<'a, str>, u64)> {
361    match value {
362        CellValue::String(text) | CellValue::Error(text) => Ok((Cow::Borrowed(text), 0)),
363        CellValue::Formula {
364            result: Some(result),
365            ..
366        } => cell_value_as_wildcard_text(result, ctx),
367        CellValue::Empty | CellValue::Formula { result: None, .. } => Ok((Cow::Borrowed(""), 0)),
368        CellValue::RichString(runs) => {
369            let (text, prepaid_bytes) = flatten_rich_text_with_budget(runs, ctx)?;
370            Ok((Cow::Owned(text), prepaid_bytes))
371        }
372        other => Ok((Cow::Owned(coerce_to_string(other)), 0)),
373    }
374}
375
376fn flatten_rich_text_with_budget(
377    runs: &[crate::rich_text::RichTextRun],
378    ctx: &Evaluator,
379) -> Result<(String, u64)> {
380    let byte_len = runs
381        .iter()
382        .try_fold(0usize, |total, run| total.checked_add(run.text.len()))
383        .and_then(|len| u64::try_from(len).ok())
384        .unwrap_or(u64::MAX);
385    ctx.charge_match_work(byte_len)?;
386    let capacity = usize::try_from(byte_len)
387        .map_err(|_| Error::Internal("rich criteria length overflow".to_string()))?;
388    let mut text = String::new();
389    text.try_reserve_exact(capacity)
390        .map_err(|_| Error::Internal("rich criteria allocation failed".to_string()))?;
391    for run in runs {
392        text.push_str(&run.text);
393    }
394    Ok((text, byte_len))
395}
396
397#[derive(Clone, Copy)]
398enum WildcardToken {
399    Star,
400    AnyOne,
401    Literal(char),
402}
403
404#[cfg(test)]
405fn wildcard_match(text: &str, pattern: &str, ctx: &Evaluator) -> Result<bool> {
406    wildcard_match_with_prepaid(text, pattern, 0, ctx)
407}
408
409fn wildcard_match_with_prepaid(
410    text: &str,
411    pattern: &str,
412    prepaid_decode_bytes: u64,
413    ctx: &Evaluator,
414) -> Result<bool> {
415    let decode_work = u64::try_from(text.len())
416        .ok()
417        .and_then(|text_bytes| {
418            u64::try_from(pattern.len())
419                .ok()
420                .and_then(|pattern_bytes| text_bytes.checked_add(pattern_bytes))
421        })
422        .unwrap_or(u64::MAX);
423    ctx.charge_match_work(decode_work.saturating_sub(prepaid_decode_bytes))?;
424
425    let text_len = u64::try_from(text.chars().count()).unwrap_or(u64::MAX);
426    let pattern_len = wildcard_pattern_token_count(pattern);
427    let work = text_len
428        .checked_add(1)
429        .and_then(|rows| {
430            pattern_len
431                .checked_add(1)
432                .and_then(|cols| rows.checked_mul(cols))
433        })
434        .unwrap_or(u64::MAX);
435    ctx.charge_match_work(work)?;
436
437    let tokens = tokenize_wildcard_pattern(pattern)?;
438    debug_assert_eq!(u64::try_from(tokens.len()).unwrap_or(u64::MAX), pattern_len);
439    let row_len = tokens
440        .len()
441        .checked_add(1)
442        .ok_or_else(|| Error::Internal("wildcard row length overflow".to_string()))?;
443    let mut row = Vec::new();
444    row.try_reserve_exact(row_len)
445        .map_err(|_| Error::Internal("wildcard row allocation failed".to_string()))?;
446    row.resize(row_len, false);
447    row[0] = true;
448    for index in 1..row_len {
449        row[index] = matches!(tokens[index - 1], WildcardToken::Star) && row[index - 1];
450    }
451
452    for text_char in text.chars() {
453        let mut diagonal = row[0];
454        row[0] = false;
455        for index in 1..row_len {
456            let above = row[index];
457            row[index] = match tokens[index - 1] {
458                WildcardToken::Star => row[index - 1] || above,
459                WildcardToken::AnyOne => diagonal,
460                WildcardToken::Literal(pattern_char) => {
461                    diagonal && chars_equal_case_insensitive(text_char, pattern_char)
462                }
463            };
464            diagonal = above;
465        }
466    }
467
468    Ok(row[row_len - 1])
469}
470
471pub(crate) fn wildcard_search(text: &str, pattern: &str, ctx: &Evaluator) -> Result<Option<usize>> {
472    let decode_work = u64::try_from(text.len())
473        .ok()
474        .and_then(|text_bytes| {
475            u64::try_from(pattern.len())
476                .ok()
477                .and_then(|pattern_bytes| text_bytes.checked_add(pattern_bytes))
478        })
479        .unwrap_or(u64::MAX);
480    ctx.charge_match_work(decode_work)?;
481
482    let text_len = u64::try_from(text.chars().count()).unwrap_or(u64::MAX);
483    let pattern_len = wildcard_pattern_token_count(pattern);
484    let work = text_len
485        .checked_add(1)
486        .and_then(|rows| {
487            pattern_len
488                .checked_add(1)
489                .and_then(|cols| rows.checked_mul(cols))
490        })
491        .unwrap_or(u64::MAX);
492    ctx.charge_match_work(work)?;
493
494    let tokens = tokenize_wildcard_pattern(pattern)?;
495    let row_len = tokens
496        .len()
497        .checked_add(1)
498        .ok_or_else(|| Error::Internal("wildcard search row length overflow".to_string()))?;
499    let mut row = Vec::new();
500    row.try_reserve_exact(row_len)
501        .map_err(|_| Error::Internal("wildcard search allocation failed".to_string()))?;
502    row.resize(row_len, None);
503    let mut next = Vec::new();
504    next.try_reserve_exact(row_len)
505        .map_err(|_| Error::Internal("wildcard search allocation failed".to_string()))?;
506    next.resize(row_len, None);
507
508    for (char_index, text_char) in text.chars().enumerate() {
509        row[0] = Some(char_index);
510        apply_wildcard_search_epsilon(&mut row, &tokens);
511        if let Some(start) = row[row_len - 1] {
512            return Ok(Some(start));
513        }
514
515        next.fill(None);
516        for index in 1..row_len {
517            next[index] = match tokens[index - 1] {
518                WildcardToken::Star => row[index],
519                WildcardToken::AnyOne => row[index - 1],
520                WildcardToken::Literal(pattern_char) => {
521                    row[index - 1].filter(|_| chars_equal_case_insensitive(text_char, pattern_char))
522                }
523            };
524        }
525        std::mem::swap(&mut row, &mut next);
526    }
527
528    row[0] = Some(usize::try_from(text_len).unwrap_or(usize::MAX));
529    apply_wildcard_search_epsilon(&mut row, &tokens);
530    Ok(row[row_len - 1])
531}
532
533fn apply_wildcard_search_epsilon(row: &mut [Option<usize>], tokens: &[WildcardToken]) {
534    for index in 1..row.len() {
535        if matches!(tokens[index - 1], WildcardToken::Star) {
536            row[index] = match (row[index], row[index - 1]) {
537                (Some(current), Some(candidate)) => Some(current.min(candidate)),
538                (current @ Some(_), None) => current,
539                (None, candidate) => candidate,
540            };
541        }
542    }
543}
544
545fn wildcard_pattern_token_count(pattern: &str) -> u64 {
546    let mut count = 0u64;
547    let mut previous_was_star = false;
548    let mut chars = pattern.chars().peekable();
549    while let Some(ch) = chars.next() {
550        let is_star = ch == '*';
551        if ch == '~' && matches!(chars.peek(), Some('*' | '?' | '~')) {
552            chars.next();
553        }
554        if is_star && previous_was_star {
555            continue;
556        }
557        count = count.saturating_add(1);
558        previous_was_star = is_star;
559    }
560    count
561}
562
563fn tokenize_wildcard_pattern(pattern: &str) -> Result<Vec<WildcardToken>> {
564    let mut tokens = Vec::new();
565    tokens
566        .try_reserve_exact(pattern.len())
567        .map_err(|_| Error::Internal("wildcard pattern allocation failed".to_string()))?;
568    let mut chars = pattern.chars().peekable();
569    while let Some(ch) = chars.next() {
570        let token = match ch {
571            '*' => WildcardToken::Star,
572            '?' => WildcardToken::AnyOne,
573            '~' => match chars.peek().copied() {
574                Some(next @ ('*' | '?' | '~')) => {
575                    chars.next();
576                    WildcardToken::Literal(next)
577                }
578                _ => WildcardToken::Literal('~'),
579            },
580            literal => WildcardToken::Literal(literal),
581        };
582        if matches!(token, WildcardToken::Star)
583            && matches!(tokens.last(), Some(WildcardToken::Star))
584        {
585            continue;
586        }
587        tokens.push(token);
588    }
589    Ok(tokens)
590}
591
592fn chars_equal_case_insensitive(left: char, right: char) -> bool {
593    left.to_lowercase().eq(right.to_lowercase())
594}
595
596/// Expand a single argument expression into a flat list of CellValues.
597pub fn collect_criteria_range_values(arg: &Expr, ctx: &mut Evaluator) -> Result<Vec<CellValue>> {
598    match arg {
599        Expr::Range { start, end } => ctx.expand_range(start, end),
600        _ => {
601            let v = ctx.eval_expr(arg)?;
602            Ok(vec![v])
603        }
604    }
605}
606
607/// Return the error value carried by `value`, looking through a cached
608/// formula result.
609pub(crate) fn error_value(value: &CellValue) -> Option<CellValue> {
610    match value {
611        CellValue::Error(_) => Some(value.clone()),
612        CellValue::Formula {
613            result: Some(inner),
614            ..
615        } => error_value(inner),
616        _ => None,
617    }
618}
619
620/// Evaluate a text argument.
621///
622/// Returns `Ok(Err(error))` when the argument is an error value so the caller
623/// can propagate it instead of measuring or slicing the error text.
624pub(crate) fn eval_text_arg(
625    expr: &Expr,
626    ctx: &mut Evaluator,
627) -> Result<std::result::Result<String, CellValue>> {
628    let value = ctx.eval_expr(expr)?;
629    Ok(match error_value(&value) {
630        Some(error) => Err(error),
631        None => Ok(coerce_to_string(&value)),
632    })
633}
634
635// -- Aggregate functions --
636
637fn fn_sum(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
638    check_arg_count("SUM", args, 1, 255)?;
639    let nums = ctx.collect_numbers(args)?;
640    Ok(CellValue::Number(nums.iter().sum()))
641}
642
643fn fn_average(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
644    check_arg_count("AVERAGE", args, 1, 255)?;
645    let nums = ctx.collect_numbers(args)?;
646    if nums.is_empty() {
647        return Ok(CellValue::Error("#DIV/0!".to_string()));
648    }
649    let sum: f64 = nums.iter().sum();
650    Ok(CellValue::Number(sum / nums.len() as f64))
651}
652
653/// COUNT(value1, ...) - counts numeric values.
654///
655/// Reference arguments contribute only numeric cells. Direct arguments also
656/// count booleans, omitted arguments, and text that converts to a finite
657/// number; direct error values are not counted.
658fn fn_count(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
659    check_arg_count("COUNT", args, 1, 255)?;
660    let mut count = 0usize;
661    for arg in args {
662        match arg {
663            Expr::Range { start, end } => {
664                let values = ctx.expand_range(start, end)?;
665                count += values
666                    .iter()
667                    .filter(|v| crate::formula::eval::reference_number(v).is_some())
668                    .count();
669            }
670            Expr::CellRef(_) => {
671                let v = ctx.eval_expr(arg)?;
672                count += usize::from(crate::formula::eval::reference_number(&v).is_some());
673            }
674            _ => {
675                let v = ctx.eval_expr(arg)?;
676                let counted = !matches!(v, CellValue::Error(_))
677                    && crate::formula::eval::coerce_to_number(&v).is_ok_and(f64::is_finite);
678                count += usize::from(counted);
679            }
680        }
681    }
682    Ok(CellValue::Number(count as f64))
683}
684
685fn fn_counta(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
686    check_arg_count("COUNTA", args, 1, 255)?;
687    let values = ctx.flatten_args_to_values(args)?;
688    let count = values
689        .iter()
690        .filter(|v| !matches!(v, CellValue::Empty))
691        .count();
692    Ok(CellValue::Number(count as f64))
693}
694
695fn fn_min(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
696    check_arg_count("MIN", args, 1, 255)?;
697    let nums = ctx.collect_numbers(args)?;
698    if nums.is_empty() {
699        return Ok(CellValue::Number(0.0));
700    }
701    let min = nums.iter().copied().fold(f64::INFINITY, f64::min);
702    Ok(CellValue::Number(min))
703}
704
705fn fn_max(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
706    check_arg_count("MAX", args, 1, 255)?;
707    let nums = ctx.collect_numbers(args)?;
708    if nums.is_empty() {
709        return Ok(CellValue::Number(0.0));
710    }
711    let max = nums.iter().copied().fold(f64::NEG_INFINITY, f64::max);
712    Ok(CellValue::Number(max))
713}
714
715// -- Logical functions --
716
717fn fn_if(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
718    check_arg_count("IF", args, 1, 3)?;
719    let cond = ctx.eval_expr(&args[0])?;
720    let truth = crate::formula::eval::coerce_to_bool(&cond)?;
721    if truth {
722        if args.len() > 1 {
723            ctx.eval_expr(&args[1])
724        } else {
725            Ok(CellValue::Bool(true))
726        }
727    } else if args.len() > 2 {
728        ctx.eval_expr(&args[2])
729    } else {
730        Ok(CellValue::Bool(false))
731    }
732}
733
734fn fn_and(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
735    check_arg_count("AND", args, 1, 255)?;
736    let values = ctx.flatten_args_to_values(args)?;
737    for v in &values {
738        if matches!(v, CellValue::Empty) {
739            continue;
740        }
741        if !crate::formula::eval::coerce_to_bool(v)? {
742            return Ok(CellValue::Bool(false));
743        }
744    }
745    Ok(CellValue::Bool(true))
746}
747
748fn fn_or(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
749    check_arg_count("OR", args, 1, 255)?;
750    let values = ctx.flatten_args_to_values(args)?;
751    for v in &values {
752        if matches!(v, CellValue::Empty) {
753            continue;
754        }
755        if crate::formula::eval::coerce_to_bool(v)? {
756            return Ok(CellValue::Bool(true));
757        }
758    }
759    Ok(CellValue::Bool(false))
760}
761
762fn fn_not(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
763    check_arg_count("NOT", args, 1, 1)?;
764    let v = ctx.eval_expr(&args[0])?;
765    let b = crate::formula::eval::coerce_to_bool(&v)?;
766    Ok(CellValue::Bool(!b))
767}
768
769// -- Math functions --
770
771fn fn_abs(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
772    check_arg_count("ABS", args, 1, 1)?;
773    let v = ctx.eval_expr(&args[0])?;
774    let n = crate::formula::eval::coerce_to_number(&v)?;
775    Ok(CellValue::Number(n.abs()))
776}
777
778fn fn_int(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
779    check_arg_count("INT", args, 1, 1)?;
780    let v = ctx.eval_expr(&args[0])?;
781    let n = crate::formula::eval::coerce_to_number(&v)?;
782    Ok(CellValue::Number(n.floor()))
783}
784
785fn fn_round(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
786    check_arg_count("ROUND", args, 2, 2)?;
787    let v = ctx.eval_expr(&args[0])?;
788    let d = ctx.eval_expr(&args[1])?;
789    let n = crate::formula::eval::coerce_to_number(&v)?;
790    let digits = crate::formula::eval::coerce_to_number(&d)?;
791    Ok(math::round_to_digits(n, digits, math::RoundMode::Nearest))
792}
793
794fn fn_mod(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
795    check_arg_count("MOD", args, 2, 2)?;
796    let a = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[0])?)?;
797    let b = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
798    if b == 0.0 {
799        return Ok(CellValue::Error("#DIV/0!".to_string()));
800    }
801    // Excel MOD: result has the sign of the divisor.
802    let result = a - (a / b).floor() * b;
803    Ok(CellValue::Number(result))
804}
805
806fn fn_power(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
807    check_arg_count("POWER", args, 2, 2)?;
808    let base = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[0])?)?;
809    let exp = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
810    let result = base.powf(exp);
811    if !result.is_finite() {
812        return Ok(CellValue::Error("#NUM!".to_string()));
813    }
814    Ok(CellValue::Number(result))
815}
816
817fn fn_sqrt(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
818    check_arg_count("SQRT", args, 1, 1)?;
819    let n = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[0])?)?;
820    if n < 0.0 {
821        return Ok(CellValue::Error("#NUM!".to_string()));
822    }
823    Ok(CellValue::Number(n.sqrt()))
824}
825
826// -- Text functions --
827
828fn fn_len(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
829    check_arg_count("LEN", args, 1, 1)?;
830    let s = match eval_text_arg(&args[0], ctx)? {
831        Ok(s) => s,
832        Err(error) => return Ok(error),
833    };
834    Ok(CellValue::Number(s.chars().count() as f64))
835}
836
837fn fn_lower(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
838    check_arg_count("LOWER", args, 1, 1)?;
839    let s = match eval_text_arg(&args[0], ctx)? {
840        Ok(s) => s,
841        Err(error) => return Ok(error),
842    };
843    Ok(CellValue::String(s.to_lowercase()))
844}
845
846fn fn_upper(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
847    check_arg_count("UPPER", args, 1, 1)?;
848    let s = match eval_text_arg(&args[0], ctx)? {
849        Ok(s) => s,
850        Err(error) => return Ok(error),
851    };
852    Ok(CellValue::String(s.to_uppercase()))
853}
854
855fn fn_trim(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
856    check_arg_count("TRIM", args, 1, 1)?;
857    let s = match eval_text_arg(&args[0], ctx)? {
858        Ok(s) => s,
859        Err(error) => return Ok(error),
860    };
861    // Excel TRIM removes leading/trailing spaces and collapses internal runs.
862    let trimmed: String = s.split_whitespace().collect::<Vec<_>>().join(" ");
863    Ok(CellValue::String(trimmed))
864}
865
866fn fn_left(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
867    check_arg_count("LEFT", args, 1, 2)?;
868    let s = match eval_text_arg(&args[0], ctx)? {
869        Ok(s) => s,
870        Err(error) => return Ok(error),
871    };
872    let n = if args.len() > 1 {
873        let count = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
874        let Some(count) = conversion::checked_trunc_count(count) else {
875            return Ok(CellValue::Error("#VALUE!".to_string()));
876        };
877        count
878    } else {
879        1
880    };
881    let result: String = s.chars().take(n).collect();
882    Ok(CellValue::String(result))
883}
884
885fn fn_right(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
886    check_arg_count("RIGHT", args, 1, 2)?;
887    let s = match eval_text_arg(&args[0], ctx)? {
888        Ok(s) => s,
889        Err(error) => return Ok(error),
890    };
891    let n = if args.len() > 1 {
892        let count = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
893        let Some(count) = conversion::checked_trunc_count(count) else {
894            return Ok(CellValue::Error("#VALUE!".to_string()));
895        };
896        count
897    } else {
898        1
899    };
900    let chars: Vec<char> = s.chars().collect();
901    let start = chars.len().saturating_sub(n);
902    let result: String = chars[start..].iter().collect();
903    Ok(CellValue::String(result))
904}
905
906/// Largest MID start position Excel accepts (2^31).
907const MID_MAX_START: usize = 2_147_483_648;
908/// Largest MID length Excel accepts (2^31 - 1).
909const MID_MAX_COUNT: usize = 2_147_483_647;
910
911/// MID(text, start_num, num_chars) - extracts characters by position.
912///
913/// Both numbers are truncated toward zero. A start outside 1..=2^31 or a
914/// length outside 0..=2^31-1 returns `#VALUE!`, as in Excel.
915fn fn_mid(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
916    check_arg_count("MID", args, 3, 3)?;
917    let s = match eval_text_arg(&args[0], ctx)? {
918        Ok(s) => s,
919        Err(error) => return Ok(error),
920    };
921    let start = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
922    let count = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[2])?)?;
923    let (Some(start), Some(count)) = (
924        conversion::checked_trunc_count(start),
925        conversion::checked_trunc_count(count),
926    ) else {
927        return Ok(CellValue::Error("#VALUE!".to_string()));
928    };
929    if !(1..=MID_MAX_START).contains(&start) || count > MID_MAX_COUNT {
930        return Ok(CellValue::Error("#VALUE!".to_string()));
931    }
932    let result: String = s.chars().skip(start - 1).take(count).collect();
933    Ok(CellValue::String(result))
934}
935
936fn fn_concatenate(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
937    check_arg_count("CONCATENATE", args, 1, 255)?;
938    let mut result = String::new();
939    for arg in args {
940        match eval_text_arg(arg, ctx)? {
941            Ok(s) => result.push_str(&s),
942            Err(error) => return Ok(error),
943        }
944    }
945    Ok(CellValue::String(result))
946}
947
948// -- Information functions --
949
950fn fn_isnumber(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
951    check_arg_count("ISNUMBER", args, 1, 1)?;
952    let v = ctx.eval_expr(&args[0])?;
953    Ok(CellValue::Bool(matches!(
954        v,
955        CellValue::Number(_) | CellValue::Date(_)
956    )))
957}
958
959fn fn_istext(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
960    check_arg_count("ISTEXT", args, 1, 1)?;
961    let v = ctx.eval_expr(&args[0])?;
962    Ok(CellValue::Bool(matches!(v, CellValue::String(_))))
963}
964
965fn fn_isblank(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
966    check_arg_count("ISBLANK", args, 1, 1)?;
967    let v = ctx.eval_expr(&args[0])?;
968    Ok(CellValue::Bool(matches!(v, CellValue::Empty)))
969}
970
971fn fn_iserror(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
972    check_arg_count("ISERROR", args, 1, 1)?;
973    let v = ctx.eval_expr(&args[0])?;
974    Ok(CellValue::Bool(matches!(v, CellValue::Error(_))))
975}
976
977// -- Conversion functions --
978
979fn fn_value(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
980    check_arg_count("VALUE", args, 1, 1)?;
981    let v = ctx.eval_expr(&args[0])?;
982    if let Some(error) = error_value(&v) {
983        return Ok(error);
984    }
985    match crate::formula::eval::coerce_to_number(&v) {
986        Ok(n) => Ok(CellValue::Number(n)),
987        Err(_) => Ok(CellValue::Error("#VALUE!".to_string())),
988    }
989}
990
991fn fn_text(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
992    check_arg_count("TEXT", args, 2, 2)?;
993    let value = ctx.eval_expr(&args[0])?;
994    let format = ctx.eval_expr(&args[1])?;
995    let CellValue::String(format_code) = format else {
996        return Ok(CellValue::Error("#VALUE!".to_string()));
997    };
998    let Ok(number) = crate::formula::eval::coerce_to_number(&value) else {
999        return Ok(CellValue::Error("#VALUE!".to_string()));
1000    };
1001    Ok(CellValue::String(crate::numfmt::format_number(
1002        number,
1003        &format_code,
1004    )))
1005}
1006
1007#[cfg(test)]
1008mod tests {
1009    use super::{engineering, financial, matches_criteria, math, text, wildcard_match};
1010    use crate::cell::CellValue;
1011    use crate::formula::ast::Expr;
1012    use crate::formula::budget::{FormulaBudget, FormulaLimits};
1013    use crate::formula::eval::{evaluate, CellSnapshot, Evaluator};
1014    use crate::formula::parser::parse_formula;
1015    use crate::rich_text::RichTextRun;
1016
1017    fn eval(formula: &str) -> CellValue {
1018        let snapshot = CellSnapshot::new("Sheet1".to_string());
1019        let expr = parse_formula(formula).unwrap();
1020        evaluate(&expr, &snapshot).unwrap()
1021    }
1022
1023    #[test]
1024    fn numeric_criteria_does_not_match_blank() {
1025        let snapshot = CellSnapshot::new("Sheet1".to_string());
1026        let evaluator = Evaluator::new(&snapshot);
1027        assert!(!matches_criteria(&CellValue::Empty, "0", &evaluator).unwrap());
1028        assert!(matches_criteria(&CellValue::Number(0.0), "0", &evaluator).unwrap());
1029        assert!(matches_criteria(&CellValue::Empty, "", &evaluator).unwrap());
1030        assert!(matches_criteria(&CellValue::Empty, "<>word", &evaluator).unwrap());
1031    }
1032
1033    #[test]
1034    fn wildcard_match_preserves_excel_escaping_and_unicode_semantics() {
1035        let snapshot = CellSnapshot::new("Sheet1".to_string());
1036        let evaluator = Evaluator::new(&snapshot);
1037
1038        assert!(wildcard_match("CAFÉ", "caf?", &evaluator).unwrap());
1039        assert!(wildcard_match("report.xlsx", "*.xlsx", &evaluator).unwrap());
1040        assert!(wildcard_match("*", "~*", &evaluator).unwrap());
1041        assert!(wildcard_match("?", "~?", &evaluator).unwrap());
1042        assert!(wildcard_match("~", "~~", &evaluator).unwrap());
1043        assert!(!wildcard_match("ab", "a?c", &evaluator).unwrap());
1044    }
1045
1046    #[test]
1047    fn wildcard_match_work_is_exact_and_cumulative() {
1048        let snapshot = CellSnapshot::new("Sheet1".to_string());
1049        let limits = FormulaLimits {
1050            operation_match_work: 17,
1051            ..FormulaLimits::secure_default()
1052        };
1053        let budget = FormulaBudget::for_calculation(limits).unwrap();
1054        let evaluator = Evaluator::with_budget(&snapshot, budget);
1055        assert!(wildcard_match("abc", "a*", &evaluator).unwrap());
1056
1057        let limits = FormulaLimits {
1058            operation_match_work: 16,
1059            ..FormulaLimits::secure_default()
1060        };
1061        let budget = FormulaBudget::for_calculation(limits).unwrap();
1062        let evaluator = Evaluator::with_budget(&snapshot, budget);
1063        let error = wildcard_match("abc", "a*", &evaluator).unwrap_err();
1064        let metadata = error.resource_metadata().unwrap();
1065        assert_eq!(metadata.dimension_code(), "match_work");
1066        assert_eq!((metadata.limit(), metadata.observed()), (16, 17));
1067
1068        let limits = FormulaLimits {
1069            operation_match_work: 33,
1070            ..FormulaLimits::secure_default()
1071        };
1072        let budget = FormulaBudget::for_calculation(limits).unwrap();
1073        let evaluator = Evaluator::with_budget(&snapshot, budget);
1074        assert!(wildcard_match("abc", "a*", &evaluator).unwrap());
1075        let error = wildcard_match("abc", "a*", &evaluator).unwrap_err();
1076        assert_eq!(error.resource_metadata().unwrap().observed(), 34);
1077    }
1078
1079    #[test]
1080    fn criteria_families_share_one_match_budget() {
1081        let mut snapshot = CellSnapshot::new("Sheet1".to_string());
1082        snapshot.set_cell("Sheet1", 1, 1, CellValue::String("CAFÉ".to_string()));
1083        snapshot.set_cell("Sheet1", 2, 1, CellValue::Number(2.0));
1084        let expression = parse_formula(
1085            "SUM(SUMIF(A1,\"caf?\",B1),SUMIFS(B1,A1,\"caf?\"),AVERAGEIF(A1,\"caf?\",B1),AVERAGEIFS(B1,A1,\"caf?\"),COUNTIFS(A1,\"caf?\"))",
1086        )
1087        .unwrap();
1088
1089        let exact = FormulaLimits {
1090            operation_match_work: 170,
1091            ..FormulaLimits::secure_default()
1092        };
1093        let budget = FormulaBudget::for_calculation(exact).unwrap();
1094        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1095        assert_eq!(
1096            evaluator.eval_expr(&expression).unwrap(),
1097            CellValue::Number(9.0)
1098        );
1099
1100        let over = FormulaLimits {
1101            operation_match_work: 169,
1102            ..FormulaLimits::secure_default()
1103        };
1104        let budget = FormulaBudget::for_calculation(over).unwrap();
1105        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1106        let error = evaluator.eval_expr(&expression).unwrap_err();
1107        let metadata = error.resource_metadata().unwrap();
1108        assert_eq!(metadata.dimension_code(), "match_work");
1109        assert_eq!((metadata.limit(), metadata.observed()), (169, 170));
1110    }
1111
1112    #[test]
1113    fn rich_text_criterion_is_precharged_and_flattened_once() {
1114        let mut snapshot = CellSnapshot::new("Sheet1".to_string());
1115        for row in 1..=3 {
1116            snapshot.set_cell("Sheet1", 1, row, CellValue::String("abc".to_string()));
1117            snapshot.set_cell("Sheet1", 3, row, CellValue::Number(f64::from(row)));
1118        }
1119        snapshot.set_cell(
1120            "Sheet1",
1121            2,
1122            1,
1123            CellValue::RichString(vec![
1124                RichTextRun {
1125                    text: "*".to_string(),
1126                    font: None,
1127                    size: None,
1128                    bold: false,
1129                    italic: false,
1130                    color: None,
1131                },
1132                RichTextRun {
1133                    text: "a*".to_string(),
1134                    font: None,
1135                    size: None,
1136                    bold: false,
1137                    italic: false,
1138                    color: None,
1139                },
1140            ]),
1141        );
1142        let expression = parse_formula("SUMIF(A1:A3,B1,C1:C3)").unwrap();
1143
1144        let exact = FormulaLimits {
1145            operation_match_work: 60,
1146            ..FormulaLimits::secure_default()
1147        };
1148        let budget = FormulaBudget::for_calculation(exact).unwrap();
1149        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1150        assert_eq!(
1151            evaluator.eval_expr(&expression).unwrap(),
1152            CellValue::Number(6.0)
1153        );
1154
1155        let over = FormulaLimits {
1156            operation_match_work: 59,
1157            ..FormulaLimits::secure_default()
1158        };
1159        let budget = FormulaBudget::for_calculation(over).unwrap();
1160        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1161        let error = evaluator.eval_expr(&expression).unwrap_err();
1162        let metadata = error.resource_metadata().unwrap();
1163        assert_eq!(metadata.dimension_code(), "match_work");
1164        assert_eq!((metadata.limit(), metadata.observed()), (59, 60));
1165    }
1166
1167    fn assert_evaluation_work_boundary(
1168        function: super::FunctionFn,
1169        args: &[Expr],
1170        exact_work: u64,
1171    ) {
1172        let snapshot = CellSnapshot::new("Sheet1".to_string());
1173        let limits = FormulaLimits {
1174            operation_evaluation_steps: exact_work,
1175            ..FormulaLimits::secure_default()
1176        };
1177        let budget = FormulaBudget::for_calculation(limits).unwrap();
1178        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1179        function(args, &mut evaluator).unwrap();
1180
1181        let limits = FormulaLimits {
1182            operation_evaluation_steps: exact_work - 1,
1183            ..FormulaLimits::secure_default()
1184        };
1185        let budget = FormulaBudget::for_calculation(limits).unwrap();
1186        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1187        let error = function(args, &mut evaluator).unwrap_err();
1188        let metadata = error.resource_metadata().unwrap();
1189        assert_eq!(metadata.dimension_code(), "operation_count");
1190        assert_eq!(
1191            (metadata.limit(), metadata.observed()),
1192            (exact_work - 1, exact_work)
1193        );
1194    }
1195
1196    #[test]
1197    fn iterative_low_formula_functions_charge_exact_work() {
1198        assert_evaluation_work_boundary(math::fn_fact, &[Expr::Number(5.0)], 5);
1199        assert_evaluation_work_boundary(
1200            financial::fn_db,
1201            &[
1202                Expr::Number(1_000.0),
1203                Expr::Number(100.0),
1204                Expr::Number(4.0),
1205                Expr::Number(4.0),
1206            ],
1207            6,
1208        );
1209        assert_evaluation_work_boundary(
1210            financial::fn_ddb,
1211            &[
1212                Expr::Number(1_000.0),
1213                Expr::Number(100.0),
1214                Expr::Number(4.0),
1215                Expr::Number(3.0),
1216            ],
1217            7,
1218        );
1219        assert_evaluation_work_boundary(
1220            financial::fn_cumipmt,
1221            &[
1222                Expr::Number(0.01),
1223                Expr::Number(3.0),
1224                Expr::Number(1_000.0),
1225                Expr::Number(1.0),
1226                Expr::Number(3.0),
1227                Expr::Number(0.0),
1228            ],
1229            9,
1230        );
1231        for function in [engineering::fn_besselk, engineering::fn_bessely] {
1232            assert_evaluation_work_boundary(function, &[Expr::Number(16.0), Expr::Number(3.0)], 4);
1233        }
1234    }
1235
1236    #[test]
1237    fn iterative_formula_work_cannot_reset_between_calls() {
1238        let snapshot = CellSnapshot::new("Sheet1".to_string());
1239        let limits = FormulaLimits {
1240            operation_evaluation_steps: 9,
1241            ..FormulaLimits::secure_default()
1242        };
1243        let budget = FormulaBudget::for_calculation(limits).unwrap();
1244        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1245        let args = [Expr::Number(5.0)];
1246        math::fn_fact(&args, &mut evaluator).unwrap();
1247        let error = math::fn_fact(&args, &mut evaluator).unwrap_err();
1248        assert_eq!(error.resource_metadata().unwrap().observed(), 10);
1249    }
1250
1251    #[test]
1252    fn iterative_formula_inputs_use_checked_conversions() {
1253        let snapshot = CellSnapshot::new("Sheet1".to_string());
1254        for function in [engineering::fn_besselk, engineering::fn_bessely] {
1255            let mut evaluator = Evaluator::new(&snapshot);
1256            assert_eq!(
1257                function(&[Expr::Number(16.0), Expr::Number(1e100)], &mut evaluator,).unwrap(),
1258                CellValue::Error("#NUM!".to_string())
1259            );
1260        }
1261        let mut evaluator = Evaluator::new(&snapshot);
1262        assert_eq!(
1263            financial::fn_cumipmt(
1264                &[
1265                    Expr::Number(0.01),
1266                    Expr::Number(3.0),
1267                    Expr::Number(1_000.0),
1268                    Expr::Number(1.0),
1269                    Expr::Number(1e100),
1270                    Expr::Number(0.0),
1271                ],
1272                &mut evaluator,
1273            )
1274            .unwrap(),
1275            CellValue::Error("#NUM!".to_string())
1276        );
1277
1278        let mut evaluator = Evaluator::new(&snapshot);
1279        let fact_error = math::fn_fact(&[Expr::Number(1e100)], &mut evaluator).unwrap_err();
1280        assert_eq!(
1281            fact_error.resource_metadata().unwrap().dimension_code(),
1282            "operation_count"
1283        );
1284
1285        for (function, args) in [
1286            (
1287                financial::fn_db as super::FunctionFn,
1288                vec![
1289                    Expr::Number(1_000.0),
1290                    Expr::Number(100.0),
1291                    Expr::Number(f64::from(i32::MAX - 1)),
1292                    Expr::Number(f64::from(i32::MAX - 1)),
1293                ],
1294            ),
1295            (
1296                financial::fn_ddb as super::FunctionFn,
1297                vec![
1298                    Expr::Number(1_000.0),
1299                    Expr::Number(100.0),
1300                    Expr::Number(f64::from(i32::MAX)),
1301                    Expr::Number(f64::from(i32::MAX)),
1302                ],
1303            ),
1304        ] {
1305            let mut evaluator = Evaluator::new(&snapshot);
1306            let error = function(&args, &mut evaluator).unwrap_err();
1307            assert_eq!(
1308                error.resource_metadata().unwrap().dimension_code(),
1309                "operation_count"
1310            );
1311        }
1312    }
1313
1314    #[test]
1315    fn countif_and_search_share_exact_match_work() {
1316        let mut snapshot = CellSnapshot::new("Sheet1".to_string());
1317        snapshot.set_cell("Sheet1", 1, 1, CellValue::String("abc".to_string()));
1318        let countif = parse_formula("COUNTIF(A1,\"a*\")").unwrap();
1319        let limits = FormulaLimits {
1320            operation_match_work: 17,
1321            ..FormulaLimits::secure_default()
1322        };
1323        let budget = FormulaBudget::for_calculation(limits).unwrap();
1324        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1325        assert_eq!(
1326            evaluator.eval_expr(&countif).unwrap(),
1327            CellValue::Number(1.0)
1328        );
1329
1330        let limits = FormulaLimits {
1331            operation_match_work: 16,
1332            ..FormulaLimits::secure_default()
1333        };
1334        let budget = FormulaBudget::for_calculation(limits).unwrap();
1335        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1336        let error = evaluator.eval_expr(&countif).unwrap_err();
1337        assert_eq!(error.resource_metadata().unwrap().observed(), 17);
1338
1339        let args = [
1340            Expr::String("a*c".to_string()),
1341            Expr::String("abc".to_string()),
1342        ];
1343        let limits = FormulaLimits {
1344            operation_match_work: 22,
1345            ..FormulaLimits::secure_default()
1346        };
1347        let budget = FormulaBudget::for_calculation(limits).unwrap();
1348        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1349        assert_eq!(
1350            text::fn_search(&args, &mut evaluator).unwrap(),
1351            CellValue::Number(1.0)
1352        );
1353
1354        let limits = FormulaLimits {
1355            operation_match_work: 21,
1356            ..FormulaLimits::secure_default()
1357        };
1358        let budget = FormulaBudget::for_calculation(limits).unwrap();
1359        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1360        let error = text::fn_search(&args, &mut evaluator).unwrap_err();
1361        assert_eq!(error.resource_metadata().unwrap().observed(), 22);
1362    }
1363
1364    #[test]
1365    fn search_preserves_excel_escaping_unicode_and_cumulative_work() {
1366        assert_eq!(eval(r#"SEARCH("~*","a*b")"#), CellValue::Number(2.0));
1367        assert_eq!(eval(r#"SEARCH("~?","a?b")"#), CellValue::Number(2.0));
1368        assert_eq!(eval(r#"SEARCH("~~","a~b")"#), CellValue::Number(2.0));
1369        assert_eq!(eval(r#"SEARCH("caf?","xxCAFÉ")"#), CellValue::Number(3.0));
1370
1371        let snapshot = CellSnapshot::new("Sheet1".to_string());
1372        let limits = FormulaLimits {
1373            operation_match_work: 43,
1374            ..FormulaLimits::secure_default()
1375        };
1376        let budget = FormulaBudget::for_calculation(limits).unwrap();
1377        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1378        let args = [
1379            Expr::String("a*c".to_string()),
1380            Expr::String("abc".to_string()),
1381        ];
1382        text::fn_search(&args, &mut evaluator).unwrap();
1383        let error = text::fn_search(&args, &mut evaluator).unwrap_err();
1384        assert_eq!(error.resource_metadata().unwrap().observed(), 44);
1385    }
1386
1387    #[test]
1388    fn search_rejects_large_failure_before_state_allocation() {
1389        let snapshot = CellSnapshot::new("Sheet1".to_string());
1390        let args = [
1391            Expr::String(format!("{}z", "*a".repeat(512))),
1392            Expr::String("a".repeat(1_024)),
1393        ];
1394        let mut evaluator = Evaluator::new(&snapshot);
1395        let error = text::fn_search(&args, &mut evaluator).unwrap_err();
1396        let metadata = error.resource_metadata().unwrap();
1397        assert_eq!(metadata.dimension_code(), "match_work");
1398        assert!(metadata.observed() > metadata.limit());
1399    }
1400
1401    #[test]
1402    fn substitute_and_width_outputs_are_precharged_exactly() {
1403        let snapshot = CellSnapshot::new("Sheet1".to_string());
1404        let substitute = Expr::Function {
1405            name: "SUBSTITUTE".to_string(),
1406            args: vec![
1407                Expr::String("aaaa".to_string()),
1408                Expr::String("a".to_string()),
1409                Expr::String("bb".to_string()),
1410            ],
1411        };
1412        let limits = FormulaLimits {
1413            operation_output_bytes: 15,
1414            ..FormulaLimits::secure_default()
1415        };
1416        let budget = FormulaBudget::for_calculation(limits).unwrap();
1417        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1418        assert_eq!(
1419            evaluator.eval_expr(&substitute).unwrap(),
1420            CellValue::String("bbbbbbbb".to_string())
1421        );
1422
1423        let limits = FormulaLimits {
1424            operation_output_bytes: 14,
1425            ..FormulaLimits::secure_default()
1426        };
1427        let budget = FormulaBudget::for_calculation(limits).unwrap();
1428        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1429        let error = evaluator.eval_expr(&substitute).unwrap_err();
1430        assert_eq!(error.resource_metadata().unwrap().observed(), 15);
1431
1432        for (function_name, args, exact) in [
1433            ("DEC2HEX", vec![Expr::Number(1.0), Expr::Number(10.0)], 10),
1434            (
1435                "HEX2BIN",
1436                vec![Expr::String("1".to_string()), Expr::Number(10.0)],
1437                11,
1438            ),
1439            (
1440                "HEX2OCT",
1441                vec![Expr::String("1".to_string()), Expr::Number(10.0)],
1442                11,
1443            ),
1444        ] {
1445            let expression = Expr::Function {
1446                name: function_name.to_string(),
1447                args,
1448            };
1449            let limits = FormulaLimits {
1450                operation_output_bytes: exact,
1451                ..FormulaLimits::secure_default()
1452            };
1453            let budget = FormulaBudget::for_calculation(limits).unwrap();
1454            let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1455            evaluator.eval_expr(&expression).unwrap();
1456
1457            let limits = FormulaLimits {
1458                operation_output_bytes: exact - 1,
1459                ..FormulaLimits::secure_default()
1460            };
1461            let budget = FormulaBudget::for_calculation(limits).unwrap();
1462            let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1463            let error = evaluator.eval_expr(&expression).unwrap_err();
1464            assert_eq!(error.resource_metadata().unwrap().observed(), exact);
1465        }
1466    }
1467
1468    #[test]
1469    fn substitute_rejects_large_amplification_before_allocation() {
1470        let snapshot = CellSnapshot::new("Sheet1".to_string());
1471        let args = [
1472            Expr::String("a".repeat(32_767)),
1473            Expr::String("a".to_string()),
1474            Expr::String("b".repeat(32_767)),
1475        ];
1476        let mut evaluator = Evaluator::new(&snapshot);
1477        let error = text::fn_substitute(&args, &mut evaluator).unwrap_err();
1478        let metadata = error.resource_metadata().unwrap();
1479        assert_eq!(metadata.dimension_code(), "output_bytes");
1480        assert!(metadata.observed() > metadata.limit());
1481    }
1482
1483    #[test]
1484    fn substitute_output_budget_is_cumulative() {
1485        let snapshot = CellSnapshot::new("Sheet1".to_string());
1486        let limits = FormulaLimits {
1487            operation_output_bytes: 29,
1488            ..FormulaLimits::secure_default()
1489        };
1490        let budget = FormulaBudget::for_calculation(limits).unwrap();
1491        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1492        let expression = Expr::Function {
1493            name: "SUBSTITUTE".to_string(),
1494            args: vec![
1495                Expr::String("aaaa".to_string()),
1496                Expr::String("a".to_string()),
1497                Expr::String("bb".to_string()),
1498            ],
1499        };
1500        evaluator.eval_expr(&expression).unwrap();
1501        let error = evaluator.eval_expr(&expression).unwrap_err();
1502        assert_eq!(error.resource_metadata().unwrap().observed(), 30);
1503    }
1504
1505    #[test]
1506    fn cumprinc_charges_checked_period_work_before_iteration() {
1507        let snapshot = CellSnapshot::new("Sheet1".to_string());
1508        let args = [
1509            Expr::Number(0.01),
1510            Expr::Number(3.0),
1511            Expr::Number(1_000.0),
1512            Expr::Number(1.0),
1513            Expr::Number(3.0),
1514            Expr::Number(0.0),
1515        ];
1516        let exact = FormulaLimits {
1517            operation_evaluation_steps: 9,
1518            ..FormulaLimits::secure_default()
1519        };
1520        let budget = FormulaBudget::for_calculation(exact).unwrap();
1521        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1522        assert!(matches!(
1523            financial::fn_cumprinc(&args, &mut evaluator).unwrap(),
1524            CellValue::Number(_)
1525        ));
1526
1527        let over = FormulaLimits {
1528            operation_evaluation_steps: 8,
1529            ..FormulaLimits::secure_default()
1530        };
1531        let budget = FormulaBudget::for_calculation(over).unwrap();
1532        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1533        let error = financial::fn_cumprinc(&args, &mut evaluator).unwrap_err();
1534        let metadata = error.resource_metadata().unwrap();
1535        assert_eq!(metadata.dimension_code(), "operation_count");
1536        assert_eq!((metadata.limit(), metadata.observed()), (8, 9));
1537    }
1538
1539    #[test]
1540    fn cumprinc_rejects_unrepresentable_periods_without_iteration() {
1541        let snapshot = CellSnapshot::new("Sheet1".to_string());
1542        let args = [
1543            Expr::Number(0.01),
1544            Expr::Number(3.0),
1545            Expr::Number(1_000.0),
1546            Expr::Number(1.0),
1547            Expr::Number(1e100),
1548            Expr::Number(0.0),
1549        ];
1550        let limits = FormulaLimits {
1551            operation_evaluation_steps: 6,
1552            ..FormulaLimits::secure_default()
1553        };
1554        let budget = FormulaBudget::for_calculation(limits).unwrap();
1555        let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1556        assert_eq!(
1557            financial::fn_cumprinc(&args, &mut evaluator).unwrap(),
1558            CellValue::Error("#NUM!".to_string())
1559        );
1560    }
1561
1562    #[test]
1563    fn cumprinc_rejects_non_finite_intermediate_results() {
1564        let snapshot = CellSnapshot::new("Sheet1".to_string());
1565        let args = [
1566            Expr::Number(f64::MAX),
1567            Expr::Number(1.0),
1568            Expr::Number(f64::MAX),
1569            Expr::Number(1.0),
1570            Expr::Number(1.0),
1571            Expr::Number(0.0),
1572        ];
1573        let mut evaluator = Evaluator::new(&snapshot);
1574        assert_eq!(
1575            financial::fn_cumprinc(&args, &mut evaluator).unwrap(),
1576            CellValue::Error("#NUM!".to_string())
1577        );
1578    }
1579
1580    #[test]
1581    fn power_rejects_non_finite_results() {
1582        assert_eq!(eval("POWER(-1,0.5)"), CellValue::Error("#NUM!".to_string()));
1583        assert_eq!(
1584            eval("POWER(1E308,2)"),
1585            CellValue::Error("#NUM!".to_string())
1586        );
1587    }
1588
1589    #[test]
1590    fn len_counts_unicode_scalars() {
1591        assert_eq!(eval("LEN(\"café\")"), CellValue::Number(4.0));
1592        assert_eq!(eval("LEN(\"한글🚀\")"), CellValue::Number(3.0));
1593    }
1594
1595    #[test]
1596    fn text_applies_excel_number_formats() {
1597        assert_eq!(
1598            eval("TEXT(12.345,\"0.00\")"),
1599            CellValue::String("12.35".to_string())
1600        );
1601        assert_eq!(
1602            eval("TEXT(1234.5,\"#,##0.00\")"),
1603            CellValue::String("1,234.50".to_string())
1604        );
1605        assert_eq!(
1606            eval("TEXT(0.256,\"0.0%\")"),
1607            CellValue::String("25.6%".to_string())
1608        );
1609        assert_eq!(
1610            eval("TEXT(45292,\"m/d/yyyy\")"),
1611            CellValue::String("1/1/2024".to_string())
1612        );
1613        assert_eq!(
1614            eval("TEXT(0.625,\"h:mm AM/PM\")"),
1615            CellValue::String("3:00 PM".to_string())
1616        );
1617    }
1618
1619    #[test]
1620    fn text_requires_numeric_value_and_string_format() {
1621        assert_eq!(eval("TEXT(12,0)"), CellValue::Error("#VALUE!".to_string()));
1622        assert_eq!(
1623            eval("TEXT(\"word\",\"0.00\")"),
1624            CellValue::Error("#VALUE!".to_string())
1625        );
1626    }
1627}