Formula Forensics No. 031 – Production Scheduling using Excel

Formula Forensics No. 031 – Production Scheduling using Excel:
Recently, Bluetaurean asked in the Chandoo.org Forums about ways to allocate work durations for various product lines across 24 hour days to create a daily schedule.
Both formula-based and VBA-based solutions were offered.
Today at formula Forensics we will take a look at the formula-based approach.
As always at Formula Forensics you can follow along, Download Here – Excel 2007-2013.

Set the Scene

Since one might encounter a similar need in a variety of contexts (manufacturing, engineering, project planning, etc.), we will look at a more general problem of allocating a set of tasks and corresponding durations to one or more days, as shown in the following diagram.
We will create two output views:

• One that is a flat list that can then be manipulated further using Excel’s Pivot table feature, and
• Another view that mimics a pivot-table (and is similar to a typical project Gantt view, but with actual values listed instead of a bar chart).

You can follow along using the attached Excel document. Download here Excel 2007+

Problem Specifics

• We have a list of tasks and their durations.
• We need to distribute the tasks to different days, without exceeding the maximum available duration in a given day.
• When the hours in a day are “used up”, we need to allocate the remaining task duration to the next day, and so on.
• On the other hand, if a given task does not use up all of the hours in a given day, we will need to assign more than one task for that day, provided the combined durations do not exceed the available hours for that day.
• In other words, we will need to split a task across one or more days, or combine one or more tasks into a single day, as needed, to maximize the work performed in a given day.

Developing the Approach

Before we tackle this problem in Excel, let us review how we might do this manually. Like most things, we might use the following three step process:

1. Take the first task and assign its duration to Day 1. If the task’s duration exceeds the maximum hours available in a day, allocate the portion of the duration that does not fit into Day 1 into Day 2.
2. Take the second task, and see whether it can fit into an existing day, or whether it needs to be distributed to multiple days
3. Etc. (OK… so that three-step process was a stretch!)

Statistics show that most people think in terms of IF-THEN-ELSE statements. So here it is…

For a given Day, and for a given Task,

If [Hours Not Allocated For that Task] > [Hours Available for that Day] Then

Set Duration for that Day as [Hours Available for that Day]

Else

Set Duration for that Day as [Hours Not Allocated for that Task]

End

Continue the above evaluation until all tasks have been allocated to days.

 

Of course, the above IF() logic can be condensed as follows:
MIN( [Hours Not Allocated For that Task] ,  [Hours Available for that Day] )

Putting it All Together: Output Option 1: Gantt-like View

Let us employ the above approach to create the Gantt-like view.
To make our approach more generic, we will use an Excel Name called “MaxHrsPerDay” to indicate the maximum available hours in a given day. (In the sample worksheet, it has been set to 24 hours.)
Our source data is setup as shown in the diagram below:

• Tasks are in the range A2:A5
• Durations are in the range B2:B5

We will create the output in a separate worksheet, in the range A1:E5 as shown below:

Put the following formula into cell A2 and copy down to A5:
=SourceData!$A2
(This formula is merely referencing the values from the SourceData sheet. The sample workbook also includes an approach to make this reference more location independent.)
Put the following formula in cell B2, and copy it down and right:
=MIN((SourceData!$B2-SUM($A2:A2)), (MaxHrsPerDay-SUM(B$1:B1)))

Setup the header row (B1:E1) as desired. (I have used text values for the header. You could also calculate the header text using formulas. Since that is straightforward, I will leave that as an exercise for the reader.)
Now let us look at what the formula in cell B2 is doing:

• SUM($A2:A2) is calculating the sum of the allocated durations for TaskA. (Please note the use of absolute and relative references. The formula is anchored on column A, but the starting row, ending row and ending column are free to expand.) SUM($A2:A2) returns zero since SUM() ignores text values.

- If you look at cell C2, the reference changes to SUM($A2:B2).

- In cell B3, the reference changes to SUM($A3:A3). You get the idea

• (SourceData!$B2-SUM($A2:A2)) calculates the difference between the duration for TaskA (40 in the example) and the hours allocated as of that point (0), to return 40-0=40.
• SUM(B$1:B1) is calculating the sum of the allocated hours for Day1. (Again, we are using a combination of absolute and relative references to keep the calculation anchored on column B.) In this case, the value is zero, since this is the first allocation for Day1.
• (MaxHrsPerDay-SUM(B$1:B1)) calculates the hours remaining (i.e. available) for Day1. Since this is for cell B2, the calculation returns 24 – 0 = 24.

That is it!
We put those absolute and relative references to good use!
This approach was easy because all we had to do was calculate the duration for a given task for a given day.

On the other hand, if we had to figure out what the Task was, or which Day it was, the calculation gets a little more involved. Since this is “formula forensics”, we would not have it any other way!

