An objective common to almost every Telecoms activity you’ll do in your career is to share your work with others. Sharing in this sense can take many forms:
- Document steps taken.
- Identify a problem.
- Teach a task to others.
- Reveal a hidden truth.
- Propose another plan.
- Brainstorm fiercely.
The context for each of these tasks is Telecoms. But the actual process might seem only distantly related to the courses you took and theories they taught. Yet it is this process of data collection, manipulation, analysis, and, most importantly, presentation and sharing, which is at the very core of Telecoms.
Communicating your ideas so that others can understand them is a huge challenge. The technological skills and comprehension of your audience can be so unpredictable. Their goals and objectives may differ wildly from your own. So to achieve your goals and objectives, it’s up to you to communicate them in a way which is suitable for your audience.
There are entire courses taught on data presentation and visualization. It’s a big topic with lots of angles. In this article, I’ll discuss a very narrow technique from the field. It’s called “normalization”. Normalization is a way to present data so that it’s meaning or implications are more clear. I’ll show you several approaches for using Excel to normalize your data.
Follow Along
You can download my workbook to follow along with this article. The workbook will help you understand what I’ve written, and allows you to copy/paste into your own workbooks. I do ask for your email address so that we can keep in touch. I dislike SPAM as much as you, and promise I won’t ever send any. You can unsubscribe at any time.
What is Normalization
First, what is Normalization? Why would you want to use normalization? When is important to normalize your data?
Normalization is a technique for comparing dissimilar values to make trends or patterns or relationships clearer.
Sometimes the data might include values which vary dramatically. Highly variable data can mask underlying trends. Or maybe you are trying to compare 2 or more data series, and the relationships between the series are lost in the variability. Or the values of the series differ by orders of magnitude. Normalization can help in each of these situations. If the values change erratically, normalization can help to reveal the trend or pattern.
Self-Referential Normalization
The simplest way to Normalize data is to compare it to itself. You can see this approach in the accompanying workbook, on the “BH – All Time” worksheet. First, choose one value with which all the other values will be compared. This is called the baseline value.
For my first example, I’ll use the minimum tput value for the day. Next, divide each value in the data series by the baseline value. Lastly, multiply each value by 100.
Now the value of the normalized series at the baseline value is 100. All other values will be more or less than 100. When the minimum tput is used as the baseline, it’s easy to see that the value of the maximum tput is 3.5 times the minimum tput.
I made a second chart using the average tput for the whole day as my baseline value. I used the same procedure, dividing each value by this baseline, then multiplying by 100. The overall shape of the graph is the same, as you would expect. But look at the vertical axis. When the average tput is used as the baseline, you can see how much the daily minimum and maximum vary below and above the average.
These 2 examples illustrate that you should choose your baseline depending upon the point you’re trying to make.
Point in Time Normalization
When your data varies over time, you might want to choose the baseline not because of its value, but because of the date or the time. The procedure is the same, only the criteria for your choice are different.
On worksheet “BH – Month-by-Month”, I’ve laid out the tput load by month. Using the dates in the source data, I set up a validation list, which allows the baseline date to be selected from a drop-down menu. When you select a date, the value associated with that date is used as the baseline. Here is an image of the chart.
Notice how the title shows which date is used as the baseline. If you change the baseline date, the Chart title will change automatically. This is the subject of a future Excel Telecom Tricks article. It’s an important technique to full Excel automation. The formulas in column U get the monthly tput value from the Pivot Table. Then, the formula divides tput value by the tput value on the date you selected.
Moving Baseline Normalization
Suppose you wanted to compare several series to another series. A common example of this is used to show how currencies track over time vs. the US dollar. But let’s stick with a Telecoms example. Let’s compare how the average tput of each element group compares to the overall average. I did this on worksheet “BH – Moving Baseline”.
To do this, we’ll compare the tput of each group to the tput average hour-by-hour. So our baseline will not be a single value, but a series of values. A moving baseline. For each hour, divide the group tput average by the overall average for that hour, then multiply by 100. See the accompanying chart.
In this case, I won’t pretend that this is a useful representation of this throughput data. But it adequately illustrates the technique. You should now be able to apply it yourself in more appropriate situations.
Conclusion
I’ve shown you several ways to normalize your data and talked about when you might want to. It’s up to you to try it for yourself. Once you get comfortable with this simple technique and when to apply it, you’ll have a powerful tool to your toolkit and can begin differentiating yourself from your less-skilled peers. Go for it!
Once again, here is the link to download the workbook.
If you’d like to see more of my Telecoms tools and career-building tips, connect with me on LinkedIn Russell Lundberg or follow me on Twitter TelecomVoices