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 August 15 – 21

New project management articles published on the web during the week of August 15 – 21. And this week’s video: a tutorial on conducting a Monte Carlo analysis using Excel. Just 11 minutes, safe for work.

Must read!

  • Michael O’Brochta looks at the cultural, experiential, and situational drivers of how we perceive behavior as ethical, or not. Including our own.
  • Kayleigh Töyrä explains how we can apply Finnish culture and values to project management.
  • Hope Reese reports on a life saved when Tesla Autopilot drove a man to the hospital. Ethical dilemmas, minimum acceptable reliability, and other social risk considerations will make future projects even more complicated.

Established Methods

  • Harry Hall describes seven simple actions to take that will make your risk management efforts more productive.
  • Andy Jordan explores the risk management techniques applicable to protecting project benefits.
  • Nick Pisano makes the case for incorporating measures of technical performance in earned value management.
  • Glen Alleman lists units of measure meaningful to expressing value.
  • Kenneth Ashe gives us a refresher course on the basics of project budgets and ROI.
  • Elizabeth Harrin shares a PM tool evaluation matrix for download.
  • Kerry Wills proposes a new Olympic event: the project management decathlon.

Agile Methods

  • The Clever PM shares the keys to driving a group of people to a decision. Actually, it’s about influence, not control.
  • John Goodpasture points out a few “problems” (which might be opportunities) with a project team made up mostly of “gig” workers.
  • Meghana Bendre is an engineer, and she doesn’t despise Agile. Far from it.
  • Jessie Chen reveals how Netflix does A/B testing.
  • Sandeep Khaira goes into the details of pair programming – when it’s a fit, what are the benefits, and how it’s done.

Applied Leadership

  • Peter Landau interviews Management Excellence blogger and speaker Art Petty.
  • Lisette Sutherland interviews Spencer Coon of hibox.co on how his 15-person company, distributed among three continents, uses their own product to collaborate. Just 36 minutes, safe for work.
  • Alison DeNisco reports on an Everest Group survey that describes a new trend: on-shoring contact centers. Cost savings and customer satisfaction are both trending down, so …
  • Bertrand Duperrin identifies the difference between your external-facing web site and your intranet: managers want content and employees want relevance.
  • Gina Abudi presents a five-step plan for controlling your anger with a co-worker.

Working and the Workplace

  • Bruce Harpham summarizes Cal Newport’s new book, “Deep Work: Rules for focused success in a distracted world.” It’s time to move past productivity!
  • Brendan Toner reviews ToDoist for Windows, as an alternative to bulky list managers with complex interfaces. Android, IOS, and web variants are also available.
  • Beth Spriggs assembles four tactics that can help you more effectively “single-task.”
  • Project Journal describes the new Holy Grail: Inbox Zero.
  • Elissa Gilbert surveys the broad mix of university and non-traditional programs in data science education.

Enjoy!

New PM Articles for the Week of June 13 – 19

New project management articles published on the web during the week of June 13 – 19. And this week’s video: Simone Giertz explains how she became the Queen of Shitty Robots. Less than two minutes, generally safe for work, and inspiring for those with imagination, but neither technical nor social skills.

Must read!

  • Thomas Carney has rounded up six diverse, well-qualified opinions on the #NoEstimates debate. Well worth reading, and even more worth thinking about.
  • Kailash Awati tells the story before the story – a parable about setting business expectations before beginning a data science project. Just 5 minutes, safe for work.
  • Kathleen O’Connor interviews Jim Dewald on his upcoming book, “Achieving Longevity: How Great Firms Prosper Through Entrepreneurial Thinking.”

Established Methods

  • Elizabeth Harrin points out the ramifications the Brexit vote will have for businesses and the project managers who will have to implement all those contingency plans.
  • Harry Hall notes the steps to take when you have to replace a team member on a project.
  • Kenneth Ashe recommends strategic thinking as an approach to identify and assess process improvements.
  • Kimberly Wamba expounds on best practices in managing uncertainty and ambiguity.
  • Oscar Berg counts off the reasons why corporate investments in IT commonly fail.
  • Ruairi O’Donnellan introduces Brightwork’s new Resource Management Pocket Guide. It’s a free download, once you provide your contact information.
  • Elise Stevens interviews Susanne Madsen on how to manage a demanding workload. Just 24 minutes, safe for work.

Agile Methods

  • Derek Huether notes, “we need a lot fewer Agile police and a lot more Agile ambassadors.”
  • Henny Portman reviews “Getting Value out of Agile Retrospectives,” by Ben Linders and Linus Goncalves. Includes links to two related YouTube videos.
  • Martin Aziz describes the Retro Game, a board game for teams prone to sitting around the table and asking each other, “Well, what do you think?”
  • Ryan Ripley interviews Dave West on the future of Scrum. Just 19 minutes, safe for work.
  • Jayaprakash Prabhakar defines two alternatives to TDD: acceptance test-driven development and exploratory test-driven development.
  • Dave Prior interviews Katrina Coker about selecting an accountability partner to help you reach your personal and professional goals. Just 33 minutes, safe for work.
  • Shoaib Ahmed identifies four key part of any organization’s transition to Agile.

Applied Leadership

  • Art Petty rants abut managers who don’t take on the responsibility to identify and develop talent.
  • Liane Davey notes that talent management can bring out the worst in bad managers.
  • Suzanne Lucas explains how managers can use a goal-based process to train their employees for success.
  • Gina Abudi reflects on what to address when considering an employee for a supervisory role.
  • Seth Godin reminds us that it takes guts to recruit people who are better than we are. But it’s necessary in order to raise the average.
  • John Goodpasture contemplates the eccentric employee, who should not be managed but should be allowed to fiddle a bit.
  • Lisette Sutherland discusses the challenges people and companies face when they transition to remote work. Just 16 minutes, safe for work.
  • Bruce Harpham opines on why most people fail at making career changes.

Enjoy!