Count rows that contains partial specific text

Count rows that contains partial specific text :

To count the number of rows that contains partial specific text.

Use formula =countif(range,"*"&text&"*"). 

Please note : 1) It is for to count non-case sensitive text. 2) It count the number of rows that consist specific text , not the occurrence of text. Let say, if text contains two times in a row then it count one instead of two.

Thank you!!

Change the marker symbol in a chart to your own favorite shape

Change the marker symbol in a chart to your own favorite shape:

All you need is to draw any shape in a worksheet from Insert>>Shape . Copy the shape (Ctrl+C), Select the bars and paste into it (Ctrl+P). And here, you get your favorite shape chart, show it in a presentation and make your boss or teacher happy.

Draw any shape in a worksheet from Insert>>Shape

Copy the shape (Ctrl+C), Select the bars 

and paste into it (Ctrl+P)

Lookup an Item value from a table.

Two way lookup :-
Index function returns a value from within a table or range and Match function finds position for an item.

If you need to find an item value from a table where you have multiple rows and columns, then you need first to find the position of a row and a column for a particular item.

To find the position of row - use match function..
To find the position column - again use match function.
To returns value from relative position of row and column - use index functiin.

As in our example - Finding position of Year 2003. Match function is used. Match 2003 in Range E4:E9 (a single column range) where type is 0 means finding exact match. Result would be 4 which means position of row is 4.

Next, Finding position of Quarter Q2. Match function is used. Match Q2 in Range E4:I4 (a single row range) where type is 0 means finding exact match. Result would be 3 which means position of column is 3.

Next, finding an item value of interestion of row and column . Use Index function where array is E4:I9 which is whole table including headings. Index(table_array, row position, column position). Result will be $350

Thank you!

Tip : Use Wildcard to Sum Cells

Use Wildcard to Sum Cells : 

Here is quick tip:

If you have multiple sheets of different stores sales data and the challenge is to make summary report by summing up all the Delhi stores sales or the Mumbai store sales etc.. Then here is a tip:

Use wildcard "*" in sum formula.

=sum('de*'!G4)

It will sum all state that is started with "De" having sales value in G4 cell of each sheet.

CONVERT function.

CONVERT function:

CONVERT function converts a number from one measurement system to another such as inches to centimeters , days into hours etc.

Syntax:
=CONVERT(number, from_unit, to_unit)

Example:
=CONVERT(A2,"day","hr") : Days into Hours
=CONVERT(A2,"day","mn") : Days into Minutes

Below are the units abbreviations:

Thanks

Convert Days to Hour or Days to Minute :

Convert Days to Hour or Days to Minute :

Excel consist a function called CONVERT function. By using CONVERT function you could convert the days into hours or days into minute or hours into minutes or minutes into seconds or hours into seconds or many more.

=CONVERT(A2,"day","hr") : Days into Hours
=CONVERT(A2,"day","mn") : Days into Minutes

Thanks

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 !!

Trick : Sort the IDs List

Trick : Sort the IDs List.

If you have the IDs column list as in our example A1, A2 … A100..A200 (Column C ) and you need to sort the list accordingly. Then, simple sorting wouldn’t helpful you anyways.

For that, you need to convert the list as A001, A002, A003…A100..A200 (Column D).

Create the list with this formula in Column D =LEFT(C3,1)&RIGHT("000"&RIGHT(C3,LEN(C3)-1),3)

 And then , Sort it with help of new list .

And Here you get the desired result.

Thank You !! :)

Trick : Replica of Exact Set of Formula Range.

Replica of Exact Set of Formula Range.

If you want the Exact Range Set Formulas. Then, here we have the simple trick for it.

Steps:

a) Select a Range of Formulas. In our Example it's D9:E11

b) After selecting the range, Group the Sheet1 and Sheet2(Sheet2 is blank sheet)-Press Ctrl and Select Sheet2. From Home tab menu, Select Fill >> Across Workbook >> Select Fill option from dialogue box. Click OK.

c) In Sheet2, you can see the Formula Range Set. Ungroup Sheets. In sheet2 Range of Formulas Selected. Press Ctrl+X to cut the range.

d) Then, Activate the cell D12 in Sheet2. And Press Enter. You can see Formulas set shift downward.
e) Again, Group Sheet1 and Sheet2-Press Ctrl and Select Sheet1. From Home tab menu, Select Fill >> Across Workbook >> Select Fill option from dialogue box. Click OK.

And it is done here. You can see the Replica of Exact set of Formula range (sheet1~D9:E11) in sheet1~D12:E14.

Thank You !!

Trick : Repeat Entries in a List - Fill the blank Cell With Repeated Text or Number

Trick to Repeat Entries in a List .

Fill the Blank Cells With Repeated Text or Number.

