Zen and the Art of Stack Ranking

Stack Ranking as the name implies is the process of stacking things or people up and applying a 1 to n rating to each.  Unlike a standard performance rating, a ranking implies that no two employees should have the same assigned rank and that when you’re finished, someone will be number one and someone will be on the bottom of the pile.

Stack Ranking the members of your team can be a loathsome task.  Especially when you consider the fact that many companies use this tool as a way to identify and carry out targeted reductions in force.  The issue that many managers face when it comes to stack ranking is that even if you have a solid team of super hero performers, someone will be on the bottom of the list.

Some have argued that there are many problems with the stack ranking systems in use today.  And I must admit the arguments against are valid.

Nevertheless, the stack ranking system, when used effectively can provide great insight into the performance of your team and can highlight important focus areas for enablement and development.  One key to ensuring that you are getting an accurate representation of your team and the rank each member assumes is by including the critical data elements by which you will evaluate them.  This means having some good reference material about what each team member’s job description or role entails.  This will be especially helpful when communicating results to employees as well as superiors.

Implementing a Ranking System

Any ranking system should have a core set of components that will enable you to record the scores of your team members as they relate to criteria or attributes.  I’ve chosen Microsoft Excel as the system I’ll use, but there are many systems available that will work.

Below, I have included an image with various highlighted sections.  The following section of this article will explain the various components and offer suggestions for implementing your own ranking system.

Core components

A – Success Criteria

Every job has a success profile or a set of criterion upon which success will be judged.  If this has not been communicated properly, you should exit this article here and get that done ASAP.  There is no point in attempting to apply a ranking when your team has no idea about what you consider critical success criteria.  For our implementation, we’re going to start with a two-tiered categorization of success criteria.  This just makes communicating the results easier.

To make things easier to communicate, I’ve enabled a two tiered categorization leveraging the ‘merge and center’ feature of Excel to consolidate the second tier of criteria under the first.

B – Criteria Weighting

I am a huge proponent of incorporating a method of expressing weights to each of the criteria upon which you will rank your staff.  Let’s face it, not every attribute is going to be of equal importance.   Is punctuality as important as follow-through or proactivity?  I won’t debate these here but I will argue that they are of differing levels of importance so any tool used to record your teams’ performance should give you the ability to reflect this.

The weights recorded in this section are used to populate a section of the results summary.   The weighted scores and ranking are calculated by multiplying the score entered by the weight for this criteria divided by the maximum possible weight (10 in my example.)  The function for the weighted score is as follows:

=IF(B5>"",SUM(($E5*$E$4)/10,($F5*$F$4)/10,($G5*$G$4)/10,($H5*$H$4)/10,($I5*$I$4)/10,($J5*$J$4)/10,($K5*$K$4)/10,($L5*$L$4)/10,($M5*$M$4)/10,($N5*$N$4)/10,($O5*$O$4)/10,($P5*$P$4)/10,($Q5*$Q$4)/10,($R5*$R$4)/10,($S5*$S$4)/10),"")

If you’re new to Excel, this will probably look like gibberish. Just understand that the letters and numbers refer to cell locations in the worksheet. Where you see a cell letter or number prefaced with a dollar sign ($), that’s used to harden the reference so that as we iterate through the various cells to perform the calculation, we don’t increment that cell reference.

There’s probably a better or more efficient way to calculate this, but I couldn’t figure one out so I did it the hard way by manually copying and pasting the calculation across all of the various criteria columns.

C – Employees

Ok, now we’re getting to some data entry.  Column B contains a list of your employees, or the members of the team you’re going to be reviewing.

D – Title

Entering a title for each team member under review enables us to gain further insight into performance of the team.  With this, we can answer questions such as “How are Sr. Hero’s doing across our criteria in comparison to Heros?”

E – Discipline

Recording a discipline for each employee is optional.  I view it as essential, however because it will give you even more insight into how your team is performing.  Think of discipline as a specialization within your team.  To determine if you’ll find a discipline useful, examine the differentiation between the roles performed by the members of your team.  Are some focusing on a completely separate project or set of customers?  If so, use this to further differentiate them.

F – Scores

