Why RANDBETWEEN breaks your audit trail — and how TB.STATICRANDBETWEEN fixes it

RANDBETWEEN is useful for generating random integers in Excel — picking a random sample item, assigning a reference number, creating test data. But it has a behaviour that causes real problems in audit and finance contexts: it recalculates every time the workbook changes.

This guide explains why that matters and how TB.STATICRANDBETWEEN solves it.

The problem with RANDBETWEEN

=RANDBETWEEN(1, 100) returns a random integer between 1 and 100. But it’s a volatile function — Excel recalculates it whenever anything in the workbook changes: a value is entered somewhere, another formula updates, the file is opened. Each recalculation produces a new number.

In most situations that’s harmless. For audit work, it isn’t.

The audit trail problem

Suppose you’re selecting a sample: you have 80 invoices to review and your sampling approach requires picking 10 at random. You use RANDBETWEEN(1, 80) ten times and note the results — say items 3, 11, 27, 44, 51, 58, 62, 67, 73, 79.

Later, a reviewer looks at your workbook and asks: how did you select these items? They open the file. RANDBETWEEN recalculates. The numbers change. Your workbook now shows a completely different set of items — one that doesn’t match what you actually reviewed.

You’ve lost the audit trail. There’s no record of how the sample was drawn, and the workbook actively contradicts your work.

The same issue arises in any situation requiring reproducibility:

• Assigning stable random reference numbers to transactions
• Generating test data you’ll need to refer back to
• Any randomisation step that forms part of a documented methodology

What TB.STATICRANDBETWEEN does differently

TB.STATICRANDBETWEEN behaves like RANDBETWEEN — it takes a lower and upper bound and returns a random integer between them — but it only calculates once. After the initial calculation, the result is fixed. Opening the file, editing other cells, recalculating the sheet: none of it changes the value.

=TB.STATICRANDBETWEEN(lower, upper)

• lower — the minimum value (inclusive)
• upper — the maximum value (inclusive)

Example: sampling from 80 invoices

=TB.STATICRANDBETWEEN(1, 80)

Enter this in ten cells. Each returns a random integer between 1 and 80. Those values don’t change when the workbook is next opened, when a colleague adds a column, or when a formula recalculates elsewhere.

The numbers in your workbook will always match the items you actually reviewed.

Practical notes

It calculates once per cell, on entry. The random number is generated when the formula is first evaluated and stored. To get a new random value, delete the cell and re-enter the formula.

It works like any other custom function. Type =TB.STATICRANDBETWEEN(1, 80) directly, or access it through the Excel Formulas tab → Insert Function → category tech+bash.

Duplicates are possible. Like RANDBETWEEN, there’s no built-in deduplication — if you generate ten values from a range of 80, you may occasionally get repeats. For most sampling work this is acceptable and consistent with random-with-replacement methodology. If you need unique values, generate a few extras and discard duplicates.

A note on RANDBETWEEN alternatives

Some accountants work around RANDBETWEEN’s volatility by copying cells and pasting as values immediately after generating them. That works, but it requires an extra manual step every time — and it’s easy to forget. TB.STATICRANDBETWEEN removes the need for that workaround: the value is stable from the moment it’s entered.

Getting started

TB.STATICRANDBETWEEN is included in the tech+bash Add-in. Once the Add-in is installed, type =TB. in any cell to see the full list of available custom functions, including TB.STATICRANDBETWEEN, TB.GROSSUP, and TB.GROSSDOWN.