Skip to main content

sheetkit_core/formula/functions/
information.rs

1//! Information formula functions: ISERR, ISNA, ISLOGICAL, ISEVEN, ISODD,
2//! TYPE, N, NA, ERROR.TYPE.
3
4use crate::cell::CellValue;
5use crate::error::Result;
6use crate::formula::ast::Expr;
7use crate::formula::eval::{coerce_to_number, Evaluator};
8use crate::formula::functions::check_arg_count;
9
10/// ISERR(value) - TRUE if value is an error other than #N/A
11pub fn fn_iserr(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
12    check_arg_count("ISERR", args, 1, 1)?;
13    let v = ctx.eval_expr(&args[0])?;
14    let result = match &v {
15        CellValue::Error(e) => e != "#N/A",
16        _ => false,
17    };
18    Ok(CellValue::Bool(result))
19}
20
21/// ISNA(value) - TRUE if value is #N/A
22pub fn fn_isna(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
23    check_arg_count("ISNA", args, 1, 1)?;
24    let v = ctx.eval_expr(&args[0])?;
25    let result = matches!(&v, CellValue::Error(e) if e == "#N/A");
26    Ok(CellValue::Bool(result))
27}
28
29/// ISLOGICAL(value) - TRUE if value is a boolean
30pub fn fn_islogical(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
31    check_arg_count("ISLOGICAL", args, 1, 1)?;
32    let v = ctx.eval_expr(&args[0])?;
33    Ok(CellValue::Bool(matches!(v, CellValue::Bool(_))))
34}
35
36/// ISEVEN(number) - TRUE if the integer part of number is even
37pub fn fn_iseven(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
38    check_arg_count("ISEVEN", args, 1, 1)?;
39    let v = ctx.eval_expr(&args[0])?;
40    let n = coerce_to_number(&v)?;
41    if !n.is_finite() {
42        return Ok(CellValue::Error("#NUM!".to_string()));
43    }
44    // Exact for every finite f64; magnitudes of 2^53 and above are even.
45    Ok(CellValue::Bool(n.trunc() % 2.0 == 0.0))
46}
47
48/// ISODD(number) - TRUE if the integer part of number is odd
49pub fn fn_isodd(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
50    check_arg_count("ISODD", args, 1, 1)?;
51    let v = ctx.eval_expr(&args[0])?;
52    let n = coerce_to_number(&v)?;
53    if !n.is_finite() {
54        return Ok(CellValue::Error("#NUM!".to_string()));
55    }
56    Ok(CellValue::Bool(n.trunc() % 2.0 != 0.0))
57}
58
59/// TYPE(value) - returns type code: 1=number, 2=text, 4=boolean, 16=error, 64=array
60pub fn fn_type(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
61    check_arg_count("TYPE", args, 1, 1)?;
62    let v = ctx.eval_expr(&args[0])?;
63    let code = match v {
64        CellValue::Number(_) | CellValue::Date(_) | CellValue::Empty => 1.0,
65        CellValue::String(_) | CellValue::RichString(_) => 2.0,
66        CellValue::Bool(_) => 4.0,
67        CellValue::Error(_) => 16.0,
68        CellValue::Formula { .. } => 1.0,
69    };
70    Ok(CellValue::Number(code))
71}
72
73/// N(value) - convert to number: numbers stay, bools become 0/1, errors stay, everything else 0
74pub fn fn_n(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
75    check_arg_count("N", args, 1, 1)?;
76    let v = ctx.eval_expr(&args[0])?;
77    match v {
78        CellValue::Number(n) => Ok(CellValue::Number(n)),
79        CellValue::Date(n) => Ok(CellValue::Number(n)),
80        CellValue::Bool(b) => Ok(CellValue::Number(if b { 1.0 } else { 0.0 })),
81        CellValue::Error(_) => Ok(v),
82        _ => Ok(CellValue::Number(0.0)),
83    }
84}
85
86/// NA() - returns #N/A error
87pub fn fn_na(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
88    check_arg_count("NA", args, 0, 0)?;
89    Ok(CellValue::Error("#N/A".to_string()))
90}
91
92/// ERROR.TYPE(value) - returns error type code.
93/// 1=#NULL!, 2=#DIV/0!, 3=#VALUE!, 4=#REF!, 5=#NAME?, 6=#NUM!, 7=#N/A.
94/// Returns #N/A if value is not an error.
95pub fn fn_error_type(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
96    check_arg_count("ERROR.TYPE", args, 1, 1)?;
97    let v = ctx.eval_expr(&args[0])?;
98    match v {
99        CellValue::Error(ref e) => {
100            let code = match e.as_str() {
101                "#NULL!" => 1.0,
102                "#DIV/0!" => 2.0,
103                "#VALUE!" => 3.0,
104                "#REF!" => 4.0,
105                "#NAME?" => 5.0,
106                "#NUM!" => 6.0,
107                "#N/A" => 7.0,
108                _ => return Ok(CellValue::Error("#N/A".to_string())),
109            };
110            Ok(CellValue::Number(code))
111        }
112        _ => Ok(CellValue::Error("#N/A".to_string())),
113    }
114}
115
116#[cfg(test)]
117mod tests {
118    use crate::cell::CellValue;
119    use crate::formula::eval::{evaluate, CellSnapshot};
120    use crate::formula::parser::parse_formula;
121
122    fn eval_with_data(formula: &str, data: &[(&str, u32, u32, CellValue)]) -> CellValue {
123        let mut snap = CellSnapshot::new("Sheet1".to_string());
124        for (sheet, col, row, val) in data {
125            snap.set_cell(sheet, *col, *row, val.clone());
126        }
127        let expr = parse_formula(formula).unwrap();
128        evaluate(&expr, &snap).unwrap()
129    }
130
131    fn eval(formula: &str) -> CellValue {
132        eval_with_data(formula, &[])
133    }
134
135    // ISERR tests
136
137    #[test]
138    fn iserr_div_zero() {
139        let data = vec![("Sheet1", 1, 1, CellValue::Error("#DIV/0!".to_string()))];
140        assert_eq!(eval_with_data("ISERR(A1)", &data), CellValue::Bool(true));
141    }
142
143    #[test]
144    fn iserr_na_is_false() {
145        let data = vec![("Sheet1", 1, 1, CellValue::Error("#N/A".to_string()))];
146        assert_eq!(eval_with_data("ISERR(A1)", &data), CellValue::Bool(false));
147    }
148
149    #[test]
150    fn iserr_not_error() {
151        assert_eq!(eval("ISERR(42)"), CellValue::Bool(false));
152    }
153
154    // ISNA tests
155
156    #[test]
157    fn isna_true() {
158        assert_eq!(eval("ISNA(NA())"), CellValue::Bool(true));
159    }
160
161    #[test]
162    fn isna_false_for_other_error() {
163        let data = vec![("Sheet1", 1, 1, CellValue::Error("#DIV/0!".to_string()))];
164        assert_eq!(eval_with_data("ISNA(A1)", &data), CellValue::Bool(false));
165    }
166
167    #[test]
168    fn isna_false_for_number() {
169        assert_eq!(eval("ISNA(1)"), CellValue::Bool(false));
170    }
171
172    // ISLOGICAL tests
173
174    #[test]
175    fn islogical_true() {
176        assert_eq!(eval("ISLOGICAL(TRUE)"), CellValue::Bool(true));
177    }
178
179    #[test]
180    fn islogical_false_for_number() {
181        assert_eq!(eval("ISLOGICAL(1)"), CellValue::Bool(false));
182    }
183
184    // ISEVEN tests
185
186    #[test]
187    fn iseven_true() {
188        assert_eq!(eval("ISEVEN(4)"), CellValue::Bool(true));
189    }
190
191    #[test]
192    fn iseven_false() {
193        assert_eq!(eval("ISEVEN(3)"), CellValue::Bool(false));
194    }
195
196    #[test]
197    fn iseven_zero() {
198        assert_eq!(eval("ISEVEN(0)"), CellValue::Bool(true));
199    }
200
201    // ISODD tests
202
203    #[test]
204    fn isodd_true() {
205        assert_eq!(eval("ISODD(3)"), CellValue::Bool(true));
206    }
207
208    #[test]
209    fn isodd_false() {
210        assert_eq!(eval("ISODD(4)"), CellValue::Bool(false));
211    }
212
213    // TYPE tests
214
215    #[test]
216    fn type_number() {
217        assert_eq!(eval("TYPE(42)"), CellValue::Number(1.0));
218    }
219
220    #[test]
221    fn type_text() {
222        assert_eq!(eval("TYPE(\"hello\")"), CellValue::Number(2.0));
223    }
224
225    #[test]
226    fn type_boolean() {
227        assert_eq!(eval("TYPE(TRUE)"), CellValue::Number(4.0));
228    }
229
230    #[test]
231    fn type_error() {
232        assert_eq!(eval("TYPE(NA())"), CellValue::Number(16.0));
233    }
234
235    // N tests
236
237    #[test]
238    fn n_number() {
239        assert_eq!(eval("N(42)"), CellValue::Number(42.0));
240    }
241
242    #[test]
243    fn n_true() {
244        assert_eq!(eval("N(TRUE)"), CellValue::Number(1.0));
245    }
246
247    #[test]
248    fn n_false() {
249        assert_eq!(eval("N(FALSE)"), CellValue::Number(0.0));
250    }
251
252    #[test]
253    fn n_string() {
254        assert_eq!(eval("N(\"text\")"), CellValue::Number(0.0));
255    }
256
257    #[test]
258    fn n_error() {
259        assert_eq!(eval("N(NA())"), CellValue::Error("#N/A".to_string()));
260    }
261
262    // NA test
263
264    #[test]
265    fn na_returns_error() {
266        assert_eq!(eval("NA()"), CellValue::Error("#N/A".to_string()));
267    }
268
269    // ERROR.TYPE tests
270
271    #[test]
272    fn error_type_div_zero() {
273        let data = vec![("Sheet1", 1, 1, CellValue::Error("#DIV/0!".to_string()))];
274        assert_eq!(
275            eval_with_data("ERROR.TYPE(A1)", &data),
276            CellValue::Number(2.0)
277        );
278    }
279
280    #[test]
281    fn error_type_na() {
282        assert_eq!(eval("ERROR.TYPE(NA())"), CellValue::Number(7.0));
283    }
284
285    #[test]
286    fn error_type_value_error() {
287        let data = vec![("Sheet1", 1, 1, CellValue::Error("#VALUE!".to_string()))];
288        assert_eq!(
289            eval_with_data("ERROR.TYPE(A1)", &data),
290            CellValue::Number(3.0)
291        );
292    }
293
294    #[test]
295    fn error_type_not_error() {
296        assert_eq!(eval("ERROR.TYPE(42)"), CellValue::Error("#N/A".to_string()));
297    }
298}