Putting it All Together: Output Option 2: A Sequential List of Tasks and Durations for Each Day (i.e. a Flat List)

As before, we will use the Excel Name “MaxHrsPerDay” to refer to the maximum hours in a Day.
As shown in the following diagram, we will turn the source data into a flat list of Days, Tasks and Durations:

Unlike with VBA, since a formula cannot choose which row and column to write its output, we have to set the formula in every cell where we suspect there might be a value.
In the above sample diagram, we copy the formulas from row 2 to row 9. However, row 9 shows “…” indicating that the list was completed by row 8.
Let us look at how to determine the value for Day, Task and Allocated Duration.
For ease of description, I have created the following Excel Names:
WorkList: =A2:A5 in the source data.
WorkDuration: =B2:B5 in the source data
While creating the Gantt-like view earlier, we were able to take advantage of the static “Day” and “Task” values to determine the Remaining Duration, Available Duration, etc. Since we now have to determine all three values (Day, Task, Allocated Duration), we will need some “helper” data.
We will add a column alongside the source data that shows the cumulative duration (for reasons that will become clear shortly), as shown in the following diagram:

Cumulative Duration is calculated as the sum of all durations up to a given row.

• For example, in cell C2, the Cumulative Duration is 40.
• In cell C3, the Cumulative Duration is 40+20=60
• And so on.

For ease of referencing, we will use an Excel Name called CumulativeDuration =C2:C5.

Let us look at why we need the “CumulativeDuration” helper column:

The circular logic problem

In order to determine the durations already allocated for a given day, we will need to know which Day it is.
We also need to know which Task we are trying to calculate the duration for.
So… do we calculate the Day or the Task or the Duration first?!! As you can imagine, that will soon land us in some circular logic.

Some helpful observations about the output:

• In column C of the output (on worksheet FlatList), the sum of allocated durations adds up to the total duration for all tasks. (No surprise here!)
• If every task had duration equal to the MaxHrsPerDay, you would have the same duration value for all days. (Not surprising, but interesting!)
• In other words, you could think of the Allocated Duration column as the total duration for all tasks, allocated MaxHrsPerDay at a time.
• Now we need a way to iterate through the duration values one at a time and account for the durations already processed. In other words, each value needs to contain all of the previous values. Welcome to an array of the cumulative durations!
• For example, in the cumulative array “{40;60;65;80}”, the value 60 already includes the previous value 40 in it. This allows us to subtract all durations allocated up to a given row, to get the duration value that is remaining to be allocated.
• Since Excel is good with numbers, we will base the calculation for AllocatedDuration and Tasks on the Duration values.
• By calculating the two values separately, we avoid the circular logic.

Let’s now look at the formulas for Day, WorkItem and AllocatedDuration.
It would be easier if we looked at the formulas in reverse order, starting with AllocatedDuration, then WorkItem, and finally Day.

Formula for “AllocatedDuration”

Enter the following formula into cell C2, ending with Ctrl+Shift+Enter, as shown in the following diagram:
=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration), “…”,MIN(INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration-SUM(C$1:C1) > 0, 0)) – SUMIFS(C$1:C1, B$1:B1,B2), MaxHrsPerDay-SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)))) Ctrl+Shift+Enter

Let us look at the formula closely (using the formula in row 2):

• SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)) -> This calculates the sum of all allocated durations up to the previous row, where the Day = current row’s day. Please note the use of absolute and relative references. They allow us to expand the range as we go down the rows, while remaining anchored to the first row.

- Since this is the first data row, C$1:C1 returns “Allocated Duration” and the ISNUMBER() function returns FALSE, and consequently, the IF() function returns 0.

- A$1:A1 returns “Day”, and the test A$1:A1=A2 returns FALSE. Please note that in this case, it does not matter whether A2 has a value in it, whether it has the value 1, etc.

- SUMPRODUCT() provides the result of FALSE * 0 = 0

• MaxHrsPerDay – SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)) -> This calculates the difference between maximum duration available for a day and the sum of durations allocated for the current day. In other words, it calculates the available duration for the current row’s day.

- In this example, the calculation results in MaxHrsPerDay (24 in our example) – 0 = 24

• SUMIFS(C$1:C1, B$1:B1,B2) -> This calculates the sum of all allocated durations for the current row’s task. Since B$1:B1 is the text value “Work Item”, the SUMIFS() returns 0. Again, it does not matter if B2 is blank or has a value like “TaskA”, since Excel correctly evaluates the condition whether B$1:B1 equals B2.
• SUM(C$1:C1) -> This calculates the sum of all allocated durations up to the previous row.
• CumulativeDuration — SUM(C$1:C1) -> CumulativeDuration evaluates to {40;60;65;80}. SUM(C$1:C1) evaluates to zero. As such, the expression evaluates to {40;60;65;80} – 0, or {40;60;65;80}.

