Random Numbers Not Divisible By 3

[mgirvin]

Dear Most Amazing Excel Team,

With a formula, how could I generate random numbers between 1 and 100 that are not divisible by 3?

I made this part of a formula that generates the numbers not divisible by 3:

MOD(ROW(INDIRECT("1:100")),3)<>0

But I am unsure how to use it to help to generate random numbers between 1 and 100 not divisible by 3.

Any ideas?

[Jon von der Heyden]

Hello Mike

Not very elegant but it's all I could think of:

PHP Code:

=INDEX({1;2;4;5;7;8;10;11;13;14;16;17;19;20;22;23;25;26;28;29;31;32;34;35;37;38;40;41;43;44;46;47;49;50;52;53;55;56;58;59;61;62;64;65;67;68;70;71;73;74;76;77;79;80;82;83;85;86;88;89;91;92;94;95;97;98;100},RANDBETWEEN(1,67)) 


[barry houdini]

You could use this formula

=SMALL(IF(MOD(ROW(INDIRECT("1:100")),3)<>0,ROW(INDIRECT("1:100"))),INT(RAND()*67+1))

confirmed with CTRL+SHIFT+ENTER

[LucB]

Try this Macro.

Option Explicit

Sub DivThreeOut()
Dim n As Long, i As Long

For n = 1 To 10
Randomize
i = Int(99 * Rnd) + 1
If i Mod 3 <> 0 Then
Range("A" & n) = i
End If
Next n
End Sub

[mgirvin]

Dear Amazing Smartest Excel Team In The World,

I love getting three different answers to the same problem because it helps me to see answers from different points of views; which in turn helps me to know more about Excel!

Thanks Jon von der Heyden, the formula may be less elegant than others, but understanding it is straight forward! And since I have to explain this to the person who asked me, that person may prefer this to a SMALL / IF formula.

barry houdini, I was thinking along these lines with the IF(MOD(ROW(INDIRECT("1:100")),3)<>0,ROW(INDIRECT("1:100"))),, But the SMALL and INT / RAND you added was the conceptual kick I needed!! EXCELlent formula!

LucB, Thanks for the VBA code. It does exactly what I want (and helps formula-geeks-know-little-about-VBA guys like me to see the broader Excel picture)!

You all are great!

[mikerickson]

This will return a random number between 1 and 100 that is not divisible by 3
=INT(RAND()*33)*3+ROUNDUP(RAND()*2,0)

[mgirvin]

Dear mikerickson,

Awesome! That certainly is the fastest calculating one. Let me ask you these questions:

Is 96 the greatest value that this can ever get:

INT(RAND()*33)*3

Is 2 the greatest value that this can ever get:

ROUNDUP(RAND()*2,0)

Is it possible to get a 100 with this formula? Or is 98 the biggest value?

I could not get 100 with this formula.

If it is true that 98 is the biggest, is there a way to tweak it. Because this would be the fastest.

[barry houdini]

Hello Mike.....and Mike....

Not very likely but, as RAND() can return zero it's possible that

=ROUNDUP(RAND()*2,0)

could also return zero, in which case you'd get a number divisible by 3, so I'd suggest revising the second part of Mike's formula along the same lines as the first, i.e.

=INT(RAND()*33)*3+INT(RAND()*2+1)

Of course that still can't return 100 so, perhaps not very elegant, but you could add an IF function like this

=IF(RAND()>1/67,INT(RAND()*33)*3+INT(RAND()*2+1),100)

[mikerickson]

The highest number that is returned by a formula similar to mine would =2 mod 3, so 98 is the highest possible result of the given forumula.
Change the 33 to 34 and the highest becomes 101.

[mgirvin]

Thank you houdini and mikerickson for the additional comments!