LO 5152 OSX 10.10.5 put in cell A1: =RAND() put in cell A2: =RANDBETWEEN(1;6) force recalculation (F9) A1 changes whereas A2 reads always the same value.
RANDBETWEEN need a hard recalc (Ctrl+Shift+F9). Look at the help for this function.
On OS X this is Cmd+Shift+F9 instead of Ctrl+Shift+F9 NB: It seems very weird that RAND and RANDBETWEEN need a different approach. BTW, excel recalculate both in a consistent way. The documentation should be updated accordingly and at least a menu item should be added exposing this shortcut. If this is definitely not a bug, I personally consider this as an UI mistake.
RANDBETWEEN() should recalculate on each sheet modification and (Shift+Ctrl+)F9 as RAND() does.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c97dd6e4ffedf4423e091b4c7e9c40c37de171e9 Resolves: tdf#102257 RANDBETWEEN() volatile like RAND(), ScRecalcMode::ALWAYS It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/help/commit/?id=019abdfc1c3d8663ec840014a6016929206b74a3 Related: tdf#102257 adjust Help, RANDBETWEEN() volatile like RAND()
CALC: Recalculate does not work with RANDBETWEEN(), changed since Version 6.2 Recalculation requires a hard recalc - this is not a bug, it's a FEATURE ! First, if you wish a bahavior, that recalculates a random number between 2 numbers you can use RAND()*(b-a)+a. Second, RANDOMBETWEEN() is very useful for Calc learning applications for pupils, (mathematics, physics, ...), for example in: www.meudela.de This behavior is also inherited from OOo. Third, it's a big disadvantage for Excel, that randombetween recalculates on every changings. It's not possible writing learning applications with random numbers, if you won't use makros. For learning programs, you need a random number which is not recalculated at every input to get the pupils a feedback (wrong, right, ...)
I support the request of Joachim Kreutzer. The old behavior was much more useful. @Joachim: Please add an example of such use cases here.
The Calc-function RANDBETWEEN() with it's previous functionality (until V6.1) makes something possible (for me a killer 'application'), what is in Excel or googledocs impossible. An example, it takes less than 2 minutes: Fill the cells F1 to G10 with =randbetween(10;20) and fill the cells A1 to A10 with: =F1&"+"&G1&"=" (automatically fill down 10 times). At last fill in D1 to D10: =if(F1+G1=C1;"OK"; "") and READY is an easily learning-application (and impossible in Excel!!!) My students are always astonished (and have so much fun) by doing this and in this way they are 'programming' complex learning applications in Calc. Further calc has such a wonderful powerful conditional-template-concept to look this learning apps great. Please look in some further examples: www.meudela.de (www.meudela.de/M.html) Thank you.
This bug is FIXED as per the request, to recalculate like Excel, it makes no sense to reopen it. And please don't fiddle with the Whiteboard targets. Apart from that, if you want stable random numbers that don't recalculate then for your example select F1:G10 and use menu Sheet -> Fill Cells -> Fill Random Number... and choose Distribution: Uniform Integer, Minimum: 10, Maximum: 20, OK.
Sorry to bother you, Eike Rathke, obviously you are the one in charge. I see the point of compatibility with Excel. And please, take a minute and give me one more chance to explain the thing. I think it's really important for the future for libreoffice. Sometimes it's the little difference, what makes a product so powerful: Simulations and learning apps with calc need an accurate approach for random-numbers. RANDBETWEEN offers a possibility to calculate the first and the last number. So you can ... a) easy avoid doublets, b) easy realize a progression of the random-numbers, c) calmy analyse the random numbers and it's volatile cells. For a new analyse, you use hard recalc (Strg-Shift-F9). This approach is usefull for simulations with calc (and yes, it's possible). An example for adding some numbers: Fill E1 to E10 with 10, 20, 30, ... then automatically fill F1:F10 and G1:G10 with: =RANDBETWEEN(E1;E1+9) and fill the cells A1 to A10 with: =F1&"+"&G1&"=" (automatically fill down 10 times). Only when you really wish a new set of random-numbers, you can force (a hard recalculate) it with Shift+CTRL+F9. In Excel (and in Calc since 6.2) you haven't any control and you can't analyse a new set of random numbers because of it's recalculation. If i didn't convince you, is it possible to use a switch in RANDBETWEEN like this: RANDBETWEEN(2;5;1) needs a hard recalc RANDBETWEEN(2;5;0) acts like in Excel? Sincerely
(In reply to Joachim Kreutzer from comment #10) > If i didn't convince you, is it possible to use a switch in RANDBETWEEN like > this: > RANDBETWEEN(2;5;1) needs a hard recalc > RANDBETWEEN(2;5;0) acts like in Excel? That would add an incompatibility to the function call, earlier release versions or other applications would not be able to execute the function when reading such document. Besides, the volatile "recalc always" property is a property of the function, determined during compile time, not tied to a value encountered during interpreting/calculating the expression (here function) (which actually would lead to a hen and egg problem as you'd need to interpret the expression to determine whether it needs to be interpreted..). A function call or other expression also doesn't know why it was triggered to recalculate, so differentiating between a hard recalc and any other recalc because of changed dependencies is not possible at that stage and even if that was implemented it still wouldn't help because a function or expression part doesn't remember its last value. For your specific problem either use the stable random numbers I lined out in comment 9 and refill that range with new random numbers when needed, or write a macro that does such thing; or a user defined macro function that remembers a last argument (e.g. a value that was obtained from a referenced cell) and if that changes (i.e. a new value in that cell) produces a set of new random numbers and call the macro function in an array formula. Different approaches are possible without bending Calc internals.
Eike Rathke, I am sure you had the best intentions. I just updated to LO 6.3.0.4. I cannot speak for everyone, I will only speak for myself. I am getting killed. I cannot update LO until this gets reversed. Before blabbering infinitely about how much this hurts my LIFE, allow me to make a few quick questions/comments. Feel free to offer solutions. 1) One could make randbetween volatile by typing randbetween(a,b)+N(T(TODAY()) , where N(T(TODAY()) could be any volatile function like: rand()*0 , offset($A$1,0,0) etc... http://www.decisionmodels.com/calcsecretsi.htm Of course one could use rand() instead. Up until now, one could choose a behaviour for randbetween, just like the feature that Kreutzer requested. Now, no one can as you stated. Volatile behaviour is forced on everyone. 2) Determining which functions are volatile and even worse which spreadsheets are affected by volatile functions was easy-peasy . Would you please tell me how one can know whether his spreadsheet's performance is affected by volatile functions? Up until now, one could take the result eg B1 and make a cell C1→B1+randbetween(1,1000) . If C1 was not changing , one could be sure that the spreadsheet's performance was not affected by volatile functions (eg. offset) . In the inferior M$ suite you needed a test workbook to find out. http://www.decisionmodels.com/calcsecretsi.htm http://www.decisionmodels.com/Downloads/VolatileFuncs.zip 3)There is absolutely NO WAY to update my 10 years of work where I have been using the randbetween function in all my spreadsheets. Of course I was using it in order to avoid burning my computer with needless recalculations. M$ would not dare change their inferior feature so as not to hurt their users. LO should not hurt their users as well. Some will get hurt. I am getting killed. 4)What's wrong with bug.reporter.21 pressing "hard recalculate" (or assigning an easier keyboard shortcut), just like everyone does? Will that kill him? Joachim Kreutzer , there are many solutions to your problem. Try enabling iterations in Tools→Options→Calc→Calculate and use B1→ "=if($A$1,randetween(1,100),B1)" and A1 to trigger change of the random numbers.
Eike Rathke, Would you please add two functions: randnv and randbetweennv? (nv meaning non-volatile). This way old users can do a find-replace in their old spreadsheets and move on with their lives. Not all users care about the M$ compatibility. For example the style() function is not compatible, but it would be murder to remove it for some users. The cell() function is also non-volatile in LO in contrast to M$ and this is life-saving for some users. Imagine the disaster it would cause to some users, not being able to guess why their spreadsheets are so much slower. Their whole spreadsheets would turn volatile for no gain at all. What do you think?
*** Bug 127700 has been marked as a duplicate of this bug. ***
Would someone please change the whiteboard bug status? I would not want to fiddle with the whiteboard myself, in case it offends the assignee. This is not a resolved bug. There is no resolution when there are critical problems arising from it for some users.
This bug *is* resolved, "critical problems arising" are due to depending on unspecified side effects of a (here wrong) implementation that was corrected, the discussion is enhancement requests. Tracked now in bug 127831 btw.
Problem with functions since Verion 7.02: randbetween.nv() and rept() Hello @Eike, there's a problem with randbetween.nv() and the rept() function. cell A1: =randbetween.nv(1;10) cell B1: =rept("a";A1) Save the spreadsheet & reload it and you will find a new random number in A1, but not the corresponding number of 'a' in cell B1. This behavior is very strange, because in other cells/tabs, it works sometimes! @Eike, I would appreciate it very much, if you have a look on it.
That is unrelated to randbetween.nv() and with 7.0.2 sounds like bug 137248 that was fixed for 7.0.3 already. Please upgrade, current version is 7.0.4
Created attachment 169232 [details] shows actual problem rept&randombetween.nv with 7.04 Sorry to bother you, Eike, but it really doesn't work, even with 7.04. There's a strange problem using Rept() and Random.nv() To check it, just open the ods file. You'll see it immediatly.
Please create a new bug for that, thanks.
New bug created: 169253 Thanks, Eike
(In reply to Joachim Kreutzer from comment #21) > New bug created: 169253 Rather, bug 139985 ;)