- If we look at the calculation for this expression in cell C3 (the expression would be “CumulativeDuration—SUM(C$1:C2)”), we would get the result of {40;60;65;80} – (0+24) = {16;36;41;56}. (As you know, subtracting a scalar value from an array results in an array with each value reduced by the scalar value.)

- If we look at the calculation for this expression in cell C4 (the expression would be “CumulativeDuration—SUM(C$1:C3)”) , we would get the result of {40;60;65;80} – (0+24+16) = {0;20;25;40}

- As you can see, each successive calculation reduces the CumulativeDuration array by the amount of hours already allocated. By reducing the CumulativeDuration array in this fashion, we ensure that we do not “double count” a duration.

- If a value in the array evaluates to zero, it means the corresponding duration has been fully allocated. (In cell C3, the first value in the array is zero, indicating that the original 40 hours has been fully allocated.) We will put this knowledge to good use in the next expression.

• MATCH(TRUE, CumulativeDuration—SUM(C$1:C1) > 0, 0) -> The expression CumulativeDuration—SUM(C$1:C1) > 0 evaluates to ={TRUE;TRUE;TRUE;TRUE} because all values are greater than zero. By performing a MATCH() for TRUE, we are able to find the first location in the array that has a non-zero value.

- If we look at the result of this expression in cell C3, we get {16;36;41;56} > 0 = {TRUE;TRUE;TRUE;TRUE}

- If we look at the result of this expression in cell C4, we get {0;20;25;40} > 0 = {FALSE;TRUE;TRUE;TRUE}

- As you recall, the zero values (or FALSE) correspond to the durations that have been fully allocated, whereas, the non-zero values (or TRUE) correspond to the durations that have NOT been fully allocated.

- It is helpful to note that MATCH() returns the LOCATION of what it finds. As such, the returned location is that of the first duration value that has not been fully allocated! Since the CumulativeDuration array is the same size as the WorkDuration array, we will be able to put this returned location value to good use in the next expression.

• INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration — SUM(C$1:C1) > 0, 0)) -> By using the location value (of the first duration value that has not been fully allocated), we find the corresponding original duration value from the WorkDuration array.

- As we saw earlier, the expression “CumulativeDiration – SUM(C$1:C1)” reduces the CumulativeDuration by the duration values allocated to that point. However, the resulting array could have partial duration values as well. By referencing the corresponding duration value from the WorkDuration array, we ensure that we retrieve the original (full) duration value that was to be allocated.

• MIN(…) -> This expression calculates the value of MIN([Hours Not Allocated For that Task], [Hours Available for that Day])

- [Hours Not Allocated For that Task] is returned by INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration—SUM(C$1:C1) > 0, 0)) – SUMIFS(C$1:C1, B$1:B1,B2)

- [Hours Available for that Day] is returned by second half of the MIN() expression: MaxHrsPerDay—SUMPRODUCT((A$1:A1=A2)* IF(ISNUMBER(C$1:C1), C$1:C1, 0)).

- So, we essentially got back to the logic we started from, which is the same logic we used for creating the Gantt-like view as well.

• The remaining portion of the formula (the IF() check) determines if all of the hours have been allocated. If all hours have been allocated, it returns “…”.

- SUMPRODUCT(WorkDuration) -> This expression calculates the total of all work duration values. In cell C2, it evaluates to SUMPRODUCT({40;20;5;15}) = 80

- SUM(C$1:C1)>=SUMPRODUCT(WorkDuration) -> Determines if the sum of durations allocated up to that point is greater than the total for all durations. (Since this is part of an array formula, you could also use the SUM function in place of SUMPRODUCT. But I am partial to the SUMPRODUCT function!! So, unless you are in a competition where the winner is determined by the shortest formula, feel free to use either one!

Formula for “WorkItem”

Enter the following formula into cell B2, ending with Ctrl+Shift+Enter, as shown in the following diagram.
=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration), “…”,INDEX(WorkList, MATCH(TRUE, (CumulativeDuration-SUM(C$1:C1)) > 0, 0))) Ctrl+Shift+Enter

You are already familiar with most of the formula components since you saw them in the formula for AllocatedDuration. The only difference is that in this formula, we are returning a value from WorkList. (i.e. we locate the position of the first non-zero duration in CumulativeDuration array, and since that array is the same size as the WorkList array, we are able to find the first Task that has not been fully allocated.)

Formula for “Day”

Enter the following formula into cell A2, ending with Ctrl+Shift+Enter, as shown in the following diagram:
=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration), “…”, MAX( N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay), 1)) Ctrl+Shift+Enter

