• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

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
0
WeThotUWasAToad
Asked:
WeThotUWasAToad
1 Solution
 
Saqib Husain, SyedEngineerCommented:
100+int((200-100)*rand())

100+int((200-100)*rand()/5)*5
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this....

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

Open in new window

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now