Excel spreadsheet formulas for Broadcast Engineers

There are many times when some mathematics is needed in this profession. For one-off situations, the calculator applications found on most smartphones will work just fine. However, sometimes the calculation is complex or is needed to be repeated many times. Excel Spreadsheets have many mathematical functions built in. Plugging a formula into an Excel spreadsheet is a handy tool.

I recently acquired this rather nice precision power meter:

Mini Circuits precision power meter

It has an input power range of -60 to +20 dBm with a stern warning not to exceed +23 dBm. Since we will be using this for a variety of applications, I thought it might be useful to know approximately how much power will be presented to the instrument in any given situation. For example, we are installing a 30 KW FM transmitter soon. The directional coupler that will be used has a coupling factor of -48.5 dBm. The TPO is 28,000 watts.

The formula to convert Watts to dBm is dBm=10 X Log10(Pw) + 30, where Pw is power in Watts. Thus dBm=10 X log10(28000) + 30 or 74.4715 dBm minus the 48.5 dBm coupling factor which is 25.9715 dBm. That is too much input for this power meter. A 20 dB attenuator will need to be used.

Since I will be using this meter in other places, rather than doing that calculation over and over again, why not build an Excel spreadsheet? That would make it easy to check.

A simple Watts to dBm calculator in Excel looks like this:


This is copied into cell C11. C6 is the cell in which the Transmitter output power in watts is entered. The other cells contain the coupling factor (C5) and external attenuation (C7) In application, it looks something like this:

Excel spreadsheet power meter calculations

You can arrange these any way you like, just change the cell numbers to suit your needs.

I like to make the data entry cells green. You can lock the formula cells so that the formulas don’t get changed accidentally. Below the Approximate port power cell, is the IF statement that will return either a “LOW”, “HIGH”, or “OK” depending on the result value in C11. That looks like this:


The spreadsheet itself is downloadable: Power meter port calculator

It would be very easy to make a system gain/loss calculator for using the licensed ERP to calculate the proper TPO.

Other examples of useful Excel spreadsheet formulas:

To convert from dBm to watts:


B22 is the cell in which the power in dBm is entered. These can be any place you want on the spreadsheet.

Radio Frequency to Wavelength in Meters:


Where B10 is the cell in which the frequency in Hz is entered. 299792458 is the speed of light (Meters per second) in a vacuum. If you wanted the input frequency to be in kHz, simply move the decimal point for the speed of light three places to the left, e.g. 299792.458. For MHz move the decimal four places to the left, GHz five places, etc.

Convert electrical degrees to Meters:


Where B10 is the frequency in kHz and B11 is the number of electrical degrees in question.

An example of that in an Excel Spreadsheet can be downloaded: Frequency to Wavelength converter

Audio Frequency to Wavelength in Meters:


Where B11 is the air temperature in degrees Celsius and B12 is the frequency in Hz. Room temperature is normally about 21 degrees Celsius (about 70 degrees Fahrenheit). Humidity and altitude can also affect the sound wave velocity, which will affect the wavelength.

Base (or common point) current calculator using base impedance and licensed power:


Where B12 is the License power in watts and B11 is the measured base impedance of the tower (or common point impedance of the phasor).

Convert meters to feet:


Where B11 is the length in meters

Convert feet to meters:


Where B12 is the length in feet.

Convert degrees F to degrees C:


Where B11 is the degrees Fahrenheit

Convert degrees C to degrees F:


Where B12 is the degrees Celsius. In this case, the order of operations will work without the prentices but I kept them in place for uniformity.

Convert BTU to KW:


Where B11 is the BTU/hr

Example of an Air Conditioner load estimation:


Where B11 is the TPO, B12 is the transmitter AC to RF efficiency. The output is in BTU.

There is an entire list of Excel functions here: Excel Functions (alphabetic order)

