Excel Functions You Probably Aren’t Using

I started using spreadsheets back in 1985, in the days of Lotus 123. At the time, I was developing reliability prediction models used in design tradeoff decisions for systems being developed for the US Army Corps of Engineers (that little reverse osmosis water purification do-dad under the counter at Starbucks is pretty straightforward technology, but when scaled to produce tens of thousands of gallons per day, using surface water and generator power, things can get complicated). I moved to Excel a few years later when I adopted Windows 3.1 but after more than three decades, I still manage to find new ways to capture and manipulate data. Let me share a few Excel functions that you probably aren’t using.

Calculating the Number of Working Days

It’s easy to calculate the difference in days, weeks, months, or years between two dates, but I often need to calculate working days.

NetWorkDays(Start_Date, End_Date,[Optional_Holidays])

This function calculates the difference between the two dates but ignores Saturdays, Sundays, and whatever holidays you pass it in a list. You can pass this example list either directly as B2:B9 or by defining a name for that range (highlight the range, right click, Define Name) and passing the name. We’ll use this list as an example:

Holiday

Celebrated

Martin Luther King’s Birthday (US)

1/16/2017

President’s Day (US)

2/20/2017

Memorial Day (US)

5/29/2017

Independence Day (US)

7/4/2017

Labor Day (US and Canada)

9/4/2017

Thanksgiving Day (US)

11/23/2017

Day after Thanksgiving (US)

11/24/2017

Christmas Day

12/25/2017

Let’s say I want to include the number of working days until some event in a status report. The effective date of the status report is in a named cell (done the same way you named the range of holidays) and so is the event date. Like so:

=NETWORKDAYS(Status_Date,Go_Live,Holidays)

This will return the integer number of working days, which you can then use directly or in another calculation.  I typically include the holidays for the project in a separate tab. But let’s say you crashed the schedule and decided you needed the team to work a few weekends, especially at go live. So create a list of Working Weekend Days, name the range, and add it to the calculation.

Working Weekend Days

Worked

Conversion Sunday

1/8/2017

Cutover Saturday

6/10/2017

Cutover Sunday

6/11/2017

Now we can incorporate those weekend working days into the formula:

=NETWORKDAYS(Status_Date,Go_Live,Holidays)
+COUNTIF(Weekend_Work,">"&Status_Date)

In this example, the CountIf function picks up the two days in the list greater than the status date of January 9, which is then added to the 108 days from the NetWorkDays function result:

Status Date

1/9/2017

Go Live

6/12/2017

Working days to Go Live

110

The Working days remaining equals the number of weekdays between the two dates, minus the three holidays that fall in the range, plus the two weekend dates greater than the status date. Note that if your weekend days are something other than Saturday and Sunday, the NetWorkDays.Intl function allows you to specify alternatives.

Summarizing Data in a Table

While we’re talking about status reports: it helps to summarize the information in your risk register, even at a high level. Take a look at this sample, which includes the results of a qualitative risk analysis.

Risk ID

Risk description

Last likely date of occuring

Probability

Impact

Calculated risk

1

This risk

1/1/2017

Medium

Medium

3.0

2

That risk

1/23/2017

Low

Medium

2.4

3

The other

4/1/2017

High

Medium

3.5

4

One more

2/1/2017

Low

Large

2.8

5

And another

7/1/2017

Very low

Medium

1.7

The Calculated risk field is based on a formula:

=SQRT(VLOOKUP(D2,RiskProbValue,2,FALSE)
*VLOOKUP(E2,RiskImpValue,2,FALSE))

Note that two lookup tables were defined to associate numeric values with the Probability and Impact scores; the risk is calculated as the square root of the product of the two numeric values. So let’s say you want to tally up the number of risks with High or Medium scores that are still likely to occur. On the status report, it looks like this:

High Risks

1

Medium Risks

2

We can use the CountIfs function to tally the risks for each criterion. For High risks, e.g. those with a Calculated risk value of 3.0 or more, and a Last likely date after the status date:

