Time Difference in Decimal

Time Difference in Decimal

Lots of time, we need to calculate the difference of time where we mistakes a lot.

You can simply subtract the difference, which is correct but not very useful or meaningful.

Here, I covered the time difference specifically to show the minutes part.

1) you can prefer example 30 minutes, which is 00:30. When you sum up two 30 minutes , it will be 60 minutes which is obvious an hour, or

2) you can prefer half an hour in 0.50 format. So, when you sum two half an hour it will show complete one hour (i.e 1)

I personally prefer 2nd one, which is easier for me for further calculations.

Option first formula: TEXT(C2-B2,"hh:mm:ss")
Option second formula: MOD(C2-B2,1)*24

Thank you!!

Sum Without Error

Sum Without Error:

If the Column contains error then summing up with formula
=sum(range), will show an error too.

First, get all the error as blank and then sum it.

For that use array function, 

{=sum(iferror(range,""))}

For array function write in formula bar

=sum(iferror(c4:c17,"")) and press ctrl+shift+enter

Similarly you can do this with average, count, max or min function

Thanks

Find First Non-Blank in a range

Find First Non-Blank in a Range :-

Let's suppose you have a range as in our example, C2:H2, C3:H3 and so on.. And in each range , you want first non-blank value.

For that, you can use formula:
=INDEX(C2:H2,MATCH(FALSE,ISBLANK(C2:H2),0))

Lets break the formula for better understanding:

1) ISBLANK(C2:H2) >> It brings the result as True or False.

2) MATCH(FALSE,ISBLANK(C2:H2),0)) >> Here , we are using match function to match the False with isblank as we have to find not blank cell value.
0 ('zero'), represent Exact match.

It will give you the position of first non-blank cell.

3) After that index function gives you relative position cell value.
INDEX(C2:H2,MATCH(FALSE,ISBLANK(C2:H2),0))

Thanks!!
Keep Practicing!!

Calculate the Age of a Person in Excel

Calculate the Age of a Person:

To claculate the age of a person, you can use TEXT function.
Formula to calculate the Age of a person:
=TEXT(NOW()-A3,"yy"" years ""m"" months ""dd"" days """) - where A3 is date of birth (DOB)

Formula is based on:
1) First , Calculate the difference of today date and date of birth i.e NOW()-A3
2) Then , format it with Text function in years months and days. TEXT(NOW()-A3,"yy"" years ""m"" months ""dd"" days """)

YEARFRAC along with INT function can also be used to find the age of a person (no. of years)
INT(YEARFRAC(A3,TODAY()))

Although DATEDIF function is not documented in Excel 5, 7 or 97, but it is in 2000.
You can also use DATEDIF function to calculate the age of a person.

DATEDIF(A3,TODAY(),"y")
DATEDIF(A3,TODAY(),"ym")
DATEDIF(A3,TODAY(),"md")

Thanks

Random Phone Number in Excel

Create Random Phone Number:

Formula to Create Random phone number:
=RANDBETWEEN(1000000000,9999999999)

Enter above formula in any cell for random phone number.

And , After that Format the cell as phone number (Right Click>> Format Cells >> Number >> Special >> Phone Number).

Create a Bell Curve Graph

To create a Bell Curve / Normal Distribution graph,

1) First we need to calculate the x-axis and y-axis.

2) For x-axis, Range is from Mean-3*SD to Mean+3*SD.(Data1 (E:E), Data2 (U:U) )
In our Example, Mean-3*SD is -78.89. So E2 = -78.89, E3=E2+1 E4=E3+1...so on . Add 1 till it reaches to Mean+3*SD i.e 152.89

3) And For y-axis , use formula =NORMDIST($E$2:$E$233,$C$2,$C$3,FALSE) in F column for Data1 , or
=NORMDIST($U$2:$U$152,$T$2,$T$3,FALSE) in V column for Data2.
syntax: =NORMDIST(x, mean, standard_dev; cumulative)

So, we have created 2 column for each data set. E2:F233 for Data1; U2:V152 for Data2.

4) Select E2:F233. Click on Insert tab >> Select scatter graph (scatter with smooth line). And here, we are done with Bell Graph for Data1.
Similary, Select U2:V152. Click on Insert tab >> Select scatter (scatter with smooth line) for Data2.

Optional, to overlap graph into one another . Click on Data2 graph. Press Ctrl+X to cut the data, then Select the Data1 graph and press Ctrl+V to paste into it.

Thank You!!

Combine Multiple Columns into One Column without Macro.

Combine Multiple Columns into One Column without Macro.

To Combine Multiple columns(A2:F21) into one column(K2:K121) without macro, it is convenient to use OFFSET function .

For Offset function ( K Column), we need Rows(I:I) and Column(J:J) number.

Steps:
1) Column H is for S.No.. Start H2 fom zero & for H3 formula is =H2+1 and drag down the formula. In our Example, we have 120 entries - sno is 0 to 119.

2) Column I is Adjusted Row, formula in I2 is =MOD(H2,20). Since each rows has 20 entries, that's why we used 20 in MOD function.

3) Column J is Adjusted column, formula in J2 is =INT(H2/20). 

4) Column K is Combined list, formula in K2 is =OFFSET($A$2,I2,J2).

Thank You !!