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.
Excel – Why do Excel RANDBETWEEN numbers change for any operation on worksheet
microsoft excel
Related Solutions
Number notation settings use the system locale setting.
In order to set this to english, go to Control Panel > Country / Region and find the More settings... at the bottom.
There you can change the notation settings. This will change the settings for the entire system though, and there's no way around it.
It appears to be a rounding error, if you round the number in both formula (B4:B8 and E1 on my sheet) first the match works fine.
=MATCH(ROUND(B2/10,4),$B$4:$B$8,0)
=ROUND($B$4-A8,4)
Best Answer
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/