Here’s where the tough work begins.  You  must record a score for each employee as to their performance against the specific criteria.  I’m suggesting a straight integer score from 0 to 10.  To enforce accuracy and eliminate errors in reporting results, I’ve created data validation rules that force users to enter a whole number between 0 and 10.

CONDITIONAL FORMATTING RULES FOR SCORES

As you enter the scores, you will notice that there are special conditional formatting rules applied to the data entry area for scores.  These rules give the data entry area additional readability by color coding high scores in green and low scores in red.  As you complete the process of evaluating and recording scores, the summary results and rankings can be viewed in columns T through Y or sections G through L respectively.

Note, that in columns V and Y, you can sort the data in ascending score or rank by weighted or raw values using the up arrow and down arrow buttons.

G – Score

Section G contains the computed raw score values for each of the values you entered in section F (columns E through S.)  Note that this section does not take into consideration the weightings that you applied in section B.  I have implemented another set of conditional formatting rules to help identify the top and bottom performers by score.  The remainder of the computed sections apply these same conditional formatting rules.

The value for this section is computed by simply summing each of the score columns.

=IF(B5>"",SUM(E5:S5),"")

H – Percentage

Section H contains the percentage of total points scored out of the total possible points.  This field is calculated by dividing the total points scored by the total points available from all of the criteria columns.

=IF(B5>"",T5/150,"")

I – Rank

Section I shows the ranking of the employee based on the unweighted, or raw total score. This is a tricky calculation due to the fact that you may encounter multiple employees with the same exact score. A proper ranking system will never provide the same rank for multiple items or employees. We start with Excel’s built-in RANK() function which will provide a numeric ranking of a series or range of data. However, to ensure that we never present multiple similar ranks, we implement a simple test to add a value to any ranks where duplicate scores in column T may appear. This ensures that no duplicates appear in our ranking system.

=IF(B5>"",RANK(T5,T$5:T$24)+COUNTIF($T$5:T5,T5)-1,"")

J – Weighted Score

Section J gives us an opportunity to leverage the weights you specified in section B for each of the criteria. Here, we’re calculating the total score offset by the individual weights for the separate criteria. This is where is gets a bit tricky. Each score gets multiplied by the weight and then divided by total possible points or 10 in this example.

=IF(B5>"",SUM(($E5*$E$4)/10,($F5*$F$4)/10,($G5*$G$4)/10,($H5*$H$4)/10,($I5*$I$4)/10,($J5*$J$4)/10,($K5*$K$4)/10,($L5*$L$4)/10,($M5*$M$4)/10,($N5*$N$4)/10,($O5*$O$4)/10,($P5*$P$4)/10,($Q5*$Q$4)/10,($R5*$R$4)/10,($S5*$S$4)/10),"")

Be sure to utilize the Up Arrows and Down Arrows in sections M and N to sort the employee data by rankings as you complete the data entry.

K – Weighted Percentage

Section K presents the total percentage of weighted score points achieved versus possible. This is the same calculation that we used for the raw percentage but uses the weighted score in column W instead of the raw score.

=IF(B5>"",W5/$W$4,"")

L – Weighted Rank

Section L gives us a view into the weighted ranking of the employee. Obviously, if you weighted each of the criteria the same, the weighted value and the raw values will be the same and thus so too will the rankings be the same.

M – Raw Rank Sort Buttons

The Up Arrow will enable you to sort the employees by ascending rank based on the raw scores and data whereas the Down Arrow will sort by descending rank.  To enable this sorting feature, I’ve created a button and an associated macro.

Sub btnSortUWAscending()
'
' btnSortUWAscending Macro
'
Range("alldata").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("uwrank") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("alldata")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

You will notice the use of several named ranges.  For example, “alldata” is a named range which refers to =Sheet1!$A$5:$Y$24.  “uwrank” is a named range which refers to all of the values in the raw ranking calculated column.

You can use the Excel Name Manager to modify this if you should modify the spreadsheet significantly.

NAMED RANGE “ALLDATA”

N – Weighted Rank Sort Buttons

The Up Arrow will enable you to sort the employees by ascending rank based on the weighted scores and data whereas the Down Arrow will sort by descending rank.

