Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Excel command RANDBETWEEN() results — multiples of n only

Posted on 2016-07-23
Medium Priority
76 Views
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

LVL 43

Expert Comment

ID: 41726172
100+int((200-100)*rand())

100+int((200-100)*rand()/5)*5
0

LVL 33

Accepted Solution

Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41726174

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

LVL 33

Expert Comment

ID: 41726176
You may also use one of the following formulas as per your requirement....

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

OR

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

LVL 14

Expert Comment

ID: 41726220
=(RANDBETWEEN(100/5,200/5)) * 5
0

LVL 26

Expert Comment

ID: 41726615
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

Author Comment

ID: 41726656
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month10 days, 12 hours left to enroll