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.

Bubble Timeline Chart

A LinkedIn colleague posted an update in a project management group about the following infographic.  It was extremely well received, and inspired me to look into the effort required to replicate it using the tools at hand, namely MS Office 2010.

WebSiteDesigned

This infographic was developed by John Furness of Simple Square; 5663 Cornwallis St.; Halifax NS B3K 1B6; ph. +1 902 452 3417; john@simplesquare.com.

I find this infographic inspirational.  It is easy to read and intuitive, and visually stunning.  It conveys a lot of data, very efficiently to me.  I can tell by the size of the circles and fonts the level of involvement and importance of each task (bubble) on the timeline.  The location of the bubble’s perimeters on the timeline indicate start and finish dates, overlap and size of bubbles is an indication of resources needed, etc.  I didn’t feel like I needed the legend, but it is nice to have.  In my opinion, this is a great first presentation of a project and an excellent sales tool.

I started building this with the data required to make a “normal” Gantt Chart:  Task, Start Date, Work Completed, Finish Date, Number of Days, and Current Date.  (my definition of a normal Gantt Chart)

The results were unreadable, even after working on the formatting… a lot!

firsttry

I obviously had too much data, so I pared it down to Task, Start Date, and Number of Days.

Secondtry

This was better, but the bubbles are lined up on the Y axis… So, I watched a few YouTube videos on Bubble Charts.  It was faster for me to see what was possible, than just exploring them by myself.

After a lot of watching and experimenting, I was able to write a macro to create this:

firstsuccess

The trick is that EACH task in the spreadsheet is a data series in the Chart.  This gives you access to the individually configurable components, color, transparency, label position, font size, etc.

Secondsuccess

At this point, I can either work in Excel’s chart object or copy the chart into PowerPoint and simply add the legend and other labels as desired.

The Finished Product:

Bubbles1

(The parts of the legend that include bubbles were created in Excel.)

 

I have used this to introduce several project in the past couple of weeks.  It has been very well received by my clients.

For a workbook example, in Excel 2010, including automation macros, download it from

https://drive.google.com/file/d/0B7-KTTJhKkb4a1JZVHpIWkV4RXM/edit?usp=sharing

For the PowerPoint example, download it from

https://drive.google.com/file/d/0B7-KTTJhKkb4UjFvZElWYmlmcW8/edit?usp=sharing

For a PDF version of the bubble chart used in an actual presentation [redacted], click here:

PMFlashBlog
PMFlashBlog
PMFlashBlog.pdf
3.5 MiB
43 Downloads
Details

Gantt Charts in Excel

This is a guest post by Joe Smith, a project manager in Austin, Texas. Thanks for sharing this cool technique, Joe!

Four columns of data are required to make meaningful Gantt Charts in Excel. They are the Task, Start (dates), Work (represents work performed), and Finish (desired finish date). Work Performed is calculated by taking the number of days between Start and Finish, multiplying it by the Percent Complete, and adding it back to the Start.

ExcelGanttChart1Langoliers

If we add a fifth column , “Today” (“=Now()”), we will be able to add a Trend Line. This will allow us to visually determine if each task is on target, behind schedule, or ahead of schedule. This approach is a little bit pessimistic because the trend line will be recalculated each time you open the workbook, but progress is generally only recorded once or twice a week.

A sixth column, Forecast Finish, is used to calculate how much longer it may take to finish a task that is behind schedule. The Forecast Finish is calculated by adding the amount of work remaining to the Scheduled Finish, or to the current date, whichever is greater. It is an optimistic approach because it assumes that the task can be finished in the same amount of time that is work remaining. However, because it uses the current date, the value is recalculated each time you open the workbook.

I refer to the fifth and sixth columns as Langoliers. The Langoliers are characters in a Stephen King novella. “Langoliers are the timekeepers of eternity; their purpose is to clean up what is left of the past by eating it.” This is the reason that work cannot be completed in the past.

ExcelGanttChart2Case Study

In January, I accepted a position as Project Manager for a 4 phase project. Phase 1 is network infrastructure installation, Phases 2 is connecting legacy systems (islands of automation) to the new network, Phase 3 is upgrading and implementing new software, and Phase 4 is virtualization of as much hardware as possible. Phase 1 had started 3 months before I joined the project. It is/was a $2 million project, with a 2 year schedule.

Task List

Fortunately, the project is based on quotes from hardware vendors and service providers.  I created a list of each quoted item, aggregated to recognizable components (ie: server components, like memory and mother boards and power supplies, became “Server 1”). I keep traceability to the Quote, PO, and Invoice. Then to most items, I added the “labor” tasks (procure, install, configure, test, qualify). For most start dates, it was when the Purchase Order (PO) for the item was issued to the vendor. I met with the resources (individually) to collect the percents complete, and where the scope had changed. On items that were not complete, I also requested the level of effort in days (LOE) and the amount of time required for the tasks. (I needed these to calculate the Float). Armed with this information, I could provide very accurate Gantt Charts.

Gantt Charting in Excel

I chose to work in Excel, because of the level of control it allows over formulae, calculations, and Gantt Charting. In the Task List, I track Deliverables and “Labor”.  For each item in the task list, I am tracking Scheduled Start, Work Complete, Scheduled Finish, Forecast Finish, Level of Effort, Percent Complete, Actual Finish, Vendor Quote, Purchase Order, Invoice, and Resources. On the Gantt Chart, I am displaying the Task, Scheduled Start, Work Complete, Scheduled Finish, Forecast Finish, and the Current Date.

ExcelGanttChart3 Score Cards

Every month, the PM’s are required to report their project health on a corporate scorecard. The projects are represented at the “ten thousand foot” level. The key performance indicators (KPIs) are Scope, Time, and Budget. “Green” means the project has less than 10% growth in any of the KPI’s. “Yellow” means between 10% and 15% growth in any of those areas, and “Red” means greater than 15% growth in any area. A project can go from Red to Green if the KPI(s) is adjusted and formally accepted/approved, or if a miracle has brought it back into the green, but it must be reported as “Red” or “Yellow” for the month that situation occurred. The Scorecards are reported the first week of each month, for the previous month.

Resource Communications

Resources are the heart and soul of any project, so I work very hard to keep them happy.  On this project, my resources wanted to see the whole project each time they need to enter percent complete, but they wanted the in-progress tasks to be highlighted in some manner, so that they would know which ones to focus on.

I developed a macro to highlight and “un-highlight” the tasks as they come due and/or are completed.

ExcelGanttChart4The resources only need to update the pink cells, and the green highlighting makes it easy to track across the page.

My goal with this article is to share my experience in the hopes that it will help others benefit from my experience and methodologies. To download a sample, click the icon, below. I welcome your questions, comments, and suggestions.

[wpdm_file id=6]