What kind of Engineer are you, how would you describe yourself?  Are you the kind who uses a straight edge when you write so the lines will be beautifully aligned, or do you scribble something barely legible to get done and to move on to the next thing?  Do you calculate cell boundaries using 8-digit precision, or do you close one eye and squint through the other to make your best guess based on the data and your experience?

I know that it takes all kinds to make the world.  There will always be a need for the beautifully aligned, 8-digit precision types.  For myself and the work I do, I always preferred the scribblers and the guessers.  I’ll take a Master of the Pareto Principle (https://goo.gl/P63YH5) over the i-dotters and t-crossers any day.

No slight intended to those people.  They do fine work, and there are times when that is essential.  Like rocket launches, aircraft manufacturing, pharmaceuticals.  But in my experience, Telecoms never respected that quality of work and the time required to do it.  Most of the time, whatever work we did today, ended up being redone, with a slightly different set of requirement, within a few months time.

The point I want to make is that there is a time and place for everything.  As Engineers and Technologists, we must maintain our situational awareness to know what kind of effort is required.  Know when the absolute best is required, and when going a little faster is more highly valued.  Put cavalierly, “Don’t make the Best the Enemy of Good Enough“.  There is a time and a place. Remember that.

Series Summary

This is the fourth article in my “Excel BTS Project Tracker” series.  In this series, I’m developing a completely functional Project Tracker for Mobile Telecoms using Microsoft Excel.  With each new article in the series, I enhance the previous version of the Tracker and teach you some new Excel techniques at the same time.  If you work in Technology, this tutorial is for you.  If you work in the Mobile RAN, you’ll be able to download the tracker and begin using it with very little modification.

You’ll learn how to quickly and easily setup Excel to track smallish project. And you’ll impress the hell out of your peers, your boss, and other coworkers, too. In short, you’ll get massive cred for this.

You can download my tracker to follow along with this article.  Or you can use the Tracker as is, or modify it for your own projects.

Click here to Download the Tracker

In the previous article, I added regional reporting to the basic tracker, using a technique I call Synthetic Data.  The Synthetic Data technique is a way to derive richer reporting from a core dataset.  Once you learn it I guarantee you’ll use it almost every time you use Pivot tables.

You can read that article by clicking the link below.

Excel BTS Project Tracker (This Technique is Much More Useful)

If you’re new to this series, start with the first article.  In just a few minutes of reading, you can get current with the series and then proceed to read this article.

Excel Project Tracker (with the Coolest Excel Function Ever!).

Approach

In this article, the new enhancement to the project tracker is to allow time-based reporting.  This means you’ll be able to show how the project is progressing, and also forecast the end.  That is a huge capability upgrade, but as you’ll see, a relatively easy functionality enhancement.

The technique introduced in this article is called Date Bucketization.  Date Bucketization is a way to group data points which are randomly distributed over time.  In our case, Updates from field workers show work which was done any day throughout the month.  But it can be confusing to display progress like that.  It makes more sense to show progress by week, or by month.  So I use Date Bucketization to group the updates.

Implementing Date Bucketization

One way to group data points distributed over time is to use Synthetic Data.  Similar to the way to implement regionalization, Date Bucketization synthesizes a new field based upon the date of each Update.  This field then becomes the basis for a Pivot Table to summarize the data using our preferred “bucket”.

I should note that there are other techniques which also allow you to group data.  For example, grouping can be done directly inside a Pivot table.  But I find this approach less flexible and harder to understand.  I prefer to use the Synthetic Data technique because the source is entirely visible in the Updates table.

To show you this technique, I’ll set up the tracker to display progress by month.  For small, quick projects you might want to show progress by week, but using weeks is a little harder than using months.  Grouping by month makes it easier grasp the concept.

The way to bucketize dates is to use Excel’s DATE function.  DATE has 3 arguments, as follows:

=DATE(year,month,day), where the first two arguments are another Excel function, YEAR and MONTH.

I don’t use a DAY function for the day argument.  Instead, I hard-code a “1”, meaning the first day of the month, in my Synthetic Data.  The year and month come directly from the Update.  But the day value is always “1”.  In other words, the Synthetic Data for all Updates having the same year and month are all clustered on the first day of that year and month.  That’s the bucket!

Now when the Pivot table summarizes our Synthetic Data, all progress made during each month will be displayed on the first day of every month. It’s like magic, no?

Requirements

I need to update the requirements to include the time component that will come from Date Bucketization. There are 5 requirements from the previous article.  I add a sixth.

1. It should be easy for workers in the field to submit Updates.
2. The PM should be able to easily import the Updates to the project tracker.
3. Project status should show automatically, without requiring further calculations.
4. Completion status should reflect the different levels of effort among the Tasks.
5. Completion status should be displayed for each defined region.
6. Progress should be displayed by Month.

Procedure

Here are the steps required to implement Date Bucketization in the tracker:

Insert a column for our Synthetic Data.  This will be column D, which I’ve named “Month”.  I’ve also added a second column, column E “Year-Week”, but won’t discuss it in this article to avoid this article becoming too long.

Add formulas in the Synthetic column.  Referring to the Approach, above, it’s  the DATE function we need.

=DATE(YEAR(H2),MONTH(H2),1)

This formula synthesizes a new date.  The new date has the same year and month as the Completed Date for each row.  The day number of the Completed Date is ignored, and substituted with a 1, the first day of the month.  Now, all updates for this Month and Year are bucketized on the first day of the month.

Because this formula returns a date, it will sort in the proper sequence even if we format it to display “MMM-YY”.  By default, then, the Pivot table will display the progress by sequential month.

Now I can create a new Pivot table that uses the Synthetic date field.  Put the Pivot table on a new worksheet.  Rename the worksheet to Date Bucketization.  Drag the Month field to the Columns area, the Region field to the Rows area and the Weighted Completion field to the Values area.  Click the “i” next to Weighted Completion and change to summarize by SUM.  Click the Number button to change the Number format to a percentage with single digit precision.

Add a Data Table

What the Pivot table shows is the total amount of the project completed each month.  But what I really want is to show the cumulative completion each month.  Cumulative completion shows progress month-by-month and allows the final completion to be forecasted.

To show cumulative completion status, the progress made each month must be added to all the progress made in the months prior.  This is essentially the Sum function using an anchored range.  To anchor a range, prepend the row or column with a “$”.  Because of how I’ve setup the Pivot, Time displays in columns increasing from left to right.  So in column C, I get a formula like this:

=SUM($C22:C22)

The formula changes from column to column so that the formulas in columns D and E looks like this

=SUM($C22:D22)

=SUM($C22:E22)

See how the second part of the range argument increases along with the column, but the first part always stay the same?  That way, every formula displays the sum starting from column C until the present column.  That’s exactly what we wanted.

The way this works you only need to manually enter the formula into cell C22. Then you can “Fill Down” to extend the formula for the remaining regions.

With the Data Table set up, it’s trivial to display the results in a chart

Hiding Excel Errors

From my earlier articles you already know that I have a “thing” about Excel errors.  I don’t like them.  They are fine and even helpful when designing and creating a workbook.  But I believe that any errors which can be anticipated without being eliminated should be hidden.  It looks more professional.  So I hide expected errors.

And this data table will definitely have errors.  The errors are caused when I set up the data table so that it no changes will be required as the project progresses.  To do that, I need to extend the formulas beyond the current extent of the Pivot table, which will cause 2 kinds of errors.

The first error happens when there is no data beyond the extent of the current Pivot table. I’ve fixed this error before using the ISBLANK function.

The second error occurs when the Pivot table header is not a number.  In other words, when it displays “Grand Total”.  Because “Grand Total” is not a number, the ISNUMBER function can be used to hide this error.  See the formulas in row 29.  With these error-catching functions in place, you can select the first column of data in the table and drag right as far as you want to fill all the cells. I’ve dragged the formulas out as far as column P.  With the Data Table set up, it is trivial to display progress as a Chart.

Conclusion

In this article, I used Synthetic Data to Bucketize dates in the Updates table.  Bucketizing groups the data from many different dates into a single date such as the month.

Click this link to read the next article in the series.

Excel BTS Project Tracker – New Date Format

Coming Up Next

This article is getting quite long, so I’ve skipped discussing several things. In the next article in this series, I’ll cover some of those missing topics.

• I’ll show how to use Week Numbers as the date Bucket.
• There was a hidden error in the data table I created for this article. I’ll show that error and a technique not for hiding it, but avoiding it completely
• Add a chart to show the time progression of the project.
• Forecasting. Everyone, especially the boss, wants to know when the project will be finished. The project tracker now has the ability to show that.

Thanks again for reading.