[Solved] RANDBETWEEN numbers keep changing

[ronanski]

Im using lists of randomly generated numbers and iv just noticed that they have been changing. The first 8 out of 10 lists have now all got different numbers than when I first started making the document, but I haven't touched those cells since, why would this happen and is there a way to stop it?

[robleyd]

From the HELP -F1:

RANDBETWEEN
Returns an integer random number in a specified range.
Syntax
RANDBETWEEN(Bottom; Top)

Returns an integer random number between integers Bottom and Top (both inclusive).
This function produces a new random number each time Calc recalculates. To force Calc to recalculate manually press Shift+Ctrl+F9.

To generate random numbers which never recalculate, copy cells containing this function, and use Edit - Paste Special (with Paste All and Formulas not marked and Numbers marked).

[Villeroy]

This function produces a new random number each time Calc recalculates.

RANDBWTWEEN(A1;B1) recalculates when A1 or B1 change, when you edit the formula, when the document is (re-)loaded or when Ctrl+Shift+F9 enforces a complete recalculation.
RAND() recalculates with every recalculation anywhere in the same document. RAND() is a volatile function. RANDBETWEEN is a regular function.
The solution remains the same. Paste-special can be used to convert formula results into constant values.

[ronanski]

Ah thankyou. It seems odd though that this would be an intentional function, especially if there is a way to manually recalculated if you wanted to anyway. Incidentally I do not have an F9 key on my macbookpro so not sure how I would use that function if I needed it.

[Villeroy]

https://support.apple.com/en-us/HT207240

[Lupp]

(Just a bit off topic:)
In recent versions of LibreOffice Calc RANDBETWEEN() also is volatile as RAND() is.
In pusuit of the old enhancement request (tdf#127831) https://bugs.documentfoundation.org/sho ... ?id=127831 the version 7.0 of LibreOffice (coming soon) will offer two new NonVolatile functions RAND.NV() and RANDBETWEEN.NV().

[Villeroy]

Changing the original RANDBEETWEEN to be volatile will break existing spreadsheets.

[Lupp]

They did nonetheless.

Just checked some of my preserved versions. It happened with V 6.2.

(I also suspect it wasn't sufficiently considered. Do you remember the "Update-References-When-Sorting-Range-Of-Cells-affair" [Kohei Yoshida]? It must have happened around LibO V4.2.)

[Villeroy]

Luckily I don't use any spreadsheets except for "cheap database reporting".

[Lupp]

Luckily I don't use any spreadsheets except for "cheap database reporting".

Looking at your current avatar I would assume you had to cope a lot with spreadsheets the last few days.
Well, get lucky again!

[Villeroy]

Thank you for the heads up. I'm running with Pluto again.

[RoryOF]

@Villeroy: I preferred your butterfly! I felt it gave a certain gravitas to your postings.

[Villeroy]

Butterfly. Red and black. Majestic.

[ronanski]

ctrl + shift + (fn) F9 doesn't do anything...

[Zizi64]

ctrl + shift + (fn) F9 doesn't do anything...

______________________________________________
Open office 4.1.7 on mac os mojave 10.14.6

It works on Windows. You must know what hotkey recalculation will activize on your operating system. Try to check it from the menu.

[ronanski]

I seem to have a different menu system to you. Was gonna upload a screenshot but it says maximum size is 128Kb, never heard of an image file being that small

[Hagar Delest]

Was gonna upload a screenshot but it says maximum size is 128Kb, never heard of an image file being that small

Then look at the screenshot from the post above yours. It's only 48kB.
Just crop your screenshot, reduce the color depth if really necessary.

[ronanski]

well its already cropped to the size i need. I have no idea how to reduce colour depth

[Hagar Delest]

You can use a 3rd party site for sharing files.
With any picture manager there should be a menu dealing with colors and reducing the number of colors.