• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rand with decimals

bobhc

bobhc

Excel Ninja
Good day All

Using the RAND() function to get integers with decimals. The range is 50 to 60 I have used for formulas to try to understand what happens and why, I do not have a problem with “+ - /” but =RAND()*10*50 returns some results I do not understand as you can see the min is down to 6.68, but the rang is 50 to 60, Although these figures will change as the work book is updated


http://dl.dropbox.com/u/75495784/Rand%20with%20decimal.xlsx
 
It looks like your formula is incorrect. From XL's help file, it should be:

=RAND()*10+50


Note that this will never generate a value of 60, as RAND gives x defined as

0 <= x < 1

and thus we'll never reach upper boundary.
 
Good day Luke M

I have used the =RAND()*10+50 function but I used the * - / to see what the results would be, just curious as the the wide rage returned with the =RAND()*10*50 function
 
Breaking it down from XL help:

To get a range of random numbers, suggested formula is:

=RAND()*(b-a)+a


Where b is upper boundary and a is upper boundary.

Simplifying formula, it's really:

=RAND()*RangeOfNumbers+LowerBoundary


So, if RAND produced 0, the formula is just the LowerBoundary. As it approaches 1, it reaches the upper boundary by doing RangeOfNumbers + LowerBoundary.


In your formula, we have:

=RAND()*(b-a)*a

=RAND()*RangeOfNumbers*LowerBoundary


From this, we can see that what's happening is the RangeOfNumbers is just being increased, while XL thinks the lower boundary is set to 0

BadRange = RangeOfNumbers*LowerBoundary

=RAND()*BadRange + 0

Now, if RAND evaluates to 0, we'll get a 0, and as it approaches 1 it can get as large as BadRange allows it to go. This is why you have such a wide range of numbers. You changed the range from 10 to 500!
 
No worries bobhc, we all need it sometimes. One that happened to me recently:

http://chandoo.org/forums/topic/extract-all-the-numeric-values-from-the-right-of-any-alphanumeric-string


I went off on a complex formula, only to be reminded how powerful LOOKUP was. =P
 
Hi bobhc,


I think we can also use this formula can get random values in decimals:


Code: 
=RANDBETWEEN(50,59)+RANDBETWEEN(1,99)/100


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top