Seeded Random Number Generator in Excel

If you've generated random numbers in Excel before, you'll know there's a limitation where, every time you make a change to the spreadsheet, functions like RAND, RANDBETWEEN, and RANDARRAY recalculate and return different values. In Excel lingo, functions with this behavior are called volatile functions.

This volatility can be frustrating when you need random results to stay put. Fortunately, there's a solution: a seeded random number generator. In this article, we'll show you how to build one using Excel's LAMBDA function, giving you full control over when your random numbers change. We'll start by looking at the problem in detail, then walk through building a custom function step by step.

Table of Contents

• The problem with random formulas
• What is a seeded random number generator?
• Benefits of a seeded random number generator
• Our approach
• The RNG algorithm
• Implementing the LAMBDA function
• Improving the formula
• Creating a custom HASH function
• Sorting students with RAND_SEQUENCE
• Using RAND_SEQUENCE in your workbooks

The problem with random formulas

To illustrate the problem with random formulas in Excel clearly, let's look at an example. Imagine a teacher wants to randomly assign students to groups every month. One simple way to do this is to set up the groups in one column and, in the next column, sort the students randomly. You can see this approach in the workbook below, where the original list of students is in column B, the list of groups is in column D, and the randomly sorted students are in column E. The formula in cell E5 looks like this:

=SORTBY(B5:B13,RANDARRAY(ROWS(B5:B13)))

See Random list of names for details about how this formula works.

At first glance, everything looks great. We have successfully placed 9 students into 3 groups using a random sort. However, the problem is that RANDARRAY is a volatile function, which means it will recalculate each time the worksheet changes. You can see how this works in the screens below, where three unrelated edits cause the formula to return new results:

Every change to the worksheet creates new random groups!

The classic solution to this problem is to copy the randomly sorted students to the clipboard and use Paste Special > Values to create static values that won't change. However, this is an annoying workaround, since it requires several manual steps, and it also destroys the formulas used to generate random results in the first place. What we really want is a way to generate new random numbers on demand.

This is not a new problem. For most programming languages, there exists a tried and true solution used when generating random numbers called seed values. It works like this: when you initialize a random number generator with a seed value, you get reproducible random numbers based on the seed value. The key idea here is reproducible, meaning two separate calls with the same seed value will produce the same random numbers. The solution is a Seeded Random Number Generator, sometimes abbreviated to "RNG".

What is a seeded random number generator?

A seeded random number generator creates numbers that look random but come from a repeatable process. You give it a starting value called a seed, and it uses that seed to produce a sequence of random-looking numbers. Use the same seed again, and you get the exact same sequence. Change the seed, and you get a completely different sequence. Random number generators are built with specific algorithms and are used in almost every programming environment and platform. Excel's built-in random functions don't let you choose a seed, so results change whenever the worksheet recalculates. A seeded generator fixes that by giving you stable, reproducible random numbers that won't change with every worksheet change.

Benefits of a seeded random number generator

As shown above, Excel's built-in random functions are volatile and recalculate with each workbook change. Having a seeded random number generator available in Excel offers important benefits, including:

• On-demand generation of new random values based on a seed.
• Stable random results that don't recalculate when workbooks are opened or edited.
• Repeatable results with the same seed value.
• Ability to avoid unwanted and expensive recalculation in complicated worksheets.
• Reproducible worksheet demos.

These benefits allow you to build stable and reproducible worksheets that do things like:

• Randomly assign students to groups at the start of every month.
• Randomly pick winners in a prize drawing.
• Simulate random events in financial modeling or risk analysis.
• Create reproducible dice rolls or card shuffles.
• Generate random lists of names, products, countries, sizes, etc

In short, a seeded Random Number Generator is valuable in Excel because it gives you "reproducible randomness"—something Excel's built-in random functions cannot provide.

Our Approach

We are going to implement a random number generator in Excel by writing a LAMBDA function and naming it RAND_SEQUENCE in the name manager. Our function will take two arguments:

• seed - a text string representing the seed value.
• n - a number indicating how many random values to return.

When we're finished, we'll be able to call our new function like any other Excel function, and it will return random decimal values. For example, if we call RAND_SEQUENCE with the seed "apple" and the number 3, we'll get back numbers like this:

