formualizer_eval/builtins/database.rs
1//! Database functions (D-functions)
2//!
3//! Excel D-functions perform aggregate operations on a database (range with header row)
4//! filtering rows that match specified criteria.
5//!
6//! Implementations:
7//! - DSUM(database, field, criteria) - Sums values in field column matching criteria
8//! - DAVERAGE(database, field, criteria) - Averages values in field column matching criteria
9//! - DCOUNT(database, field, criteria) - Counts numeric cells in field column matching criteria
10//! - DMAX(database, field, criteria) - Maximum value in field column matching criteria
11//! - DMIN(database, field, criteria) - Minimum value in field column matching criteria
12//!
13//! Database structure:
14//! - First row contains column headers (field names)
15//! - Subsequent rows contain data records
16//!
17//! Field argument:
18//! - String matching a column header (case-insensitive)
19//! - Number representing 1-based column index
20//!
21//! Criteria structure:
22//! - First row contains column headers (subset of database headers)
23//! - Subsequent rows contain criteria values (OR relationship between rows)
24//! - Multiple columns in same row have AND relationship
25//! - Supports comparison operators (>, <, >=, <=, <>), wildcards (*, ?)
26
27use super::utils::{ARG_ANY_ONE, coerce_num, criteria_match};
28use crate::args::{ArgSchema, CriteriaPredicate, parse_criteria};
29use crate::function::Function;
30use crate::traits::{ArgumentHandle, CalcValue, FunctionContext};
31use formualizer_common::{ExcelError, LiteralValue};
32use formualizer_macros::func_caps;
33
34/// Aggregation operation type for database functions
35#[derive(Debug, Clone, Copy, PartialEq, Eq)]
36enum DAggregate {
37 Sum,
38 Average,
39 Count,
40 Max,
41 Min,
42 Product,
43}
44
45/// Resolve the field argument to a 0-based column index within the database.
46/// Field can be:
47/// - A string matching a column header (case-insensitive)
48/// - A number representing 1-based column index
49fn resolve_field_index(
50 field: &LiteralValue,
51 headers: &[LiteralValue],
52) -> Result<usize, ExcelError> {
53 match field {
54 LiteralValue::Text(name) => {
55 let name_lower = name.to_ascii_lowercase();
56 for (i, h) in headers.iter().enumerate() {
57 if let LiteralValue::Text(hdr) = h
58 && hdr.to_ascii_lowercase() == name_lower
59 {
60 return Ok(i);
61 }
62 }
63 Err(ExcelError::new_value()
64 .with_message(format!("Field '{}' not found in database headers", name)))
65 }
66 LiteralValue::Number(n) => {
67 let idx = *n as i64;
68 if idx < 1 || idx as usize > headers.len() {
69 return Err(ExcelError::new_value().with_message(format!(
70 "Field index {} out of range (1-{})",
71 idx,
72 headers.len()
73 )));
74 }
75 Ok((idx - 1) as usize)
76 }
77 LiteralValue::Int(i) => {
78 if *i < 1 || *i as usize > headers.len() {
79 return Err(ExcelError::new_value().with_message(format!(
80 "Field index {} out of range (1-{})",
81 i,
82 headers.len()
83 )));
84 }
85 Ok((*i - 1) as usize)
86 }
87 _ => Err(ExcelError::new_value().with_message("Field must be text or number")),
88 }
89}
90
91/// Parse criteria range into a list of criteria rows.
92/// Each row is a vector of (column_index, predicate) pairs.
93/// Multiple rows have OR relationship; columns within a row have AND relationship.
94fn parse_criteria_range(
95 criteria_view: &crate::engine::range_view::RangeView<'_>,
96 db_headers: &[LiteralValue],
97) -> Result<Vec<Vec<(usize, CriteriaPredicate)>>, ExcelError> {
98 let (crit_rows, crit_cols) = criteria_view.dims();
99 if crit_rows < 1 || crit_cols < 1 {
100 return Ok(vec![]);
101 }
102
103 // First row is criteria headers - map to database column indices
104 let mut crit_col_map: Vec<Option<usize>> = Vec::with_capacity(crit_cols);
105 for c in 0..crit_cols {
106 let crit_header = criteria_view.get_cell(0, c);
107 if let LiteralValue::Text(name) = &crit_header {
108 let name_lower = name.to_ascii_lowercase();
109 let mut found = None;
110 for (i, h) in db_headers.iter().enumerate() {
111 if let LiteralValue::Text(hdr) = h
112 && hdr.to_ascii_lowercase() == name_lower
113 {
114 found = Some(i);
115 break;
116 }
117 }
118 crit_col_map.push(found);
119 } else if matches!(crit_header, LiteralValue::Empty) {
120 crit_col_map.push(None);
121 } else {
122 // Non-text, non-empty header - try to match as-is
123 crit_col_map.push(None);
124 }
125 }
126
127 // Parse criteria rows (starting from row 1)
128 let mut criteria_rows = Vec::new();
129 for r in 1..crit_rows {
130 let mut row_criteria = Vec::new();
131 let mut has_any_criteria = false;
132
133 for (c, db_col) in crit_col_map.iter().enumerate() {
134 let crit_val = criteria_view.get_cell(r, c);
135 if matches!(crit_val, LiteralValue::Empty) {
136 continue;
137 }
138
139 if let Some(db_col) = db_col {
140 let pred = parse_criteria(&crit_val)?;
141 row_criteria.push((*db_col, pred));
142 has_any_criteria = true;
143 }
144 }
145
146 if has_any_criteria {
147 criteria_rows.push(row_criteria);
148 }
149 }
150
151 Ok(criteria_rows)
152}
153
154/// Check if a database row matches any of the criteria rows (OR relationship).
155/// Each criteria row is a list of (column_index, predicate) pairs (AND relationship).
156fn row_matches_criteria(
157 db_view: &crate::engine::range_view::RangeView<'_>,
158 row: usize,
159 criteria_rows: &[Vec<(usize, CriteriaPredicate)>],
160) -> bool {
161 // If no criteria, all rows match
162 if criteria_rows.is_empty() {
163 return true;
164 }
165
166 // OR relationship between criteria rows
167 for crit_row in criteria_rows {
168 let mut all_match = true;
169 // AND relationship within a criteria row
170 for (col_idx, pred) in crit_row {
171 let cell_val = db_view.get_cell(row, *col_idx);
172 if !criteria_match(pred, &cell_val) {
173 all_match = false;
174 break;
175 }
176 }
177 if all_match {
178 return true;
179 }
180 }
181
182 false
183}
184
185/// Core evaluation function for all D-functions.
186fn eval_d_function<'a, 'b>(
187 args: &[ArgumentHandle<'a, 'b>],
188 _ctx: &dyn FunctionContext<'b>,
189 agg_type: DAggregate,
190) -> Result<CalcValue<'b>, ExcelError> {
191 if args.len() != 3 {
192 return Ok(CalcValue::Scalar(LiteralValue::Error(
193 ExcelError::new_value().with_message(format!(
194 "D-function expects 3 arguments, got {}",
195 args.len()
196 )),
197 )));
198 }
199
200 // Get database range
201 let db_view = match args[0].range_view() {
202 Ok(v) => v,
203 Err(_) => {
204 // Try to get as array literal
205 let val = args[0].value()?.into_literal();
206 if let LiteralValue::Array(arr) = val {
207 crate::engine::range_view::RangeView::from_owned_rows(
208 arr,
209 crate::engine::DateSystem::Excel1900,
210 )
211 } else {
212 return Ok(CalcValue::Scalar(LiteralValue::Error(
213 ExcelError::new_value().with_message("Database must be a range or array"),
214 )));
215 }
216 }
217 };
218
219 let (db_rows, db_cols) = db_view.dims();
220 if db_rows < 2 || db_cols < 1 {
221 return Ok(CalcValue::Scalar(LiteralValue::Error(
222 ExcelError::new_value()
223 .with_message("Database must have headers and at least one data row"),
224 )));
225 }
226
227 // Get database headers (first row)
228 let headers: Vec<LiteralValue> = (0..db_cols).map(|c| db_view.get_cell(0, c)).collect();
229
230 // Get field argument and resolve to column index
231 let field_val = args[1].value()?.into_literal();
232 let field_idx = resolve_field_index(&field_val, &headers)?;
233
234 // Get criteria range
235 let crit_view = match args[2].range_view() {
236 Ok(v) => v,
237 Err(_) => {
238 let val = args[2].value()?.into_literal();
239 if let LiteralValue::Array(arr) = val {
240 crate::engine::range_view::RangeView::from_owned_rows(
241 arr,
242 crate::engine::DateSystem::Excel1900,
243 )
244 } else {
245 return Ok(CalcValue::Scalar(LiteralValue::Error(
246 ExcelError::new_value().with_message("Criteria must be a range or array"),
247 )));
248 }
249 }
250 };
251
252 // Parse criteria
253 let criteria_rows = parse_criteria_range(&crit_view, &headers)?;
254
255 // Collect matching values from the field column
256 let mut values: Vec<f64> = Vec::new();
257
258 // Iterate over data rows (starting from row 1, skipping header)
259 for row in 1..db_rows {
260 if row_matches_criteria(&db_view, row, &criteria_rows) {
261 let cell_val = db_view.get_cell(row, field_idx);
262
263 // For DCOUNT, only count numeric cells
264 // For other functions, try to coerce to number
265 match &cell_val {
266 LiteralValue::Number(n) => values.push(*n),
267 LiteralValue::Int(i) => values.push(*i as f64),
268 LiteralValue::Boolean(b) => {
269 // Include booleans for DCOUNT only when explicitly numeric context
270 if agg_type != DAggregate::Count {
271 values.push(if *b { 1.0 } else { 0.0 });
272 }
273 }
274 LiteralValue::Empty => {
275 // Empty cells are skipped for all D-functions
276 }
277 LiteralValue::Text(s) => {
278 // Try numeric coercion for text
279 if let Ok(n) = coerce_num(&cell_val) {
280 values.push(n);
281 }
282 // Non-numeric text is skipped
283 }
284 LiteralValue::Error(e) => {
285 // Propagate errors
286 return Ok(CalcValue::Scalar(LiteralValue::Error(e.clone())));
287 }
288 _ => {}
289 }
290 }
291 }
292
293 // Compute aggregate result
294 let result = match agg_type {
295 DAggregate::Sum => {
296 let sum: f64 = values.iter().sum();
297 LiteralValue::Number(sum)
298 }
299 DAggregate::Average => {
300 if values.is_empty() {
301 LiteralValue::Error(ExcelError::new_div())
302 } else {
303 let sum: f64 = values.iter().sum();
304 LiteralValue::Number(sum / values.len() as f64)
305 }
306 }
307 DAggregate::Count => {
308 // DCOUNT counts only numeric cells
309 LiteralValue::Number(values.len() as f64)
310 }
311 DAggregate::Max => {
312 if values.is_empty() {
313 LiteralValue::Number(0.0)
314 } else {
315 let max = values.iter().cloned().fold(f64::NEG_INFINITY, f64::max);
316 LiteralValue::Number(max)
317 }
318 }
319 DAggregate::Min => {
320 if values.is_empty() {
321 LiteralValue::Number(0.0)
322 } else {
323 let min = values.iter().cloned().fold(f64::INFINITY, f64::min);
324 LiteralValue::Number(min)
325 }
326 }
327 DAggregate::Product => {
328 if values.is_empty() {
329 LiteralValue::Number(0.0)
330 } else {
331 let product: f64 = values.iter().product();
332 LiteralValue::Number(product)
333 }
334 }
335 };
336
337 Ok(CalcValue::Scalar(result))
338}
339
340/// Statistical operation type for database variance/stdev functions
341#[derive(Debug, Clone, Copy, PartialEq, Eq)]
342enum DStatOp {
343 VarSample, // DVAR - sample variance (n-1 denominator)
344 VarPop, // DVARP - population variance (n denominator)
345 StdevSample, // DSTDEV - sample standard deviation (n-1 denominator)
346 StdevPop, // DSTDEVP - population standard deviation (n denominator)
347}
348
349/// Core evaluation function for database statistical functions (DVAR, DVARP, DSTDEV, DSTDEVP).
350fn eval_d_stat_function<'a, 'b>(
351 args: &[ArgumentHandle<'a, 'b>],
352 _ctx: &dyn FunctionContext<'b>,
353 stat_op: DStatOp,
354) -> Result<CalcValue<'b>, ExcelError> {
355 if args.len() != 3 {
356 return Ok(CalcValue::Scalar(LiteralValue::Error(
357 ExcelError::new_value().with_message(format!(
358 "D-function expects 3 arguments, got {}",
359 args.len()
360 )),
361 )));
362 }
363
364 // Get database range
365 let db_view = match args[0].range_view() {
366 Ok(v) => v,
367 Err(_) => {
368 let val = args[0].value()?.into_literal();
369 if let LiteralValue::Array(arr) = val {
370 crate::engine::range_view::RangeView::from_owned_rows(
371 arr,
372 crate::engine::DateSystem::Excel1900,
373 )
374 } else {
375 return Ok(CalcValue::Scalar(LiteralValue::Error(
376 ExcelError::new_value().with_message("Database must be a range or array"),
377 )));
378 }
379 }
380 };
381
382 let (db_rows, db_cols) = db_view.dims();
383 if db_rows < 2 || db_cols < 1 {
384 return Ok(CalcValue::Scalar(LiteralValue::Error(
385 ExcelError::new_value()
386 .with_message("Database must have headers and at least one data row"),
387 )));
388 }
389
390 // Get database headers (first row)
391 let headers: Vec<LiteralValue> = (0..db_cols).map(|c| db_view.get_cell(0, c)).collect();
392
393 // Get field argument and resolve to column index
394 let field_val = args[1].value()?.into_literal();
395 let field_idx = resolve_field_index(&field_val, &headers)?;
396
397 // Get criteria range
398 let crit_view = match args[2].range_view() {
399 Ok(v) => v,
400 Err(_) => {
401 let val = args[2].value()?.into_literal();
402 if let LiteralValue::Array(arr) = val {
403 crate::engine::range_view::RangeView::from_owned_rows(
404 arr,
405 crate::engine::DateSystem::Excel1900,
406 )
407 } else {
408 return Ok(CalcValue::Scalar(LiteralValue::Error(
409 ExcelError::new_value().with_message("Criteria must be a range or array"),
410 )));
411 }
412 }
413 };
414
415 // Parse criteria
416 let criteria_rows = parse_criteria_range(&crit_view, &headers)?;
417
418 // Collect matching numeric values from the field column
419 let mut values: Vec<f64> = Vec::new();
420
421 for row in 1..db_rows {
422 if row_matches_criteria(&db_view, row, &criteria_rows) {
423 let cell_val = db_view.get_cell(row, field_idx);
424
425 match &cell_val {
426 LiteralValue::Number(n) => values.push(*n),
427 LiteralValue::Int(i) => values.push(*i as f64),
428 LiteralValue::Boolean(b) => {
429 values.push(if *b { 1.0 } else { 0.0 });
430 }
431 LiteralValue::Text(s) => {
432 if let Ok(n) = coerce_num(&cell_val) {
433 values.push(n);
434 }
435 }
436 LiteralValue::Error(e) => {
437 return Ok(CalcValue::Scalar(LiteralValue::Error(e.clone())));
438 }
439 _ => {}
440 }
441 }
442 }
443
444 // Compute statistical result
445 let result = match stat_op {
446 DStatOp::VarSample | DStatOp::StdevSample => {
447 // Sample variance/stdev requires at least 2 values
448 if values.len() < 2 {
449 LiteralValue::Error(ExcelError::new_div())
450 } else {
451 let n = values.len() as f64;
452 let mean = values.iter().sum::<f64>() / n;
453 let variance = values.iter().map(|x| (x - mean).powi(2)).sum::<f64>() / (n - 1.0);
454 if matches!(stat_op, DStatOp::VarSample) {
455 LiteralValue::Number(variance)
456 } else {
457 LiteralValue::Number(variance.sqrt())
458 }
459 }
460 }
461 DStatOp::VarPop | DStatOp::StdevPop => {
462 // Population variance/stdev requires at least 1 value
463 if values.is_empty() {
464 LiteralValue::Error(ExcelError::new_div())
465 } else {
466 let n = values.len() as f64;
467 let mean = values.iter().sum::<f64>() / n;
468 let variance = values.iter().map(|x| (x - mean).powi(2)).sum::<f64>() / n;
469 if matches!(stat_op, DStatOp::VarPop) {
470 LiteralValue::Number(variance)
471 } else {
472 LiteralValue::Number(variance.sqrt())
473 }
474 }
475 }
476 };
477
478 Ok(CalcValue::Scalar(result))
479}
480
481/// Core evaluation function for DGET - returns single value matching criteria.
482fn eval_dget<'a, 'b>(
483 args: &[ArgumentHandle<'a, 'b>],
484 _ctx: &dyn FunctionContext<'b>,
485) -> Result<CalcValue<'b>, ExcelError> {
486 if args.len() != 3 {
487 return Ok(CalcValue::Scalar(LiteralValue::Error(
488 ExcelError::new_value()
489 .with_message(format!("DGET expects 3 arguments, got {}", args.len())),
490 )));
491 }
492
493 // Get database range
494 let db_view = match args[0].range_view() {
495 Ok(v) => v,
496 Err(_) => {
497 let val = args[0].value()?.into_literal();
498 if let LiteralValue::Array(arr) = val {
499 crate::engine::range_view::RangeView::from_owned_rows(
500 arr,
501 crate::engine::DateSystem::Excel1900,
502 )
503 } else {
504 return Ok(CalcValue::Scalar(LiteralValue::Error(
505 ExcelError::new_value().with_message("Database must be a range or array"),
506 )));
507 }
508 }
509 };
510
511 let (db_rows, db_cols) = db_view.dims();
512 if db_rows < 2 || db_cols < 1 {
513 return Ok(CalcValue::Scalar(LiteralValue::Error(
514 ExcelError::new_value()
515 .with_message("Database must have headers and at least one data row"),
516 )));
517 }
518
519 // Get database headers (first row)
520 let headers: Vec<LiteralValue> = (0..db_cols).map(|c| db_view.get_cell(0, c)).collect();
521
522 // Get field argument and resolve to column index
523 let field_val = args[1].value()?.into_literal();
524 let field_idx = resolve_field_index(&field_val, &headers)?;
525
526 // Get criteria range
527 let crit_view = match args[2].range_view() {
528 Ok(v) => v,
529 Err(_) => {
530 let val = args[2].value()?.into_literal();
531 if let LiteralValue::Array(arr) = val {
532 crate::engine::range_view::RangeView::from_owned_rows(
533 arr,
534 crate::engine::DateSystem::Excel1900,
535 )
536 } else {
537 return Ok(CalcValue::Scalar(LiteralValue::Error(
538 ExcelError::new_value().with_message("Criteria must be a range or array"),
539 )));
540 }
541 }
542 };
543
544 // Parse criteria
545 let criteria_rows = parse_criteria_range(&crit_view, &headers)?;
546
547 // Find matching values
548 let mut matching_values: Vec<LiteralValue> = Vec::new();
549
550 for row in 1..db_rows {
551 if row_matches_criteria(&db_view, row, &criteria_rows) {
552 matching_values.push(db_view.get_cell(row, field_idx));
553 }
554 }
555
556 // DGET returns:
557 // - #VALUE! if no match
558 // - #NUM! if more than one match
559 // - The single value if exactly one match
560 let result = if matching_values.is_empty() {
561 LiteralValue::Error(ExcelError::new_value().with_message("No record matches criteria"))
562 } else if matching_values.len() > 1 {
563 LiteralValue::Error(
564 ExcelError::new_num().with_message("More than one record matches criteria"),
565 )
566 } else {
567 matching_values.into_iter().next().unwrap()
568 };
569
570 Ok(CalcValue::Scalar(result))
571}
572
573/// Core evaluation function for DCOUNTA - counts non-blank cells matching criteria.
574fn eval_dcounta<'a, 'b>(
575 args: &[ArgumentHandle<'a, 'b>],
576 _ctx: &dyn FunctionContext<'b>,
577) -> Result<CalcValue<'b>, ExcelError> {
578 if args.len() != 3 {
579 return Ok(CalcValue::Scalar(LiteralValue::Error(
580 ExcelError::new_value()
581 .with_message(format!("DCOUNTA expects 3 arguments, got {}", args.len())),
582 )));
583 }
584
585 // Get database range
586 let db_view = match args[0].range_view() {
587 Ok(v) => v,
588 Err(_) => {
589 let val = args[0].value()?.into_literal();
590 if let LiteralValue::Array(arr) = val {
591 crate::engine::range_view::RangeView::from_owned_rows(
592 arr,
593 crate::engine::DateSystem::Excel1900,
594 )
595 } else {
596 return Ok(CalcValue::Scalar(LiteralValue::Error(
597 ExcelError::new_value().with_message("Database must be a range or array"),
598 )));
599 }
600 }
601 };
602
603 let (db_rows, db_cols) = db_view.dims();
604 if db_rows < 2 || db_cols < 1 {
605 return Ok(CalcValue::Scalar(LiteralValue::Error(
606 ExcelError::new_value()
607 .with_message("Database must have headers and at least one data row"),
608 )));
609 }
610
611 // Get database headers (first row)
612 let headers: Vec<LiteralValue> = (0..db_cols).map(|c| db_view.get_cell(0, c)).collect();
613
614 // Get field argument and resolve to column index
615 let field_val = args[1].value()?.into_literal();
616 let field_idx = resolve_field_index(&field_val, &headers)?;
617
618 // Get criteria range
619 let crit_view = match args[2].range_view() {
620 Ok(v) => v,
621 Err(_) => {
622 let val = args[2].value()?.into_literal();
623 if let LiteralValue::Array(arr) = val {
624 crate::engine::range_view::RangeView::from_owned_rows(
625 arr,
626 crate::engine::DateSystem::Excel1900,
627 )
628 } else {
629 return Ok(CalcValue::Scalar(LiteralValue::Error(
630 ExcelError::new_value().with_message("Criteria must be a range or array"),
631 )));
632 }
633 }
634 };
635
636 // Parse criteria
637 let criteria_rows = parse_criteria_range(&crit_view, &headers)?;
638
639 // Count non-blank cells in matching rows
640 let mut count = 0;
641
642 for row in 1..db_rows {
643 if row_matches_criteria(&db_view, row, &criteria_rows) {
644 let cell_val = db_view.get_cell(row, field_idx);
645
646 // DCOUNTA counts all non-blank cells (unlike DCOUNT which only counts numbers)
647 match &cell_val {
648 LiteralValue::Empty => {
649 // Empty cells are NOT counted
650 }
651 LiteralValue::Text(s) if s.is_empty() => {
652 // Empty strings are treated as blank and NOT counted
653 }
654 LiteralValue::Error(e) => {
655 // Propagate errors
656 return Ok(CalcValue::Scalar(LiteralValue::Error(e.clone())));
657 }
658 _ => {
659 // All other values (numbers, non-empty text, booleans) are counted
660 count += 1;
661 }
662 }
663 }
664 }
665
666 Ok(CalcValue::Scalar(LiteralValue::Number(count as f64)))
667}
668
669/* ─────────────────────────── DSUM ──────────────────────────── */
670#[derive(Debug)]
671pub struct DSumFn;
672
673/// Sums values in a database field for records that match criteria.
674///
675/// `DSUM` filters database rows using a criteria range, then adds the selected field values.
676///
677/// # Remarks
678/// - Criteria rows are evaluated with OR semantics; populated criteria columns within one row are ANDed.
679/// - `field` resolves by case-insensitive header text or 1-based column index; unknown headers and out-of-range indexes return `#VALUE!`.
680/// - Non-numeric values in the target field are ignored unless they coerce to numbers.
681///
682/// # Examples
683///
684/// ```yaml,sandbox
685/// title: "Sum revenue for East or West regions"
686/// grid:
687/// A1: "Region"
688/// B1: "Salesperson"
689/// C1: "Product"
690/// D1: "Units"
691/// E1: "Revenue"
692/// A2: "West"
693/// B2: "Diaz"
694/// C2: "Widget"
695/// D2: 24
696/// E2: 126000
697/// A3: "East"
698/// B3: "Patel"
699/// C3: "Gadget"
700/// D3: 31
701/// E3: 142500
702/// A4: "North"
703/// B4: "Kim"
704/// C4: "Widget"
705/// D4: 18
706/// E4: 87000
707/// A5: "West"
708/// B5: "Ramos"
709/// C5: "Service"
710/// D5: 12
711/// E5: 46000
712/// A6: "South"
713/// B6: "Lee"
714/// C6: "Gadget"
715/// D6: 27
716/// E6: 119000
717/// A7: "East"
718/// B7: "Noor"
719/// C7: "Widget"
720/// D7: 22
721/// E7: 101000
722/// G1: "Region"
723/// G2: "West"
724/// G3: "East"
725/// formula: "=DSUM(A1:E7, \"Revenue\", G1:G3)"
726/// expected: 415500
727/// ```
728///
729/// ```yaml,sandbox
730/// title: "Sum revenue by field index with numeric criteria"
731/// grid:
732/// A1: "Region"
733/// B1: "Salesperson"
734/// C1: "Product"
735/// D1: "Units"
736/// E1: "Revenue"
737/// A2: "West"
738/// B2: "Diaz"
739/// C2: "Widget"
740/// D2: 24
741/// E2: 126000
742/// A3: "East"
743/// B3: "Patel"
744/// C3: "Gadget"
745/// D3: 31
746/// E3: 142500
747/// A4: "North"
748/// B4: "Kim"
749/// C4: "Widget"
750/// D4: 18
751/// E4: 87000
752/// A5: "West"
753/// B5: "Ramos"
754/// C5: "Service"
755/// D5: 12
756/// E5: 46000
757/// A6: "South"
758/// B6: "Lee"
759/// C6: "Gadget"
760/// D6: 27
761/// E6: 119000
762/// A7: "East"
763/// B7: "Noor"
764/// C7: "Widget"
765/// D7: 22
766/// E7: 101000
767/// G1: "Units"
768/// G2: ">20"
769/// formula: "=DSUM(A1:E7, 5, G1:G2)"
770/// expected: 488500
771/// ```
772///
773/// ```yaml,docs
774/// related:
775/// - DAVERAGE
776/// - DCOUNT
777/// - SUMIFS
778/// faq:
779/// - q: "How are multiple criteria rows interpreted in DSUM?"
780/// a: "Each criteria row is an OR branch, while multiple populated criteria columns in one row are combined with AND."
781/// ```
782///
783/// [formualizer-docgen:schema:start]
784/// Name: DSUM
785/// Type: DSumFn
786/// Min args: 3
787/// Max args: 1
788/// Variadic: false
789/// Signature: DSUM(arg1: any@scalar)
790/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
791/// Caps: PURE, REDUCTION
792/// [formualizer-docgen:schema:end]
793impl Function for DSumFn {
794 func_caps!(PURE, REDUCTION);
795
796 fn name(&self) -> &'static str {
797 "DSUM"
798 }
799
800 fn min_args(&self) -> usize {
801 3
802 }
803
804 fn variadic(&self) -> bool {
805 false
806 }
807
808 fn arg_schema(&self) -> &'static [ArgSchema] {
809 &ARG_ANY_ONE[..]
810 }
811
812 fn eval<'a, 'b, 'c>(
813 &self,
814 args: &'c [ArgumentHandle<'a, 'b>],
815 ctx: &dyn FunctionContext<'b>,
816 ) -> Result<CalcValue<'b>, ExcelError> {
817 eval_d_function(args, ctx, DAggregate::Sum)
818 }
819}
820
821/* ─────────────────────────── DAVERAGE ──────────────────────────── */
822#[derive(Debug)]
823pub struct DAverageFn;
824
825/// Returns the arithmetic mean of values in a database field for matching records.
826///
827/// `DAVERAGE` applies criteria filtering first, then averages the numeric values in `field`.
828///
829/// # Remarks
830/// - Criteria rows are OR conditions, while criteria columns in the same row are AND conditions.
831/// - `field` can be a case-insensitive header name or a 1-based column index; invalid field resolution returns `#VALUE!`.
832/// - If no numeric values match, the function returns `#DIV/0!`.
833///
834/// # Examples
835///
836/// ```yaml,sandbox
837/// title: "Average units for Gadget sales"
838/// grid:
839/// A1: "Region"
840/// B1: "Salesperson"
841/// C1: "Product"
842/// D1: "Units"
843/// E1: "Revenue"
844/// A2: "West"
845/// B2: "Diaz"
846/// C2: "Widget"
847/// D2: 24
848/// E2: 126000
849/// A3: "East"
850/// B3: "Patel"
851/// C3: "Gadget"
852/// D3: 31
853/// E3: 142500
854/// A4: "North"
855/// B4: "Kim"
856/// C4: "Widget"
857/// D4: 18
858/// E4: 87000
859/// A5: "West"
860/// B5: "Ramos"
861/// C5: "Service"
862/// D5: 12
863/// E5: 46000
864/// A6: "South"
865/// B6: "Lee"
866/// C6: "Gadget"
867/// D6: 27
868/// E6: 119000
869/// A7: "East"
870/// B7: "Noor"
871/// C7: "Widget"
872/// D7: 22
873/// E7: 101000
874/// G1: "Product"
875/// G2: "Gadget"
876/// formula: "=DAVERAGE(A1:E7, \"Units\", G1:G2)"
877/// expected: 29
878/// ```
879///
880/// ```yaml,sandbox
881/// title: "Average revenue for West or South regions"
882/// grid:
883/// A1: "Region"
884/// B1: "Salesperson"
885/// C1: "Product"
886/// D1: "Units"
887/// E1: "Revenue"
888/// A2: "West"
889/// B2: "Diaz"
890/// C2: "Widget"
891/// D2: 24
892/// E2: 126000
893/// A3: "East"
894/// B3: "Patel"
895/// C3: "Gadget"
896/// D3: 31
897/// E3: 142500
898/// A4: "North"
899/// B4: "Kim"
900/// C4: "Widget"
901/// D4: 18
902/// E4: 87000
903/// A5: "West"
904/// B5: "Ramos"
905/// C5: "Service"
906/// D5: 12
907/// E5: 46000
908/// A6: "South"
909/// B6: "Lee"
910/// C6: "Gadget"
911/// D6: 27
912/// E6: 119000
913/// A7: "East"
914/// B7: "Noor"
915/// C7: "Widget"
916/// D7: 22
917/// E7: 101000
918/// G1: "Region"
919/// G2: "West"
920/// G3: "South"
921/// formula: "=DAVERAGE(A1:E7, 5, G1:G3)"
922/// expected: 97000
923/// ```
924///
925/// ```yaml,docs
926/// related:
927/// - DSUM
928/// - DCOUNT
929/// - AVERAGEIFS
930/// faq:
931/// - q: "What happens if criteria match rows but field values are non-numeric?"
932/// a: "DAVERAGE skips non-numeric values and returns #DIV/0! if no numeric values remain after filtering."
933/// ```
934///
935/// [formualizer-docgen:schema:start]
936/// Name: DAVERAGE
937/// Type: DAverageFn
938/// Min args: 3
939/// Max args: 1
940/// Variadic: false
941/// Signature: DAVERAGE(arg1: any@scalar)
942/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
943/// Caps: PURE, REDUCTION
944/// [formualizer-docgen:schema:end]
945impl Function for DAverageFn {
946 func_caps!(PURE, REDUCTION);
947
948 fn name(&self) -> &'static str {
949 "DAVERAGE"
950 }
951
952 fn min_args(&self) -> usize {
953 3
954 }
955
956 fn variadic(&self) -> bool {
957 false
958 }
959
960 fn arg_schema(&self) -> &'static [ArgSchema] {
961 &ARG_ANY_ONE[..]
962 }
963
964 fn eval<'a, 'b, 'c>(
965 &self,
966 args: &'c [ArgumentHandle<'a, 'b>],
967 ctx: &dyn FunctionContext<'b>,
968 ) -> Result<CalcValue<'b>, ExcelError> {
969 eval_d_function(args, ctx, DAggregate::Average)
970 }
971}
972
973/* ─────────────────────────── DCOUNT ──────────────────────────── */
974#[derive(Debug)]
975pub struct DCountFn;
976
977/// Counts numeric cells in a database field for records matching criteria.
978///
979/// `DCOUNT` ignores non-numeric values in the selected field even when the row itself matches.
980///
981/// # Remarks
982/// - Criteria rows are ORed, and criteria columns inside a single row are ANDed.
983/// - `field` header lookup is case-insensitive, and numeric `field` uses 1-based indexing; unresolved headers or invalid indexes return `#VALUE!`.
984/// - Only numeric field values contribute to the count.
985///
986/// # Examples
987///
988/// ```yaml,sandbox
989/// title: "Count numeric revenue entries in East region"
990/// grid:
991/// A1: "Region"
992/// B1: "Salesperson"
993/// C1: "Product"
994/// D1: "Units"
995/// E1: "Revenue"
996/// A2: "West"
997/// B2: "Diaz"
998/// C2: "Widget"
999/// D2: 24
1000/// E2: 126000
1001/// A3: "East"
1002/// B3: "Patel"
1003/// C3: "Gadget"
1004/// D3: 31
1005/// E3: 142500
1006/// A4: "North"
1007/// B4: "Kim"
1008/// C4: "Widget"
1009/// D4: 18
1010/// E4: 87000
1011/// A5: "West"
1012/// B5: "Ramos"
1013/// C5: "Service"
1014/// D5: 12
1015/// E5: 46000
1016/// A6: "South"
1017/// B6: "Lee"
1018/// C6: "Gadget"
1019/// D6: 27
1020/// E6: 119000
1021/// A7: "East"
1022/// B7: "Noor"
1023/// C7: "Widget"
1024/// D7: 22
1025/// E7: 101000
1026/// G1: "Region"
1027/// G2: "East"
1028/// formula: "=DCOUNT(A1:E7, \"Revenue\", G1:G2)"
1029/// expected: 2
1030/// ```
1031///
1032/// ```yaml,sandbox
1033/// title: "Count numeric units for Widget or Service products"
1034/// grid:
1035/// A1: "Region"
1036/// B1: "Salesperson"
1037/// C1: "Product"
1038/// D1: "Units"
1039/// E1: "Revenue"
1040/// A2: "West"
1041/// B2: "Diaz"
1042/// C2: "Widget"
1043/// D2: 24
1044/// E2: 126000
1045/// A3: "East"
1046/// B3: "Patel"
1047/// C3: "Gadget"
1048/// D3: 31
1049/// E3: 142500
1050/// A4: "North"
1051/// B4: "Kim"
1052/// C4: "Widget"
1053/// D4: 18
1054/// E4: 87000
1055/// A5: "West"
1056/// B5: "Ramos"
1057/// C5: "Service"
1058/// D5: 12
1059/// E5: 46000
1060/// A6: "South"
1061/// B6: "Lee"
1062/// C6: "Gadget"
1063/// D6: 27
1064/// E6: 119000
1065/// A7: "East"
1066/// B7: "Noor"
1067/// C7: "Widget"
1068/// D7: 22
1069/// E7: 101000
1070/// G1: "Product"
1071/// G2: "Widget"
1072/// G3: "Service"
1073/// formula: "=DCOUNT(A1:E7, 4, G1:G3)"
1074/// expected: 4
1075/// ```
1076///
1077/// ```yaml,docs
1078/// related:
1079/// - DCOUNTA
1080/// - DSUM
1081/// - COUNTIFS
1082/// faq:
1083/// - q: "Does DCOUNT count text values that look like numbers?"
1084/// a: "Only values resolved as numeric in the target field are counted; true non-numeric text is ignored."
1085/// ```
1086///
1087/// [formualizer-docgen:schema:start]
1088/// Name: DCOUNT
1089/// Type: DCountFn
1090/// Min args: 3
1091/// Max args: 1
1092/// Variadic: false
1093/// Signature: DCOUNT(arg1: any@scalar)
1094/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1095/// Caps: PURE, REDUCTION
1096/// [formualizer-docgen:schema:end]
1097impl Function for DCountFn {
1098 func_caps!(PURE, REDUCTION);
1099
1100 fn name(&self) -> &'static str {
1101 "DCOUNT"
1102 }
1103
1104 fn min_args(&self) -> usize {
1105 3
1106 }
1107
1108 fn variadic(&self) -> bool {
1109 false
1110 }
1111
1112 fn arg_schema(&self) -> &'static [ArgSchema] {
1113 &ARG_ANY_ONE[..]
1114 }
1115
1116 fn eval<'a, 'b, 'c>(
1117 &self,
1118 args: &'c [ArgumentHandle<'a, 'b>],
1119 ctx: &dyn FunctionContext<'b>,
1120 ) -> Result<CalcValue<'b>, ExcelError> {
1121 eval_d_function(args, ctx, DAggregate::Count)
1122 }
1123}
1124
1125/* ─────────────────────────── DMAX ──────────────────────────── */
1126#[derive(Debug)]
1127pub struct DMaxFn;
1128
1129/// Returns the largest value in a database field for records matching criteria.
1130///
1131/// `DMAX` scans the filtered records and returns the maximum numeric value found in `field`.
1132///
1133/// # Remarks
1134/// - Criteria rows are OR conditions; multiple non-empty criteria columns in one row are AND conditions.
1135/// - `field` can be a case-insensitive header string or a 1-based column index; failed resolution returns `#VALUE!`.
1136/// - If no numeric values are matched, this implementation returns `0`.
1137///
1138/// # Examples
1139///
1140/// ```yaml,sandbox
1141/// title: "Maximum revenue for West or South"
1142/// grid:
1143/// A1: "Region"
1144/// B1: "Salesperson"
1145/// C1: "Product"
1146/// D1: "Units"
1147/// E1: "Revenue"
1148/// A2: "West"
1149/// B2: "Diaz"
1150/// C2: "Widget"
1151/// D2: 24
1152/// E2: 126000
1153/// A3: "East"
1154/// B3: "Patel"
1155/// C3: "Gadget"
1156/// D3: 31
1157/// E3: 142500
1158/// A4: "North"
1159/// B4: "Kim"
1160/// C4: "Widget"
1161/// D4: 18
1162/// E4: 87000
1163/// A5: "West"
1164/// B5: "Ramos"
1165/// C5: "Service"
1166/// D5: 12
1167/// E5: 46000
1168/// A6: "South"
1169/// B6: "Lee"
1170/// C6: "Gadget"
1171/// D6: 27
1172/// E6: 119000
1173/// A7: "East"
1174/// B7: "Noor"
1175/// C7: "Widget"
1176/// D7: 22
1177/// E7: 101000
1178/// G1: "Region"
1179/// G2: "West"
1180/// G3: "South"
1181/// formula: "=DMAX(A1:E7, \"Revenue\", G1:G3)"
1182/// expected: 126000
1183/// ```
1184///
1185/// ```yaml,sandbox
1186/// title: "Maximum units for Widget deals"
1187/// grid:
1188/// A1: "Region"
1189/// B1: "Salesperson"
1190/// C1: "Product"
1191/// D1: "Units"
1192/// E1: "Revenue"
1193/// A2: "West"
1194/// B2: "Diaz"
1195/// C2: "Widget"
1196/// D2: 24
1197/// E2: 126000
1198/// A3: "East"
1199/// B3: "Patel"
1200/// C3: "Gadget"
1201/// D3: 31
1202/// E3: 142500
1203/// A4: "North"
1204/// B4: "Kim"
1205/// C4: "Widget"
1206/// D4: 18
1207/// E4: 87000
1208/// A5: "West"
1209/// B5: "Ramos"
1210/// C5: "Service"
1211/// D5: 12
1212/// E5: 46000
1213/// A6: "South"
1214/// B6: "Lee"
1215/// C6: "Gadget"
1216/// D6: 27
1217/// E6: 119000
1218/// A7: "East"
1219/// B7: "Noor"
1220/// C7: "Widget"
1221/// D7: 22
1222/// E7: 101000
1223/// G1: "Product"
1224/// G2: "Widget"
1225/// formula: "=DMAX(A1:E7, 4, G1:G2)"
1226/// expected: 24
1227/// ```
1228///
1229/// ```yaml,docs
1230/// related:
1231/// - DMIN
1232/// - DGET
1233/// - MAXIFS
1234/// faq:
1235/// - q: "What does DMAX return when no numeric field values match criteria?"
1236/// a: "This implementation returns 0 when the filtered set has no numeric values."
1237/// ```
1238///
1239/// [formualizer-docgen:schema:start]
1240/// Name: DMAX
1241/// Type: DMaxFn
1242/// Min args: 3
1243/// Max args: 1
1244/// Variadic: false
1245/// Signature: DMAX(arg1: any@scalar)
1246/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1247/// Caps: PURE, REDUCTION
1248/// [formualizer-docgen:schema:end]
1249impl Function for DMaxFn {
1250 func_caps!(PURE, REDUCTION);
1251
1252 fn name(&self) -> &'static str {
1253 "DMAX"
1254 }
1255
1256 fn min_args(&self) -> usize {
1257 3
1258 }
1259
1260 fn variadic(&self) -> bool {
1261 false
1262 }
1263
1264 fn arg_schema(&self) -> &'static [ArgSchema] {
1265 &ARG_ANY_ONE[..]
1266 }
1267
1268 fn eval<'a, 'b, 'c>(
1269 &self,
1270 args: &'c [ArgumentHandle<'a, 'b>],
1271 ctx: &dyn FunctionContext<'b>,
1272 ) -> Result<CalcValue<'b>, ExcelError> {
1273 eval_d_function(args, ctx, DAggregate::Max)
1274 }
1275}
1276
1277/* ─────────────────────────── DMIN ──────────────────────────── */
1278#[derive(Debug)]
1279pub struct DMinFn;
1280
1281/// Returns the smallest value in a database field for records matching criteria.
1282///
1283/// `DMIN` applies criteria filtering and then evaluates the minimum numeric value from `field`.
1284///
1285/// # Remarks
1286/// - Criteria rows are ORed together; criteria columns on the same row are ANDed.
1287/// - `field` resolves from a case-insensitive header label or 1-based index, and invalid resolution yields `#VALUE!`.
1288/// - If no numeric values are matched, this implementation returns `0`.
1289///
1290/// # Examples
1291///
1292/// ```yaml,sandbox
1293/// title: "Minimum revenue for East or West"
1294/// grid:
1295/// A1: "Region"
1296/// B1: "Salesperson"
1297/// C1: "Product"
1298/// D1: "Units"
1299/// E1: "Revenue"
1300/// A2: "West"
1301/// B2: "Diaz"
1302/// C2: "Widget"
1303/// D2: 24
1304/// E2: 126000
1305/// A3: "East"
1306/// B3: "Patel"
1307/// C3: "Gadget"
1308/// D3: 31
1309/// E3: 142500
1310/// A4: "North"
1311/// B4: "Kim"
1312/// C4: "Widget"
1313/// D4: 18
1314/// E4: 87000
1315/// A5: "West"
1316/// B5: "Ramos"
1317/// C5: "Service"
1318/// D5: 12
1319/// E5: 46000
1320/// A6: "South"
1321/// B6: "Lee"
1322/// C6: "Gadget"
1323/// D6: 27
1324/// E6: 119000
1325/// A7: "East"
1326/// B7: "Noor"
1327/// C7: "Widget"
1328/// D7: 22
1329/// E7: 101000
1330/// G1: "Region"
1331/// G2: "East"
1332/// G3: "West"
1333/// formula: "=DMIN(A1:E7, \"Revenue\", G1:G3)"
1334/// expected: 46000
1335/// ```
1336///
1337/// ```yaml,sandbox
1338/// title: "Minimum units where revenue exceeds 100000"
1339/// grid:
1340/// A1: "Region"
1341/// B1: "Salesperson"
1342/// C1: "Product"
1343/// D1: "Units"
1344/// E1: "Revenue"
1345/// A2: "West"
1346/// B2: "Diaz"
1347/// C2: "Widget"
1348/// D2: 24
1349/// E2: 126000
1350/// A3: "East"
1351/// B3: "Patel"
1352/// C3: "Gadget"
1353/// D3: 31
1354/// E3: 142500
1355/// A4: "North"
1356/// B4: "Kim"
1357/// C4: "Widget"
1358/// D4: 18
1359/// E4: 87000
1360/// A5: "West"
1361/// B5: "Ramos"
1362/// C5: "Service"
1363/// D5: 12
1364/// E5: 46000
1365/// A6: "South"
1366/// B6: "Lee"
1367/// C6: "Gadget"
1368/// D6: 27
1369/// E6: 119000
1370/// A7: "East"
1371/// B7: "Noor"
1372/// C7: "Widget"
1373/// D7: 22
1374/// E7: 101000
1375/// G1: "Revenue"
1376/// G2: ">100000"
1377/// formula: "=DMIN(A1:E7, 4, G1:G2)"
1378/// expected: 22
1379/// ```
1380///
1381/// ```yaml,docs
1382/// related:
1383/// - DMAX
1384/// - DGET
1385/// - MINIFS
1386/// faq:
1387/// - q: "How are mixed criteria (text plus numeric operators) handled in DMIN?"
1388/// a: "Criteria are parsed per criteria cell, then applied as AND within row and OR across rows before the minimum is chosen."
1389/// ```
1390///
1391/// [formualizer-docgen:schema:start]
1392/// Name: DMIN
1393/// Type: DMinFn
1394/// Min args: 3
1395/// Max args: 1
1396/// Variadic: false
1397/// Signature: DMIN(arg1: any@scalar)
1398/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1399/// Caps: PURE, REDUCTION
1400/// [formualizer-docgen:schema:end]
1401impl Function for DMinFn {
1402 func_caps!(PURE, REDUCTION);
1403
1404 fn name(&self) -> &'static str {
1405 "DMIN"
1406 }
1407
1408 fn min_args(&self) -> usize {
1409 3
1410 }
1411
1412 fn variadic(&self) -> bool {
1413 false
1414 }
1415
1416 fn arg_schema(&self) -> &'static [ArgSchema] {
1417 &ARG_ANY_ONE[..]
1418 }
1419
1420 fn eval<'a, 'b, 'c>(
1421 &self,
1422 args: &'c [ArgumentHandle<'a, 'b>],
1423 ctx: &dyn FunctionContext<'b>,
1424 ) -> Result<CalcValue<'b>, ExcelError> {
1425 eval_d_function(args, ctx, DAggregate::Min)
1426 }
1427}
1428
1429/* ─────────────────────────── DPRODUCT ──────────────────────────── */
1430#[derive(Debug)]
1431pub struct DProductFn;
1432
1433/// Multiplies values in a database field for records that satisfy criteria.
1434///
1435/// `DPRODUCT` filters the database first, then returns the product of numeric values in `field`.
1436///
1437/// # Remarks
1438/// - Criteria rows are evaluated as OR alternatives; criteria columns in one row are AND constraints.
1439/// - `field` resolves via case-insensitive header text or 1-based column index; unresolved field references return `#VALUE!`.
1440/// - If no numeric values match, this implementation returns `0`.
1441///
1442/// # Examples
1443///
1444/// ```yaml,sandbox
1445/// title: "Product of units in North or South"
1446/// grid:
1447/// A1: "Region"
1448/// B1: "Salesperson"
1449/// C1: "Product"
1450/// D1: "Units"
1451/// E1: "Revenue"
1452/// A2: "West"
1453/// B2: "Diaz"
1454/// C2: "Widget"
1455/// D2: 24
1456/// E2: 126000
1457/// A3: "East"
1458/// B3: "Patel"
1459/// C3: "Gadget"
1460/// D3: 31
1461/// E3: 142500
1462/// A4: "North"
1463/// B4: "Kim"
1464/// C4: "Widget"
1465/// D4: 18
1466/// E4: 87000
1467/// A5: "West"
1468/// B5: "Ramos"
1469/// C5: "Service"
1470/// D5: 12
1471/// E5: 46000
1472/// A6: "South"
1473/// B6: "Lee"
1474/// C6: "Gadget"
1475/// D6: 27
1476/// E6: 119000
1477/// A7: "East"
1478/// B7: "Noor"
1479/// C7: "Widget"
1480/// D7: 22
1481/// E7: 101000
1482/// G1: "Region"
1483/// G2: "North"
1484/// G3: "South"
1485/// formula: "=DPRODUCT(A1:E7, \"Units\", G1:G3)"
1486/// expected: 486
1487/// ```
1488///
1489/// ```yaml,sandbox
1490/// title: "Product of units for East or West by index field"
1491/// grid:
1492/// A1: "Region"
1493/// B1: "Salesperson"
1494/// C1: "Product"
1495/// D1: "Units"
1496/// E1: "Revenue"
1497/// A2: "West"
1498/// B2: "Diaz"
1499/// C2: "Widget"
1500/// D2: 24
1501/// E2: 126000
1502/// A3: "East"
1503/// B3: "Patel"
1504/// C3: "Gadget"
1505/// D3: 31
1506/// E3: 142500
1507/// A4: "North"
1508/// B4: "Kim"
1509/// C4: "Widget"
1510/// D4: 18
1511/// E4: 87000
1512/// A5: "West"
1513/// B5: "Ramos"
1514/// C5: "Service"
1515/// D5: 12
1516/// E5: 46000
1517/// A6: "South"
1518/// B6: "Lee"
1519/// C6: "Gadget"
1520/// D6: 27
1521/// E6: 119000
1522/// A7: "East"
1523/// B7: "Noor"
1524/// C7: "Widget"
1525/// D7: 22
1526/// E7: 101000
1527/// G1: "Region"
1528/// G2: "East"
1529/// G3: "West"
1530/// formula: "=DPRODUCT(A1:E7, 4, G1:G3)"
1531/// expected: 196416
1532/// ```
1533///
1534/// ```yaml,docs
1535/// related:
1536/// - DSUM
1537/// - DCOUNT
1538/// - PRODUCT
1539/// faq:
1540/// - q: "What result does DPRODUCT return if no numeric records match?"
1541/// a: "This implementation returns 0 when no numeric field values remain after criteria filtering."
1542/// ```
1543///
1544/// [formualizer-docgen:schema:start]
1545/// Name: DPRODUCT
1546/// Type: DProductFn
1547/// Min args: 3
1548/// Max args: 1
1549/// Variadic: false
1550/// Signature: DPRODUCT(arg1: any@scalar)
1551/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1552/// Caps: PURE, REDUCTION
1553/// [formualizer-docgen:schema:end]
1554impl Function for DProductFn {
1555 func_caps!(PURE, REDUCTION);
1556
1557 fn name(&self) -> &'static str {
1558 "DPRODUCT"
1559 }
1560
1561 fn min_args(&self) -> usize {
1562 3
1563 }
1564
1565 fn variadic(&self) -> bool {
1566 false
1567 }
1568
1569 fn arg_schema(&self) -> &'static [ArgSchema] {
1570 &ARG_ANY_ONE[..]
1571 }
1572
1573 fn eval<'a, 'b, 'c>(
1574 &self,
1575 args: &'c [ArgumentHandle<'a, 'b>],
1576 ctx: &dyn FunctionContext<'b>,
1577 ) -> Result<CalcValue<'b>, ExcelError> {
1578 eval_d_function(args, ctx, DAggregate::Product)
1579 }
1580}
1581
1582/* ─────────────────────────── DSTDEV ──────────────────────────── */
1583#[derive(Debug)]
1584pub struct DStdevFn;
1585
1586/// Returns the sample standard deviation of a database field for matching records.
1587///
1588/// `DSTDEV` computes standard deviation with the sample denominator (`n - 1`) after criteria filtering.
1589///
1590/// # Remarks
1591/// - Criteria rows represent OR branches; criteria columns in each row are combined with AND.
1592/// - `field` is resolved by case-insensitive header text or 1-based column index; invalid field resolution returns `#VALUE!`.
1593/// - At least two numeric values must match criteria, otherwise the result is `#DIV/0!`.
1594///
1595/// # Examples
1596///
1597/// ```yaml,sandbox
1598/// title: "Sample stdev of units for East or West"
1599/// grid:
1600/// A1: "Region"
1601/// B1: "Salesperson"
1602/// C1: "Product"
1603/// D1: "Units"
1604/// E1: "Revenue"
1605/// A2: "West"
1606/// B2: "Diaz"
1607/// C2: "Widget"
1608/// D2: 24
1609/// E2: 126000
1610/// A3: "East"
1611/// B3: "Patel"
1612/// C3: "Gadget"
1613/// D3: 31
1614/// E3: 142500
1615/// A4: "North"
1616/// B4: "Kim"
1617/// C4: "Widget"
1618/// D4: 18
1619/// E4: 87000
1620/// A5: "West"
1621/// B5: "Ramos"
1622/// C5: "Service"
1623/// D5: 12
1624/// E5: 46000
1625/// A6: "South"
1626/// B6: "Lee"
1627/// C6: "Gadget"
1628/// D6: 27
1629/// E6: 119000
1630/// A7: "East"
1631/// B7: "Noor"
1632/// C7: "Widget"
1633/// D7: 22
1634/// E7: 101000
1635/// G1: "Region"
1636/// G2: "East"
1637/// G3: "West"
1638/// formula: "=DSTDEV(A1:E7, \"Units\", G1:G3)"
1639/// expected: 7.847504911329036
1640/// ```
1641///
1642/// ```yaml,sandbox
1643/// title: "Sample stdev of widget revenue"
1644/// grid:
1645/// A1: "Region"
1646/// B1: "Salesperson"
1647/// C1: "Product"
1648/// D1: "Units"
1649/// E1: "Revenue"
1650/// A2: "West"
1651/// B2: "Diaz"
1652/// C2: "Widget"
1653/// D2: 24
1654/// E2: 126000
1655/// A3: "East"
1656/// B3: "Patel"
1657/// C3: "Gadget"
1658/// D3: 31
1659/// E3: 142500
1660/// A4: "North"
1661/// B4: "Kim"
1662/// C4: "Widget"
1663/// D4: 18
1664/// E4: 87000
1665/// A5: "West"
1666/// B5: "Ramos"
1667/// C5: "Service"
1668/// D5: 12
1669/// E5: 46000
1670/// A6: "South"
1671/// B6: "Lee"
1672/// C6: "Gadget"
1673/// D6: 27
1674/// E6: 119000
1675/// A7: "East"
1676/// B7: "Noor"
1677/// C7: "Widget"
1678/// D7: 22
1679/// E7: 101000
1680/// G1: "Product"
1681/// G2: "Widget"
1682/// formula: "=DSTDEV(A1:E7, 5, G1:G2)"
1683/// expected: 19756.85535031659
1684/// ```
1685///
1686/// ```yaml,docs
1687/// related:
1688/// - DSTDEVP
1689/// - DVAR
1690/// - STDEV.S
1691/// faq:
1692/// - q: "Why does DSTDEV return #DIV/0! with one matching row?"
1693/// a: "DSTDEV uses sample statistics and needs at least two numeric matches for an n-1 denominator."
1694/// ```
1695///
1696/// [formualizer-docgen:schema:start]
1697/// Name: DSTDEV
1698/// Type: DStdevFn
1699/// Min args: 3
1700/// Max args: 1
1701/// Variadic: false
1702/// Signature: DSTDEV(arg1: any@scalar)
1703/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1704/// Caps: PURE, REDUCTION
1705/// [formualizer-docgen:schema:end]
1706impl Function for DStdevFn {
1707 func_caps!(PURE, REDUCTION);
1708
1709 fn name(&self) -> &'static str {
1710 "DSTDEV"
1711 }
1712
1713 fn min_args(&self) -> usize {
1714 3
1715 }
1716
1717 fn variadic(&self) -> bool {
1718 false
1719 }
1720
1721 fn arg_schema(&self) -> &'static [ArgSchema] {
1722 &ARG_ANY_ONE[..]
1723 }
1724
1725 fn eval<'a, 'b, 'c>(
1726 &self,
1727 args: &'c [ArgumentHandle<'a, 'b>],
1728 ctx: &dyn FunctionContext<'b>,
1729 ) -> Result<CalcValue<'b>, ExcelError> {
1730 eval_d_stat_function(args, ctx, DStatOp::StdevSample)
1731 }
1732}
1733
1734/* ─────────────────────────── DSTDEVP ──────────────────────────── */
1735#[derive(Debug)]
1736pub struct DStdevPFn;
1737
1738/// Returns the population standard deviation of a database field for matching records.
1739///
1740/// `DSTDEVP` computes standard deviation with the population denominator (`n`) after criteria filtering.
1741///
1742/// # Remarks
1743/// - Criteria rows are OR branches, and each row's populated criteria columns are ANDed.
1744/// - `field` can be a case-insensitive header label or 1-based index; invalid lookup returns `#VALUE!`.
1745/// - At least one numeric value must match criteria, otherwise the result is `#DIV/0!`.
1746///
1747/// # Examples
1748///
1749/// ```yaml,sandbox
1750/// title: "Population stdev of units for East or West"
1751/// grid:
1752/// A1: "Region"
1753/// B1: "Salesperson"
1754/// C1: "Product"
1755/// D1: "Units"
1756/// E1: "Revenue"
1757/// A2: "West"
1758/// B2: "Diaz"
1759/// C2: "Widget"
1760/// D2: 24
1761/// E2: 126000
1762/// A3: "East"
1763/// B3: "Patel"
1764/// C3: "Gadget"
1765/// D3: 31
1766/// E3: 142500
1767/// A4: "North"
1768/// B4: "Kim"
1769/// C4: "Widget"
1770/// D4: 18
1771/// E4: 87000
1772/// A5: "West"
1773/// B5: "Ramos"
1774/// C5: "Service"
1775/// D5: 12
1776/// E5: 46000
1777/// A6: "South"
1778/// B6: "Lee"
1779/// C6: "Gadget"
1780/// D6: 27
1781/// E6: 119000
1782/// A7: "East"
1783/// B7: "Noor"
1784/// C7: "Widget"
1785/// D7: 22
1786/// E7: 101000
1787/// G1: "Region"
1788/// G2: "East"
1789/// G3: "West"
1790/// formula: "=DSTDEVP(A1:E7, \"Units\", G1:G3)"
1791/// expected: 6.796138609534093
1792/// ```
1793///
1794/// ```yaml,sandbox
1795/// title: "Population stdev of widget revenue"
1796/// grid:
1797/// A1: "Region"
1798/// B1: "Salesperson"
1799/// C1: "Product"
1800/// D1: "Units"
1801/// E1: "Revenue"
1802/// A2: "West"
1803/// B2: "Diaz"
1804/// C2: "Widget"
1805/// D2: 24
1806/// E2: 126000
1807/// A3: "East"
1808/// B3: "Patel"
1809/// C3: "Gadget"
1810/// D3: 31
1811/// E3: 142500
1812/// A4: "North"
1813/// B4: "Kim"
1814/// C4: "Widget"
1815/// D4: 18
1816/// E4: 87000
1817/// A5: "West"
1818/// B5: "Ramos"
1819/// C5: "Service"
1820/// D5: 12
1821/// E5: 46000
1822/// A6: "South"
1823/// B6: "Lee"
1824/// C6: "Gadget"
1825/// D6: 27
1826/// E6: 119000
1827/// A7: "East"
1828/// B7: "Noor"
1829/// C7: "Widget"
1830/// D7: 22
1831/// E7: 101000
1832/// G1: "Product"
1833/// G2: "Widget"
1834/// formula: "=DSTDEVP(A1:E7, 5, G1:G2)"
1835/// expected: 16131.404843417147
1836/// ```
1837///
1838/// ```yaml,docs
1839/// related:
1840/// - DSTDEV
1841/// - DVARP
1842/// - STDEV.P
1843/// faq:
1844/// - q: "When should I prefer DSTDEVP over DSTDEV?"
1845/// a: "Use DSTDEVP when matching rows represent the full population; DSTDEV is for samples."
1846/// ```
1847///
1848/// [formualizer-docgen:schema:start]
1849/// Name: DSTDEVP
1850/// Type: DStdevPFn
1851/// Min args: 3
1852/// Max args: 1
1853/// Variadic: false
1854/// Signature: DSTDEVP(arg1: any@scalar)
1855/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1856/// Caps: PURE, REDUCTION
1857/// [formualizer-docgen:schema:end]
1858impl Function for DStdevPFn {
1859 func_caps!(PURE, REDUCTION);
1860
1861 fn name(&self) -> &'static str {
1862 "DSTDEVP"
1863 }
1864
1865 fn min_args(&self) -> usize {
1866 3
1867 }
1868
1869 fn variadic(&self) -> bool {
1870 false
1871 }
1872
1873 fn arg_schema(&self) -> &'static [ArgSchema] {
1874 &ARG_ANY_ONE[..]
1875 }
1876
1877 fn eval<'a, 'b, 'c>(
1878 &self,
1879 args: &'c [ArgumentHandle<'a, 'b>],
1880 ctx: &dyn FunctionContext<'b>,
1881 ) -> Result<CalcValue<'b>, ExcelError> {
1882 eval_d_stat_function(args, ctx, DStatOp::StdevPop)
1883 }
1884}
1885
1886/* ─────────────────────────── DVAR ──────────────────────────── */
1887#[derive(Debug)]
1888pub struct DVarFn;
1889
1890/// Returns the sample variance of a database field for records matching criteria.
1891///
1892/// `DVAR` filters records first, then computes variance using the sample denominator (`n - 1`).
1893///
1894/// # Remarks
1895/// - Criteria rows are OR alternatives; criteria columns within each row are AND constraints.
1896/// - `field` can be resolved by case-insensitive header text or 1-based index; unresolved fields return `#VALUE!`.
1897/// - At least two numeric values must match criteria, otherwise the function returns `#DIV/0!`.
1898///
1899/// # Examples
1900///
1901/// ```yaml,sandbox
1902/// title: "Sample variance of units for East or West"
1903/// grid:
1904/// A1: "Region"
1905/// B1: "Salesperson"
1906/// C1: "Product"
1907/// D1: "Units"
1908/// E1: "Revenue"
1909/// A2: "West"
1910/// B2: "Diaz"
1911/// C2: "Widget"
1912/// D2: 24
1913/// E2: 126000
1914/// A3: "East"
1915/// B3: "Patel"
1916/// C3: "Gadget"
1917/// D3: 31
1918/// E3: 142500
1919/// A4: "North"
1920/// B4: "Kim"
1921/// C4: "Widget"
1922/// D4: 18
1923/// E4: 87000
1924/// A5: "West"
1925/// B5: "Ramos"
1926/// C5: "Service"
1927/// D5: 12
1928/// E5: 46000
1929/// A6: "South"
1930/// B6: "Lee"
1931/// C6: "Gadget"
1932/// D6: 27
1933/// E6: 119000
1934/// A7: "East"
1935/// B7: "Noor"
1936/// C7: "Widget"
1937/// D7: 22
1938/// E7: 101000
1939/// G1: "Region"
1940/// G2: "East"
1941/// G3: "West"
1942/// formula: "=DVAR(A1:E7, \"Units\", G1:G3)"
1943/// expected: 61.583333333333336
1944/// ```
1945///
1946/// ```yaml,sandbox
1947/// title: "Sample variance of widget revenue"
1948/// grid:
1949/// A1: "Region"
1950/// B1: "Salesperson"
1951/// C1: "Product"
1952/// D1: "Units"
1953/// E1: "Revenue"
1954/// A2: "West"
1955/// B2: "Diaz"
1956/// C2: "Widget"
1957/// D2: 24
1958/// E2: 126000
1959/// A3: "East"
1960/// B3: "Patel"
1961/// C3: "Gadget"
1962/// D3: 31
1963/// E3: 142500
1964/// A4: "North"
1965/// B4: "Kim"
1966/// C4: "Widget"
1967/// D4: 18
1968/// E4: 87000
1969/// A5: "West"
1970/// B5: "Ramos"
1971/// C5: "Service"
1972/// D5: 12
1973/// E5: 46000
1974/// A6: "South"
1975/// B6: "Lee"
1976/// C6: "Gadget"
1977/// D6: 27
1978/// E6: 119000
1979/// A7: "East"
1980/// B7: "Noor"
1981/// C7: "Widget"
1982/// D7: 22
1983/// E7: 101000
1984/// G1: "Product"
1985/// G2: "Widget"
1986/// formula: "=DVAR(A1:E7, 5, G1:G2)"
1987/// expected: 390333333.3333333
1988/// ```
1989///
1990/// ```yaml,docs
1991/// related:
1992/// - DVARP
1993/// - DSTDEV
1994/// - VAR.S
1995/// faq:
1996/// - q: "Does DVAR use sample or population variance math?"
1997/// a: "DVAR uses sample variance with an n-1 denominator and returns #DIV/0! when fewer than two numeric rows match."
1998/// ```
1999///
2000/// [formualizer-docgen:schema:start]
2001/// Name: DVAR
2002/// Type: DVarFn
2003/// Min args: 3
2004/// Max args: 1
2005/// Variadic: false
2006/// Signature: DVAR(arg1: any@scalar)
2007/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
2008/// Caps: PURE, REDUCTION
2009/// [formualizer-docgen:schema:end]
2010impl Function for DVarFn {
2011 func_caps!(PURE, REDUCTION);
2012
2013 fn name(&self) -> &'static str {
2014 "DVAR"
2015 }
2016
2017 fn min_args(&self) -> usize {
2018 3
2019 }
2020
2021 fn variadic(&self) -> bool {
2022 false
2023 }
2024
2025 fn arg_schema(&self) -> &'static [ArgSchema] {
2026 &ARG_ANY_ONE[..]
2027 }
2028
2029 fn eval<'a, 'b, 'c>(
2030 &self,
2031 args: &'c [ArgumentHandle<'a, 'b>],
2032 ctx: &dyn FunctionContext<'b>,
2033 ) -> Result<CalcValue<'b>, ExcelError> {
2034 eval_d_stat_function(args, ctx, DStatOp::VarSample)
2035 }
2036}
2037
2038/* ─────────────────────────── DVARP ──────────────────────────── */
2039#[derive(Debug)]
2040pub struct DVarPFn;
2041
2042/// Returns the population variance of a database field for records matching criteria.
2043///
2044/// `DVARP` computes variance with the population denominator (`n`) over filtered records.
2045///
2046/// # Remarks
2047/// - Criteria rows are OR branches; populated criteria cells in the same row are combined with AND.
2048/// - `field` accepts case-insensitive header text or 1-based index; bad field/header resolution returns `#VALUE!`.
2049/// - At least one numeric value must match criteria, otherwise the function returns `#DIV/0!`.
2050///
2051/// # Examples
2052///
2053/// ```yaml,sandbox
2054/// title: "Population variance of units for East or West"
2055/// grid:
2056/// A1: "Region"
2057/// B1: "Salesperson"
2058/// C1: "Product"
2059/// D1: "Units"
2060/// E1: "Revenue"
2061/// A2: "West"
2062/// B2: "Diaz"
2063/// C2: "Widget"
2064/// D2: 24
2065/// E2: 126000
2066/// A3: "East"
2067/// B3: "Patel"
2068/// C3: "Gadget"
2069/// D3: 31
2070/// E3: 142500
2071/// A4: "North"
2072/// B4: "Kim"
2073/// C4: "Widget"
2074/// D4: 18
2075/// E4: 87000
2076/// A5: "West"
2077/// B5: "Ramos"
2078/// C5: "Service"
2079/// D5: 12
2080/// E5: 46000
2081/// A6: "South"
2082/// B6: "Lee"
2083/// C6: "Gadget"
2084/// D6: 27
2085/// E6: 119000
2086/// A7: "East"
2087/// B7: "Noor"
2088/// C7: "Widget"
2089/// D7: 22
2090/// E7: 101000
2091/// G1: "Region"
2092/// G2: "East"
2093/// G3: "West"
2094/// formula: "=DVARP(A1:E7, \"Units\", G1:G3)"
2095/// expected: 46.1875
2096/// ```
2097///
2098/// ```yaml,sandbox
2099/// title: "Population variance of widget revenue"
2100/// grid:
2101/// A1: "Region"
2102/// B1: "Salesperson"
2103/// C1: "Product"
2104/// D1: "Units"
2105/// E1: "Revenue"
2106/// A2: "West"
2107/// B2: "Diaz"
2108/// C2: "Widget"
2109/// D2: 24
2110/// E2: 126000
2111/// A3: "East"
2112/// B3: "Patel"
2113/// C3: "Gadget"
2114/// D3: 31
2115/// E3: 142500
2116/// A4: "North"
2117/// B4: "Kim"
2118/// C4: "Widget"
2119/// D4: 18
2120/// E4: 87000
2121/// A5: "West"
2122/// B5: "Ramos"
2123/// C5: "Service"
2124/// D5: 12
2125/// E5: 46000
2126/// A6: "South"
2127/// B6: "Lee"
2128/// C6: "Gadget"
2129/// D6: 27
2130/// E6: 119000
2131/// A7: "East"
2132/// B7: "Noor"
2133/// C7: "Widget"
2134/// D7: 22
2135/// E7: 101000
2136/// G1: "Product"
2137/// G2: "Widget"
2138/// formula: "=DVARP(A1:E7, 5, G1:G2)"
2139/// expected: 260222222.2222222
2140/// ```
2141///
2142/// ```yaml,docs
2143/// related:
2144/// - DVAR
2145/// - DSTDEVP
2146/// - VAR.P
2147/// faq:
2148/// - q: "Why can DVARP return a value with only one matched row?"
2149/// a: "Population variance divides by n, so one numeric match yields a defined result instead of #DIV/0!."
2150/// ```
2151///
2152/// [formualizer-docgen:schema:start]
2153/// Name: DVARP
2154/// Type: DVarPFn
2155/// Min args: 3
2156/// Max args: 1
2157/// Variadic: false
2158/// Signature: DVARP(arg1: any@scalar)
2159/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
2160/// Caps: PURE, REDUCTION
2161/// [formualizer-docgen:schema:end]
2162impl Function for DVarPFn {
2163 func_caps!(PURE, REDUCTION);
2164
2165 fn name(&self) -> &'static str {
2166 "DVARP"
2167 }
2168
2169 fn min_args(&self) -> usize {
2170 3
2171 }
2172
2173 fn variadic(&self) -> bool {
2174 false
2175 }
2176
2177 fn arg_schema(&self) -> &'static [ArgSchema] {
2178 &ARG_ANY_ONE[..]
2179 }
2180
2181 fn eval<'a, 'b, 'c>(
2182 &self,
2183 args: &'c [ArgumentHandle<'a, 'b>],
2184 ctx: &dyn FunctionContext<'b>,
2185 ) -> Result<CalcValue<'b>, ExcelError> {
2186 eval_d_stat_function(args, ctx, DStatOp::VarPop)
2187 }
2188}
2189
2190/* ─────────────────────────── DGET ──────────────────────────── */
2191#[derive(Debug)]
2192pub struct DGetFn;
2193
2194/// Returns a single field value from the only record that matches criteria.
2195///
2196/// `DGET` is useful for keyed lookups where criteria are expected to identify exactly one record.
2197///
2198/// # Remarks
2199/// - Criteria rows are OR alternatives; criteria columns inside one row are AND predicates.
2200/// - `field` resolves from a case-insensitive header name or 1-based index; unresolved field/header references return `#VALUE!`.
2201/// - Returns `#VALUE!` when no records match and `#NUM!` when multiple records match.
2202///
2203/// # Examples
2204///
2205/// ```yaml,sandbox
2206/// title: "Get salesperson for a unique North Widget record"
2207/// grid:
2208/// A1: "Region"
2209/// B1: "Salesperson"
2210/// C1: "Product"
2211/// D1: "Units"
2212/// E1: "Revenue"
2213/// A2: "West"
2214/// B2: "Diaz"
2215/// C2: "Widget"
2216/// D2: 24
2217/// E2: 126000
2218/// A3: "East"
2219/// B3: "Patel"
2220/// C3: "Gadget"
2221/// D3: 31
2222/// E3: 142500
2223/// A4: "North"
2224/// B4: "Kim"
2225/// C4: "Widget"
2226/// D4: 18
2227/// E4: 87000
2228/// A5: "West"
2229/// B5: "Ramos"
2230/// C5: "Service"
2231/// D5: 12
2232/// E5: 46000
2233/// A6: "South"
2234/// B6: "Lee"
2235/// C6: "Gadget"
2236/// D6: 27
2237/// E6: 119000
2238/// A7: "East"
2239/// B7: "Noor"
2240/// C7: "Widget"
2241/// D7: 22
2242/// E7: 101000
2243/// G1: "Region"
2244/// H1: "Product"
2245/// G2: "North"
2246/// H2: "Widget"
2247/// formula: "=DGET(A1:E7, \"Salesperson\", G1:H2)"
2248/// expected: "Kim"
2249/// ```
2250///
2251/// ```yaml,sandbox
2252/// title: "Multiple matches return NUM error"
2253/// grid:
2254/// A1: "Region"
2255/// B1: "Salesperson"
2256/// C1: "Product"
2257/// D1: "Units"
2258/// E1: "Revenue"
2259/// A2: "West"
2260/// B2: "Diaz"
2261/// C2: "Widget"
2262/// D2: 24
2263/// E2: 126000
2264/// A3: "East"
2265/// B3: "Patel"
2266/// C3: "Gadget"
2267/// D3: 31
2268/// E3: 142500
2269/// A4: "North"
2270/// B4: "Kim"
2271/// C4: "Widget"
2272/// D4: 18
2273/// E4: 87000
2274/// A5: "West"
2275/// B5: "Ramos"
2276/// C5: "Service"
2277/// D5: 12
2278/// E5: 46000
2279/// A6: "South"
2280/// B6: "Lee"
2281/// C6: "Gadget"
2282/// D6: 27
2283/// E6: 119000
2284/// A7: "East"
2285/// B7: "Noor"
2286/// C7: "Widget"
2287/// D7: 22
2288/// E7: 101000
2289/// G1: "Region"
2290/// G2: "East"
2291/// formula: "=DGET(A1:E7, 5, G1:G2)"
2292/// expected: "#NUM!"
2293/// ```
2294///
2295/// ```yaml,docs
2296/// related:
2297/// - DSUM
2298/// - DCOUNT
2299/// - XLOOKUP
2300/// faq:
2301/// - q: "Why does DGET fail when criteria match two rows?"
2302/// a: "DGET requires exactly one matching record; multiple matches produce #NUM! and zero matches produce #VALUE!."
2303/// ```
2304///
2305/// [formualizer-docgen:schema:start]
2306/// Name: DGET
2307/// Type: DGetFn
2308/// Min args: 3
2309/// Max args: 1
2310/// Variadic: false
2311/// Signature: DGET(arg1: any@scalar)
2312/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
2313/// Caps: PURE, REDUCTION
2314/// [formualizer-docgen:schema:end]
2315impl Function for DGetFn {
2316 func_caps!(PURE, REDUCTION);
2317
2318 fn name(&self) -> &'static str {
2319 "DGET"
2320 }
2321
2322 fn min_args(&self) -> usize {
2323 3
2324 }
2325
2326 fn variadic(&self) -> bool {
2327 false
2328 }
2329
2330 fn arg_schema(&self) -> &'static [ArgSchema] {
2331 &ARG_ANY_ONE[..]
2332 }
2333
2334 fn eval<'a, 'b, 'c>(
2335 &self,
2336 args: &'c [ArgumentHandle<'a, 'b>],
2337 ctx: &dyn FunctionContext<'b>,
2338 ) -> Result<CalcValue<'b>, ExcelError> {
2339 eval_dget(args, ctx)
2340 }
2341}
2342
2343/* ─────────────────────────── DCOUNTA ──────────────────────────── */
2344#[derive(Debug)]
2345pub struct DCountAFn;
2346/// Counts non-blank values in a database field for records matching criteria.
2347///
2348/// # Remarks
2349/// - `DCOUNTA` counts both text and numeric non-empty values.
2350/// - Criteria rows are OR-ed; criteria columns in the same row are AND-ed.
2351/// - Blank cells are excluded from the count.
2352///
2353/// # Examples
2354/// ```yaml,sandbox
2355/// title: "Count non-blank names where score > 80"
2356/// grid:
2357/// A1: "Name"
2358/// B1: "Score"
2359/// A2: "Ana"
2360/// B2: 92
2361/// A3: "Bo"
2362/// B3: 75
2363/// A4: "Cy"
2364/// B4: 88
2365/// D1: "Score"
2366/// D2: ">80"
2367/// formula: "=DCOUNTA(A1:B4,\"Name\",D1:D2)"
2368/// expected: 2
2369/// ```
2370///
2371/// ```yaml,sandbox
2372/// title: "Count all non-blank values in a field"
2373/// grid:
2374/// A1: "Item"
2375/// A2: "X"
2376/// A3: "Y"
2377/// A4: ""
2378/// C1: "Item"
2379/// formula: "=DCOUNTA(A1:A4,\"Item\",C1:C1)"
2380/// expected: 2
2381/// ```
2382///
2383/// ```yaml,docs
2384/// related:
2385/// - DCOUNT
2386/// - DGET
2387/// - COUNTA
2388/// faq:
2389/// - q: "What is treated as blank in DCOUNTA?"
2390/// a: "Empty cells and empty strings are treated as blank; other value types are counted when their row matches criteria."
2391/// ```
2392/// [formualizer-docgen:schema:start]
2393/// Name: DCOUNTA
2394/// Type: DCountAFn
2395/// Min args: 3
2396/// Max args: 1
2397/// Variadic: false
2398/// Signature: DCOUNTA(arg1: any@scalar)
2399/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
2400/// Caps: PURE, REDUCTION
2401/// [formualizer-docgen:schema:end]
2402impl Function for DCountAFn {
2403 func_caps!(PURE, REDUCTION);
2404
2405 fn name(&self) -> &'static str {
2406 "DCOUNTA"
2407 }
2408
2409 fn min_args(&self) -> usize {
2410 3
2411 }
2412
2413 fn variadic(&self) -> bool {
2414 false
2415 }
2416
2417 fn arg_schema(&self) -> &'static [ArgSchema] {
2418 &ARG_ANY_ONE[..]
2419 }
2420
2421 fn eval<'a, 'b, 'c>(
2422 &self,
2423 args: &'c [ArgumentHandle<'a, 'b>],
2424 ctx: &dyn FunctionContext<'b>,
2425 ) -> Result<CalcValue<'b>, ExcelError> {
2426 eval_dcounta(args, ctx)
2427 }
2428}
2429
2430/// Register all database functions.
2431pub fn register_builtins() {
2432 use std::sync::Arc;
2433 crate::function_registry::register_function(Arc::new(DSumFn));
2434 crate::function_registry::register_function(Arc::new(DAverageFn));
2435 crate::function_registry::register_function(Arc::new(DCountFn));
2436 crate::function_registry::register_function(Arc::new(DMaxFn));
2437 crate::function_registry::register_function(Arc::new(DMinFn));
2438 crate::function_registry::register_function(Arc::new(DProductFn));
2439 crate::function_registry::register_function(Arc::new(DStdevFn));
2440 crate::function_registry::register_function(Arc::new(DStdevPFn));
2441 crate::function_registry::register_function(Arc::new(DVarFn));
2442 crate::function_registry::register_function(Arc::new(DVarPFn));
2443 crate::function_registry::register_function(Arc::new(DGetFn));
2444 crate::function_registry::register_function(Arc::new(DCountAFn));
2445}
2446
2447#[cfg(test)]
2448mod tests {
2449 use super::*;
2450 use crate::test_workbook::TestWorkbook;
2451 use formualizer_parse::parser::{ASTNode, ASTNodeType};
2452 use std::sync::Arc;
2453
2454 fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
2455 wb.interpreter()
2456 }
2457
2458 fn lit(v: LiteralValue) -> ASTNode {
2459 ASTNode::new(ASTNodeType::Literal(v), None)
2460 }
2461
2462 fn make_database() -> LiteralValue {
2463 // Simple database with headers: Name, Age, Salary
2464 LiteralValue::Array(vec![
2465 vec![
2466 LiteralValue::Text("Name".into()),
2467 LiteralValue::Text("Age".into()),
2468 LiteralValue::Text("Salary".into()),
2469 ],
2470 vec![
2471 LiteralValue::Text("Alice".into()),
2472 LiteralValue::Int(30),
2473 LiteralValue::Int(50000),
2474 ],
2475 vec![
2476 LiteralValue::Text("Bob".into()),
2477 LiteralValue::Int(25),
2478 LiteralValue::Int(45000),
2479 ],
2480 vec![
2481 LiteralValue::Text("Carol".into()),
2482 LiteralValue::Int(35),
2483 LiteralValue::Int(60000),
2484 ],
2485 vec![
2486 LiteralValue::Text("Dave".into()),
2487 LiteralValue::Int(30),
2488 LiteralValue::Int(55000),
2489 ],
2490 ])
2491 }
2492
2493 fn make_criteria_all() -> LiteralValue {
2494 // Criteria that matches all (just header, no criteria values)
2495 LiteralValue::Array(vec![vec![LiteralValue::Text("Name".into())]])
2496 }
2497
2498 fn make_criteria_age_30() -> LiteralValue {
2499 // Criteria: Age = 30
2500 LiteralValue::Array(vec![
2501 vec![LiteralValue::Text("Age".into())],
2502 vec![LiteralValue::Int(30)],
2503 ])
2504 }
2505
2506 fn make_criteria_age_gt_25() -> LiteralValue {
2507 // Criteria: Age > 25
2508 LiteralValue::Array(vec![
2509 vec![LiteralValue::Text("Age".into())],
2510 vec![LiteralValue::Text(">25".into())],
2511 ])
2512 }
2513
2514 #[test]
2515 fn dsum_all_salaries() {
2516 let wb = TestWorkbook::new().with_function(Arc::new(DSumFn));
2517 let ctx = interp(&wb);
2518
2519 let db = lit(make_database());
2520 let field = lit(LiteralValue::Text("Salary".into()));
2521 let criteria = lit(make_criteria_all());
2522
2523 let args = vec![
2524 crate::traits::ArgumentHandle::new(&db, &ctx),
2525 crate::traits::ArgumentHandle::new(&field, &ctx),
2526 crate::traits::ArgumentHandle::new(&criteria, &ctx),
2527 ];
2528
2529 let f = ctx.context.get_function("", "DSUM").unwrap();
2530 let result = f.dispatch(&args, &ctx.function_context(None)).unwrap();
2531
2532 // Sum of all salaries: 50000 + 45000 + 60000 + 55000 = 210000
2533 assert_eq!(result.into_literal(), LiteralValue::Number(210000.0));
2534 }
2535
2536 #[test]
2537 fn dsum_age_30() {
2538 let wb = TestWorkbook::new().with_function(Arc::new(DSumFn));
2539 let ctx = interp(&wb);
2540
2541 let db = lit(make_database());
2542 let field = lit(LiteralValue::Text("Salary".into()));
2543 let criteria = lit(make_criteria_age_30());
2544
2545 let args = vec![
2546 crate::traits::ArgumentHandle::new(&db, &ctx),
2547 crate::traits::ArgumentHandle::new(&field, &ctx),
2548 crate::traits::ArgumentHandle::new(&criteria, &ctx),
2549 ];
2550
2551 let f = ctx.context.get_function("", "DSUM").unwrap();
2552 let result = f.dispatch(&args, &ctx.function_context(None)).unwrap();
2553
2554 // Sum of salaries where Age = 30: 50000 + 55000 = 105000
2555 assert_eq!(result.into_literal(), LiteralValue::Number(105000.0));
2556 }
2557
2558 #[test]
2559 fn daverage_age_gt_25() {
2560 let wb = TestWorkbook::new().with_function(Arc::new(DAverageFn));
2561 let ctx = interp(&wb);
2562
2563 let db = lit(make_database());
2564 let field = lit(LiteralValue::Text("Salary".into()));
2565 let criteria = lit(make_criteria_age_gt_25());
2566
2567 let args = vec![
2568 crate::traits::ArgumentHandle::new(&db, &ctx),
2569 crate::traits::ArgumentHandle::new(&field, &ctx),
2570 crate::traits::ArgumentHandle::new(&criteria, &ctx),
2571 ];
2572
2573 let f = ctx.context.get_function("", "DAVERAGE").unwrap();
2574 let result = f.dispatch(&args, &ctx.function_context(None)).unwrap();
2575
2576 // Average of salaries where Age > 25: (50000 + 60000 + 55000) / 3 = 55000
2577 assert_eq!(result.into_literal(), LiteralValue::Number(55000.0));
2578 }
2579
2580 #[test]
2581 fn dcount_age_30() {
2582 let wb = TestWorkbook::new().with_function(Arc::new(DCountFn));
2583 let ctx = interp(&wb);
2584
2585 let db = lit(make_database());
2586 let field = lit(LiteralValue::Text("Salary".into()));
2587 let criteria = lit(make_criteria_age_30());
2588
2589 let args = vec![
2590 crate::traits::ArgumentHandle::new(&db, &ctx),
2591 crate::traits::ArgumentHandle::new(&field, &ctx),
2592 crate::traits::ArgumentHandle::new(&criteria, &ctx),
2593 ];
2594
2595 let f = ctx.context.get_function("", "DCOUNT").unwrap();
2596 let result = f.dispatch(&args, &ctx.function_context(None)).unwrap();
2597
2598 // Count of numeric cells in Salary where Age = 30: 2
2599 assert_eq!(result.into_literal(), LiteralValue::Number(2.0));
2600 }
2601
2602 #[test]
2603 fn dmax_all() {
2604 let wb = TestWorkbook::new().with_function(Arc::new(DMaxFn));
2605 let ctx = interp(&wb);
2606
2607 let db = lit(make_database());
2608 let field = lit(LiteralValue::Text("Salary".into()));
2609 let criteria = lit(make_criteria_all());
2610
2611 let args = vec![
2612 crate::traits::ArgumentHandle::new(&db, &ctx),
2613 crate::traits::ArgumentHandle::new(&field, &ctx),
2614 crate::traits::ArgumentHandle::new(&criteria, &ctx),
2615 ];
2616
2617 let f = ctx.context.get_function("", "DMAX").unwrap();
2618 let result = f.dispatch(&args, &ctx.function_context(None)).unwrap();
2619
2620 // Max salary: 60000
2621 assert_eq!(result.into_literal(), LiteralValue::Number(60000.0));
2622 }
2623
2624 #[test]
2625 fn dmin_all() {
2626 let wb = TestWorkbook::new().with_function(Arc::new(DMinFn));
2627 let ctx = interp(&wb);
2628
2629 let db = lit(make_database());
2630 let field = lit(LiteralValue::Text("Salary".into()));
2631 let criteria = lit(make_criteria_all());
2632
2633 let args = vec![
2634 crate::traits::ArgumentHandle::new(&db, &ctx),
2635 crate::traits::ArgumentHandle::new(&field, &ctx),
2636 crate::traits::ArgumentHandle::new(&criteria, &ctx),
2637 ];
2638
2639 let f = ctx.context.get_function("", "DMIN").unwrap();
2640 let result = f.dispatch(&args, &ctx.function_context(None)).unwrap();
2641
2642 // Min salary: 45000
2643 assert_eq!(result.into_literal(), LiteralValue::Number(45000.0));
2644 }
2645
2646 #[test]
2647 fn dsum_field_by_index() {
2648 let wb = TestWorkbook::new().with_function(Arc::new(DSumFn));
2649 let ctx = interp(&wb);
2650
2651 let db = lit(make_database());
2652 let field = lit(LiteralValue::Int(3)); // Column 3 = Salary
2653 let criteria = lit(make_criteria_all());
2654
2655 let args = vec![
2656 crate::traits::ArgumentHandle::new(&db, &ctx),
2657 crate::traits::ArgumentHandle::new(&field, &ctx),
2658 crate::traits::ArgumentHandle::new(&criteria, &ctx),
2659 ];
2660
2661 let f = ctx.context.get_function("", "DSUM").unwrap();
2662 let result = f.dispatch(&args, &ctx.function_context(None)).unwrap();
2663
2664 // Sum of all salaries: 210000
2665 assert_eq!(result.into_literal(), LiteralValue::Number(210000.0));
2666 }
2667}