=COUNTIFS(Calculated_risk, ">="&3, Last_Date,">"&Status_Date)

For the Medium count, we’ll use a range of values:

=COUNTIFS(Calculated_risk, ">="&2, Calculated_risk, 
"<"&3, Last_Date,">"&Status_Date)

Getting a Completion Date

Sometimes I need to pencil out a very high-level timeline to determine if a goal is even achievable by some date. So I’ll create a list of tasks, each with a proposed duration, and start date. The assumption is that each task begins the day after the predecessor completes. I can then use the WorkDay function:

WorkDay(Start_Date, Duration,[Optional_Holidays])

This returns the serial number of the date Duration days after Start_Date. I can convert it for display using the Text function, like so:

=TEXT(WORKDAY(B7,C7,Holidays),"m/d/yyy")

I can also convert the date to the day of the week, again using the Text function:

=TEXT(WORKDAY(B11,C11,Holidays),"dddd")

This lets me specify a State Date for the first task and durations for each task in the list, with the subsequent start dates and completion date calculated automatically.

Task

Start Date

Duration

Plan

1/5/2017

5

Analyze

1/12/2017

25

Build

2/17/2017

15

Test

3/13/2017

26

Cutover

4/18/2017

3

Complete on

4/21/2017

Friday

Of course, the danger in this approach is that by tweaking the start date or individual durations, you can convince yourself that something is achievable simply by giving yourself less time to do it. So, don’t do that.

The ability to create reusable spreadsheets that use Excel functions to provide actionable information from raw data is still one of those skills that will pay big dividends over the course of a career in project management. All you really need is a little imagination and an understanding of what the data actually represents.

New PM Articles for the Week of January 2 – 8

New project management articles published on the web during the week of January 2 – 8. And this week’s video: Brooke Deterline talks about creating ethical cultures in business. Just eight minutes, safe for work.

Must read!

  • Andy Kaufman asks several project management influencers, what was the most important lesson you learned last year? Just 40 minutes, safe for work.
  • Eamonn McGuinness describes a structured model for adapting your leadership approach to the situation.
  • Ryan Ogilvie expounds on continual service improvement, and the principle of learning by (and while) doing and continually improving while being transparent and inclusive.

Established Methods

  • Mary Shacklett provides examples of how critical thinking (or the lack thereof) can impact a project.
  • Elise Stevens explains how to deal with irrational stakeholders. Or at least, those brief periods where the rationality of their position is less than clear.
  • Rachel Burger identifies the five biggest project management trends of 2017.
  • Shuba Kathikeyan links us to six free online resources to learn about ITIL. But as Rob England reports, the number of folks taking the ITIL exam is shrinking.
  • Amber Lee Dennis has compiled a primer on the Data Warehouse. Well worth reading, even if you’ve been around a while.

Agile Methods

  • Stefan Wolpers curates his weekly list of Agile content, from Agile at scale to Hybrid Agile, to principles of Emergent Organizations.
  • Dave Prior interviews Michael Daly and Matt Volpe on how they’re making Agile work at Major League Baseball (not playing; Advanced Media). Just 50 minutes, safe for work.
  • Cornelius Fichtner interviews Joy Beatty on scaled Agile in the Large Enterprise. Just 31 minutes, safe for work.
  • Glen Alleman contemplates Scrum roles in the context of accountability and responsibility in the presence of a governance framework that extends beyond the team.
  • Craig Smith and Tony Ponton interview Betty Enyonam Kumahor on the practice of Frugal Innovation in Africa. Just 27 minutes, safe for work.
  • Cornelius Parkin demonstrates how to assess user stories using the definition of done and the SMART criteria.

Applied Leadership

  • Art Petty explores former Navy SEAL Jocko Willink’s mantra, “Discipline equals freedom.”
  • Grace Windsor lists five New Year’s resolutions to not make and five alternatives that we should try, instead.
  • Johanna Rothman reflects on the failure of Holocracy at Zappos and the way we naturally develop relationships.
  • Karen McGraw writes about the “Bad boss experience,” as a starting point for becoming a good boss.
  • Andy Wolber shows how to make your IT project portfolio more understandable to your customers by grouping them into start, switch, and stop.

