Automating Metrics with Formulas

Step 1: Prepare Your Tracker and Data

First, please open the incident tracker workbook you created in the last lesson. We need some data to work with.

1. Add the following 5 records to your tracker. Remember to use the dropdowns for Incident Type, Severity, and Status. For the dates, you can type them in as shown (e.g., 5-Oct-2024).
2. On your Settings worksheet, create a small list of company holidays. This will be important later. For now, just add two dates in an empty area:

3. Select these two dates (e.g., E2:E3 if you placed them there) and name this range Holidays. To do this, click in the Name Box (to the left of the formula bar), type Holidays, and press Enter.

Sample Data for Your Tracker:
(For any blank Resolution Date, just leave the cell empty)

Your tracker is now set up with a mix of open and closed incidents. Let's start calculating.

Step 2: Calculate "Aging" for Open Incidents

"Aging" tells us how long an incident has been open right now. This is a dynamic metric that updates every day. It's critical for prioritizing old or "stale" tickets.

Our logic will be: if an incident is not closed, calculate the number of days from when it was raised until today. If it is closed, the aging is zero.

1. Go to your Tracker sheet. Click on the header of the Resolution Date column (cell H1). Right-click and select Insert > Table Column to the Right.

2. Name the new column header Aging (Days).

3. In the first data cell of this new column (I2), enter the following formula:

   =IF(OR([@Status]="Open", [@Status]="In Progress"), INT(TODAY()-[@[Date Raised]]), 0)

Let's break this down:

• [@Status] and [@[Date Raised]] are structured references. Because we're using an Excel Table, the formula is more readable, referring to column names instead of cell addresses like G2 or B2.
• OR([@Status]="Open", [@Status]="In Progress"): This checks if the incident is currently active.
• TODAY(): This function always returns the current date. It's what makes the aging calculation dynamic.
• INT(TODAY()-[@[Date Raised]]): If the incident is active, this calculates the difference between today and the date it was raised. We use INT() to get a whole number of days.
• , 0): If the incident is not active (i.e., it's "Closed" or "On Hold"), the formula returns 0.

Once you press Enter, the Excel Table should automatically fill this formula down for all rows. Make sure the column is formatted as a Number, not a Date.

Step 3: Calculate Turnaround Time (TAT) in Business Days

Turnaround Time (TAT) measures how long it took to resolve an incident. Unlike aging, it's a fixed value calculated only for closed incidents. In most business settings, you'll be asked to calculate this in working days, excluding weekends and public holidays.

To do this, we'll use one of Excel's most useful functions for business analysis: NETWORKDAYS.INTL.

Now, let's apply this to our tracker:

1. Add another new column to the right of "Aging (Days)" and name it TAT (Work Days).

2. In the first cell of this column (J2), enter this formula:

   =IF([@Status]="Closed", NETWORKDAYS.INTL([@[Date Raised]], [@[Resolution Date]], 1, Holidays), "")

Let's dissect this one:

• IF([@Status]="Closed", ...): This ensures we only calculate TAT for incidents that are actually finished.
• NETWORKDAYS.INTL(...): This is the core of the calculation.
  • [@[Date Raised]]: The start date.
  • [@[Resolution Date]]: The end date.
  • 1: This is the [weekend] argument. The number 1 tells Excel that weekends are Saturday and Sunday.
  • Holidays: This is the [holidays] argument. It refers to the named range you created on the Settings sheet.
• , ""): If the status is not "Closed", the formula returns an empty text string "", which looks much cleaner than a 0 or an error.

You should now see the TAT calculated for the three closed incidents in your list. Notice how INC-005, which crossed the Christmas holiday, has its TAT calculated correctly by skipping those non-working days.

Step 4: Categorize Aging into "Buckets"

Having a list of aging days is good, but for reporting, it's much more effective to group them into "buckets" (e.g., "1-7 Days", "8-15 Days"). This is a classic MIS technique. We could use a complex nested IF statement, but a far more professional and scalable method is to use VLOOKUP with an approximate match.

Let's implement this.

1. On your Settings sheet, create the following "helper table" for our aging buckets. It's critical that the first column is sorted in ascending order.

2. Select this table (e.g., G2:H6), go to the Name Box, type AgingBuckets, and press Enter to name the range.

3. Back on your Tracker sheet, add one final column to the right and name it Aging Bucket.

4. In the first cell of this column (K2), enter the following formula:

   =VLOOKUP([@[Aging (Days)]], AgingBuckets, 2, TRUE)

• [@[Aging (Days)]]: This is the value we want to categorize.
• AgingBuckets: The named range of our helper table.
• 2: We want to return the category name from the second column of the helper table.
• TRUE: This specifies an approximate match. It tells VLOOKUP to find the largest value in the first column of AgingBuckets that is less than or equal to our lookup value. For example, if the aging is 12 days, it finds 8 and returns "8-15 Days".

This VLOOKUP method is far superior to nested IFs because if you need to change your bucket definitions, you only have to update the simple helper table on the Settings sheet; you don't have to rewrite a complicated formula.

Conclusion

Fantastic work today! You have successfully transformed your static tracker into a dynamic analysis tool. By adding calculated columns, you've automated the generation of core business metrics, which is a fundamental skill for any MIS Executive.

Key Takeaways:

• Calculate Aging with TODAY(): Use the TODAY() function within an IF statement to calculate the current age of open items.
• Calculate Business Duration with NETWORKDAYS.INTL: This is the standard for calculating TAT, excluding weekends and holidays.
• Use Helper Tables for Categorization: Combining a helper table with a VLOOKUP(..., TRUE) formula is the most professional and scalable way to group data into buckets.
• Structured References are Your Friend: Formulas within Excel Tables are self-documenting and easy to read (e.g., [@[Date Raised]] is clearer than B2).

In our next lesson, we will leverage these new, powerful columns. You will learn to build a separate summary/calculation sheet using SUMIFS and COUNTIFS to create dynamic Key Performance Indicators (KPIs). This is where we start building the summary report layer that sits on top of your tracker.