O – Hide / Unhide Detail Data

This button gives you the ability to hide or show the detail data from sections A, B, and F. The following image shows the result of hiding the detail data.

DETAIL DATA HIDDEN

Use this button as a toggle to alternately show or hide the detail scores and criteria.

Analyzing the Results

As we can see from the summary view above, we have some issues to address.  The first thing you may gravitate toward is the lower end of the stack but be certain that you must pay just as much if not more attention to those consistently scoring highly.  Retaining top performers is of utmost importance.  You need to be certain that you have a plan to keep these top performers challenged, and interested.  The key to this unfortunately is not illuminated by a tool like this.  That will take more time and effort to get to know your team and what gets them motivated to come to work.

Once you’ve gone through the exercise of recording your team’s scores and reviewed the individual rankings, go back through the scores and make sure that you’ve been objective and that the scores are accurate and reflect real performance.  Also, avoid the temptation to craft the results to get a ranking according to a pre-conceived notion about your team.  Be objective and accept the rankings.  These rankings are a tool to improve the performance of your team as well as improving their level of satisfaction.

Summary

So there you have it, a complete example of a stack ranking system built in Microsoft Excel.  I hope you found this tutorial useful.  As with any tool the hard part is really in the implementation and making sure that you create realistic criteria upon which to judge your employees.  Another reminder to make absolutely certain that the criteria upon which you judge is the same that you’ve communicated to your employees.

If you’ve enjoyed this tutorial and would like a copy of the MS Excel Stack Ranking tool, simply click the following to get a free copy.  Keep in mind that this is a “Macro-Enabled” Microsoft Excel spreadsheet and will require you to enable macro content.  Also – this was created and has only been tested with Microsoft Office Excel Version 2010 although the functions and features used should be backward compatible to older versions of Excel.

Use “Tweet and Get It” or download the macro enabled spreadsheet below.

Download the Stack Ranking tool and try for yourself. Comments welcome!

My Last One

I’ve been told that if you don’t remember your last drink, you probably haven’t had it yet. I don’t know if that’s true, but I remember mine well. And while the last drink I do remember may not be the last drink I’ll ever have, I can say with relative certainty that I won’t be having one today.

My last alcoholic bender began on January 9, 2014, when I guzzled vodka from a two-liter bottle. This bottle had patiently waited for me in the trunk of my car, which was parked at the airport for several days while I was away on a business trip. That evening would culminate in a series of events so painful that it would alter my perception of what I was willing and able to do to change my life.

I drove home from the airport that day with a headache so severe I could barely see. Arriving home, I sheepishly greeted my wife before rushing to the pool room in the back of the house to grab a beer in a feeble attempt to mask the liquor on my breath. My wife’s silent glare telegraphed her extreme disappointment.

She had had enough of this insanity and we both knew it. I carried my things up the stairs and placed the beer on the desk in my office. Checking over my shoulder, I snuck the remaining vodka out of my messenger bag and onto a bookshelf inside my office closet— out of plain sight but within reach.

As I slumped into my office chair and slid into yet another evening of sneaking more drinks and hiding from my wife, the thought occurred to me that this had to stop, but that thought died just about as soon as the next sip hit my lips. I have a hazy recollection of downing several more beers and the remainder of that vodka. There are some hazy flashbacks of a sharp exchange of words with my wife. I vaguely remember thinking I needed to leave but not knowing how or where I’d go and then somehow falling into the tub in the upstairs bathroom. Then I see another flash of me pulling the shower curtain down around me as I fell, and then nothing.

I came to on the floor of my office, under my desk. Morning light streamed in through the blinds as my wife stood over me. I recognized

that she had been talking for several minutes, but her words fell only on my unconscious ears. That is until she spoke the two words I knew were coming for quite a while. “I’m leaving,” she said, and did just that.

In my hungover haze, I wasn’t quite sure if she meant leaving for now, for work or forever. I lay there under my desk for a long time. My head was pounding, and I felt utterly defeated. I finally made it to my feet, but my ringing phone pulled me back under the desk.

