Example 1: What if you want to generate a real number, but you want to include possibilities greater than 1? That is, let’s suppose we want to produce a random real number between 0 and 500, including 0 but not 500. We can still use the RAND function, but our formula will also have to include an additional multiplier. Since RAND will generate a number between 0 and 1, multiplying the result by 500 will give us a random number between 0 and 500. The resulting function would be the following.
Example 2: Now, suppose that we want to produce a random real number between -500 and 500, including -500 but not 500. We can’t use the same method as we did in Example 1, since simply multiplying by a constant number would give us a value between 0 and that constant. Instead, we can create a formula that combines the RAND and RANDBETWEEN functions.
We know that the function RANDBETWEEN(-500, 499) will produce a random integer in our chosen range, but we want to be able to produce any real number, not just integers. We can accomplish this by adding RAND() to the prior function to obtain the following formula.
=RANDBETWEEN(-500, 499) + RAND( )
Additional Resources: For more tips and tricks, take a look at the ever-growing collection of Microsoft Excel user guides and tutorials available on Bright Hub’s Windows Channel. Learn design tips for various charts and graphs, how to use the LOOKUP function to query a table, how to create a dropdown list, and more. More resources are being added all the time so be sure to keep checking back.