=RAND_SEQUENCE("apple",3) // returns {0.710028;0.444729;0.560484}

And, when we call the function again somewhere else in the spreadsheet and pass in the same seed value, we'll get the same random values:

=RAND_SEQUENCE("apple",3) // returns {0.710028;0.444729;0.560484}

In other words, the function returns random numbers, but these numbers are uniquely determined by the seed value passed into the function. To generate a new sequence of random numbers, we pass in a different seed value. This is what makes a function like this so useful and is exactly what the teacher in our scenario could use to reassign students into groups at the start of every month.

This approach is made possible by the new LAMBDA functions introduced in Excel over the past few years, including LET, LAMBDA, MAP, SCAN, REDUCE, and more. These functions really expand what's possible in Excel by unlocking new ways to tackle hard problems. In our case, implementing a seeded random number generator is an excellent example of how these functions can be used to create a useful new feature that can be used just like a built-in Excel function.

The RNG algorithm

There are many different ways to implement a seeded random number generator. We are going to write a formula that uses the Park-Miller "minimal standard" LCG algorithm, because it's well-documented and straightforward to implement.

The way this algorithm works is by performing a series of multiplications and mod operations on an initial seed value. For example, starting with an integer seed value, we multiply it by a=16807 and then take the remainder of dividing the result by m=2147483647 using the MOD function. This gives us a random integer between zero and the value of m. To generate the next random integer, we use the previous result as the seed value and repeat the process.

For example, to generate the first integer using a seed value of 42 we calculate the result of MOD(a*42, m) like this:

=LET(
  seed,42,
  a,16807,
  m,2147483647,
  MOD(a*seed,m)
) // returns 705894

Then, to generate the next integer, we pass in 705894 as the current seed value and perform the same operation: multiply by a and take the remainder of dividing by m.

=LET(
  seed,705894,
  a,16807,
  m,2147483647,
  MOD(a*seed,m)
) // returns 1126542223

And so on. The screenshot below shows how this formula can be adapted to generate values in Excel. Notice that the seed value is defined as the value in the cell above, starting at B5. As the formula in B6 is dragged down, it generates the sequence of random integers seen below:

To normalize this sequence of random integers to numbers between zero and one, we can divide by the value of m=2147483647 like this:

This gives us a seeded sequence of random numbers using the Park-Miller algorithm. Now you could use this formula as-is in a helper column, but what we want to do is convert this to a LAMBDA function that takes in a seed and quantity as arguments and spills the random numbers. There are a number of benefits to doing this:

1. Naming the function in the name manager makes it so we can use it elsewhere in the spreadsheet and abstracts the implementation details from the user.
2. Having a function that spills the results removes the need for a helper column and makes the random number generator more practical to use.
3. The arguments can be normalized to prevent errors.

The end result will be a function that takes in a seed value and quantity as arguments and spills the random numbers. This is similar to the behavior of RANDARRAY, except our function allows us to control when the random numbers are generated.

Implementing the LAMBDA function

First, let's create a formula that has the spill behavior that we want. Once we get that working, we'll wrap it in a LAMBDA and give it a name in the name manager.

To start, we'll use the SCAN function to generate a sequence of seed values. The syntax of the SCAN function looks like this:

=SCAN([initial_value],array,lambda)

• initial_value (optional): The starting value for the accumulation.
• array: The array (range or constant array) to process.
• lambda(accumulator, value): A custom function that defines how to combine the running total (accumulator) with each element (value).

The way SCAN works is it processes each element of the array. At each step:

• The accumulator holds the running result so far
• The value is the current element from the array
• The LAMBDA function is called with the current value of the accumulator and the current element from the array to calculate the next value of the accumulator

For example, the following formula generates a running total for the input array {1; 2; 3} generated by the SEQUENCE function:

=SCAN(0,SEQUENCE(3),LAMBDA(acc,val,acc+val)) // returns {1;3;6}

The key feature of SCAN is that it outputs an array of running results for each step, which is the spill behavior we want. In other words, the length of the input array controls how many times the lambda function is called, so our basic setup looks something like this:

=LET(
  seed,42,
  n,5,
  step,LAMBDA(...)
  SCAN(seed,SEQUENCE(n),step)
)

We call SCAN with the initial seed value and an array whose length is equal to the number of random integers to generate. The step function will implement the logic from earlier to calculate the next seed value using the current seed value.

For example, to generate the first three values, set up the formula like this with the seed=42 and n=3:

=LET(
  seed,42,
  n,3,
  a,16807,
  m,2147483647,
  step,LAMBDA(s,_,MOD(a*s,m)),
  SCAN(seed,SEQUENCE(n),step)
) // returns {705894;1126542223;1579310009}

This formula returns the same sequence of integers as before: {705894; 1126542223; 1579310009}.

To understand how SCAN works in this context, let's trace through the chain of calls to the lambda function. When we call SCAN(seed, SEQUENCE(3), step), the lambda function step gets called three times with the following arguments:

1. step(42, 1) → returns 705894
2. step(705894, 2) → returns 1126542223
3. step(1126542223, 3) → returns 1579310009

The lambda function calculates the next seed value by multiplying the current seed value by a=16807 and taking the remainder of dividing by m=2147483647 using the MOD function. For each call, the result is passed to the next call to be used as the seed value represented by the argument s. This is why the first argument of the lambda function is called an accumulator, because it gets passed between each call.

Note: We don't actually use the values in the array generated by SEQUENCE. This is why the second argument of the step lambda function is named with an underscore symbol (_) to indicate that the value is never used. What's important about the input array is that it controls how many times the step lambda function is called.

As before, to normalize these random numbers to be in the range from zero to one, we divide by the value of m.

=LET(
  seed,42,
  n,5,
  a,16807,
  m,2147483647,
  step,LAMBDA(s,_,MOD(a*s,m)),
  results,SCAN(seed,SEQUENCE(n),step),
  results/m
)

This formula generates a sequence of random numbers based on the seed value, where n=5 controls how many values are generated.

To convert this into a lambda function, wrap the formula in a lambda function and change seed and n to be parameters of the lambda function. For example, the following formula is equivalent to the previous version that uses LET:

=LAMBDA(seed,n,
  LET(
    a,16807,
    m,2147483647,
    step,LAMBDA(s,_,MOD(a*s,m)),
    results,SCAN(seed,SEQUENCE(n),step),
    results/m
  )
)(42,5)

Here we are invoking our lambda function and passing in seed=42 and n=5 as input, which is a good way to test and tweak the lambda function before adding it to the name manager. When we add the formula to the name manager, we'll remove the (42, 5) which is a special syntax used to call the function before it has a name. To name the formula, go to Formulas > Name Manager > New and enter RAND_SEQUENCE for the name and then enter the following formula for the value:

=LAMBDA(seed,n,
  LET(
    a,16807,
    m,2147483647,
    step,LAMBDA(s,_,MOD(a*s,m)),
    results,SCAN(seed,SEQUENCE(n),step),
    results/m
  )
)

After adding the formula to the name manager, you should see the function in the name manager like this:

Now we can use the RAND_SEQUENCE function in the spreadsheet like this to generate a sequence of 10 random numbers using a seed value.

Improving the formula

This is a good start, but there are some improvements we should make to make the function more robust and user-friendly. To begin with, we should normalize the seed value so it is an integer greater than zero and less than m. This prevents some issues that can arise if the seed value is zero or greater than m. To do this, modify the formula by introducing a new variable start that gets set to the normalized seed value:

=LAMBDA(seed,n,
  LET(
    a,16807,
    m,2147483647,
    start,IF(INT(seed)=0,1,MOD(INT(seed),m)),
    step,LAMBDA(s,_,MOD(a*s,m)),
    results,SCAN(start,SEQUENCE(n),step),
    results/m
  )
)

The second improvement addresses some more subtle issues.

• If the seed value is small, say in the range of 1-100, the first random number will always be a relatively small number.
• Because of how the algorithm works, adjacent seed values produce similar sequences of random numbers. This isn't a huge issue, but it is noticeable if using adjacent seed values like 42 and 43 to sort a list.