“Hey, Mike,” a voice said. “How are you?” There was a long pause while my fogged brain tried to identify

the voice.

“Gene. It’s Gene, Mike,” he said, breaking the silence. “I just wanted to see how you’re making out—how are you?” I had met Gene about a year earlier during a short bout of sobriety. He was sober too and we knew a lot of the same people. I paused because I was wrestling with the thought of letting someone else know how desperate my situation had become. I thought, Do I let him know? Can I get honest with another human being?

The moment I received that call was quite possibly the lowest point in my life. I don’t recall ever feeling worse than I did in those few moments, nor have I felt that way since. The religious or spiritual among you will struggle less with this next statement, but I’ll make no apologies to

those of you who are neither. I genuinely believe that something more powerful than human power intervened in my life at that moment. I somehow made a decision to get honest with myself and with another human being.

“It’s really bad, Gene,” I admitted, choking up. “I can’t imagine how this has happened again. I’m sick and I think my wife just left me.” Looking back now, I realize that I had surrendered at that moment. Gene chuckled a bit, which pissed me off. “Well, I’m glad you’re still alive,” he said. “Let’s get some coffee.”

Gene later described that meeting. My skin had a dead-grey hue and the whites of my eyes were awash in yellow, he said. My mind was so fogged that I could barely speak. That day, Gene shared with me the solution he found to his drinking problem in the hope that maybe it would help me with my problem. He and several other men I met spent a great deal of time with me over the next weeks,

months and eventually years. For that, I’m forever grateful and indebted.

Today my life is good, even great. I still have problems, as everyone does. What’s different is that now I have tools, a design for living and a purpose. Today the most important things in my life are my relationship with something more powerful than myself, my ability to help others and my wife and two amazing children. Everything else is gravy.

I recently celebrated five years of sobriety in AA. I want people to know that the possibility of changing your life is real. Also, I’m not a poster child for any particular method of getting sober. Pretty much all I know is that I’ve found something powerful that worked for me today and I’m pretty sure if I do the same things I’ve done for the past 1825 days, I’ll enjoy one more day sober, happy and purposeful tomorrow.

My Morning Routine

It all begins with an idea. Maybe you want to launch a business. Maybe you want to turn a hobby into something more. Or maybe you have a creative project to share with the world. Whatever it is, the way you tell your story online can make all the difference.

Don’t worry about sounding professional. Sound like you. There are over 1.5 billion websites out there, but your story is what’s going to separate this one from the rest. If you read the words back and don’t hear your own voice in your head, that’s a good sign you still have more work to do.

Be clear, be confident and don’t overthink it. The beauty of your story is that it’s going to continue to evolve and your site can evolve with it. Your goal should be to make it feel right for right now. Later will take care of itself. It always does.

Maecenas sed diam eget risus varius blandit sit amet non magna. Vestibulum id ligula porta felis euismod semper. Duis mollis, est non commodo luctus, nisi erat porttitor ligula, eget lacinia odio sem nec elit. Aenean eu leo quam. Pellentesque ornare sem lacinia quam venenatis vestibulum. Sed posuere consectetur est at lobortis. Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.

Donec id elit non mi porta gravida at eget metus. Nullam quis risus eget urna mollis ornare vel eu leo. Sed posuere consectetur est at lobortis. Maecenas sed diam eget risus varius blandit sit amet non magna. Nulla vitae elit libero, a pharetra augue. Etiam porta sem malesuada magna mollis euismod.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec ullamcorper nulla non metus auctor fringilla. Donec id elit non mi porta gravida at eget metus. Duis mollis, est non commodo luctus, nisi erat porttitor ligula, eget lacinia odio sem nec elit. Etiam porta sem malesuada magna mollis euismod. Praesent commodo cursus magna, vel scelerisque nisl consectetur et.

Fusce dapibus, tellus ac cursus commodo, tortor mauris condimentum nibh, ut fermentum massa justo sit amet risus. Vestibulum id ligula porta felis euismod semper. Aenean eu leo quam. Pellentesque ornare sem lacinia quam venenatis vestibulum. Maecenas faucibus mollis interdum. Cras mattis consectetur purus sit amet fermentum.