I need help with generating a formula on excel

Occasional Contributor

I need a formula to randomly generate a number between 1 and 5 but excluding the number in the cell before it. For example, if A1 has =RANDBETWEEN(1,5) and the number is 5 then I need a formula for B1 to generate either 1,2,3,4 but not 5. Then for the next collum it can either be 1,2 or 3 and for the next 1 or 2 and for the last, only 1.

 

I need help ASAP

 

9 Replies

@dihansa_u 

ok I'm not sure how you do it in one thing or even if you can.

However I did manage to get it to work sort of. See attached workbook
I did the following
First I made a table with the numbers 1 to 5 [A2:A6]

Then made a formula to randomly select one of those [J4 =INDEX(A2:A6,RANDBETWEEN(1,ROWS(A2:A6)),1)]
next in column B i made a formula to create a new list less the one in J4 [B2 =IF(A2=$J$4,"",A2)]
I copied this down.
K4 = =INDEX(B:B,INDEX(MODE.MULT(IF(B2:B6<>{"",""},ROW(B2:B6))),RANDBETWEEN(1,SUM(N(LEN(B2:B6)>0))))) which is an array formula so you have to hold down Ctrl and Shift the press enter otherwise it won't work.

I basically just repeated the process

@Wildecoyote1966 

It still doesn't work. 

 I want it to look something like this but with five collums. I'm also not given the formulas for this either.

Screen Shot 2022-03-14 at 2.44.47 pm.png

A couple of things
1. you should have said that in the first place as the thing I did has 5 columns and fits what you asked for.
2. upload the file not a screenshot.
did you try opening text editor?
I don't use Google sheets

@Wildecoyote1966 

It still doesn't work

I haven't done anything else
oh as in, I can't upload the file

@dihansa_u If you are using Excel for MS365 or 2021, the attached file is similar to what your file looks like. Though with a lot less formulae and much easier to maintain. I built it to allow up to 6 cards, but you can easily expand it. Change the number in A2 and see what happens when you change the number of cards. Press F9 if you want to re-run the randomiser.