With any tool you use frequently to solve many problems, there’s sure to be lots of tricks and shortcuts that can make your job so much easier. Microsoft Excel is one of those tools for Telecoms, and there certainly are tricks and shortcuts. Learning them will help you become more proficient with Excel, and give you a pocketful of shortcuts for doing real work quickly.
This will do more than simply save your time. It allows you to derive insights and identify opportunities. You can actually start to understand what the data mean.
A common problem in Telecoms is that machine-generated raw data isn’t in the format you want. Or maybe there is data missing which is required to complete your analysis.
In this article, I’ll show you a technique I call Synthetic Fields. Synthetic fields allow you to modify data, or add data related to the raw data. Often Synthetic Fields are used to provide additional ways for Pivot Tables to summarize your data. This helps when you want to automate your Dashboards and recurring reports.
Follow Along
You can download my workbook to follow along with this article. The workbook will help you understand what I’ve written, and you can copy/paste to your own workbooks. I will ask for your email address so that I can send updates. I dislike SPAM as much as you, and I promise won’t ever send any. You can unsubscribe at any time.
Approach
Generally, my input data is imported or pasted. Sometimes it has to be typed. But that is always my last choice. As needed, the input data can be augmented using Synthetic Fields. The Synthetic Fields are just formulas which display modifications to the raw data, without actually changing the raw data itself. It’s good practice to avoid changing your raw data.
Synthetic Fields can also augment the raw data using lookup functions. Synthetic Fields use the raw data, without changing it in any way. Using Synthetic Fields is an Excel Best Practice.
The purpose of the Synthetic Fields is to provide another way for a Pivot Table to summarize the data.
The downloaded workbook displays the raw data in the yellow-highlighted columns. The purple-highlighted columns are the Synthetic Fields. I’ve mocked up this data to showcase the 3 main uses of Synthetic Fields. Although there can be many others uses of Synthetic Fields, these are the ones you’ll probably use most often.
- Parse useful data from an overly verbose input data
- Modify dates
- Lookup other information based upon some index value in the raw data.
That’s a lot of things. Let’s look at them one by one.
Note that the formulas in the Synthetic Fields are all wrapped by IF(ISBLANK()). These formulas are only intended to hide Excel errors when formulas are pre-populated in the workbook, but there is not yet any raw data for that row. This is not truly necessary. I do it because I don’t like the errors displaying. I think it looks more professional for the workbook to display no errors. I hide errors by testing the “Date Raw” column. If there is no data in that column, the Synthetic Field displays nothing. If there is data, the RIGHT() function returns it, and VALUE() tries to make it numeric.
Parse Raw Data
It’s pretty common for some Telecoms kit to output ASCII data in a key/value style notation, similar to this:
SITE=14,DATE=01/24/18,PEAK=29.7,LOSS=0.05%In this example, data fields are separated by commas, in CSV format. Each field is formatted “key=value”. The Peak Usage and Loss columns of the downloaded workbook show these values after the CSV has already been split into columns. I left this out for Site Number and Date Raw to simplify this article.
To uses this “key-value” data in any meaningful way, the values must be separated from the “key=” components. You do this with a formula. Remember to always set up your workbook to use the raw data in whatever form it’s provided. That minimizes the typing and editing required whenever you add data. With good judgment and a little luck, you’ll be able to simply import or paste your data without additional typing.
It is also a good idea to try saving the resulting numeric data in a numeric format, such as date, integer, float, percent, accounting, etc. That allows Excel to use the data as it normally would. If numeric data is left as text, Excel’s numeric functions might not work as you expect.
Handling Text
Excel has many functions for handling text data. In the downloaded workbook, look at the columns labeled “Peak” and “Loss”. Here is an example of “Loss”:
=VALUE(RIGHT(I2,LEN(I2)-Loss_Key_Length))Let’s look at the RIGHT() function first. The proper syntax is like this:
=RIGHT(text,num_chars)where the text is the raw data. The raw data is I2, which is in the “key-value” notation you started with, and num_chars is the number of characters of text you want to extract from the right end of the text. You cannot use a fixed number for num_chars because the length of the “value” can change on each row.
So I’ve started with the length of the entire “key=value” text, LEN(I2), and subtracted the length of the key. The key for this field is “Loss”, so the length is 4. You must also subtract the “=”. So you must subtract 5 from the total length of the raw data to give you num_chars.
Remember, it’s not good practice to embed magic numbers in formulas. So I created a named range Peak_Key_Length, entered “5”, then used this named range in the formula.
Values
Lastly, because the text returned by RIGHT() is numeric, or should be, I wrap it in a VALUE() function. That forces Excel to treat the returned text as a number.
I repeated more or less the same steps with the column “Peak”. In your workbooks you might have to repeat these steps many times to separate all the cruft from the raw values. These formulas will do all this work for you, as long as the data is consistent.
Modify Dates
Synthetic Fields help when you want to use dates in a structured way, but the raw data is scattered randomly in time. This Synthetic Field relies on the DATE() function. Look at the column named “Date” in the workbook. I want to group all the dates by month. Pivot Tables can do this natively with the “grouping” capability. But I prefer using Synthetic Fields so that the grouping is more obvious. Also, other groupings, such as by week number, are more challenging to do natively in a Pivot table.
I’ve written about the DATE() function before. You can see it below for row 2 of the workbook. The year and month arguments can be extracted directly from the Date Raw column.
But the day argument is different. I’ll group all values for a given month and year by forcing a specific day each month. I usually use the first day of the month. But once again, it’s bad practice to embed magic numbers in formulas. So I named a cell Report_Date, entered “1”, then used this named range in the formula.
=DATE(YEAR(H2), MONTH(H2), Report_Date)This Synthetic Field allows the Pivot table to summarize all the values in each month. And that’s what we wanted. When you set up your pivot table, use this dated field, rather than the “Date Raw” field.
Lookups
Any Telecoms kit can have hundreds of associated data elements. It’s impractical for performance statistics to include everything. Think about your BTS, about how much data the business maintains about each site. Lookups are a great way to augment your reporting by associating the equipment with an external data store.
I’ll use the example of BTS name and backhaul capacity. Look on worksheet Lookups. I created a lookup table called Site_Lookup which associates each site number with a name, a region, a backhaul medium and a capacity in Mbps. The index into this lookup table is the site number, which is part of the raw data output by the equipment. Here is the syntax of the VLOOKUP() function:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)Site Name
In this invocation, lookup_value is the site number, in cell G2. table_array is the named range Site_Lookup. To return the site name col_index_num is 2 because the site name is in the 2nd column of Site_Lookup. I want an exact match with the site number, so the value of range_lookup is FALSE. This returns the site name in column E. Here is the formula for site name:
=VLOOKUP(G2, Site_Lookup, 2, FALSE)Site Capacity
I also want to use the capacity value to display the utilization value as a percent of capacity, rather than as an absolute speed. All the arguments are the same as the previous VLOOKUP() except col_index_num. Backhaul capacity is in the 5th column of Site_Lookup. This formula returns backhaul capacity for the given site number:
=VLOOKUP(G2, Site_Lookup, 5, FALSE)Embedding a magic number in the VLOOKUP() can be avoided by creating a named range for each of the Site Name and Capacity columns in Site_Lookup. I’ll leave that up to you.
Finally, to get the utilization, divide the Peak Usage value in this row by the capacity from Site_Lookup. Like this, including the error-trapping code:
=IF(ISBLANK(H2),"",C2/VLOOKUP(G2,Site_Lookup,5,FALSE))Conclusion
I hope you try this in your own work and see how much faster you can update your workbooks. If you want to fully automate any of your Dashboards or recurring reports, Synthetic Fields are essential. For more Excel Telecom Tricks, read my other articles.
General Principles
A couple of general principles I want to emphasize with this approach. First, always set up your input worksheet to match the raw data as closely as possible. This reduces the number of manual steps required to update the workbook. If you set up your worksheet properly, adding data will be the only manual task you must do routinely. Always set up your workbook to make this as easy as possible.
Second, keeping the input data clustered together makes it easy to comply with another general principle and that is “separate content from presentation”. In this example, the raw data is the content. The Synthetic Fields, Pivot Table, and charts are all part of the presentation. When raw data is added to your workbook no manual changes should be required.
Once again, here is the link to download the workbook.
If you’d like to see more of my Telecoms technology and career-building tips, connect with me on LinkedIn Russell Lundberg or follow me on Twitter TelecomVoices