• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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