14

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.

  • Questions with "Why...?" are often off topic here as probably only the author could give a definite answer. Your question doesn't contain the desired functionality, so it's difficult to suggest you anything. – Máté Juhász Sep 25 '17 at 4:42
  • 2
    @MátéJuhász It seems the answers given show that it is not difficult... – Solar Mike Sep 25 '17 at 5:39
  • Not an answer, but here's a couple of workarounds for those who need one: stackoverflow.com/questions/8011032/… – Adam Sep 25 '17 at 21:29
28

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/

  • 3
    Of course, if "volatile function" is defined correctly, there should be no visible difference between recalculating everything and recalculating only what's needed, apart from speed. – hobbs Sep 25 '17 at 19:09
12

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.

  • This fine explanation, while taking me closer to the answer I seek, still leaves me unconvinced. I'm trying to generate 25 random numbers between 1 and 365; I need to do this TWENTY TIMES (yes, it's a simulation of the "classic" birthday problem). Once I generate a set of 25 random numbers, I'd like them to freeze it (1) while I generate the other sets of 25, and (2) so I can sort the 20 sets I just generated. I can do this using workarounds suggested by others herein, but this is quite a chore to get across to many students studying elementary statistics. – nowradioguy Sep 26 '17 at 3:51
  • 3
    @nowradioguy, what's the part you're unconvinced about? Excel works a certain way and we're kind of stuck with its capabilities and design. For your requirement, preserving the results is pretty simple, it just needs to be done a bunch of times. If the numbers are in a column, select and copy the set of 25 in a single step, then paste-special values into a cell in another spot. That's just a few clicks, probably the fastest way to do it. The paste will probably trigger a recalc, so you're ready to repeat. You can do 20 sets in a couple of minutes. Then have fun with it. :-) – fixer1234 Sep 26 '17 at 4:13
2

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

  • 1
    I understand that, but would you happen to know WHY? There's nothing obvious about why a random numbers generated in a particular cell would be affected by an operation performed elsewhere on that worksheet. – nowradioguy Sep 25 '17 at 3:46
  • 3
    How would Excel know in advance if you want a random value "for life" or if you want change it frequently? The second case is the more probably one (as in the opposite case you may type the random value directly to the cell). – MarianD Sep 25 '17 at 4:07
  • Your statement is wrong. Automatic recalculation does NOT cause recalculation of all formulas. Only when the workbook has volatile functions. – teylyn Sep 25 '17 at 5:19
1

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

  • I've tried Googling "volatile formula", and a number of links come up explaining wghat is on the list of "volatile" formulas, but nowhere can I find an explanation as to WHY RANDBETWEEN is - or has to be - a volatile formula. – nowradioguy Sep 25 '17 at 4:15
  • 1
    One would assume because it is "random" so it is logical that it will recalculate every time... As pointed out if you want your random number fixed then you have 2 different methods - which mean that it will no longer change. Some good info here : decisionmodels.com/calcsecretsi.htm – Solar Mike Sep 25 '17 at 4:44
  • Yes, thank you; that was one of the results that came up in my Google search, but it doesn't directly address my question. Think about it: if I'm doing an unrelated operation in another cell, and a calculation ensues, then any random numbers previously generated are generated anew and are changed. Makes little sense to me. – nowradioguy Sep 25 '17 at 4:50
  • Excel recalculates cell values when an event triggers that process, randbetween is classed as volatile (I did a dice sheet for my daughter so it recalculated everytime I hit F9...) so it gets recalculated, see here as well : beatexcel.com/volatile-functions-excel . If you want a previously random number to stop changing then there are two methods to achieve that. – Solar Mike Sep 25 '17 at 4:57
  • @fixer1234 NOT EVERYTHING is volatile. Not every formula gets re-evaluated when anything changes on a spreadsheet. That only happens if the workbook contains volatile formulas. If you have only Sum() or Countif() or some such, it will only recalculate the cells affected by a change, NOT the wole workbook. – teylyn Sep 25 '17 at 5:21
0

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.

  • Welcome to Super User. The site is a knowledge base of solutions, so answer posts need to focus on answers to what was asked in the question. Almost your entire post is really a rant, opinion, and commentary, which is likely to attract downvotes because it is non-productive within the site's framework. Your last paragraph might be promising. Consider removing everything else, and expand the last paragraph to include more of the "how", maybe an example. For Excel newbies, it would also be helpful to mention how to load that toolpak. – fixer1234 Nov 16 '17 at 20:03

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.