Count number of words in a cell

Count number of words in a cell :-

Here in below example we have list of name, we need to count each word and skip blank, if any.

Formula:
=LEN(TRIM(A3))-(LEN(TRIM(A3))-LEN(SUBSTITUTE(TRIM(A3)," ","")))

Explanation:

• TRIM function used to remove all spaces from text except for single spaces between words.
• (LEN(TRIM(A3))-LEN(SUBSTITUTE(TRIM(A3)," ",""))), function used to count single spaces between words. As SUBSTITUTE function here we used to delete spaces between words and LEN function to count the trim text with and without spaces.
• LEN(TRIM(A3))-(LEN(TRIM(A3))-LEN(SUBSTITUTE(TRIM(A3)," ",""))) , subtract the length of trim text with no space trim text.

Hence, we get our desired result i.e total number of words in a cell.

Thank You !!

Lookup from the duplicate data and skip correspond blank cells.

Lookup from the duplicate data and skip correspond blank cells.

Since Vlookup have some limitation that suppose if we do have lots of repeated data then lookup functions used first cum data and listed correspond cell data . In this blog, I have lots of repeated "email" data and I need to list correspond "address". And, "address" column consists many blanks cells.

Example: In A2 i.e email column : sid@xyz.com and correspond D2 i.e Address column : NIL (Blank) , whereas A17 : sid@xyz.com and D17 : 7 street

If we used VLookup or Index match function the result would be blank as it comes first, but we need to skip blank cells and use non blank cell i.e D17 : 7 street.

To rectify this problem, I used CONCATENATE function, where I concatenate email and address and the cells which consists only email are ignored.

COLUMN A : Duplicate Email data

COLUMN D : Address data where some address cells are blank.

Steps: 

First , I created unique emails data . 

Formula :

COLUMN F: Unique Email Data

{=IFERROR(INDEX(combined_email,SMALL(IF(MATCH(combined_email,combined_email,0)=ROW(INDIRECT("1:"&ROWS(combined_email))),ROW(INDIRECT("1:"&ROWS(combined_email))),""),ROW(INDIRECT("1:"&ROWS(combined_email))))),"")}

For above formula explanation please visit below link:

http://myresolution-to-learn-excel-daily.blogspot.in/2013/12/creating-unique-list-of-variable-and.html

where , combined_email : A:A

COLUMN H: Concatenate Column A and Column D and if it consists only email then ignore it (i.e blank).

=IF(CONCATENATE(A2,D2)=A2,"",CONCATENATE(A2,D2))

COLUMN I: Column H left part which is email.

=LEFT(H2,LEN(H2)-LEN(D2))

COLUMN J: Column H right part which is address.

=IFERROR(RIGHT(H2,LEN(H2)-LEN(A2)),"")

COLUMN K: For non blank Address, use index and match function and new column ie. I:J would be now lookup_array  

=IFERROR(INDEX($I$2:$J$53,MATCH(F2,$I$2:$I$53,FALSE),2),"-")

And to count repeated times that particular email appears use below formula
Column G:
=COUNTIF(combined_email,F2)

Thank you !

Keep leading Zeros

Add Leading Zeros to the Values :-

In many cases , Excel takes numeric code as numeric value and by default it removed leading zero from it. Example we have the original data as "001" EmpID and excel takes it as numeric value ie "1" removed leading zeros. So, here we have a simple formula to keep that leading zeros.

Formula : 

=REPT("0",3-LEN(A2))&A2

Another Formula :

=Text(B2,"000")

