Why do Excel RANDBETWEEN numbers change for any operation on worksheet?

Question

I need to use Microsoft Excel's RANDBETWEEN function, and am aware that a new set of random numbers is generated any time you do anything else anywhere on the worksheet. I am also aware of the workarounds to "freeze" a set of random numbers that has been generated, say, for sorting n lists of random numbers between a and b. My question is this: WHY do the random numbers generated change? Is there something in the algorithm that necessitates this? FWIW, Google Sheets, LibreOffice Calc, Apple Numbers all exhibit the same behavior.

Answer 1

Charles Williams has a good explanation of how Excel calculates things and why.

http://www.decisionmodels.com/calcsecretsi.htm

In essence, if a workbook contains volatile functions (refer to Charles' list, since what's a volatile function has changed over the years with different Excel versions), a whole workbook recalculation will be triggered when any cell in the workbook is changed (if Excel is set to automatic calculation). If there are no volatile functions in the workbook, only the cells that are affected by the last change will recalculate.

Others here have stated that automatic calculation will always calculate everything in the workbook when any cell is changed, but that is wrong. Only volatile functions cause the automatic recalculation of all cells in the workbook. Without volatile functions, Excel recalculates only what needs to be recalculated.

That is why people like me, who know the difference, strongly advise to avoid volatile functions like OFFSET() or INDIRECT() in worksheet cells if possible, since whey will slow the workbook down by causing a full recalculation after every cell change. Learning to use INDEX() instead of OFFSET() can result in dramatic speed improvements, because Index is not volatile (see Charles' article for more details).

Excel's calculation engine is based on the "recalc or die" philosophy that set Excel apart from competing products when it was in development. Check out this article: https://www.geekwire.com/2015/recalc-or-die-30-years-later-microsoft-excel-1-0-vets-recount-a-project-that-defied-the-odds/

Answer 2

Other answers focus on the mechanics of recalculation and the role of volatile functions, but I think that misses part of the gist of the question. It addresses the rules that have been employed but not so much the "why". I'll focus on that.

Design Concept

Let me start with a design concept and the building blocks of most modern spreadsheets. There's a "division of responsibility". Functions are dedicated routines that perform a specific task, and do it every time they are asked. Separate from that, the spreadsheet application controls when they get asked.

The functions, themselves, don't contain any logic to choose to recalculate or not recalculate. The nature of all Excel functions is to perform some type of operation whenever triggered to do so. No function has its own "memory" of its current value, or a history of its past calculations. It has no way to know whether this is the first time it is being asked to do its job. So no native function has the ability to run only once. If any function is recalculated, it will do what it's designed to do and produce a fresh value.

The spreadsheet application does contain some intelligence that lets it save time by skipping needless recalculations (as described in teylyn's answer). The only time recalculation is skipped is if the application knows that the function's value was not affected by the spreadsheet changes that triggered the need to recalculate things.

So what if you need to have a function run once and then preserve its value? Take your example of producing a random number and then having the result not change. That describes creating randomly generated constants, and you can do that with Excel.

Excel can't know how you want to use its capabilities; whether you want to continue producing new values or preserve the last set. Each option is a use case that Excel supports. Excel accommodates both cases by giving you the tools to calculate new values, and the tools to preserve old values. You can build whatever you want. But the user has the responsibility to make it do what they want.

Function Logic

So lets look at the logic for what the function does. Might it make sense for the recalculation logic to skip a random function because its value should not be affected by other changes in the spreadsheet?

Whether or not a function's value changes upon recalculation depends on its purpose and what it is based on. A calculation on constant values will always produce the same result. An operation based on cell values that haven't changed will produce the same result.

However, some functions are designed with the intention and purpose of producing a different result every time. Consider, for example, functions based on the current time. They will produce a new result based on the time at recalculation. You can't have a function whose purpose is to produce a value based on the current time and have it not update when recalculated.

The recalculation control objective is to always make everything reflect the current state of things when recalculation is triggered. That criterion would not be met if functions based on the current time were not updated.

The "random" functions, like RANDBETWEEN, have the purpose of producing a new random number when recalculated. That's what they are intended to do. You could argue that the recalculation could be skipped because the value should not be affected by other things happening on the spreadsheet.

If that was the default behavior, you would be updating the spreadsheet, but the random value would remain constant. That's not very random behavior. That would support one use case but not the other. Going back to the basic design concept, it's handled like any other function. The default behavior is to have Excel recalculate it. If you want to preserve the previous value for your use case, it is up to you to do that with the available tools.

Answer 3

Every change in worksheet launches automatic recalculation of all formulas, which is the default behavior.

You may disable it or freeze the actual value by converting it to pure value (as the opposite of a formula).

Answer 4

It is classed as a "volatile" formula and as such is part of the list of functions recalculated when a change is detected or a new function is entered.

Another option to consider is to switch the sheet to manual calculation so that you control when it happens - just make sure you remember to recalculate though as any changes to values or formulae will not cause a recalculate...

Answer 5

Despite the heroic efforts to make this sound like a feature and not a bug, I find it extremely unlikely that this behavior is routinely advantageous. "Features" make our lives easier, "bugs" make it harder. In my case, entering a label or explanation in a cell that is not referenced by any other cell causes re-randomization that is inconvenient to deal with. I would call that a "bug".

Yes, I can control recalculation manually (a pain) or I can paste values to prevent it (again, a pain), but in what cases does re-randomization caused by entry of text in a remote region of the sheet make life easier?

Even if there is some logic behind the handling of "volatile" functions, it would seem trivially easy to trigger recalculation via appropriate criteria other than simply hitting "enter" after text entry in unrelated and unreferenced cells, or by other completely unrelated edits. Preventing that should be the default and I can't imagine it would be difficult to implement. If someone wants that "feature" then they can turn it on, but I can't imagine when that would be a good thing. At best, it might be relatively harmless. For me, it's a big hassle because of the type of sheet I'm creating.

Finally -- The Analysis Toolpak allows you to create distributions of random numbers in a variety of ways, and the ranges of numbers so created do not recalculate (re-randomize) at all, unless you tell it to do so. So I recommend that people use the Analysis ToolPak when it meets their needs.