Let us look at the formula in detail (using the formula in row 2):

• SUMIFS(C$1:C1, A$1:A1, A1) -> This expression calculates the sum of all durations (in column C) where the Days (in column A) equal the previous day.

- In cell A2, this expression evaluates to “SUMIFS(“Allocated Duration”, “Day”, “Day”)” = 0. (Excel smartly ignores any non-numeric values in the first argument.)

- In cell A3, this expression evaluates to “SUMIFS({“Allocated Duration”;24}, {“Day”;1}, 1)” = 24.

• SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay -> This expression checks if the sum of all durations where the Days equal the previous day is greater than or equal to MaxHrsPerDay.

- In cell A2, this expression evaluates to FALSE

- In cell A3, this expression evaluates to TRUE

• N(A1) -> This expression returns the numeric value for its argument. Since N() returns zero for any non-numeric arguments, we use this function to return zero for the heading (“Day”) in A1. (Any numeric values are returned as is.)
• MAX( N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay), 1) -> The first argument of the MAX function “N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay)”returns the next increment for day, if the previous day has been fully allocated. Otherwise, it returns the same value as the previous day.

- In cell A2, this expression evaluates to MAX( N(“Day”) + (SUMIFS(“Allocated Duration”, “Day”, “Day”)>=24), 1), which evaluates to MAX( N(“Day”) + (0>=24), 1), which evaluates to MAX( 0 + (FALSE), 1), which finally evaluates to 1.

