Saturday, June 22, 2013

Generate Random Numbers in Excel

Do you feel need for inserting random/dummy numbers in excel cells?

Well, lot of times while making new reports or making new model in Excel I don't have actual data to work with. Sometimes back-end team is still working to get the data, sometimes it is dependent on someone else or sometimes you just have to create blank template and send it to business users who will enter the details and model should give the results based on the entered value by the end user etc.

Being the Excel users you all will know how difficult it is to check the formula without any values.

One can insert numbers manually in the cells. However, it will be very tedious task especially when you have lots of cells to fill.

Better alternative is to use formula. You can use below mentioned formulas to get the random numbers...

1. RAND
Syntex: =RAND()
It gives any random numbers from 0 to 1. One can use it with combination of ROUND function to get the rounded number up to specified decimals. Given below is the formula for getting random number from 0 to 1 having 2 decimals...
=ROUND(RAND(),2)

2. RANDBETWEEN
This function is similar to the RAND function, only difference is that you get the option of adding upper and lower limit within which the random numbers are returned by Excel.

Syntex: =RANDBETWEEN(lower limit, upper limit)

Example: =RANDBETWEEN(1000,10000) will return random number between 1000 to 10000

Note: Whenever there is change in any value of any cell, Excel recalculates values in all the cells having formula. This causes changes in the values having RAND or RANDBETWEEN formulas. If you don't want these values to get changed, remove the formula by pasting values immediately after getting random numbers.

No comments:

Post a Comment