As a Telecoms Technical manager, your job is often about speed.  Make a report faster. Prepare a presentation faster.  Forecast usage faster.  Analyze a problem faster.  Create a budget faster.  See the pattern?

Youʼll use Excel to create models in all these examples; make assumptions and build them into those models.  Often, there will be several use cases for you to model.

How do you do that?  What’s your style?  Do you work on one case to completion, then copy/ paste to start the next case?

If you want to impress your boss and propel your career, learning how to properly model different use cases is an indispensable technique.  A key trick to effectively modeling different use cases is called Abstraction.  Abstraction is also an important trick for automating your dashboards and recurring reports.

Read on to learn how to add abstraction to your models.  Iʼll show you 3 techniques for using abstraction.  And Iʼll do it by building on another Excel Telecom Trick which I showed you earlier, the 2-Color Chart Series. 

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 to read along might help you.

Workbook to Follow Along

Approach using IF

2-Color Chart Series used a chart of Busy Hour Performance with the busy hour defined as a percentage of the maximum throughput.  But there are other ways to define the Busy Hour.  Letʼs use abstraction to see several different Busy Hour use cases.

In the first example, Iʼll use a simple IF function to determine which of two Busy Hour definitions to use. This is shown in the worksheet called “IF Abstraction”.

The Busy Hour condition from the 2-Color Chart Series trick is in its own cell, cell J10.

To create a second Busy Hour case, letʼs assume that we define the Busy Hour as a static value.  That value is entered in cell J9 and highlighted yellow to indicate manual input.

So now we have 2 distinct definitions of Busy Hour.  We need a way to choose which one to use.  The simplest way is to use an IF function in cell K4.  The IF function will first check to see if an absolute value is defined.  If it is, the busy hour will be defined by that absolute value.

If no absolute throughput value is defined, the other Busy Hour definition will be used, the percentage of max value.  Here is the formula using IF

=IF(ISBLANK(J9),J10,J9)

In English, this says simply “if present, use the value in cell J9, otherwise use the value in J10.”

To make the formulas in range G2:H25 more readable, I’ve named cell K4 Busy_Hour_Threshold.  Because in the previous article I setup those formulas to use the same named range, I have not had to change those formulas at all to implement this abstraction.

Approach using MATCH

What if we had more than 2 cases, more than 2 Busy Hour definitions?  Said another way, what if your model has 3 or even more use cases? The IF function could still be used.

But the IF approach requires using 2 or more IF functions, called “nested IFs”.  In a nested IF approach, the leftmost IF will perform the desired action for the TRUE condition.  For the FALSE condition, another IF is inserted.  This is repeated until all use cases are considered.

This approach pretty quickly becomes hard to read and debug.  There are so many parentheses that you have to be very careful to track where you are in the IF hierarchy.

For more than 2 use cases, the complexity of nested IFs can be avoided by using a different approach, of which there might be several.  I’ll show one using a MATCH function. This is shown on worksheet “MATCH Abstraction”.

I’ve inserted 2 additional use cases, in column K rows 12 and 13. You can read those formulas for yourself in the workbook. So now there are a total of 4 use cases.

Note that along with the 4 use cases, I also inserted a “Threshold Type” in column L, and a “chart title” in column M.  These are another form of abstraction.  The Threshold Type column simply gives a name to each use case, and the Chart titles column creates a use-case-specific title which changes with each use case.

We’ve discussed INDEX/MATCH functions before. Here is the formula:

=INDEX($K$10:$K$13,MATCH($L$7,L10:L13,0))

In this example, the array argument to INDEX is one-dimensional, so only 2 arguments, array, and row_num are required.

The image shows the drop-down menu as well as the chart showing the “Percent of Capacity” use case.  Notice how the chart title also reflects the use case, “(BH=82% of 2,000 Mbps)”.

The choice of use case displayed in the chart is determined by Cell L7.  I set up this cell to use “List” type data validation using the Threshold Type values in L10:L13 as the source for the list.  (Let me know if you aren’t familiar with this kind of data validation and we can discuss it in another article.)

To change the use case, click the drop-down menu to the right of cell L7 and select the use case you want, one of the Threshold Types.  As soon as the value in cell L7 changes, the values in columns G and H change, which also changes the chart columns and also the title. All 100% automatic!

Conclusion

Although this article is about Abstraction, manually changing text labels in charts whenever the underlying data changes is one of the most time-consuming parts of producing recurring reports and dashboards.  Abstraction allows you to automate this changes which is a huge time-saver!

Once again, here is the link to download the workbook.

Download the Workbook

If you want to see more of my Telecoms technology and career-building tips, connect with me on LinkedIn, Russell Lundberg or follow me on Twitter , TelecomVoices