- In cell A3, this expression evaluates to MAX( N(1) + (SUMIFS({“Allocated Duration”;24}, {“Day”;1}, 1)>=24), which evaluates to MAX( N(1) + (24>=24), 1), which evaluates to MAX( 1+ (TRUE), 1), which finally evaluates to 2 since 1 + TRUE = 2.

Download

You can download a copy of the above file and follow along, Download Here – Excel 2007-2013.

Final Thoughts

While we used the same basic logic for both output options in this article, there are probably many other ways to tackle the age-old problem of production scheduling.
I would love to hear about some of your ideas, as well as ways to extend the concepts described here.
In the meantime, I wish you continued EXCELlence!
Sajan.

Other Chandoo.org Posts related to Scheduling

Here at Chandoo.org you can find the following related posts:
http://www.chandoo.org/wp/2010/11/18/scheduling-variable-sources/
http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/
http://chandoo.org/wp/project-management-templates/gantt-charts/

Thank You

This was Sajan’s second post at Chandoo.org and so a special thank you to Sajan for putting pen to paper to describe the technique here.
You may want to read Sajan’s first post here or thank him in the comments below:

Formula Forensics “The Series”

This is the 31st post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts: Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like Sajan has done above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

Use Indexed charts when understanding change [Charting Techniques]

Use Indexed charts when understanding change [Charting Techniques]:
Today, lets talk about indexing, a technique used to compare changes in values over time.

What is indexing?

Lets say you want to compare prices of Gold & Coffee over last few years. Gold price in 2011 (oct) is $1,655 per ounce. And now (sept 2012) it is $1,744. Like wise, Silver price in 2011 is $32.06 and in 2012 it is $33.61. How do we compare such diverse numbers?
Enter indexing.
First we need to calculate price of Gold and Silver in 2012 assuming their starting price is 100. This can be done with simple arithmetic.
We will get this:

Now, we can easily compare the prices. Looking at the indexed prices, we can conclude that both Gold & Silver prices have gone up by similar percentage (~5%).

When to use Indexing?

There are many good reasons to use indexed values. Some of the common reasons are,

• To compare values which are vastly apart – ex: price movements of gold, silver & coffee
• To understand growth (or non growth). Subtract 100 from any indexed value to know how much it has grown (or shrunk) compared to base value.
• To understand change with respect to a bench mark – ex: performance of a company with respect to stock market index.

For more detailed discussion on indexation & its applications, refer to this article by Paresh.

Indexed Chart Example – Commodity prices in last 5 years

Lets say you are a savvy commodity investor and want to understand how the prices of gold, silver, bananas and coffee have changed since 2007. Now, each of them have a different range of values and comparing all of them in same chart can be very confusing.
Let us index the values to 100 and then compare.
Step 1: Arrange your data.
Lets assume we have our data like this:

Step 2: First indexed value is 100 for all items
Step 3: Calculate next indexed value using simple formula.
See this illustration to understand how to calculate the indexed values.

Step 4: Make a line chart
Select the indexed values and create a line chart. And you are done!
Step 5: Format the chart
This is where you can unleash your creativity. Add labels, legend, format axis etc. Here is a version I came up with.

Download Indexed Chart Example

Click here to download example workbook & play with it. Poke the formulas & chart options to understand this better.

Do you use Indexed charts?

I use indexing technique often to compare various metrics in my own business. I also use these type of charts in various dashboards & client reports.
What about you? Do you use indexation as a technique to compare values? What other techniques you rely on? Please share using comments.

More charting techniques:

• Show both average & distribution of data in your charts
• Use multiple charts to give users a choice of analysis
• Analyze competition with scatter plots
• How to chart when you have lots of small & few very large values?
• Use small multiples (panel charts) to to compare lots of things
• More charting examples & charting principles

Excel Formatting Tips – Gangnam Style [open thread]

Excel Formatting Tips – Gangnam Style [open thread]:
Ever seen a glaring, over the top, wow-I-am-sooo-cool type of spreadsheet? Lets call them Gangnam spreadsheets!

Gangnam what?!?

If you have never heard about Gangnam style, do not worry. Just like you I too was living under a rock for about a week ago. Then I watched the awesome Gangnam style song. And now I am hooked. You can see it below (or here):
My Korean is just as good as my tap dancing – lousy and non-existent. But I can search. As per wikipedia, the song refers to

“Gangnam Style” is a Korean neologism that refers to a lifestyle associated with the Gangnam district of Seoul, where people are trendy, hip and exude a certain supposed “class”. … Psy likened the Gangnam District to Beverly Hills, California, and said in an interview that he intended a twisted sense of humor by claiming himself to be “Gangnam Style” when everything about the song, dance, looks, and the music video is far from being such a high class. In another interview with CNN, Psy added that:

“People who are actually from Gangnam never proclaim that they are — it’s only the posers and wannabes that put on these airs and say that they are “Gangnam Style” — so this song is actually poking fun at those kinds of people who are trying so hard to be something that they’re not.”

[more]

What has all this got to do with Excel?

Oh I am coming to the point. One of the key ingredients of being awesome in Excel is,

To make our Excel workbooks communicate best by avoiding over the top formatting, unnecessary bells & whistles and focusing on what our users want.

But Excel being a feature rich software, it does have various so called Gangnam styles - superfluous 3d effects, formatting options, charting choices and as such.

Today, lets talk Excel formatting – Gangnam style

Some of my favorite Gangnam formatting tips are,

• Using too many tab colors on your excel workbooks [how to do this]
  
  
• Overdose of conditional formats
  
  
• 3D charting effects [how to do this]
  
  
• Comment shapes and formats [how to do this]
  
  
• Rotated text
  
  

What are your favorite Gangnam formatting tips?

Go ahead and post a Gangnam formatting tip. Lets all make Excel a stylish place. Post using comments.

Bonus: Gangnam style ft. 3 kids & a dad with cam

As you can guess, my kids love the song. So yesterday evening we played the video on TV and they danced. See their awesome steps  below (or click here):
PS: Sowmya is my brothers daughter, the other 2 are ours.
PPS: The loud rept(“hehe hahaha”,20) kind of laugh in background is mine!

Formula Forensics No. 030 – Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence

Formula Forensics No. 030 – Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence:
This post is the first of hopefully many posts by Guest author Sajan.

Excel offers many ways to sort and group data. (If you have not explored Pivot Tables in Excel, I would highly encourage you to try them out.) However, sometimes it is necessary to be able to control the results using a formula.
The following is a technique to extract a sorted, unique list of items, displaying the most frequently occurring items first, while restricting the output based on some additional criteria.
As always at Formula Forensics you can follow along with a sample file Download Here Excel 2007-13

The Formula

=INDEX(List, MATCH(MIN(MODE.MULT(IF(Criteria*NOT(COUNTIF($E$1:$E1, List)), (COUNTIF(List, “<”&List)+1)*{1,1}))), IF(Criteria,COUNTIF(List, “<”&List)+{1}), 0))
Entered into cell E2 with Ctrl+Shift+Enter, and copied down.
(We will add in error checking later.)

Sample results can be seen in following figure:

“List” is a Named Formula for the source list. (A2:A13 in the example shown.)
“Criteria” is a Named Formula for the criteria to apply against the list. For example, (List<> “”)

Disclaimer: Since all of these formulas traverse the source lists, they can get very slow when applied to large lists. I am sharing the formulas more to illustrate the techniques than to endorse them as approaches for every situation. Please determine the suitability for your specific situation.

Before I explain the formula, let us start with some history!

Chandoo’s Technique

In an October 2008 article, Chandoo described an ingenious technique of using the COUNTIF() function to sort a list.
http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/

Oscar’s formula

Oscar Cronquist took it to the next level by describing a formula to create a sorted list using the same technique, in his March, 2009 article:
http://www.get-digital-help.com/2009/03/27/sorting-text-cells-using-array-formula/
=INDEX(List, MATCH(SMALL(COUNTIF(List, “<”&List), ROW(1:1)), COUNTIF(List, “<”&List), 0))
Entered into cell B1 with Ctrl + Shift + Enter, and copied down.
For example, the above formula turns {“DD”; “AA”; “QQ”; “CC”} into {“AA”; “CC”; “DD”; “QQ”}
The heart of Oscar’s formula is the COUNTIF segment where he converts the strings into numbers based on whether a given string is less than other strings in the list. (Please see Oscar’s site for a full explanation of his formula.)
The technique is so simple that you might wonder… why didn’t I think of that?!
That is the sheer genius of the technique!

Haseeb A’s formula

Recently, Haseeb A provided the following brilliant formula to extract unique items from a list, listing the most frequent items first:
http://chandoo.org/forums/topic/ranking-string-data-for-one-column
=LOOKUP(REPT(“z”,99),CHOOSE({1,2},”",IF(ROWS(E$4:E4)<=F$1,INDEX(costcenter,MODE(IF((costcenter<>”")*ISNA(MATCH(costcenter,E$3:E3,0)),MATCH(costcenter,costcenter,0)*{1,1}))),”")))