These issues can be avoided by using seed values that are evenly distributed across the range of 1 to m, but this is a bit of a hassle for the user. We can address both of these issues with a hash function.

Creating a custom HASH function

A simple way to handle the issues mentioned above is to introduce a "hash" function in the name manager and use it to generate seed values. Hash functions are another example of a well-understood and useful concept that can be implemented in Excel using the new lambda functions. Simply put, a hash function takes in a string and returns a number.

What this means for our RAND_SEQUENCE function is that we can pass a string instead of an integer to be used as the seed value. This will resolve both issues. The hash function will produce a suitably large integer for the seed value, and make it easy for the user to avoid adjacent seed values.

The following formula implements a hash function (DJB2) that takes in a string and returns a number. For example, given the string "apple", the hash function returns the number 253337143:

=LAMBDA(str,
  LET(
    s,TEXT(str,"@"),
    bytes,UNICODE(MID(s,SEQUENCE(LEN(s)),1)),
    h0,5381,
    step,LAMBDA(h,c,MOD(h*33+c,2^32)),
    REDUCE(h0,bytes,step)
  )
)("apple") // returns 253337143

As before, to create a new custom function, we open the Name Manager at Formulas > Name Manager > New and enter HASH for the name and the lambda function above for the value. Now we can use the new HASH function like this:

=HASH("apple") // returns 253337143
=HASH("orange") // returns 319921761

To incorporate the HASH function into the RAND_SEQUENCE function, we use it to normalize the seed value like this:

=LAMBDA(seed,n,
  LET(
    a,16807,
    m,2147483647,
    h,HASH(seed),
    start,IF(INT(h)=0,1,MOD(INT(h),m)),
    step,LAMBDA(s,_,MOD(a*s,m)),
    results,SCAN(start,SEQUENCE(n),step),
    results/m
  )
)

Now we have a robust and user-friendly random number generator that can be used to generate reproducible random numbers using a seed value like "apple" or "orange".

=RAND_SEQUENCE("apple",10) // spills ten random numbers

Sorting students with RAND_SEQUENCE

To use the custom RAND_SEQUENCE function to sort students into groups, we can adapt our original random sort formula above to use random numbers generated by the RAND_SEQUENCE function instead of RANDARRAY:

=SORTBY(range,RANDARRAY(ROWS(range)))

Becomes:

=SORTBY(range,RAND_SEQUENCE(ROWS(range)))

A final touch is to wrap the formula in the LET function so that we only refer to the range once. You can see how this works in the worksheet below, where the following formula sorts the students in B3:B11 with the seed value of "apple" in G3.

=LET(
  students,B3:B11,
  SORTBY(students,RAND_SEQUENCE(G3,ROWS(students)))
)

Now that we have a seeded random number determining the sort order, the results will not change when routine edits are made to the worksheet. Only the seed value in G3 controls the output — other changes in the workbook will not cause the formula to generate new results. When we change the seed value in G5 to "orange", the formula will sort the students in a different random order:

Even better, the random sort is reproducible: if you change the seed value back to "apple", you again see the random sort associated with that seed. In other words, you can easily reproduce the first random sort.

This is exactly what we set out to do. We have a function that takes in a seed value and quantity as arguments and spills random numbers. This is a significant upgrade to the volatile results that Excel's random functions generate, and very handy in any situation where you need stable and reproducible random results.

Using RAND_SEQUENCE in your workbooks

You might wonder how you can easily define the two custom functions explained above, RAND_SEQUENCE and HASH in your own workbooks without recreating them from scratch with the Name Manager. This is not difficult. The Excel workbook attached to this article contains the RAND_SEQUENCE function and the HASH function already defined and ready to use. To quickly copy these functions into your own workbook, follow these steps:

1. Go to Sheet8 in the workbook, which uses the final function
2. Right-click the sheet name and select "Move or Copy…"
3. For "To book", select an existing (or new) workbook.
4. Tick the "Create a copy" checkbox to leave the original sheet intact.
5. Click OK to copy the sheet.
6. The named formulas are now in the destination workbook.
7. If desired, delete the copied sheet. The named formulas will remain.