In Telecoms we deal with data.  Lots of data.  Tasks such as budgeting, analyzing, reporting and forecasting use this data. This data buries your desk.  You yourself might actually be stuffed with data. 

We gorge on data and starve for information.

Amidst all this data is a concerted search for information.  Ferreting out information and deriving insight from all this data is what you must do as a Telecoms Manager.  

In this article, I’ll show you a trick for revealing that information.  Often in Telecoms and in other disciplines, you want to highlight specific values in a chart.  A good example is a chart of Busy Hour Performance. 

Highlighting the color of the hour(s) having the greatest value can add value to your audience.  Microsoft Excel doesn’t allow you to do this natively.  In this Excel Telecom Trick, I’ll show you how to do that.  It’s super simple, and like most Excel Telecom Tricks, explaining it is more difficult than doing it.

Approach

Many of you have to deal with Busy Hour performance in one way or another.  The performance data I’ll use for this example will be throughput on an Ethernet link.  I want to show the average throughput during each hour of the day, highlighting the throughput for any hour which exceeds my defined busy hour threshold.  I’ll begin with mocked-up performance data for a 24-hour period already in hand.

I’ll start by defining Busy Hour as any hour where the average throughput exceeds 92% of the maximum throughput.  Of course, there are other ways to define the busy hour.  Your company may have its own way.  Whichever way you define it, put that definition in its own Excel cell.  Then, refer to that cell when you test each hourly value.  This approach gives you space to explain clearly what you are doing, which helps someone else, or even yourself, understand what you are doing.

Whatever way you define your busy hour, this technique uses that definition to create 2 columns of data from the original single column.

Supporting Workbook

You can download my workbook to follow along with this article.  I refer to the workbook throughout the rest of the article, so having it will help you learn the trick.  I’ve also included the PDF “Excel Telecoms Tricks”, essential reading for Telecoms Technology Managers who frequently create dashboards, forecasts, financial models, and analyses.

Download the Workbook

Procedure

1. The average hourly throughput is shown in columns B & C.  We will chart this metric.
2. Midnight to 1 AM is hour 0; 11 PM to Midnight is hour 23.
3. I want to use the technique of showing MAX() values in a different color in a chart.
4. Define the Busy Hour Threshold as any throughput value exceeding 92% of the maximum throughput.
5. It’s bad practice to embed numbers in formulas.  So avoid this by defining a named range for this threshold.  Set the threshold value in this named range. This has been done in cell K2.  The range is called Busy_Hour_Threshold.
6. To simplify the threshold-determining formula, the maximum throughput value was also calculated and named in cell K3. The name is Tput_Max.
7. The final named range shows the product of the Maximum Throughput and the Busy Hour Percentage. This is named Busy_Hour_Threshold, Cell K4.
8. In column G, insert a formula which shows the throughput only if it exceeds the threshold. Here is the formula:

IF($C2>Busy_Hour_Threshold,$C2,"")

In English, this says if the Hourly Throughput value is greater than the threshold value, display the Hourly Throughput.

10. In Column H, show the throughput if it is less than or equal to the threshold. Here is the formula:

IF($C2<=Busy_Hour_Threshold,$C2,"")

In English, this says if the Hourly Throughput value is less than or equal to the threshold value, display the Hourly Throughput.

So you can see that the only difference between the 2 formulas is the operator in the test expression: “greater than” versus “less than or equal to”.

The net effect of these 2 formulas is two columns of Hourly Throughput values. One of these columns contains numbers which exceed the threshold value and the other numbers which are less than the threshold value.

11. When I chart these 2 separate values, column H will be blue, and column G red.
12. The Busy_Hour_Pct value can be changed to control how many red values will display, and the duration of the Busy Hour.

Analysis

The point of this Excel Telecom Trick is to show you how to create a chart which automatically highlights some values.  But your job description probably demands that you also analyze and interpret; provide insight.

Using this chart, you should now seek to understand whatever it is telling you and share that understanding with your peers, your boss, and others throughout your company.  If you can do that consistently, I promise you’ll become an increasingly valued member of the team.  Congratulations!

Conclusion

Here is the link to download the workbook.

Download the Workbook