formualizer_eval/builtins/random.rs
1//! Volatile functions like RAND, RANDBETWEEN.
2use crate::args::ArgSchema;
3use crate::function::Function;
4use crate::traits::{ArgumentHandle, FunctionContext};
5use formualizer_common::{ExcelError, LiteralValue};
6use formualizer_macros::func_caps;
7use rand::Rng;
8
9#[derive(Debug)]
10pub struct RandFn;
11
12/// Returns a uniformly distributed pseudo-random number in the interval `[0, 1)`.
13///
14/// `RAND` is volatile and recalculates whenever dependent formulas recalculate.
15///
16/// # Remarks
17/// - The result is always greater than or equal to `0` and strictly less than `1`.
18/// - Because the function is volatile, repeated evaluations can return different values.
19/// - The engine seeds randomness per evaluation context for reproducible execution flows.
20///
21/// # Examples
22///
23/// ```yaml,sandbox
24/// title: "RAND stays within bounds"
25/// formula: "=LET(n, RAND(), AND(n>=0, n<1))"
26/// expected: true
27/// ```
28///
29/// ```yaml,sandbox
30/// title: "Derived integer bucket from RAND"
31/// formula: "=LET(n, INT(RAND()*10), AND(n>=0, n<=9))"
32/// expected: true
33/// ```
34///
35/// ```yaml,docs
36/// related:
37/// - RANDBETWEEN
38/// - LET
39/// - INT
40/// faq:
41/// - q: "Can RAND return the same value on every recalculation?"
42/// a: "Not by default. RAND is volatile, so recalculation can produce a different sample each time."
43/// - q: "If RAND is used twice in one formula, do both uses share one sample?"
44/// a: "Only if you bind it once (for example with LET). Two separate RAND calls are two separate draws."
45/// - q: "Why does RAND look deterministic in some engine runs?"
46/// a: "Randomness is seeded per evaluation context, which keeps a run reproducible while still treating RAND as volatile across recalculations."
47/// ```
48///
49/// [formualizer-docgen:schema:start]
50/// Name: RAND
51/// Type: RandFn
52/// Min args: 0
53/// Max args: 0
54/// Variadic: false
55/// Signature: RAND()
56/// Arg schema: []
57/// Caps: VOLATILE
58/// [formualizer-docgen:schema:end]
59impl Function for RandFn {
60 func_caps!(VOLATILE);
61
62 fn name(&self) -> &'static str {
63 "RAND"
64 }
65 fn min_args(&self) -> usize {
66 0
67 }
68
69 fn eval<'a, 'b, 'c>(
70 &self,
71 _args: &'c [ArgumentHandle<'a, 'b>],
72 ctx: &dyn FunctionContext<'b>,
73 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
74 let mut rng = ctx.rng_for_current(self.function_salt());
75 Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
76 rng.gen_range(0.0..1.0),
77 )))
78 }
79}
80
81pub fn register_builtins() {
82 crate::function_registry::register_function(std::sync::Arc::new(RandFn));
83 crate::function_registry::register_function(std::sync::Arc::new(RandBetweenFn));
84}
85
86#[cfg(test)]
87mod tests {
88 use super::*;
89 use crate::{interpreter::Interpreter, test_workbook::TestWorkbook};
90 use formualizer_parse::LiteralValue;
91
92 fn interp(wb: &TestWorkbook) -> Interpreter<'_> {
93 wb.interpreter()
94 }
95
96 #[test]
97 fn test_rand_caps() {
98 let rand_fn = RandFn;
99 let caps = rand_fn.caps();
100
101 // Check that VOLATILE is set
102 assert!(caps.contains(crate::function::FnCaps::VOLATILE));
103
104 // Check that PURE is not set (volatile functions are not pure)
105 assert!(!caps.contains(crate::function::FnCaps::PURE));
106 }
107
108 #[test]
109 fn test_rand() {
110 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(RandFn));
111 let ctx = interp(&wb);
112
113 let f = ctx.context.get_function("", "RAND").unwrap();
114 let fctx = ctx.function_context(None);
115 let args: Vec<ArgumentHandle<'_, '_>> = Vec::new();
116 let result = f.dispatch(&args, &fctx).unwrap().into_literal();
117 match result {
118 LiteralValue::Number(n) => assert!((0.0..1.0).contains(&n)),
119 _ => panic!("Expected a number"),
120 }
121 }
122
123 #[test]
124 fn test_randbetween_basic() {
125 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(RandBetweenFn));
126 let ctx = interp(&wb);
127 let f = ctx.context.get_function("", "RANDBETWEEN").unwrap();
128 let fctx = ctx.function_context(None);
129 // Build two scalar args 1 and 3
130 let lo = formualizer_parse::ASTNode::new(
131 formualizer_parse::ASTNodeType::Literal(LiteralValue::Int(1)),
132 None,
133 );
134 let hi = formualizer_parse::ASTNode::new(
135 formualizer_parse::ASTNodeType::Literal(LiteralValue::Int(3)),
136 None,
137 );
138 let args = vec![
139 ArgumentHandle::new(&lo, &ctx),
140 ArgumentHandle::new(&hi, &ctx),
141 ];
142 let v = f.dispatch(&args, &fctx).unwrap().into_literal();
143 match v {
144 LiteralValue::Int(n) => assert!((1..=3).contains(&n)),
145 _ => panic!("Expected Int"),
146 }
147 }
148}
149
150#[derive(Debug)]
151pub struct RandBetweenFn;
152
153/// Returns a random integer between two inclusive bounds.
154///
155/// `RANDBETWEEN` evaluates both bounds, then samples an integer in `[low, high]`.
156///
157/// # Remarks
158/// - Bounds are coerced to numbers and truncated to integers.
159/// - If `high < low`, the function returns `#NUM!`.
160/// - The function is volatile and may return a different integer each recalculation.
161///
162/// # Examples
163///
164/// ```yaml,sandbox
165/// title: "Value always falls inside the requested interval"
166/// formula: "=LET(n, RANDBETWEEN(1, 3), AND(n>=1, n<=3, INT(n)=n))"
167/// expected: true
168/// ```
169///
170/// ```yaml,sandbox
171/// title: "Equal bounds produce a fixed value"
172/// formula: "=RANDBETWEEN(7, 7)"
173/// expected: 7
174/// ```
175///
176/// ```yaml,sandbox
177/// title: "Upper bound below lower bound is invalid"
178/// formula: "=RANDBETWEEN(5, 1)"
179/// expected: "#NUM!"
180/// ```
181///
182/// ```yaml,docs
183/// related:
184/// - RAND
185/// - INT
186/// - LET
187/// faq:
188/// - q: "Are both bounds included in RANDBETWEEN?"
189/// a: "Yes. RANDBETWEEN samples an integer in the closed interval [low, high]."
190/// - q: "What happens with decimal bounds like RANDBETWEEN(1.9, 4.2)?"
191/// a: "Bounds are truncated to integers before sampling, so this behaves like RANDBETWEEN(1, 4)."
192/// - q: "Is RANDBETWEEN deterministic?"
193/// a: "It is volatile, so results can change on recalculation, though a single evaluation context uses seeded randomness for reproducible execution."
194/// ```
195///
196/// [formualizer-docgen:schema:start]
197/// Name: RANDBETWEEN
198/// Type: RandBetweenFn
199/// Min args: 2
200/// Max args: 2
201/// Variadic: false
202/// Signature: RANDBETWEEN(arg1: number@scalar, arg2: number@scalar)
203/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
204/// Caps: VOLATILE
205/// [formualizer-docgen:schema:end]
206impl Function for RandBetweenFn {
207 func_caps!(VOLATILE);
208
209 fn name(&self) -> &'static str {
210 "RANDBETWEEN"
211 }
212 fn min_args(&self) -> usize {
213 2
214 }
215 fn arg_schema(&self) -> &'static [ArgSchema] {
216 &crate::builtins::utils::ARG_NUM_LENIENT_TWO[..]
217 }
218
219 fn eval<'a, 'b, 'c>(
220 &self,
221 args: &'c [ArgumentHandle<'a, 'b>],
222 ctx: &dyn FunctionContext<'b>,
223 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
224 // Evaluate bounds as integers
225 let lo_v = args[0].value()?.into_literal();
226 let hi_v = args[1].value()?.into_literal();
227 let lo = match lo_v {
228 LiteralValue::Int(n) => n,
229 LiteralValue::Number(n) => n as i64,
230 _ => 0,
231 };
232 let hi = match hi_v {
233 LiteralValue::Int(n) => n,
234 LiteralValue::Number(n) => n as i64,
235 _ => 0,
236 };
237 if hi < lo {
238 return Err(ExcelError::new(formualizer_common::ExcelErrorKind::Num)
239 .with_message("RANDBETWEEN: hi < lo".to_string()));
240 }
241 let mut rng = ctx.rng_for_current(self.function_salt());
242 let n = if (hi - lo) as u64 == u64::MAX {
243 lo
244 } else {
245 rng.gen_range(lo..=hi)
246 };
247 Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(n)))
248 }
249}