In a list of random numbers - 1 should never be the first

[ElmerS]

Hi,

I have generated a set of 6 cells (A1:A6) with =RAND()

In B1 I used =RANK(A1,A$1:A$6) (copied down to B2:B6) in order to get numbers fro 1 to 6 in random order and every press on F9 generates a new list.

The request is that the number 1 will never appear in cell B1 (it should always appear in one of the cells B2 -> B6).

I assume the solution involves VBA but I like to hear your opinion.

Thanks, Elm

[teylyn]

hello Elmer,

can you explain what should actually be in B1, then? Can you post a file with a few scenarios and the expected results in column B?

If you rank the random numbers of column A in column B, then then B1 will have the smallest (or largest, depending on your rank settings) number. If it's a 1 then it's a 1.

Explain what you are trying to do, please.

[ElmerS]

Thanks teylyn.

The matter a fact, I still don't have anything in a WB (what I wrote was theoretical).

You understood me well.

The number 1 can and should be always in one of the other 5 cells (B2 -> B6)

B1 will always have one of the remaining numbers (2-6) except 1.

My guess is that this should involve a macro which will generate 6 random numbers and pput them into an Array and then check if the first number is 1 the code will either run again or change places between the first number and the second number.

I once saw a UDF which was typed into 6 cells - the 6 cells were selected and then the UDF with its arguments were typed into the Formula bar.


Thanks, Elm

[teylyn]

I still don't understand.

If the 6 numbers generated by the rand() are 1,2,3,4,5,6, and you want to rank them in B1 to B6: what do you expect to see as the result, if B1 is not allowed to show a 1????

Again, please mock up a workbook and show us a few scenarios of your expected results.

[ElmerS]

Well, here is an example.

Try to slowly press <F9> several times and look at column B after every press.

After 10-20 times you will probably see that 1 is located in cell B1.

This is what I want to avoid.

The original request is not mine so please don't ask me why not to press again until 1 is not in cell B1.

1 should NEVER be present in cell B1 even after pressing 10,000 on <F9>

Eventually, I don't care how the 6 numbers are generated - by RANK or by VBA.

I hope this is clear now.

Elm

[teylyn]

Elmer, this is not clear at all. Please give an example of what you would want to see in B1 to B6 for the situation where the formula will calculate a 1 for B1.

[ElmerS]

Well..., this makes me think more and more that it can be acheived only with VBA.

If the result, is 1,4,2,3,6,5 then I want it to become: 4,1,2,3,6,5 or 2,1,4,3,6,5, or 3,1,4,3,6,5,
OR EVEN 6,5,4,3,1,2 and so on...

When I say BECOME I don't mean by pressing <F9> again as by pressing <F9> again and again there would be no question at all.

Every combination will be fine AS LONG AS the number 1 will not be in the first cell.

If this is not clear, I have no farther explanations. sorry...

Elm

[daddylonglegs]

Here's a formula solution - no helper cells

In A1

=RANDBETWEEN(2,6)

Then in A2 copied down to A6

=SMALL(IF(COUNTIF(A$1:A1,{1,2,3,4,5,6}),"",{1,2,3,4,5,6}),INT(RAND()*(6-ROWS(A$1:A1)))+1)

[daddylonglegs]

To avoid RANDBETWEEN A1 can also be

=INT(RAND()*5+2)

[ElmerS]

DLL,

This is exactly what I was after.

Thanks a lot.

Elm

[squiggler47]

Try this :-

You may need to turn on ciruclar references in options, just tick the check box

[ElmerS]

Thank you too.

I will stick to the solution presented by DLL.

Elm