RAND Function - Generate Random 'Decimal' Numbers In Excel

In my last article I discussed about how to generate RANDOM numbers in Microsoft Excel which is very easy using the function RANDBETWEEN. You type the function in excel and provide the Bottom and Top number (as lower and upper limit) and you get the Random numbers.

But if you notice carefully all these randomly generated numbers are integer type numbers, therefore, in this article I will discuss with you how to generate Decimal Random Numbers.

One solution is to divide the output of RANDBETWEEN numbers by 10, 100 or 1000, i.e., in multiple of 10.

But the problem is the integer part of these numbers will reduce and the number will not be within bottom and top range.

For example, suppose the output of RANDBETWEEN is 4387 and we divide it by 10, it will turn into 438.7, divide by 100 it will be 43.87, further divide by 1000, the output will be 4.387.

Which is not desirable.

The solution is to use RAND function.

Lets first discuss about the RAND Function.

Syntax

The syntax for the RAND function in Microsoft Excel is very simple.

=RAND( )

Parameters or Arguments

No parameters or arguments require for the RAND function.

Note : The Microsoft Excel RAND function returns a random number which is greater than or equal to 0 and less than 1. The RAND function returns a new random number each time your spreadsheet recalculates, similar to RANDBETWEEN function.

Example :

To use RAND function type =RAND() and press enter and you are done.

RAND Function

But did you noticed integer part is missing, why ?

Because RAND function output is from 0 to 1.

So, you can modify your formula by multiplying 10 and you may or may not get the decimal number having integer part.

RAND Function

This is because if the output of RAND function is starting with 0 then the number will be without having any integer part.

Also notice that we have no control over these Random numbers i.e., no upper or lower boundary or limit is implemented.

The Solution :

The solution is to modify the formula as below :

=RAND()*(B-A)+A

Where B represents Upper limit or Top number and A presents the Lower limit or bottom number.

RAND Function

What is the function of (B-A)+A in RAND formula ?

The Ordering of Mathematical Operation teaches us that multiplication is first to be calculated in this formula and then addition and then Subtraction will be applied.

Ordering of Mathematical Operation

The steps are

       

1.   RAND generates a random number between 0 to 1

2.   This number gets multiplied by B and A

3.   New numbers are subtracted

4.   then summed up by A

Hope you have enjoyed this article ...

Keep reading, sharing & Stay blessed ...