Mar 13 2022 07:24 PM
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
Mar 13 2022 08:27 PM
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
Mar 13 2022 08:45 PM
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.
Mar 13 2022 08:56 PM
Mar 13 2022 09:03 PM
Sorry
I can't upload the excel file, but I converted it into a google sheets
Mar 13 2022 09:07 PM
Mar 13 2022 09:13 PM
It still doesn't work
Mar 13 2022 09:27 PM
Mar 13 2022 09:30 PM
Mar 14 2022 01:07 AM
@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.