Counting Multiple Occurances of A Text within a String Over a Range...

What if you have a list of string and you want to compile a list of them contain certain word and you want to count a certain text that appears multiple times with a string. Lets examine the following list:

My name is faseeh faseeh
My name is not faseeh
My Name is faraz
My Name is fawwad
My Name is farooq


Each of the them contains "My Name", two of them contains "faseeh", all five contains "is". Now the question is that how will we find the string contains "Faseeh"? 


The COUNTIF() with wildcard can calculate the frequency of the text but it will not count for the text that appears twice within these string.

The following formula does the trick, Enter and Press Control+Shift+Enter, and drag down.

=IFERROR(INDEX($A$1:$A$5,SMALL(IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0),ROW($A$1:$A$5)),ROW(C1)),0),"---")

The Search() Function:

IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0)

The Search function looks up for the Lookup_Criteria, which is present in B1 (i.e Faseeh) in the Array A1:A5, if the search is gives VALUE# Error, the IFERROR() formula replaces the error with zero. This in retrun is feed to the IF() that return ROW() # for the Trues. 

=IFERROR(INDEX($A$1:$A$5,SMALL({1,2,FALSE,FALSE,FALSE,ROW(C1)),0),"---")

The Small() Function: 

The SMALL() function returns the first smallest value in the array, the result is feed to the second argument of INDEX() function that is a row number, with column offset equals to zero, if the result is an error the outside IFERROR() formula wraps it into and gives you "---". 

Hope this post helps you once again.

The SUMPRODUCT() function...

Introduction:

The SUMPRODUCT() function is a very important function when it comes to validiate multiple conditions and sum a certain range that satifies the given criteria. In this post i will explain and highlight some of the very commonly encountered situtation where SUMPORDUCT() function can be used.

The Syntex:

The MS Excel 2007 describes it as:

SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ...   are 2 to 255 arrays whose components you want to multiply and then add.

With Remarks that:
1. The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. 
2. SUMPRODUCT treats array entries that are not numeric as if they were zeros

Senario # 01: 

The basic usage of SUMPRODUCT() is when we try to multiple different ranges of equal diemntions and want to get their sum. The attached sample file has tab Senario # 01 that describes the process so it will easy if you download it:

Product Price Quaintity

A 10 50
B 15 55
C 20 40
D 25 35

For the given example that consitute the Range from A1:C5, we enter following formula in Cell C6 to get the PRODUCT+SUM of Price & Quaintity:

=SUMPRODUCT($B$2:$B$5,$C$2:$C$5)   

If you select the cell C6 and go to Tab Formula > Evaluate Formula it will take you from follwing steps:

Step 01: =SUMPRODUCT($B$2:$B$5,$C$2:$C$5)   
Step 02: =SUMPRODUCT((10,15,20,25,),(50,55,40,35))
Step 03: =SUMPRODUCT((500,825,800,875))
Step 04: =3000

Thus an additional column that had been need to multiply the Price and Quaintity has been avoided and we get the same result.

Senario # 02:

The SUMPRODUCT() function can be used to verifiy multiple conditions. Contining with the same example of Product, Price & Quaintity, we add some further details to elaborate this type of usage as well. Please switch to the second sheet named "Senario # 02" to understand this example:

Product Month Price Quaintity
A Jan, 12 10 50
B Feb, 12 15 55
C Mar, 12 20 40
A Jan, 12 25 35
B Feb, 12 35 15
B Mar, 12 40 20
C Apr, 12 45 27

First Situation: Summing the Quaintity for a particular month:

In such a case we will setup a cell to input the "Month" for which we want to sum the sales. The formula in this case will look like:

=SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))

For this formula to work we will enter in $C$11 the desired month eg "Jan,12", the formula will work like this:

Step 01: =SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))
Step 02: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12","Feb, 12","Mar, 12","Apr, 12",=$C$11)*(D2:D8))
Step 03: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12","Feb, 12","Mar, 12","Apr, 12",="Jan, 12"}))*(D2:D8))
Step 04: =SUMPRODUCT({True,False,False,True,False,False,False}*{50,55,40,35,15,20,27})
Step 05: =SUMPRODUCT({50,False,False,35,False,False,False})
Step 06: =85

Second Sitatuion: If we want to get "Total Cost" then we will multiply it with ($C$2:$C$8), the formula will look like then

=SUMPRODUCT(($B$2:$B$8=$C$11)*($C$2:$C$8)*($D$2:$D$8))

Step 01: =SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))
Step 02: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12",...,"Apr, 12",=$C$11)*($C$2:$C$8)*(D2:D8))
Step 03: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12",...,"Apr, 12",="Jan, 12"}))*($C$2:$C$8)*(D2:D8))
Step 04: =SUMPRODUCT({True,False,False,True,...,False}*{10,15,20,25,35,40,45}*{50,55,40,35,15,20,27})
Step 05: =SUMPRODUCT({500,False,False,875,False,False,False})
Step 02: =1375   

Third Situation: Consider the thrid sheet where we have to check for Month as well as week, the Month is present in columns while week numbers are present as the header row of the table. We have incorporated an option in the workshee that will look into the range of the weeks specified through input cell C7:C8, the month is specified in C9. The table looks like this:

Product Month Price 1 2 3 4 5
A Jan, 12 10 40 10 30 30 50
B Feb, 12 15 50 20 10 30 20
C Mar, 12 20 40 10 30 10 20

And the formula is: 

=SUMPRODUCT(($B$3:$B$5=$C$9)*($C$3:$C$5)*($D$2:$H$2>=$C$7)*($D$2:$H$2<=$C$8)*($D$3:$H$5))

