Excel command RANDBETWEEN() results — multiples of n only

WeThotUWasAToad
WeThotUWasAToad used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
100+int((200-100)*rand())

100+int((200-100)*rand()/5)*5
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please try this....

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

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Commented:
=(RANDBETWEEN(100/5,200/5)) * 5
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....

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial