When I’m building models, I first create rapid prototypes. I quickly:

Create the structure (e.g., categories/dimensions) and the matrices I’ll need.
LOD usually comes next, which stands for level of detail. If I create the structure first, it gives me an idea of whether I can speed up the process by focusing on the macro level rather than excessive granularity (which growth-centric CEOs prefer).
I’ll then determine the output and reporting. Sometimes, the output is already in my head, so that’s my starting point. So, this step is either first or third.
Then I import datasets to determine if my model is working. I’m not just planning for the future; I need history for context. Years ago, I’d go on long safari hunts seeking the best dataset for my prototyping process. I even checked Reddit forums for ideas. Finally (I think), I started doing what other smarter modelers did — creating their own datasets using the rand() function.

Quantrix, like Excel, has not only a rand() function but also a randbetween() function. I prefer the latter because I have more control over the random numbers.

In this brief discussion, I’ll present the arguments for RandBetween(), but more importantly, I’ll reveal a way to overcome one of its limitations.

RandBetween’s Limitation

According to the Quantrix help documentation,

RandBetween() generates a random integer greater than or equal to its lower_bound and less than or equal to its upper_bound.

The lower_bound is the smallest integer it will return.

The upper_bound is largest integer it will return.

The forumula returns a different number each time the model is recalculated.
https://quantrix.com/help/modeler/Math_Functions.htm

Below is what the function could look like in a sales matrix where you want to return actual sales in the first year:

Sales:Year[This] = randbetween(500,750)

In all other years, except the current one, the function will return blanks, while the first year of sales will randomly select a number between 499 and 751.

When I tried using this function for decimals and percentages, Quantrix was reading my random number as one (the number 1). I’d try again, and again. I changed the number formatting—same result.

Yeah, please read the directions, idiot (as in me). A closer reading of the instructions for using this function includes the word ‘integer’. The randbetween() function requires integers in its two arguments. So what is the workaround?

For example, if you want to return a percentage between 101% and 110%, create the following formula:

The excerpt above is a scenario manager matrix for a group of grocery stores. The item called planned is what I’ll use in a forecast matrix. While planned percentages will be captured from historical data and/or management feedback, I wanted to test my model with sample data.

To randomize percentages, I divided my RandBetween() function by 100:

Planned = randbetween(101, 110) / 100

Afterward, I changed the formatting on the Planned item to be a percentage.