Working and the Workplace

  • Lisette Sutherland samples answers from various interviewees on the question she always asks: What does your virtual office look like? Just 23 minutes, safe for work.
  • Elizabeth Harrin tells of her adoption of a “transitions” strategy at the end of her work day. Ah, the things you learn from being a Mum …
  • Jeff Wise reviews the science behind changing our habits, and thus our lives.
  • Coert Visser reflects on moments of spontaneous progress, as opposed to the stuff we work so hard to achieve.
  • Jessica Meher recounts the realization that holding back, not speaking up, was just being selfish. Leadership requires confidence.
  • Jennifer Aldrich shares her list of questions to ask when considering a job offer from a start-up (or from established companies).

Enjoy!

New PM Articles for the Week of November 28 – December 4

New project management articles published on the web during the week of November 28 – December 4. And this week’s video: children narrate a Museum of London video of a man demonstrating how to cast an axe head using Bronze Age technologies. Just four minutes, safe for work, and far more thought-provoking than anything on television.

Must read / view / listen!

  • Sathappan Chinnakaruppan reports on teaching project management terminology, processes, and skills to sixth-grade kids – including his daughter.
  • Elizabeth Harrin recommends eleven must-have gadgets for the office worker on your holiday gift list.
  • Mike Cohn makes the case for standards of excellence in Agile and stimulates a whole lot of comments.

Established Methods

  • Women Testers Magazine October 2016 edition is now available for download, and it includes a variety of excellent articles. Yes, I know – it’s December …
  • Scott Matteson details ten (non-mutually exclusive) ways to kill a zombie IT project. No edged weapons required.
  • John McIntyre explains why the US government’s Program Management Improvement and Accountability Act is a big deal.
  • John Goodpasture quotes John LeCarre in asserting that part of assessing the quality of data is identifying the source.
  • Kerry Wills demonstrates the value of managing expectations when failure is a distinct possibility.
  • Nick Pisano updates us on progress toward producing a user experience completely under user control.

Agile Methods

  • Stefan Wolpers shares his weekly round-up of all things Agile, from Scrum to Kanban, and from teams to customers.
  • Johanna Rothman explains why both pushing work (i.e. Scrum) and pulling work (i.e. Kanban) may be right for your team.
  • Dave Prior interviews Derek Huether on the Triangle of Productivity, his new theory on what makes us effective. Just 30 minutes, safe for work.
  • Ben Linders explains the Agile Self-Assessment Game, an interesting way for teams to discover how well they’ve embraced Agile methods.
  • Henny Portman reviews “The Product Samurai,” by Chris Lukassen, which maps the seven principles of the Samurai to product management. But no swords.
  • Shay Peleg debunks a half-dozen myths that senior management frequently believes about Agile methods.
  • Moira Alexander provides the smart person’s guide to Agile project management. Dummies need not apply.

Applied Leadership

  • Michael Wood identifies the critical “people realities” of project management, and the people skills we need to hone to deal with them.
  • Dmitriy Nizhebetskiy lists the “do’s” and “don’ts” of successfully managing your project team.
  • Laura Barnard deconstructs the instruction, “Be more strategic.”

Technology and Techniques

  • Jennifer Zaino reports from the Dataversity Enterprise Data World 2016 conference on the existential question: Is NoSQL the future of databases?
  • Jeff Boehm explains the notions behind NewSQL, which attempts to bridge the gap between traditional relational databases and NoSQL.
  • Nir Eyal tells how “multiple discovery theory” explains why great minds think alike, at about the same time.

Working and the Workplace

  • yawn-for-coffeeRebecca Knight provides a detailed course of action in getting your manager’s respect.
  • Leigh Espy talks with Bruce Harpham about how to get project management experience through volunteer work.
  • Nina Semczuk points out three signs your communication skills might need some work.

Enjoy!