Exclude numbers from randbetween() function based on previously referenced numbers in related cells

[appreciatethehelp]

I have 3 cells, A1, B1 and C1.
• I use RANDBETWEEN(1,8) in A1.
• I then want to use RANDBETWEEN(1,8) again in B1, whilst excluding the value of A1 from the possibilities generated by the RANDBETWEEN in B1.
E.G if A1 = 2, B1 cannot equal 2, it may only equal 1/3/4/5/6/7/8.
• Lastly, I want to use RANDBETWEEN(1,8) a third time in C1, whilst excluding the values of both A1 and B1 from the possibilities e.g. if A1 = 2 and B1 = 4, C1 may not equal either of these, it may only equal 1/3/5/6/7/8.

How might this be achieved? Differing methods are most welcome.

Obviously if preceding numbers lie either at the top or bottom of the number range, a simple IFS(<previous cell’s number>=1,RANDBETWEEN(2,8),<previous cell’s number>=8,RANDBETWEEN(1,7)) would suffice, but what about instances in which the previous cell’s number lies in the middle of the RANDBETWEEN range?

Thanks guys.

[karolus]

this tiny snippet would do the job for 3 cells in a row selected

from random import sample

def sample_of_three():
    doc =XSCRIPTCONTEXT.getDocument()
    doc.CurrentSelection.DataArray = [sample(range(1,9),3)]

[sokol92]

Example on Calc formulas (see column B).
RandBetween.ods (11.1 KB)

[mikekaganski]

Reminded me of this one: I Want to generate randomly 9 numbers in Excel Sheet cells 3X3 out of numbers - #13 by mikekaganski

[sokol92]

@mikekaganski

Reminded me of this one

Yes, it’s the same trick.

[appreciatethehelp]

Being a non-coder, this is the one I’ll mark as correct for now. I Haven’t the time nor knowledge to look into the validity of Karolus’ answer; I’ll leave it to someone with more knowledge to mark that solution as solved, and comment accordingly where necessary, at least for now.

Cheers.

[karolus]

And the pragmatically solution for non-Coders is:
fill the three Cells with =RANDBETWEEN.NV(1;8) and hit <ctrl><shift><F9> until no number repeats!