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

Solution 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/

Solution 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.