Description: I generated ca. 240 (hopefully) gauss-distributed random numbers (as a column) using NORMINV(RAND();NDmean;NDsigma), then made histogram data using FREQUENCY(C6:C249;F6:F16). The histogram is displayed by the Diagram feature. Then a row with theoretical gauss-distributed data was generated using NORMDIST(F6;mean;sigma;0)*nfac, plus another with the differencies of the 2 datasets. Up to now everything worked well. A nonlinear regression with the SOLVER worked, but extremely slow and it stopped at 2000 iterations. "Continue"(Fortfahren) minimized the sum-of-squares further. (I have another file with a gauss-distribution fit and comparaby number of classes. This one computes fast and comes to a solution at <2000 iterations. The difference is: there is no RAND() function included!) The 2nd and more grave problem with SOLVER is: when Stop (Anhalten) or OK has been clicked to take-over the fitted params, a new set of Random-data is generated which makes the fit useless. Another unwanted behaviour is: SOLVER apparently does not use the start-values given in the changeable cells. You can reproduce this behaviour easily by the attached file. Thanx & best wishes, Gerd Steps to Reproduce: 1.see text 2.see attatched ods-file 3. Actual Results: SOLVER-related Buttons trigger RAND() Nonlin. Regression unuable when RAND() is present. Expected Results: obviously the negative of the above. Reproducible: Always User Profile Reset: No Additional Info: OS: Win10, LO: (64Bit)
Created attachment 164219 [details] Gauss distributed RANDom() numbers, SOLVER
this bug(s) makes CALC-SOLVER unusable for some tasks, so i.m.h.o. it is at least as major bug. Maybe I should reinstall and try MS-Office 2010,..hm... but I really don´t like MS.
RAND always returns a new random number when the formula is recalculated and with the solver you are changing the variables (cells), which forces all the dependent formulas to recalculate. You obviously don't want this behaviour so you can store the random values in the sheet and reference them as cells and use "Sheet->Fill Cells->Fill Random Numbers..." in the menu to regenerate the random numbers on demand.
Thanks Tomasz for pointing me to the rand number generator. This works well. However, I just wanted to check NORMINV() whether this really would generate gauss-distributed numbers. Now I copied these numbers to a named cell-range and applied the regression analysis to this one. It´s a bit circuitious but works well. Thanks and please excuse my bad english.
(In reply to gmildner from comment #4) > Thanks Tomasz for pointing me to the rand number generator. This works well. > However, I just wanted to check NORMINV() whether this really would generate > gauss-distributed numbers. Now I copied these numbers to a named cell-range > and applied the regression analysis to this one. It´s a bit circuitious but > works well. > Thanks and please excuse my bad english. You can update your LibreOffice to 7.0 version and use a new RAND.NV() function. It's a non-volatile random number generating function that are not recalculated on every input like RAND() And I think we can close this as notabug by Comment 3