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 ...
No comments:
Post a Comment