Step 01: =SUMPRODUCT({"Jan, 12","Feb, 12","Mar, 12"}="Jan, 12"*($C$3:$C$5)*($D$2:$H$2>=$C$7)*($D$2:$H$2<=$C$8)*($D$3:$H$5))
Step 02: =SUMPRODUCT({True,False,False}*{10,15,20}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*($D$3:$H$5))
Step 03: =SUMPRODUCT({True,False,False}*{10,15,20}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 04: =SUMPRODUCT({10,0,0}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 05: =SUMPRODUCT({True,True,True,True,True}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 06: =SUMPRODUCT({10,10,10,10,10,0,0,0,0,0,0,0,0,0,0}*{True,True,Flase,Flase,Flase}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 07: =SUMPRODUCT({10,10,0,0,0,0,0,0,0,0,0,0,0,0,0}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 08: =SUMPRODUCT({400,100,0,0,0,0,0,0,0,0,0,..,0,0,0,0})
Step 09: =500

Fourth Situtation:  We can use "<>" operator to select everything else then a particular criteria, see the fourth sheet for this example: 

The table looks like following:

Product Month Price Quaintity
A Jan, 12 10 50
B Feb, 12 15 55
C Mar, 12 20 40
A Jan, 12 25 35

Lets assume that we can to sum the Total Cost for every thing else then "A" we will replace the equal sign "=" with "<>":

=SUMPRODUCT(($A$2:$A$5<>"A")*($C$2:$C$5)*($D$2:$D$5))

The formula will check the first condition and will give these result: 

Step 01: =SUMPRODUCT({"A","B","C","A"<>"A"}*($C$2:$C$5)*($D$2:$D$5))
Step 02: =SUMPRODUCT({False,True,True,False}*{10,15,20,25}*{50,55,40,35})
Step 03: =SUMPRODUCT({0,825,800,0})
Step 04: =1625

Conclusion: The SUMPRODUCT() function gives you flexibiility to check for multiple criterias and sum a range, in caes of unique value, it can also retrive one that meets your critieria, but being an array formula it works slower then SUMIF().

Creating a Search Styled List in Excel...

What if you have a list of string and you want to compile a list of them contain certain word. The feature is very common when we search for certain file in windows explorer through Search Option.

Lets examine the following list:

My name is faseeh
My name is not faseeh
My Name is faraz
My Name is fawwad
My Name is farooq

Each of the them contains "My Name", two of them contains "faseeh", all five contains "is". Now the question is that how will we find the string contains "Faseeh"? 

The following formula does the trick, Enter and Press Control+Shift+Enter, and drag down.

=IFERROR(INDEX($A$1:$A$5,SMALL(IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0),ROW($A$1:$A$5)),ROW(C1)),0),"---")

The Search Function:

IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0)

The Search function looks up for the Lookup_Criteria, which is present in B1 (i.e Faseeh) in the Array A1:A5, if the search is gives VALUE# Error, the IFERROR() formula replaces the error with zero. This in retrun is feed to the IF() that return ROW() # for the Trues. 

=IFERROR(INDEX($A$1:$A$5,SMALL({1,2,FALSE,FALSE,FALSE,ROW(C1)),0),"---")

The SMALL() function returns the first smallest value in the array, the result is feed to the second argument of INDEX() function that is a row number, with column offset equals to zero, if the result is an error the outside IFERROR() formula wraps it into and gives you "---". 

Hope this post helps you once again.

Looking up & Matching Repeated Values...

Look at the following table, how will you find the Lowest two values and the corresponding Person & Region? The Question had been less trickier if the 5% value had not been repeated twice. In this post i will show you how to retrieve the values that have duplicate appearance in the tables.

Person Region Score
Engineer 2         South 5%
Engineer 3         North 5%
Engineer 8         South 15%
Engineer 1         North 20%
Engineer 4         South 25%
Engineer 5         North 30%
Engineer 10 South 35%
Engineer 7         North 40%
Engineer 6         South 50%
Engineer 9         North 65%

Using the following formula does the task very well. Lets take it up and split to understand how does it works!

=INDEX($A$3:$A$12,MATCH(SMALL(IF($B$3:$B$12=$F$2,($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001),ROW(A1)),($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001,0),0)

=VLOOKUP(F4,$A$3:$D$12,3,FALSE)

Today we will discuss a technique that is useful in getting smallest of values while testing for certain conditions. The usually used formula for getting nth smalles value is SMALL() that works fine for simple tables where we are just interested in the number itself. But when it comes to find the nth smallest number and then finding something against that number then it becomes a challange because there could be multiple enteries of a same value. 

Lets me explain it with this table: 

If i try to found the first three smallest values for the above table we can safely conclude them to be 5%, 5% & 15%. But when we try to fetch the corresponding "Person" against these values using MATCH() function, the result is "Engineer2", "Engineer2" and "Engineer8". So why this. This is because MATCH() always looks for the first match that in encounter swhile looking for a values. so it does not distinguishes between 5% of Engineer2 & 5% of Engineer3!!

The solution to this problem is to make each of these 5% different from each other so that when MATCH() goes for a lookup, it differentiate between the First & the Second 5% respectively. In order to make them different we use following formula. 

The IF() in the above formula IF($B$3:$B$12=$F$2, ($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001) checks for every value in B3:B12 for whether it is "South" or not, if it is "South", the second argument of the function will pass a value that will have added value of ROW()*0.0000001. This results in addition of 0.0000002, 0.0000003, 0.0000004,...., 0.0000010, 0.0000011, 0.0000012 to the each of the %age values thus two 5s are now differentiate as they are 0.0500002 & 0.0500003 respectively, the logic is applied to the entire range. Note that not all the values in the resulting array will be numbers,  only those that are upto the creiteria will be values, rest will be FALSE.

The SMALL() function examines the resulting values [SMALL(0.0500002,0.0500003,0.1500004,0.2000005,...,0.6500011,0.0000012)]
and picks up the first smallest value. 

Now this value needs to be matched with an array of similarly generated values so that we can find its location. the same formula is feed to MATCH()'s second argument so that it can lookup for the values. Once these values are found, INDEX() finds out the corresponding value from Column A to reach the solution. 

Now this values can be VLOOKUP()-ed in the table to get the corresponding values-a thing that has other wise been impossible. 

I hope that you will enjoy the post...am waiting for feedback

Counting Strings Containing Certain Text

Some times we want to calculate how many strings contains certain text. Lets consider this example text:

shahjahankhanalibahadur
shahjahanalibahadurkhan
jahankhanalibahadurshah
shahjahankhanbahadur
jahanalibahadur

I want to find how many of above five contains the text "ali" the formula is simple!! We can use COUNTIF() with a little variation that allows us to "look into each of them for "ali" and then give count of them". Here is the formula:

=COUNTIF(A1:A5,"*"&"ali"&"*")

The result will be 04.

The same thing can be done using SEARCH() with following Array Formula:

=SUM(IFERROR(SEARCH(C1,A1:A5)>=1,0)*1)

This formula seraches for "ALI and gives an array of true & falses and errors, where it find or does not find the match. In order to avoid error message, the IFERROR() function is used to replace them with zeros. When these are multiplied with 1, it assure that all TRUE are replaced by one and then they are summed up by SUM() to give us 04 as a result.

Here is the Sample File

Triming a Text without a workbreak

Sometimes it is desired to trim a sentence to a given length so that i can be accommodated into a a cell. Lets say we have a sentence but we want to restrict its length to 80 characters. A solution could be to use LEFT() function that will take Text & Text Length as arguments and will furnish a 80 character long Text String but what if the 80th character ends up in middle of a word?  Lets understand it with an example, take the following sentence:

"MY NAME IF FASEEH AND I AM LEAVING FOR TEXAS. I WILL BE TRAVELING BY A LOCAL TRAIN THAT WILL TAKE 08 HRS TO REACH BOSTON."

This sentence has 121 characters including spaces. If i use LEFT() to find the first 80 characters, it will return following text:

"MY NAME IF FASEEH AND I AM LEAVING FOR TEXAS. I WILL BE TRAVELING BY A LOCAL TRA"

You see that the sentence does ends "naturally" rather it is terminated in middle of a word. The ideal solution would have been either to include the complete word "TRAIN" or stop before the word starts. In case we want to stop before the word "TRAIN" started (that should be the case because we restricted to limit of 80 characters) the length of the sentence would have been 76 characters instated of 80. (The " TR" portion being excluded}.

The following formula assured that "Word at the end of the sentence is not got chewed up by our formula"

=IF(LEN(A2)<=80,A2,LEFT(A2,MATCH(80,IF(MID(A2,ROW($A$1:$A$250),1)=" ",ROW($A$1:$A$250)))-1))

Here is the Sample Workbook

Using MS Excel Filters & Subtotal Feature in Maintenance Planning & Budgeting.

Excel 2007’s Filters can be used to plan your maintenance activity and hence maintenance budget. Filters provide us with option to filter in between two instances of time thus yield relevant data that you can use further.

The Filter Option in Excel 2007 can be accessed by going to Data Tab > Filter or Data Tab > Advanced Filters; however for this post we will keep ourselves restricted to Filter, that is more appropriate for our use.

We first need to setup a database in the shape of a list since filter work best with data in this shape. The Header row contains titles like S.No. , Machine, Item/Description, Worked Date, Life, Next Due, Rate, Quantity, and Total.

S.No will be used to keep data in chronological order as you enter it. It can be used for sorting a getting to the original shape of the list after it has been sorted for some other criteria. Machines, Item/Description, Worked Date are self-descriptive. Life should be in Months. Next Due is calculated by using EDATE() function that adds months to a certain date giving us next due date, Rate & Quantity are quoted as it is giving us Total in the last column.

Setting Up the List:

The list can be easily setup and is available in the Sample File.

Using EDATE() Function:

The function has following syntax: EDATE (start_date, months)

The Start Date is the Worked Date, a month is Life in months, and the formula is entered in column for Next Due.

Getting Data in between Two Dates:

Let’s assume that Today is 1^st of July and we want to find the maintenance activities that are due this month (July, 2012). This will help us manage the inventory required and plan our activities more effectively.

Go the Data > Filter > Date Filters > This Month

In fact the last step of this can be changes for Weeks, Years, Quarters, Day-Before, Today & Day-After, and In Between Any Two Dates. So you can plan for the maintenance activities in next quarter way ahead of it, you can have  detailed schedule using Months, Weeks even Days.

Example:

Let’s consider an example. Download the attached sheet. This sheet enlists few machines with data against each of them and details, along with quantity and price. We will use filter the data to see:

> What are the activities that are due this month, next month or this quarter?

> What has been the Maintenance Budget for this month, Next Month, This quarter etc?

Scrolling and following the procedure reveals that there is no maintenance activity / replacement activity is schdudeld this month, neither for the following month. However we needed some replacements in the last days of the first quarter of the year. See the following picture:

When we select the Totals Column we can see the Total as Auto-Sum or alternatively we could use subtotals for this purpose.

Go the Data > Subtotals

… To see the dialogue box shown in the picture below and add subtotals for each machine under column Totals.

Conclusion:

There are variations to this method, you can add more detail to the workbook adding more levels of detail to make thing more practical like Department, Lines, Machines, Assemblies, Individual Parts etc to pin point things.

Download the Workbook

Creating Gartner Hype Chart in Excel 2007

PLOTTING THE BASIC CURVE:

Since there is no specific equation available to plot the chart therefore I tried to create it in two parts:

1. First portion that resembles the Left half of a Normal Distribution.

2. Drawing a line using Curve from Shapes and then plotting points that follow the initially drawn curve. 

(The second half of the curve resembles something like an exponential function being plotted.)

The first step to plot this curve was to get a “Line Chart” on the sheet so that whatever we plot we can see how it looks like on the graph. 

1. Go to Insert>Chart>Scatter Plot and Press OK.

2. Once inserted, select the Chart, Right click and select the data present B3:B105 to be plotted as X-Value and A1:A105 as Y-Value.  

3. Select the Curve, remove the Markers, and increase the line width to 2.25, Line color to Blue.

This sets up the Basic Curve, not exactly as it was in the referred picture, but a partly acceptable approximation of original curve.

[Note: In order to plot the normal curve, the excel function NORMSDIST() has been used that has the Syntex: 

NORMSDIST  = (X, Mean ,Standard_Deviation , Cumulatve)

…has been used. With Mean (X) ranging from -4 to +1.2 (at a decrement/ increment of 0.2 on each step), we get the first half, the right side of the curve. We have got our first 27 points for the curve through this process but we haven’t plotted it on the same scale (-4 to +1.2) instead on 1~27. The final Excel Formula look like this:

=NORMDIST(X , 0 ,1 , FALSE)

…where X is -4.0, -3.8, -3.6 to 0 and finally 1.2  

The second half of the curve is plotted empirically using error & trail method. Once you get this curve, Copy this data & Paste Special > Values to get rid of the formulas and proceed with formatting of the curve.]

PLOTTING THE "YEARS TO MAINSTREAM ADAPTATION":

The points that are plotted on the basic curve have been divided into 04 categories i.e.: 

1. Less than 2 Years (Triangular marker with Yellow Fill)

2. 02 to 05 Years (Round marker with without any Fill)

3. 05 to 10 Years (Round marker with Light Blue Fill)

4. More than 10 Years (Round marker with Dark Blue Fill)

5. Obsolete before Plateau (Not Plotted)

We setup a table that contains points for each of the four; the Values of X & the Corresponding values of Y to be found by using a VLOOKUP() and a cell where we can enter Reason.

VLOOKUP() has following syntax: 

VLOOKUP = (Lookup_Value, Table_Array, Col_Index_num, Range_Lookup)

Here…

Lookup_Value: Is the value of X (1.0 to 21.40)

Table_Array: Is the corresponding values of Y from Column B (B3:B105) in Sheet1

Col_Index_num: Is 02 since we want to look into the second column 

…hence the final formula becomes =VLOOKUP(X-Values, $A$3:$B$97, 2)

 Using this formula we get all the points for all the four categories that are to be plotted on the graph. 

In order to plot these four series, select the chart, Right Click & select “Select Data”, Add a New Series and point to the values of X & Y to add that series to the chart. Same steps will work for rest of the three series. 

Now in order for series to appear as what they are in the original picture, select each of the series and: 

1. Right click, select Format Data Series > Line Color > No Line

2. While remaining in the same dialogue box, select Marker Option > Built In > Type to select the corresponding type (Triangular or Circular) & Marker Fill > Solid Fill > Color to select the appropriate color for the series. 

LABELING THE DATA:

Once we got the four series on our chart, we will have to “Name” each point as mentioned in Original picture. These Names must be entered in the space provided under “Reason” in the tables for each category. 

In order to do this, we have two options: 

1. We can either do it manually or

2. We can use an add-in that will do it for us.

Since there are many points to be mentioned on the graph, I preferred using add-in instead of manual work, but I will mention here both for the convenience of readers. 

LABELING DATA MANUALLY:

1. Select a single point on the chart; so that only one point is select (not entire series).

2. Point the cursor to the formula bar and link it through a formula to cell containing the desired label. (see this picture)

3. Once linked all the points you are done with labeling.

LABELING DATA THROUGH AN ADD-IN:

An add-in that makes labeling a lot easier is available from Rob Bovey, Application Professionals. The add-in looks like this when installed.  

It’s a free ware so fell free to download it and install (until you are using it on commercial basis). The add-in could help you by either “Add Labels” or “Manual Labeler”. I opted to use the “Add Labels” that will still make this easier for me, the following dialogue box is shown:

…keeping chart selected, select “Data Series” and Label Range (that is present under “Reason”) this will be finished. Repeat the task for all four data series. You have got labels to your four series. Adjust the position of the labels so that they denote overlap.  

ADDING VERTICAL LINES:

The vertical lines present can be plotted as “Scatter graph with line” plotted on secondary axis of the chart. The five lines actually are ten points, two for each of them, plotted on the same scale as that of the primary chart, but on secondary axis and connected through a line… 

In order to plot these five series, select the chart, Right Click & select “Select Data”, Add a New Series and point to the values of X & Y to add that series to the chart. Same steps will work for rest of the three series. 

Both primary & secondary axis should have one scale, in this case Values on X-Axis are going from 0 to 25 and that of Secondary Axis varies between 0 to 0.45. Once you are finished with these five lines, you may hide the axis. 

LABELING VERTICAL LINES:

Using the previously mentioned add-in we can label discontinues ranges as well. In this case, the point that needs to be labeled is actually the point touching the X-Axis, the lower end of the line. So select lower point of all fove lines and use “Manual Labeler” to add labels to it. See this pic:

The “manual labeler “ will ask you to select the series and the point to be labeled, once selected and pressed apply, the label will be placed on the point. You have to do it for all five points on X-Axis

(I used manual labeler here because labels are not contagious to each other, had they been, “Add Labeler” could have sufficed our need.)

THE FINAL STEP:

Increase the font size of the last five points so that they appear highlighted. Move the last line to the right most side so that you can only see the label but not the line. Place this chart on a separate sheet and freeze pan so that if any one scrolls the sheet, the chart retains its position. That all! 

That’s all from me. Thank You.