1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
use statrs::distribution::{ContinuousCDF, Normal};
use crate::expressions::token::Error;
use crate::expressions::types::CellReferenceIndex;
use crate::{calc_result::CalcResult, expressions::parser::Node, model::Model};
impl<'a> Model<'a> {
// Z.TEST(array, x, [sigma])
pub(crate) fn fn_z_test(&mut self, args: &[Node], cell: CellReferenceIndex) -> CalcResult {
// 2 or 3 arguments
if args.len() < 2 || args.len() > 3 {
return CalcResult::new_args_number_error(cell);
}
let array_arg = self.evaluate_node_in_context(&args[0], cell);
// Flatten first argument into Vec<Option<f64>> (numeric / non-numeric)
let values = match array_arg {
CalcResult::Range { left, right } => match self.values_from_range(left, right) {
Ok(v) => v,
Err(error) => return error,
},
CalcResult::Array(array) => match self.values_from_array(array) {
Ok(v) => v,
Err(error) => {
return CalcResult::new_error(
Error::VALUE,
cell,
format!("Error in array argument: {:?}", error),
);
}
},
CalcResult::Number(v) => vec![Some(v)],
error @ CalcResult::Error { .. } => return error,
_ => {
return CalcResult::new_error(
Error::VALUE,
cell,
"Z.TEST first argument must be a range or array".to_string(),
);
}
};
// Collect basic stats on numeric entries
let mut sum = 0.0;
let mut count: u64 = 0;
for x in values.iter().flatten() {
sum += x;
count += 1;
}
// Excel: if array has no numeric values -> #N/A
if count == 0 {
return CalcResult::new_error(
Error::NA,
cell,
"Z.TEST array has no numeric data".to_string(),
);
}
let n = count as f64;
let mean = sum / n;
// x argument (hypothesized population mean)
let x_value = match self.evaluate_node_in_context(&args[1], cell) {
CalcResult::Number(v) => v,
error @ CalcResult::Error { .. } => return error,
_ => {
return CalcResult::new_error(
Error::VALUE,
cell,
"Z.TEST second argument (x) must be numeric".to_string(),
);
}
};
// Optional sigma
let mut sigma: Option<f64> = None;
if args.len() == 3 {
match self.evaluate_node_in_context(&args[2], cell) {
CalcResult::Number(v) => {
if v == 0.0 {
return CalcResult::new_error(
Error::NUM,
cell,
"Z.TEST sigma cannot be zero".to_string(),
);
}
sigma = Some(v);
}
error @ CalcResult::Error { .. } => return error,
_ => {
return CalcResult::new_error(
Error::VALUE,
cell,
"Z.TEST sigma (third argument) must be numeric".to_string(),
);
}
}
}
// If sigma omitted, use sample standard deviation STDEV(array)
let sigma_value = if let Some(s) = sigma {
s
} else {
// Excel: if only one numeric value and sigma omitted -> #DIV/0!
if count <= 1 {
return CalcResult::new_error(
Error::DIV,
cell,
"Z.TEST requires at least two values when sigma is omitted".to_string(),
);
}
// Compute sum of squared deviations
let mut sumsq_dev = 0.0;
for x in values.iter().flatten() {
let d = x - mean;
sumsq_dev += d * d;
}
let var = sumsq_dev / (n - 1.0);
if var <= 0.0 {
return CalcResult::new_error(
Error::DIV,
cell,
"Z.TEST standard deviation is zero".to_string(),
);
}
var.sqrt()
};
// Compute z statistic: (mean - x) / (sigma / sqrt(n))
let denom = sigma_value / n.sqrt();
if denom == 0.0 {
return CalcResult::new_error(
Error::DIV,
cell,
"Z.TEST denominator is zero".to_string(),
);
}
let z = (mean - x_value) / denom;
// Standard normal CDF
let dist = match Normal::new(0.0, 1.0) {
Ok(d) => d,
Err(_) => {
return CalcResult::new_error(
Error::NUM,
cell,
"Cannot create standard normal distribution in Z.TEST".to_string(),
);
}
};
let mut p = 1.0 - dist.cdf(z);
// clamp tiny FP noise
if p < 0.0 && p > -1e-15 {
p = 0.0;
}
if p > 1.0 && p < 1.0 + 1e-15 {
p = 1.0;
}
CalcResult::Number(p)
}
}