Haseeb’s formula returns a value for “Top n” (as specified in cell F$1).
To make it easy for explanations, I will shorten it by using the same Named Formula “List” as in Oscar’s formula, removing the check for “Top n”, and using the Named Formula “Criteria”:
=INDEX(List,MODE(IF(Criteria*ISNA(MATCH(List, C$1:C1,0)),MATCH(List,List,0)*{1,1}))) Entered with Ctrl+Shift+Enter into cell C2, and copied down
Haseeb’s formula produces output in the same sequence as the original list, allowing you the flexibility to sort it the way you like it!

For example, the formula turns {“QQ”; “AA”; “XX”; “DD”; “XX”; “DD”; “XX”} into {“XX”; “DD”; “QQ”; “AA”} since “XX” is the most frequently occurring item, followed by “DD”, then “QQ”, then “AA” (the last two presented in the same order as in the source list.)
The formula uses a few different techniques worth calling out:

• ISNA(MATCH(List, C$1:C1, 0)) is used to skip the items already included in the output. (Please note that the formula is setup in cell C2 and below, while the reference is for the cell up to the previous cell – C1. Also note the use of absolute and relative references to ensure that as the formula gets copied down, the range expands, but still remains anchored on cell C1.)
• MATCH(List, List, 0) is used to convert the strings into numbers (Excel’s forte). The MATCH function returns an array with the location of each string in the list. i.e. if a string is repeated, the same (first) location is returned for both occurrences of the string.
• MATCH(List,List,0)*{1,1} duplicates the result from the MATCH function into column 2 of the array. This is necessary for preventing errors in the MODE function, since MODE does not like it when there are no duplicates in a list. (For example, if List does not have any duplicate strings, MATCH would return a sequential array.)
• The MODE function returns the most frequently occurring number in a list. As such, the MODE(…) segment of the formula returns the most frequently occurring number from MATCH, after skipping the items already displayed in the output. Also, please note that the MATCH function returns the position of a string. As such, the value returned by MODE is the most frequently occurring position in the list.
• Finally, the INDEX function returns the item for the position returned by the MODE function.

A very clever formula! All packed into a small “footprint”!!

Putting it all Together

Combining the ideas from Chandoo, Oscar, and Haseeb:
Let us now look at my first formula that combines the ideas from Chandoo, Oscar and Haseeb. (i.e. a formula to produce a unique list, sorted alphabetically, and listing the most frequent items first, while restricting the output based on some conditions.)
=INDEX(List, MATCH(MIN(MODE.MULT(IF(Criteria*NOT(COUNTIF($E$1:$E1, List)), (COUNTIF(List, “<”&List)+1)*{1,1}))), IF(Criteria,COUNTIF(List, “<”&List)+{1}), 0))
Entered into cell E2 with Ctrl+Shift+Enter, and copied down.
In the sample worksheet, Criteria is a named formula set to =(List <> “”)
Later on, we will look at expanding this criterion.
The results from the three formulas can be seen in the following figure.

(By the way, the “count” shown in the figure is the count of the adjacent item in the List.)
Let us look at each segment of the formula:

• (COUNTIF($E$1:$E1, List)) returns an array of numbers where $E$1:$E1 was found in the List. In cell E2, the COUNTIF returns the array “{0;0;0;0;0;0;0;0;0;0;0;0}” indicating that the output(in cell E1:E1, which does not correspond to anything in the List) did not match any values in the List. (In cell E3, COUNTIF($E$1:$E2, List) returns the array “{0;0;1;0;0;1;0;0;0;0;0;1}” to indicate that matches were found for the string “BB”. Similarly, in cell E4, COUNTIF($E$1:$E3, List) returns the array “{0;1;1;0;1;1;0;0;0;0;1;1}” to indicate that matches were found for “BB” and “DD”.) Since the output list has each item just once, the COUNTIF function returns zeros or ones. It is also useful to note that the Ones in the returned array correspond to the position of each found item.
• NOT(COUNTIF($E$1:$E1, List)) reverses the results of the COUNTIF function, switching the zeros and ones. Effectively, the resulting array corresponds to the items from the List that are NOT present in the output.
• Criteria*NOT(COUNTIF($E$1:$E1, List)) produces an array with zeros and ones, with the ones corresponding to the items in the List that meet the Criteria and are not present in the output. In the sample worksheet, the Criteria is defined as (List<> “” ). One could easily extend the criteria to include additional columns, etc. We will look at an example later in this article.
• COUNTIF(List, “<”&List)+1 returns an array of counts for number of items in the List that are smaller than an item, and increments them by 1. In the sample worksheet, in cell E2, the function returns “{1;7;3;10;7;3;6;1;12;11;7;3}” indicating that 0 items (1-1=0, since we had incremented it) are less than the first item in the list (“AA”), 6 items (7-1=6, since we had incremented it) are smaller than the second item in the list (“DD”), etc. Please note that the function includes duplicates in the counts. The reason for incrementing the results of COUNTIF by 1 is to handle the case where the COUNTIF returns a zero. (The COUNTIF will return a zero when the item is the smallest value in the List.) A zero, while an accurate count, throws the MIN function off, since we do not want MIN to return zero. So, by incrementing all of the values by 1, we keep the accuracy of the order of the results.
• IF(Criteria*NOT(COUNTIF($E$1:$E1, List)), (COUNTIF(List, “<”&List)+1)*{1,1}) returns an array of counts for the items that are not present in the output, incremented by 1. The multiplication with {1,1} replicates the results of the IF() function into a second column in the array. This duplication is to prevent errors in the MODE function.
• MODE.MULT() returns the most frequently occurring number in a list. If multiple numbers repeat with the same frequency, all of those numbers are returned. For example, for the array {1,2,2,3,2,3,4}, MODE.MULT returns {2} since it is the most frequent item in the array. For the array {1,2,2,3,3,4}, MODE.MULT returns {2,3} since each of them occur with the same frequency. For the array {1,2,3,4}, MODE.MULT returns an error. By multiplying {1;2;3;4} with {1,1}, we get {1,1;2,2;3,3;4,4} creating some duplicates, preventing errors with MODE.MULT.
• MODE.MULT(IF(Criteria*NOT(COUNTIF($E$1:$E1, List)), (COUNTIF(List, “<”&List)+1)*{1,1})) returns an array of the most frequently occurring counts. For example, in cell E2, the function returns “{7;3}” indicating that 6 and 2 (because we incremented the values) are the most frequently occurring numbers in the array of counts.
• MIN(MODE.MULT(…)) returns the smallest value returned by MODE.MULT. i.e. it returns the number in the earliest position in an alphabetic sort order.
• IF(Criteria,COUNTIF(List, “<”&List)+{1}) returns the counts of items in the list, if the conditions in the Criteria are met. The +{1} forces the result to an array, while incrementing the counts. This is to handle the special case of the List consisting of exactly one item. By adding {1}, we ensure that MATCH() processes its second argument as an array instead of a single value.
• The MATCH(…) function looks up the result of the MIN function ( the lowest value in the sort order) in the count of items in List. The returned value from MATCH provides the location of the matching entry.
• The INDEX(MATCH(…)) returns the value from the location returned by the MATCH function.

Thankfully, the formula is much shorter than the explanation!

Expanding the Criteria

We can extend the “Criteria” to handle additional conditions. For example, the following figure (Figure 3) indicates column K as showing TRUE or FALSE to indicate whether a certain row in column A should be included in determining the output. (The conditional formatting rule I applied to column A has greyed out those items with a FALSE condition in column K.)


