How to generate random numbers based on a condition.

[sktneer]

hi,

Is is possible to generate random numbers in cells say A1 and B1 so that mod(B1,A1)=0 ?

For example I want a random number is cell B1 which is exactly divisible by another random number in cell A1.

Is is possible in excel to do this ? If yes, please help me.

Thanks in advance.

sktneer

[dilipandey]

HI Sktneer,

See the attached file and click on the blue button to generate your desired number.
Random number generation for MOD function.xlsm
I wrote below code to achieve the desired results;-

Please Login or Register  to view this content.

Regards,
DILIPandey
<click on below * if this helps>

[Dimitrov]

I hope I am not misunderstanding you, but imagine the follow scenario. There are two urns. In one urn, I have the set of strictly positive integers: 1, 2, 3, 4, and so on. In the second urn, I have the same set of strictly positive integers. Let us say that I reach into the first urn and pick out a number. If I then go to the second urn and pick out a number, and the draws are independent, then is there any way to guarantee that the first number is precisely divisible by the second number? It does not look like there ought to be any reason why it should. I could pick 9 from the first urn and a 2 in the second urn. There is nothing in this urn example that guarantees that the first number is divisible by the second number. So without any more structure to the problem, the answer is no, there is no way to guarantee that Mod(first number, second number) = 0.

[sktneer]

Thanks Dilip for your reply.
But I am unable to understand the code you have written.
Is there any way to write a formula within the worksheet to achieve this?
Thanks for taking pain.
Please let me know if I can write a formula to get the desired random numbers.
Thanks.

[dilipandey]

Hi sktneer,

Since you need to check a single suitable value among multiple values that would be entered in a cell, hence formula would not work and as per me, only solution is vba (macro). Thanks.

To understand the macro, open the excel, press alt + F11, adjust the new screen (code screen) so that you can see excel worksheet and code screen - both. Now keep pressing f8 and see how code gets executed line by line


Regards,
DILIPandey
<click on below * if this helps>

[martindwilson]

well if your not woried about the divisor being an integer you could just put something like

=RANDBETWEEN(1,100) in a1 and
=A1/RANDBETWEEN(1,100) in b1

[sktneer]

Thanks Dilip,
No doubt, you are a genius man.

[sktneer]

Thanks Martin for your help. But I wanted an integer in the divisor's place.
Thanks.

[shg]

VBA seems like overkill for this.

A1 =randbetween(1,100)

B1 =A1*randbetween(1,100)

[dilipandey]

you are welcome sktneer

Please mark this thread as [SOLVED].. thanks.


Regards,
DILIPandey
<click on below * if this helps>

[sktneer]

Hi shg,

Thanks a lot dear. You made it very simple and perfect that's what I really wanted. Moreover I am surprised that how simple is this approach to achieve the desired results. You are super GENIUS.