Excel command RANDBETWEEN() results — multiples of n only

Hello,

How would you configure the Excel command, RANDBETWEEN(), to only return multiples of some number, n?

For example, if you specify

    min = 100 & 
    max = 200,

RANDBETWEEN() will return an integer, n, for which 100 < n < 200 (or is it 100 <= n <= 200?) — not sure. But regardless, suppose you want the returned value to also be only a multiple of 5 (ie 105, 110, 115, etc)?

Thanks
WeThotUWasAToadAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please try this....

=MROUND(RANDBETWEEN(100,200),5)

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
100+int((200-100)*rand())

100+int((200-100)*rand()/5)*5
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may also use one of the following formulas as per your requirement....

=CEILING(RANDBETWEEN(100,200),5)

Open in new window


OR

=FLOOR(RANDBETWEEN(100,200),5)

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
wsh2Commented:
=(RANDBETWEEN(100/5,200/5)) * 5
0
 
Fred MarshallPrincipalCommented:
It appears that it's 100 <= n <= 200 (as confirmed by testing)

From Excel Help:
The RANDBETWEEN function syntax has the following arguments:
Bottom    Required. The smallest integer RANDBETWEEN will return.
Top    Required. The largest integer RANDBETWEEN will return.
"Will return" is the key here....
0
 
WeThotUWasAToadAuthor Commented:
Thank you for the several and varied responses. There are indeed many ways to skin a cat.

Best solution to Subodh Tiwari (Neeraj) was chosen based on simplicity. Honorable mention to wsh2 for the cleverest solution and extra thanks to Fred Marshall for looking up something I could/should have taken 30 seconds to look up on my own. :P

Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.