You get the idea. Yes, there are smartphone applications as well as online calculators for most of these functions. However, I have found smartphone apps are becoming more painful to deal with as time goes on, mostly due to the ads. App developers need to make money, and you can buy apps for things that are often used. However, it is nice to have these types of calculators available offline. Besides, it is fun to play around with Excel formulas.

Fixing the switching power supply

This particular power supply is used in Broadcast Electronics AM1A, AM2.5E, AM5E, AM6A, AM10A, FM1C, FM10T, FM20T, FM30T and FM35T transmitters. It is a Computer Products NFN 40-7610, 40 Watt, +5 VDC, +/- 15 VDC BE part number 540-0006.

BE AM1A ECU power supply, C-15 marked with pen for replacement

Generally, one component fails over time on this unit, C-15 which is a 680 uF 35 V electrolytic capacitor. When that capacitor dries out, the power supply will fail to start, do odd things like start and fail after a second or two, or cycle on and off. This will happen after the transmitter has been off for a few minutes. Replacing C-15 with a 1000 uF 50 V capacitor will fix the problem. There is enough room for the larger capacitor if the leads are left a little bit long.

BE AM1A repaired ECU power supply re-installed

We have several of these repaired units on various shelves at various transmitter sites.

As always, when replacing electrolytic capacitors, pay attention to the polarity otherwise this will happen:

Blown Electrolytic Capacitor installed backward

I suppose somebody was in a hurry to get home that day. After I installed this repaired unit, it ran for about 15 seconds and then there was a pop. I opened the door on the ECU and white smoke was wafting out from under the power supply cover. Since the Pope is still The Pope, I knew it was the electrolytic capacitor.

Back in business

Our beloved BE AM1A is back in service. This transmitter is 22 years old and we can keep it going for as long as parts are available.

Continental 816R2 still chugging along

Had to do some work at a transmitter site today and had to put the backup transmitter on the air for a few minutes.

WFLY backup transmitter, on the air

These were good units in their day and many continue on. This transmitter was new in 1986 and served as the main transmitter until 2015 or so. This unit still has the tube driver (4CX250B) which tended to use a set of tubes every year or so. The PA tube normally lasted 4 years or so.


I miss the analog meters sometimes. There is something satisfying watching all the meters come up together when the plate on button is pushed.


Greetings from the Roxborough tower farm, a place with roots. It is slightly northwest of Philadelphia, PA, and is home to many TV and FM stations. The public road that cuts through the tower farm is called Domino Lane because if one tower falls, they all fall. A comforting thought to those that live in the vicinity I am sure.

View from the residential neighborhood next to the site

The reason for the visit; this rather nice GatesAir FLX20 transmitter:

Newly installed FLX20+HD, WRNB Philadelphia, PA

I must admit, I am growing rather fond of these transmitters. This unit is being installed because the station had to move from its old site, just down the hill. The tower owner is taking down the tower and building due to the age of the tower. Thus, it was moved into the KYW-TV building. If Wikipedia is to be believed, KYW-TV is the oldest TV station in Philadelphia, signing on in 1932.

The site is still being built as we were installing this transmitter. These days, the electricians are having supply chain problems like everyone else. There were delays getting the large electrical panel board and other necessary things for the build-out.

Cooling system high point, sight glass, and air purge valve

Overall, the installation went well. This system is using flexible hoses for the coolant loop. We have installed two of these liquid-cooled transmitters with 1 1/2 copper pipe. These days, copper pipe is expensive, so most are opting for flexible hose installation.

Pump Station; system top off

Topping off the pump station after 50/50 fillup. After the initial system fillup, it takes a while for all of the dissolved air to come out of the Heat Transfer Fluid (HTF). The extra steps with a liquid-cooled system are worth it, especially if the station is running HD. With the HD carrier on, the transmitter efficiency is 54% AC to RF. With a TPO of around 15 KW, that is a whole lot of heat that needs to be dissipated during operation. It is much cheaper to pipe the heat outside to a heat exchanger than to use several tons of AC to remove it from the room.

Heat Exchanger

Overall, this was a fun project.