1use crate::cell::CellValue;
5use crate::error::Result;
6use crate::formula::ast::Expr;
7use crate::formula::eval::{coerce_to_number, reference_number, Evaluator};
8use crate::formula::functions::conversion::{checked_trunc_count, checked_trunc_i64};
9use crate::formula::functions::{
10 check_arg_count, collect_criteria_range_values, matches_prepared_criteria, prepare_criteria,
11};
12
13pub fn fn_averageif(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
15 check_arg_count("AVERAGEIF", args, 2, 3)?;
16 let range_vals = collect_criteria_range_values(&args[0], ctx)?;
17 let criteria_val = ctx.eval_expr(&args[1])?;
18 let criteria = prepare_criteria(criteria_val, ctx)?;
19 let avg_vals = if args.len() == 3 {
20 collect_criteria_range_values(&args[2], ctx)?
21 } else {
22 range_vals.clone()
23 };
24 let mut sum = 0.0;
25 let mut count = 0u64;
26 for (i, rv) in range_vals.iter().enumerate() {
27 if matches_prepared_criteria(rv, &criteria, ctx)? {
28 if let Some(n) = avg_vals.get(i).and_then(reference_number) {
29 sum += n;
30 count += 1;
31 }
32 }
33 }
34 if count == 0 {
35 return Ok(CellValue::Error("#DIV/0!".to_string()));
36 }
37 Ok(CellValue::Number(sum / count as f64))
38}
39
40pub fn fn_averageifs(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
42 check_arg_count("AVERAGEIFS", args, 3, 255)?;
43 if !(args.len() - 1).is_multiple_of(2) {
44 return Ok(CellValue::Error("#VALUE!".to_string()));
45 }
46 let avg_vals = collect_criteria_range_values(&args[0], ctx)?;
47 let pair_count = (args.len() - 1) / 2;
48 let mut criteria_ranges: Vec<Vec<CellValue>> = Vec::with_capacity(pair_count);
49 let mut criteria_values = Vec::with_capacity(pair_count);
50 for i in 0..pair_count {
51 let range_vals = collect_criteria_range_values(&args[1 + i * 2], ctx)?;
52 let crit_val = ctx.eval_expr(&args[2 + i * 2])?;
53 criteria_ranges.push(range_vals);
54 criteria_values.push(prepare_criteria(crit_val, ctx)?);
55 }
56 let mut sum = 0.0;
57 let mut count = 0u64;
58 for (idx, sv) in avg_vals.iter().enumerate() {
59 let mut all_match = true;
60 for (range_vals, criteria) in criteria_ranges.iter().zip(criteria_values.iter()) {
61 let Some(value) = range_vals.get(idx) else {
62 all_match = false;
63 break;
64 };
65 if !matches_prepared_criteria(value, criteria, ctx)? {
66 all_match = false;
67 break;
68 }
69 }
70 if all_match {
71 if let Some(n) = reference_number(sv) {
72 sum += n;
73 count += 1;
74 }
75 }
76 }
77 if count == 0 {
78 return Ok(CellValue::Error("#DIV/0!".to_string()));
79 }
80 Ok(CellValue::Number(sum / count as f64))
81}
82
83pub fn fn_countblank(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
85 check_arg_count("COUNTBLANK", args, 1, 1)?;
86 let values = collect_criteria_range_values(&args[0], ctx)?;
87 let count = values
88 .iter()
89 .filter(|v| matches!(v, CellValue::Empty))
90 .count();
91 Ok(CellValue::Number(count as f64))
92}
93
94pub fn fn_countif(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
96 check_arg_count("COUNTIF", args, 2, 2)?;
97 let range_vals = collect_criteria_range_values(&args[0], ctx)?;
98 let criteria_val = ctx.eval_expr(&args[1])?;
99 let criteria = prepare_criteria(criteria_val, ctx)?;
100 let mut count = 0usize;
101 for value in &range_vals {
102 if matches_prepared_criteria(value, &criteria, ctx)? {
103 count += 1;
104 }
105 }
106 Ok(CellValue::Number(count as f64))
107}
108
109pub fn fn_countifs(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
111 check_arg_count("COUNTIFS", args, 2, 255)?;
112 if !args.len().is_multiple_of(2) {
113 return Ok(CellValue::Error("#VALUE!".to_string()));
114 }
115 let pair_count = args.len() / 2;
116 let mut criteria_ranges: Vec<Vec<CellValue>> = Vec::with_capacity(pair_count);
117 let mut criteria_values = Vec::with_capacity(pair_count);
118 for i in 0..pair_count {
119 let range_vals = collect_criteria_range_values(&args[i * 2], ctx)?;
120 let crit_val = ctx.eval_expr(&args[i * 2 + 1])?;
121 criteria_ranges.push(range_vals);
122 criteria_values.push(prepare_criteria(crit_val, ctx)?);
123 }
124 let len = criteria_ranges.first().map_or(0, |r| r.len());
125 let mut count = 0u64;
126 for idx in 0..len {
127 let mut all_match = true;
128 for (range_vals, criteria) in criteria_ranges.iter().zip(criteria_values.iter()) {
129 let Some(value) = range_vals.get(idx) else {
130 all_match = false;
131 break;
132 };
133 if !matches_prepared_criteria(value, criteria, ctx)? {
134 all_match = false;
135 break;
136 }
137 }
138 if all_match {
139 count += 1;
140 }
141 }
142 Ok(CellValue::Number(count as f64))
143}
144
145pub fn fn_median(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
147 check_arg_count("MEDIAN", args, 1, 255)?;
148 let mut nums = ctx.collect_numbers(args)?;
149 if nums.is_empty() {
150 return Ok(CellValue::Error("#NUM!".to_string()));
151 }
152 nums.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
153 let n = nums.len();
154 let median = if n % 2 == 1 {
155 nums[n / 2]
156 } else {
157 (nums[n / 2 - 1] + nums[n / 2]) / 2.0
158 };
159 Ok(CellValue::Number(median))
160}
161
162pub fn fn_mode(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
164 check_arg_count("MODE", args, 1, 255)?;
165 let nums = ctx.collect_numbers(args)?;
166 if nums.is_empty() {
167 return Ok(CellValue::Error("#N/A".to_string()));
168 }
169 let mut counts: std::collections::HashMap<u64, (f64, usize)> = std::collections::HashMap::new();
170 for &n in &nums {
171 let key = n.to_bits();
172 let entry = counts.entry(key).or_insert((n, 0));
173 entry.1 += 1;
174 }
175 let max_count = counts.values().map(|(_, c)| *c).max().unwrap_or(0);
176 if max_count <= 1 {
177 return Ok(CellValue::Error("#N/A".to_string()));
178 }
179 for &n in &nums {
181 let key = n.to_bits();
182 if let Some((_, c)) = counts.get(&key) {
183 if *c == max_count {
184 return Ok(CellValue::Number(n));
185 }
186 }
187 }
188 Ok(CellValue::Error("#N/A".to_string()))
189}
190
191pub fn fn_large(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
193 check_arg_count("LARGE", args, 2, 2)?;
194 let mut nums = collect_criteria_range_values(&args[0], ctx)?
195 .iter()
196 .filter_map(reference_number)
197 .collect::<Vec<f64>>();
198 let k = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[1])?)?).unwrap_or(0);
199 if k == 0 || k > nums.len() {
200 return Ok(CellValue::Error("#NUM!".to_string()));
201 }
202 nums.sort_by(|a, b| b.partial_cmp(a).unwrap_or(std::cmp::Ordering::Equal));
203 Ok(CellValue::Number(nums[k - 1]))
204}
205
206pub fn fn_small(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
208 check_arg_count("SMALL", args, 2, 2)?;
209 let mut nums = collect_criteria_range_values(&args[0], ctx)?
210 .iter()
211 .filter_map(reference_number)
212 .collect::<Vec<f64>>();
213 let k = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[1])?)?).unwrap_or(0);
214 if k == 0 || k > nums.len() {
215 return Ok(CellValue::Error("#NUM!".to_string()));
216 }
217 nums.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
218 Ok(CellValue::Number(nums[k - 1]))
219}
220
221pub fn fn_rank(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
224 check_arg_count("RANK", args, 2, 3)?;
225 let number = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
226 let ref_vals = collect_criteria_range_values(&args[1], ctx)?;
227 let order = if args.len() > 2 {
228 let value = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
229 let Some(order) = checked_trunc_i64(value, i64::MIN, i64::MAX) else {
230 return Ok(CellValue::Error("#NUM!".to_string()));
231 };
232 order
233 } else {
234 0
235 };
236 let nums: Vec<f64> = ref_vals.iter().filter_map(reference_number).collect();
237 let rank = if order == 0 {
238 nums.iter().filter(|&&n| n > number).count() + 1
240 } else {
241 nums.iter().filter(|&&n| n < number).count() + 1
243 };
244 if !nums.iter().any(|&n| (n - number).abs() < f64::EPSILON) {
246 return Ok(CellValue::Error("#N/A".to_string()));
247 }
248 Ok(CellValue::Number(rank as f64))
249}
250
251#[cfg(test)]
252mod tests {
253 use crate::cell::CellValue;
254 use crate::formula::eval::{evaluate, CellSnapshot};
255 use crate::formula::parser::parse_formula;
256
257 fn eval_with_data(formula: &str, data: &[(&str, u32, u32, CellValue)]) -> CellValue {
258 let mut snap = CellSnapshot::new("Sheet1".to_string());
259 for (sheet, col, row, val) in data {
260 snap.set_cell(sheet, *col, *row, val.clone());
261 }
262 let expr = parse_formula(formula).unwrap();
263 evaluate(&expr, &snap).unwrap()
264 }
265
266 fn eval(formula: &str) -> CellValue {
267 eval_with_data(formula, &[])
268 }
269
270 #[test]
273 fn averageif_greater_than() {
274 let data = vec![
275 ("Sheet1", 1, 1, CellValue::Number(2.0)),
276 ("Sheet1", 1, 2, CellValue::Number(4.0)),
277 ("Sheet1", 1, 3, CellValue::Number(6.0)),
278 ];
279 let result = eval_with_data("AVERAGEIF(A1:A3,\">3\")", &data);
280 assert_eq!(result, CellValue::Number(5.0));
281 }
282
283 #[test]
284 fn averageif_with_avg_range() {
285 let data = vec![
286 ("Sheet1", 1, 1, CellValue::String("A".to_string())),
287 ("Sheet1", 2, 1, CellValue::Number(10.0)),
288 ("Sheet1", 1, 2, CellValue::String("B".to_string())),
289 ("Sheet1", 2, 2, CellValue::Number(20.0)),
290 ("Sheet1", 1, 3, CellValue::String("A".to_string())),
291 ("Sheet1", 2, 3, CellValue::Number(30.0)),
292 ];
293 let result = eval_with_data("AVERAGEIF(A1:A3,\"A\",B1:B3)", &data);
294 assert_eq!(result, CellValue::Number(20.0));
295 }
296
297 #[test]
298 fn averageif_no_match() {
299 let data = vec![
300 ("Sheet1", 1, 1, CellValue::Number(1.0)),
301 ("Sheet1", 1, 2, CellValue::Number(2.0)),
302 ];
303 let result = eval_with_data("AVERAGEIF(A1:A2,\">100\")", &data);
304 assert_eq!(result, CellValue::Error("#DIV/0!".to_string()));
305 }
306
307 #[test]
310 fn averageifs_multi_criteria() {
311 let data = vec![
312 ("Sheet1", 1, 1, CellValue::String("A".to_string())),
313 ("Sheet1", 2, 1, CellValue::Number(1.0)),
314 ("Sheet1", 3, 1, CellValue::Number(100.0)),
315 ("Sheet1", 1, 2, CellValue::String("A".to_string())),
316 ("Sheet1", 2, 2, CellValue::Number(5.0)),
317 ("Sheet1", 3, 2, CellValue::Number(200.0)),
318 ("Sheet1", 1, 3, CellValue::String("B".to_string())),
319 ("Sheet1", 2, 3, CellValue::Number(3.0)),
320 ("Sheet1", 3, 3, CellValue::Number(300.0)),
321 ];
322 let result = eval_with_data("AVERAGEIFS(C1:C3,A1:A3,\"A\",B1:B3,\">2\")", &data);
323 assert_eq!(result, CellValue::Number(200.0));
324 }
325
326 #[test]
329 fn countblank_basic() {
330 let data = vec![
331 ("Sheet1", 1, 1, CellValue::Number(1.0)),
332 ("Sheet1", 1, 2, CellValue::Empty),
333 ("Sheet1", 1, 3, CellValue::String("x".to_string())),
334 ("Sheet1", 1, 4, CellValue::Empty),
335 ];
336 let result = eval_with_data("COUNTBLANK(A1:A4)", &data);
337 assert_eq!(result, CellValue::Number(2.0));
338 }
339
340 #[test]
343 fn countif_greater_than() {
344 let data = vec![
345 ("Sheet1", 1, 1, CellValue::Number(1.0)),
346 ("Sheet1", 1, 2, CellValue::Number(5.0)),
347 ("Sheet1", 1, 3, CellValue::Number(10.0)),
348 ];
349 let result = eval_with_data("COUNTIF(A1:A3,\">3\")", &data);
350 assert_eq!(result, CellValue::Number(2.0));
351 }
352
353 #[test]
354 fn countif_text_match() {
355 let data = vec![
356 ("Sheet1", 1, 1, CellValue::String("Apple".to_string())),
357 ("Sheet1", 1, 2, CellValue::String("Banana".to_string())),
358 ("Sheet1", 1, 3, CellValue::String("apple".to_string())),
359 ];
360 let result = eval_with_data("COUNTIF(A1:A3,\"Apple\")", &data);
361 assert_eq!(result, CellValue::Number(2.0));
362 }
363
364 #[test]
367 fn countifs_multi_criteria() {
368 let data = vec![
369 ("Sheet1", 1, 1, CellValue::String("A".to_string())),
370 ("Sheet1", 2, 1, CellValue::Number(10.0)),
371 ("Sheet1", 1, 2, CellValue::String("A".to_string())),
372 ("Sheet1", 2, 2, CellValue::Number(20.0)),
373 ("Sheet1", 1, 3, CellValue::String("B".to_string())),
374 ("Sheet1", 2, 3, CellValue::Number(30.0)),
375 ];
376 let result = eval_with_data("COUNTIFS(A1:A3,\"A\",B1:B3,\">15\")", &data);
377 assert_eq!(result, CellValue::Number(1.0));
378 }
379
380 #[test]
383 fn median_odd_count() {
384 assert_eq!(eval("MEDIAN(1,3,2)"), CellValue::Number(2.0));
385 }
386
387 #[test]
388 fn median_even_count() {
389 assert_eq!(eval("MEDIAN(1,2,3,4)"), CellValue::Number(2.5));
390 }
391
392 #[test]
393 fn median_single() {
394 assert_eq!(eval("MEDIAN(42)"), CellValue::Number(42.0));
395 }
396
397 #[test]
400 fn mode_basic() {
401 assert_eq!(eval("MODE(1,2,2,3,3,3)"), CellValue::Number(3.0));
402 }
403
404 #[test]
405 fn mode_no_repeat() {
406 assert_eq!(eval("MODE(1,2,3)"), CellValue::Error("#N/A".to_string()));
407 }
408
409 #[test]
412 fn large_basic() {
413 let data = vec![
414 ("Sheet1", 1, 1, CellValue::Number(5.0)),
415 ("Sheet1", 1, 2, CellValue::Number(3.0)),
416 ("Sheet1", 1, 3, CellValue::Number(8.0)),
417 ("Sheet1", 1, 4, CellValue::Number(1.0)),
418 ];
419 let result = eval_with_data("LARGE(A1:A4,2)", &data);
420 assert_eq!(result, CellValue::Number(5.0));
421 }
422
423 #[test]
424 fn large_k_out_of_range() {
425 let data = vec![
426 ("Sheet1", 1, 1, CellValue::Number(1.0)),
427 ("Sheet1", 1, 2, CellValue::Number(2.0)),
428 ];
429 let result = eval_with_data("LARGE(A1:A2,5)", &data);
430 assert_eq!(result, CellValue::Error("#NUM!".to_string()));
431 }
432
433 #[test]
436 fn small_basic() {
437 let data = vec![
438 ("Sheet1", 1, 1, CellValue::Number(5.0)),
439 ("Sheet1", 1, 2, CellValue::Number(3.0)),
440 ("Sheet1", 1, 3, CellValue::Number(8.0)),
441 ("Sheet1", 1, 4, CellValue::Number(1.0)),
442 ];
443 let result = eval_with_data("SMALL(A1:A4,2)", &data);
444 assert_eq!(result, CellValue::Number(3.0));
445 }
446
447 #[test]
450 fn rank_descending() {
451 let data = vec![
452 ("Sheet1", 1, 1, CellValue::Number(5.0)),
453 ("Sheet1", 1, 2, CellValue::Number(3.0)),
454 ("Sheet1", 1, 3, CellValue::Number(8.0)),
455 ("Sheet1", 1, 4, CellValue::Number(1.0)),
456 ];
457 let result = eval_with_data("RANK(5,A1:A4)", &data);
459 assert_eq!(result, CellValue::Number(2.0));
460 }
461
462 #[test]
463 fn rank_ascending() {
464 let data = vec![
465 ("Sheet1", 1, 1, CellValue::Number(5.0)),
466 ("Sheet1", 1, 2, CellValue::Number(3.0)),
467 ("Sheet1", 1, 3, CellValue::Number(8.0)),
468 ("Sheet1", 1, 4, CellValue::Number(1.0)),
469 ];
470 let result = eval_with_data("RANK(5,A1:A4,1)", &data);
472 assert_eq!(result, CellValue::Number(3.0));
473 }
474
475 #[test]
476 fn rank_not_found() {
477 let data = vec![
478 ("Sheet1", 1, 1, CellValue::Number(1.0)),
479 ("Sheet1", 1, 2, CellValue::Number(2.0)),
480 ];
481 let result = eval_with_data("RANK(99,A1:A2)", &data);
482 assert_eq!(result, CellValue::Error("#N/A".to_string()));
483 }
484}