I modified the “Criteria” named formula in the sample worksheet to include column K:
=(List<>”")*( $K$2:$K$13)
One could add additional conditions (involving additional columns, etc.) to expand the criteria.

Error Handling

To trap and handle errors, we could wrap the whole formula in an IFERROR().
The formula (in E2), with error handling would become:
=IFERROR(INDEX(List, MATCH(MIN(MODE.MULT(IF(Criteria*NOT(COUNTIF($E$1:$E1, List)), (COUNTIF(List, “<”&List)+1)*{1,1}))), IF(Criteria,COUNTIF(List, “<”&List)+{1}), 0)), “…”) Ctrl+Shift+Enter
and copied down
Sample results from the worksheet are shown in the following figure:

Final Thoughts

Hopefully, this article has offered a few additional tools and techniques for your Excel “tool box”. The great thing about Excel is that you have choices!!
I wish you EXCELlence!

Sajan

Download

You can download a copy of the above file and follow along, Download Here – Excel 2007-2013.

Formula Forensics “The Series”

This is the 30th post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like Sajan has done above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

Using pivot tables to find out non performing customers

Using pivot tables to find out non performing customers:
Moosa, one of our readers emailed this interesting question:

I have huge list of customers (around 1500).

Table includes following information

Customer # , Customer Name, Sales 2002, sales 2003, … sales 2012
My requirements are

1. list of customer who did not have sales during all these years

2. List of customer who have not business from 2002

3. List of customer who have not business from 2003

…

10.List of customer who have not business from 2012

So how do we identify these customers?
Of course, we can write a very long and complex formula to get the list. I think we are better off using that energy to reach out to these customers and improve the sales. So lets figure out an easy solution.

Enter Pivot Tables

Assuming our data looks like this:

1. Select any cell and insert a pivot table
2. Set up pivot table like this:

3. Add Value filter show only customers with sales
Click on row label > value filter and set up criteria like this:

[More: using value filters with pivot tables]

4. Our report for non performing customers in 2002 is ready!

Hmm.. this good, but tedious

You are right. Although this approach gives answer for a particular year, when we want results for another year, we need to repeat all steps again. Not cool man, not cool.
So what next?
Part of the problem is due to how our data is structured. If we had 3 column structure like below,

we could set up a report filter on year and see which customers did not have any sales for any given year.
Alas, lets assume Moosa is stuck with this data.

Enter a helper column

We could improve our original solution so that user can select any year (or all) and see which customers did not fetch any sales by using a simple helper column.

1. Just go to the original data set and add an extra column at the end.
2. Call this selected year
3. Now, go to an empty cell somewhere else in the worksheet and name it asselYear
4. This is where we will keep the year for which we want the results (can be 2002, 2003…2012 or all)
5. Lets assume our data is in range C4:M4 (C4 has 2002, D4 has 2003 … M4 has 2012)
6. Now, we want to fetch only the selYear’s data in to this helper column. So if 2002 is selected, we want data in C4, for 2003 data in D4… and for all we want sum of all numbers in C4:M4.
7. Looks like we can use some INDEX magic here.
8. In the helper column write =IF(selYear="all",sum(C4:M4),INDEX(C4:M4,selYear-2001))
9. Go ahead and examine that formula. I am not going to explain

Now, our helper column fetches any one years data or sum of all years data, based on what users want. Awesome!

Lets go back to the pivot

Armed with our helper column, lets re-create the pivot table. But this time, instead of dropping any one year, we will drop selected year column in to “Values” area.  This way, our pivot report shows customer names for selected year.
Lets add a combo-box form control so that we can select the year interactively.
But there is one problem!
Our pivot report does not refresh whenever we select another year.
Of course, we can easily fix this with a one line macro & some duct tape.
Right click on the combo box and choose “Assign macro”
Name the macro as refresh Pivot and write below code [more on the macro here]

Sub refreshPivot()

ActiveWorkbook.RefreshAll

End Sub

And we are done! We can interactively see which customers did not fetch us any sales for any given year. See this demo:

Download Example workbook

Click here to download example workbook & see this in action. Explore the macro & pivot table settings to understand how this works.

Using Pivot tables vs. Formulas for cases like this

I think this is a perfect example when Pivot table based solution is simpler compared to formula based one. Not only is it simple to set up, but it is very usable & modifiable. Often we complicate a problem by trying to figure out the perfect formula for it. I think an intelligent Excel user needs to mix various options – pivot tables, vba, formulas, tables etc. to get the solution in few simple steps.
This way, we can spend rest of our time finding out why Foger Rederer never bought anything from us after 2005.
What do you think? Do you use pivot tables often? How would you have solved Moosa’s problem? Please share using comments.

Learn Pivot Tables & Become a data rock-star

If you are new to pivot tables or have not used them to their full potential, now is the time to dip your toes. Check out below resources:

• Introduction to Excel Pivot Tables
• My top 5 tips for Pivot Tables
• How to group data in pivot tables
• Using slicers to filter pivot tables – new feature in Excel 2010
• Using report filters
• Visit our extensive pivot table section

Consider joining in our Excel School program: If you want to learn how to combine formulas, pivots, conditional formatting, charts & various other features of Excel to do awesome stuff, then please consider joining my Excel School program. It is a completely online course designed to make you awesome in Excel and Dashboards. To know more and join us, please click here.