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.

Five Boxes, Three Ways

I read a lot of articles every week in curating these round-ups, and not all of the content is produced by project managers. Probably less than half, most weeks. I see a lot of excellent content from non-project managers, and a lot of gibberish, in about the same ratio that I see from project managers. Not everyone shares the same understanding of project management methodologies, even among the practitioners. I typically use the general classifications “Established” or “Traditional” methods and “Agile” methods while some folks refer to a methodology called “Waterfall.” So, in an effort to over-simplify these three commonly referenced methodologies, I’d like to show five boxes, three different ways.

This first version is frequently referred to as “waterfall.” Back in the 80’s, there were a few projects that were actually run in a fashion similar to this. Most failed, because you have to monitor while executing, or you don’t catch the errors until it’s too expensive to correct them. Ever seen that poster of two teams, building a bridge from opposite shores of a river, getting to the middle and suddenly realizing that they’re not matching up? Yeah, like that.

five-boxes-one

The second version is the way most projects have been managed for the last few decades: complete the planning stage, and then move on to execution, while monitoring the process and quality as you go. This is especially critical in civil engineering projects, like the apocryphal bridge, but also for those where compliance with some external protocols or requirements is required, or where powerful stakeholders have to be satisfied, or where a lot of sub-contractors, inspectors, or other contributors are involved.

five-boxes-two

These days, many projects are being run using Agile methods: plan enough to begin execution, monitor more-or-less continuously, and re-plan based on what you learn as you go. This is great for certain kinds of software and consumer product development projects; not so much for civil engineering, pharmaceutical development, and other projects where the product will have a lot of potentially catastrophic failure modes and a very long life.

five-boxes-three

 

 

 

 

 

 

 

 

 

 

Note that the contents of the boxes have not changed. Poor execution will doom a project, no matter what else is going on. Initiating the wrong project or starving the right one for resources will generate a negative ROI, no matter how you manage it. And failing to monitor scope, schedule, cost, quality, and the mood of the stakeholders will burn any project to the ground. Simply re-arranging the boxes, like re-arranging the deck chairs on the Titanic, won’t change the outcome. But there will always be people who want to try.

Major Update to my Home Office!

I addition to writing and blogging, I’m a project management consultant working from an office in my home. Many of my clients supply a laptop that they want me to use when accessing their network. Up until recently, I just spread everything around on my U-shaped desk – laptop, monitor, monitor, laptop, monitor – and tried not to knock anything over. Then a few months ago, I started looking at standing desks. I just don’t have room in my home office for another table or desk – if I did, I’d add a woodworking bench. For a while, it looked like I was going to have to ditch what I had in order to be able to start over. Not my idea of a positive solution. So I asked my daughter-in-law for her recommendation.

Home office sitting configuration

Sitting configuration, sans mug

Like me, Nancy works with multiple monitors. She has been using a gadget from Varidesk for several months. It sits on the tabletop and lets you raise and lower your monitors, keyboard, coffee mug, and so on with minimal exertion. Her experience has been positive, although she is considering a product from another company with an electric motor to handle the lifting. Since I need the exercise, I opted for the manual version of the desk. But that really only solved half of the problem.

Home office sitting configuration

Standing configuration, avec mug

I found a dual monitor KVM switch from StarTech, which allows me to toggle between the laptops. Then I ordered a Vivo laptop stand so I could mount the client laptop above my Dell, which lives in a docking station. I now have the two laptops “stacked” vertically next to my standing desk and I can work on one computer while monitoring the other for activity. I can toggle both monitors, keyboard, and trackball with a single button on the right side of the StarTech KVM box, located between and beneath the monitors. The third 1920 by 1200 monitor is sitting in the corner, pending other uses.

The Vivo mount is stable enough to type on when logging in or when I want to respond to an Email or IM without switching to that laptop. It never moves, even when raising and lowering the VariDesk. I considered mounting the pole in an existing hole in the desktop return at the base of the U, but by using the C-clamp on the edge of the return behind the other laptop, I was able to reclaim that space for other uses. And when I need to remove the lower laptop from its docking station, the Vivo arm swings the upper laptop out of the way.

At this point, I’m sold on the health benefits and relative comfort of using a desk that lets me alternate between sitting and standing – when I say I’m an Agile project manager, I really mean it! My next purchase will likely be one of those soft padded mats to stand on and maybe an IV pole to supplement my coffee mug. If I ever decide to mount my Macbook, I’ll use that return desktop hole for another Vivo mount. They have one that supports both a laptop and a monitor, at standing height. And I still have space under the hutch on the left side of the desk for other gadgets.

Final note: I don’t have any relationships with any of these vendors, and I didn’t even add them to the Practicing IT PM Bookstore, although maybe I should. This is just my personal product review.