Please Note: 
While Entering the numeric text you could also use Single Quotation (') before Zero..

See below example :

Thank you !!

Age of a person.

Find an age of a person based on his/her date of birth :-

To find an age of a person, we need to subtract current date ( NOW() function) with his/her birth date. It brings the result in numerical values. Format it with TEXT function to convert the numeric values in years, months and days .



Formula :
=TEXT((NOW()-A2)&"","yy"" years ""m"" months ""dd"" days """)


See example in below print shot:-

Thank you !!

Frequency Distribution and Chart - discrete as well as grouped (By Pivot table, Pivot Chart).

Frequency Distribution - discrete  (ungrouped) (By Pivot table) :

To create frequency distribution through pivot table is quite easy .

We, already covered frequency distribution with complex formulas in below blogs. See the links for detail.

http://myresolution-to-learn-excel-daily.blogspot.in/2013/12/creating-unique-list-of-variable-and.html

http://myresolution-to-learn-excel-daily.blogspot.in/2013/12/grouped-frequency-distribution-class.html


Let's begin ,

Here we have data of student's marks .

From insert tab, create Pivot table. I choose "existing worksheet" radio button instead you can also choose "new worksheet" , choice yours.

In Pivot Table Field List, put Score in both "Row Labels" as well as "Values". Since we need "count of score" instead "sum of score", select "Value Field Settings.." as shown below. 

In "Value Field Settings" , from tab "Summarize Values By " select "Count".

And yeah ! here we done discrete frequency distribution . Simple !

Frequency Distribution (Grouped) :

Same procedure as with ungrouped frequency distribution. For Grouped data , we further need to add little step :

In PivotTable Tools , from Options tab select "Group Selection".

Group Selection Dialogue Box appears like as shown in below print shot. And in that dialogue box, enter starting and ending values with the width of classes lets say 5. 

And here we done with our grouped frequency distribution .

You can also customized grouping score with different width . Like if you want to grouped as "below 35" , "between 35 to 75" , and "above 75".

For that, same way select "Group selection" and group it manually . Like in this example, I used to enter middle class interval i.e for "between 35 to 75" which automatically make the classes of "below 35" and "above 75". Enter in "Starting at" value 35 and in "Ending at" value 75. and in "By:" value 20 (difference of 75 and 35).Click Ok.

Result are shown in below printshot :

Frequency Chart, can be done with pivot chart.

Select pivot chart instead of pivot table from insert tab . As pivot chart opens both pivot table and pivot chart. Then in Axis field put "Score" and in Values field put "Count of Scores". From Group Selection in PivotTables Tools, grouped it as per your choice, here the magnitude of classes is 5 . Once, this is done pivot chart shown like below:

Thank you !!

Creating Text Histogram

Text Histogram : 

Text Histogram can be created with the help of IF and REPT function .
 Example shown in below print shot :

In Example , we calculated Balance of Trade (column D) by subtracted column C from column B, which comes some positive value as well as some negative values. Column F, G and H are outlined as text histogram. So, Column F is for negative values. Column G for Years and Column H for positive values.

Column F formula : =IF(D2<0,REPT("n",-D2),"")
Column G formula : =A2
Column H formula : =IF(D2>0,REPT("n",D2),"")

In Column F and H, we are repeating text  "n" equal to the values in Column D. Difference in both column formula is just comes with negative sign as column F is for negative value and for text "n" repeating times we need to convert it as positive.

Thank you !!

Count Total Unique Values in a Data.

Count Unique Values : - 

Suppose we have a data as shown in below printshot, where we have repeated occurrence of alphabets (A,B,C and D - seven times each as in example ) and we need to count every alphabet only one time. i.e Result should be 4 .

 

So, to count unique values , here is a formula :

{=SUM(1/COUNTIF(DATA,DATA))}

In nutshell , above formula implies fractionate the repeated occurance and then sum it . Like if "A" occurs seven times, then fraction it i.e 1/7 is 0.1428571 and sum it . Since for every "A" in data its fraction should be 0.1428571 and when you sum it 7 times 0.1428571 it obviously would come 1. And same case with other alphabets. Summing all the 1's would give us the result of total unique values in a data.

Two-Column lookup

Sometimes we require to lookup the information based on the information of two columns .

In below Example printshot  we need to know the Total sales of Ajay Arora in North region .

As we can see the Employee name and the Region is in different columns ie. in Column B and Column C. So, we need to concatenate the contents of the both column range (i.e B1:B17&C1:C17) as well we need to concatenate the particular information (i.e H1&H2) which we need to find in the concatenated column for the purpose to lookup the TotalSales value which is in column E.

It is an Array formula  as shown below:-
{=INDEX(E1:E17,MATCH(H1&H2,B1:B17&C1:C17,0))}

Removing title from Names.

Removing title from names :-

If we do need to remove common title (Mr., Mrs., Dr.) from a name . Then the following formula will be helpful:-

=IF(SUMPRODUCT(--ISNUMBER(FIND({"Mr.","Mrs.","Dr."},A3))),RIGHT(A3,LEN(A3)-FIND(".",A3)))

Find the location of data which contains maximum value.

Find the ROW number of maximum value :-

To find the ROW number of data which contains maximum value.

For example See below printshot.

The following array formula returns the row number of the maximum value.
{=MIN(IF(data=MAX(data),ROW(data),""))}

In case , if we need to find the cell address of the maximum value in the data range. Then, add  ADDRESS function to above formula where ROW number that are found is the first argument of it , another argument required is COLUMN number.

The following array formula returns the cell address of the maximum value.

{=ADDRESS(MIN(IF(data=MAX(data),ROW(data),"")),COLUMN(data))}