Steps:
a) Select a B:B column.

 b) Press Ctrl+G - Go To Dialogue Box will open.

c) Click on Special and Select "Blanks" option. Click OK.

d) In formula bar write =B3. And press Ctrl+Enter.

And here, you get the desired result. 

Now, Copy the column B:B and paste special it as a values.

Thank You!!

AutoCorrect Options

AutoCorrect Options: 

Repeat Complicated term on worksheets or workbooks, by just assigning short symbol for it.

If you are tired of entering complicated word many times , then the best way to make your work easy is to use AutoCorrect Options.
AutoCorrect Options, will replace the input text with corrected text. For that assign the Input text and Corrected text. As many times as you enter the input text , it will replace it with corrected text.

Steps:
a) Click on Office >> Excel Options >> Proofing >> AutoCorrect Option
b) On replace enter the corrected text (original text), in our example it is - Worcester, Massachusetts. And on With: option enter the short symbol , in our eg. it is- W, MA
c) Click on Add button, and close.

Now , as many times as you enter W, MA in a cell or in statement. It will automatically replace it with Worcester, Massachusetts.

If you, want to delete the assign symbol. Follow the same steps a) & b) above. Click on the symbol and then click delete button

Thank you !!

Near Number From the List of Numbers

Near Number From the List of Numbers

To find the very near value from the list of numbers in a row,

Here, we have array formula
AC3= '{=IFERROR(INDEX($C3:$Y3,MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0)),"")} . For curly brackets , use Ctrl+shift+Enter

If Header , you want to show , just change the Index range with dollar sign(F4) to make it fixed for other cells when you drag down the formula.
AD3 = {=IFERROR(INDEX($C$2:$Y$2,MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0)),"")} . For curly brackets , use Ctrl+shift+Enter

Let's understand the formula here:

Formula: = {=IFERROR(INDEX($C3:$Y3,MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0)),"")}

What we are doing here, 
a) is to get the Difference of every value $C3:$Y3-$AA3. 
b) Converted every difference into all positive value (as difference can be negative too)with ABS function: ABS($C3:$Y3-$AA3).

c) Then, exact Match the minimum value value of above with the range of absolute difference values. MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0))
d) Match will find the position of the value ,  get the value or header with INDEX function
{=IFERROR(INDEX($C3:$Y3,MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0)),"")}

Press Ctrl+shift+Enter for curly brackets as it is array function.

Have a Nice Day!!

Ratio

Ratio:

Ratio : Number1:Number2

G column , we used GCD function =D4/GCD(D4,E4)&":"&E4/GCD(D4,E4)

H column , we used Text & Substitute Function =SUBSTITUTE(TEXT(D4/E4,"#/######"),"/",":")

Text function is suitable for negative number also, whereas GCD function is Not.

GCD function stands Greater common factor.
syntax : GCD(number1, [number2], ...)

Example: GCD(100,200) = 100
When you divide 100 by 100, its 1
& 200 by 100, its 2.

Since we need the ratio, by concatenate the results by using concatenate function or &":"&.

So, our formula returns as =D4/GCD(D4,E4)&":"&E4/GCD(D4,E4)

For H column Formula,  Using Text function TEXT(D4/E4,"#/######"), returns the result of maximum divided into the format "number1/number2". Example for if 100 is divided by 200, then text format will return 1/2 which is the maximum possible divide.
Since we need the result in 1:2 insteaed of 1/2, so we are using SUBSTITUTE function for it to substitute"/" by ":".

Our formula will be then,
=SUBSTITUTE(TEXT(D4/E4,"#/######"),"/",":")

Thanks

Split a number into integer and decimal portions.

Split a Number into Integer and Decimal portions.

For integer, you could also use formula =INT(D4). But since INT function is for only positive numbers. So, we are using here TRUNC function, =TRUNC(D4,0)

And for decimal , it is =MOD(D4,SIGN(D4))

Sequence of Months parallel to sequence of number.

Get Sequence of Months parallel to sequence of number.

In previous blog :
For sequence of number we used MOD function.
C4  =MOD(C3,12)+1

For, Sequence Month:
Formula: =TEXT(DATE(2000,C4,1),"mmm") in E4 cell.

Drag down the formula, you will get Months in series.
From Jan to Dec , then start jan to Dec and so on.

Repeate a Sequence of Number

To Repeat a sequence of number, we could use MOD function for it.
For Example , We need a column of sequence 1,2,3,4,5 and then start from 1,2,3,4,5 and so on..

For that use formula :
C4 = MOD(C3,5)+1, Where C3 is blank .
Drag down the formula ..

MOD function (MOD( number , divisor ) >> returns the remainder after a number is divided by a divisor. 
In our example 5 is the divisor, when number reaches to 5 in C9 cell  , it make it zero and since we are adding it with 1, our series again started it with one.

Thanks