Forum Discussion
I need help with generating a formula on excel
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
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
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.
- 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.
