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.

Summing cells having Years and Months stored as years.month format in Excel

Yesterday, one of my friend asked me a question. It was quite simple, he had a data stored in specific way, and he wanted to do additions of few columns….

Question:

He had an Excel file, where data is stored in Years.Month format (i.e. 5 years 8 months is stored as 5.08) in two columns. He wanted to add these columns. Returned value should be in Years.Month format only. For e.g. 5.08 + 8.05 (i.e 5 Years and 8 Months + 8 years and 5 Months) the answer should be 14.01 (i.e. 14 Years and 1 Months). A simple addition formula in Excel will return 13.13 as Excel considers these values as normal numeric values.  Hence he wanted some way to do these additions.

I gave him the solution by writing below mentioned formula. Above mentioned situation may be common for many of us hence thought of sharing it here for others.

Solution:

We can achieve desired output by using below mentioned formula.  Assuming that 2 data items are stored in cell A2 and Cell B2, formula will be…

=IF(IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2))) + IF(MOD(B2,1)=0,0,VALUE(MID(B2&"0",FIND(".",B2)+1,2)))>=12,

VALUE(1+INT(A2)+INT(B2) & "." & RIGHT(0&IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2))) + IF(MOD(B2,1)=0,0,VALUE(MID(B2&"0",FIND(".",B2)+1,2))) - 12,2)),

VALUE(INT(A2)+INT(B2) & "." & RIGHT("0"&IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2))) + IF(MOD(B2,1)=0,0,VALUE(MID(B2&"0",FIND(".",B2)+1,2))),2)))

Explanation:

Logic: We are adding Numeric portion (i.e. the years) and the Decimals portion (i.e. the months) separately. If summation of decimal portion is greater than 12 then 1 is added into numeric summation (i.e. 1 is added into the summation of years as 12 months is equivalent to 1 year) and at the same time decimal summation (i.e. summation of month) is reduced by 12.

Finally after addition, both numeric and decimal portion are concatenated with a dot (decimal sign) and shown as numerical value using the VALUE function.

IF(IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2))): It returns decimal portion from the given value. E.g. for 5.05 it will return 5, for 6.10 it will return 10 and so on. If there is no decimal value then it returns 0. MOD is used to check if the given value has any decimal portion or not. If there is any decimal portion, MID function is used to take out only the decimal portion.

INT(A2): returns the Numeric portion of the given value. For e.g. for 5.05 it will return 5, for 6.10 it will return 6 and so on.

VALUE: function is used to convert text into values. All the operations are done by text functions and the final outcome is also a text. To convert this text into numeric value, Value function is used.