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.

WKIP; removal of the taller tower

This is the original tower for WKIP, but not the original antenna. It was put up circa 1960 or so and like many towers from that era, has hollow legs. Thus, after 60 years or so, it is deteriorating from the inside out.

WKIP tower #1

This was part of a two-tower directional array. It is odd that a class C station on 1,450 KHz would have a directional antenna at all. Even stranger still, it was directional daytime, non-directional night, both at 1,000 watts. The reason for such an odd situation; the station was co-owned with WGNY in Newburgh and the daytime coverage contours would have overlapped without a directional array. The taller tower is 215 degrees tall with top loading. During the daytime, the pattern goes to the North and it covered very well.

Vertical Bridge, the tower owner, decided it was time to replace the aging structure with a monopole. They are completing the project this summer. Our part is to move WKIP to the shorter tower and put up a temporary FM antenna for the translator. Once the project is completed, WKIP will operate from the shorter tower (which is 85 degrees) permanently, getting rid of the now unnecessary directional antenna on a class C channel. The translator antenna will move back to the monopole, once it is put up.

Problems… Yes, we have a few of those…

WKIP tower #2 with broken guy wire

First, the short tower had a broken guy wire. Actually, the guy wire was fine, but the lowest grip connecting to the equalizing plate was rusted through. It is fortunate that this was discovered because the upper guy wire was getting ready to let go too. Northeast Towers was able to replace all of the grips on that set of guy wires and re-tension the tower. They did a full investigation of all of the other anchors prior to any climbing. This is in a swamp, which has flooded several times over the last few years.

Tower #2, guy wire repaired, Scala FMVMP translator antenna mounted

Next, the temporary FM translator antenna was hung on the tower. It was thought that the 3/8 sample line from the old AM sample system could be used as a temporary transmission line for this system. Unfortunately, that line turned out to be 75-ohm cable TV drop line and was not suitable for transmission of VHF. We had about 600 feet of leftover 3/8 sample line (Cablewave FCC 38-50J) from a decommissioned AM site, so we used that instead. It has quite a bit of loss on VHF, however, for temporary use, it will work.

Black Rat Snake, harmless and helpful
Black Rat Snake

Next, it seems this black rat snake had taken up residence in the ATU cabinet. The bottom of the ATU was full of mouse nests going back many years. One of our employees dutifully cleaned out the mouse nests unknowingly under the watchful eyes of this snake. Only after he was done, did he see the snake coiled up on the disused current meter shunt. There was a mild freakout for several minutes, but the snake left on his own and we got back to work. The black rat snakes are helpful to have around, but perhaps best if he stays outside of the ATU. We will seal up the entryway for the coax, which seems to be where all the critters are coming in.

Kintronic ISO-130-FM-N Isocoil

This Kintronic Isocoil was mounted to the back of the ATU with unistrut. Even though this is a temporary installation, I have found that sometimes temporary things can last much longer than anticipated. Besides, it was easier than trying to use pressure treated 4 x 4 lumber.

Next, we measured the ATU with the fancy machine (Agilent E5061B network analyzer). In theory, the ATU input should be 50 ohms to match the incoming transmission line. No, instead it was 38 Ohms -j20.

So, a little bit of a retune was required. With the fancy machine, we were able to get it to 52 ohms -j9 or so. This is good enough for now, there will be numerous cranes in the air and the station has an STA to run at 250 watts for the project’s duration. After the new monopole is up, we will measure the base impedance of the tower and tune up the ATU for 50 ohms and then return the station to full power at 1 KW.

Smaller crane, used to assemble the larger cranes

The old tower coming down:

Top section and top loading wires separated

Two cranes were used; one to hold and lower the tower section, the other to lift two tower workers to cut away the sections. The tower was deemed unsafe to climb, therefore it had to be removed like this. It was also unsafe to drop because of the proximity to the studio building and the other tower, which is being retained.

Top section being lowered
Next section removed and being lowered
Next section removed

You get the idea. These tower sections and guy wires were cut up and put in a scrap metal dumpster. They will be recycled into something else.

Now, they will work on removing the old tower base and putting up the monopole. Once that is done, we will tune up the AM on the short tower and get it back to full power.

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.

On the subject of All Digital Medium Wave

I want to explore all digital modulation methods for Standard Broadcast (AM, Medium Wave, or Medium Frequency). The most pressing technical problem for AM reception is electrical impulse noise. Can digital modulation solve this problem? Perhaps, but I am a natural-born skeptic.

To start out; I will say up front that the hybrid HD Radio (MA1) employed on AM was (or still is) a travesty. It never worked very well and it created massive interference +/- 20 KHz of the assigned frequency, especially when employed at night. Secondly; the all-digital version of HD Radio (HDMA3) remains a proprietary system with non-standard codecs. The current owner, Experi, has a license fee structure based on station type (AM, FM, LPFM, or Non-commercial) which ranges from $5,000 to $10,000 one-time fee for a five-year period. In all fairness; DRM pays a technology license fee to Fraunhofer for MPEG codecs used by receiver manufacturers and broadcast equipment. This is estimated to be between $0.13 to $1.13 US per receiver.

Those things being said, I thought a deep dive into the technical side of HDMA3 and DRM (Digital Radio Mondial) would be interesting. I did an article comparing MA3 and DRM a while ago: All Digital Medium Wave Transmission

What challenges are there to transmitting digital radio on MW? First, there is the very limited bandwidth of the channel itself. In North and South America, AM channels are spaced every 10 KHz (9 kHz in other places). On Medium Wave, the analog channel is +/- the carrier spacing, e.g. 20 KHz (or 18 KHz) with half of that channel potentially interfering with the adjacent channels. On a 20 kHz channel, this limits data transmission rates to 72 kbps or less with DRM and 40 kbps or less with HDMA3.

Secondly, skywave propagation is a potential difficulty for all digital broadcasts. Ionospheric changes can create multipath and fading, especially as the sun rises and sets causing the D layer to form or dissipate. Changes in the E and F layers can make or completely break skywave reception. Ground wave reception is reliable out to the limits of the noise floor, and varies based on transmitter frequency, power, and ground conductivity, and electrical noise in the area.

Everything that can potentially mitigate noise and skywave reception problems is a trade-off between robustness and data throughput.

Screenshot of an HF DRM exciter from RF Mondial showing a 10 KHz wide channel on HF.

Screen Shot of an HF DRM transmission showing mask, courtesy of DRM Consortium

This is a screenshot of an SDR showing an HF DRM transmission received from a distance:

Radio Romania International, 13,650 kHz 90 KW, 7,530 KM path

The receiver is not quite on bearing for this broadcast, however, it seems to be doing well. This is Radio Romania International’s Spanish broadcast targeting South America. The Pan Adaptor shows the signal is 10.2 kHz wide, but that doesn’t mean much from a $30.00 RTL SRD. The waterfall display below shows it is spectrally dense compared to the analog signals to the left and right. Note that with DRM there is no analog carrier being sent. Instead, a series of pilot tones are attached to various OFDM subcarriers for the receiver to lock onto.

A short Primer on COFDM

The modulation method for both systems is Coded Orthogonal Frequency Division Multiplexing (COFDM), which is the same system used by mobile phones, cable systems, WiFi (802.11), ATSC 3.0 TV, etc. COFDM consists of a group of subcarriers multiplexed onto one channel. The number of subcarriers and the subcarrier spacing relates directly to the data throughput and the robustness of the signal. OFDM is a very robust method that works well in the upper VHF, UHF, and SHF bands. It can work well in lower frequencies, however, there can be issues with multipath and Doppler effect. The coded part consists of forward error correction, which may include interleaving and subtracts from the data throughput.

The ability of an OFDM signal to reject electrical impulse noise, and deal with potential fading or multipath interference is based on a few things. The cyclic prefix sets the Guard Interval for the OFDM frame. The length of the Guard Interval should be the same as the multipath delay which helps mitigate inter-symbol interference and inter-subcarrier interference. Since the Medium Wave channels are fairly narrow, the number of OFDM carriers and spacing between carriers have a great effect on robustness. The fewer carriers the more robust the signal. This comes at the expense of data throughput; the fewer carriers the less data can be sent.

Raised Cosine impulses, similar to Orthogonal frequency-division multiplexing. (2023, May 10). In Wikipedia. https://en.wikipedia.org/wiki/Orthogonal_frequency-division_multiplexing

A short Primer on QAM

Each individual OFDM subcarrier is modulated with a Quadrature amplitude modulation (QAM) signal. The advantage of this is that each individual carrier sends data at a relatively slow rate and the aggregate data rate is the sum of all the subcarriers. QAM uses two carriers 90 degrees out of phase. The amplitude of each carrier determines the resultant vector of the modulated wave to create a data bit. For example; the sum of the carriers equals +45 degrees at 25% amplitude a 1101 data bit is sent.

16-QAM Constellation diagram. (2022, December 17). In Wikipedia. https://en.wikipedia.org/wiki/Constellation_diagram

Both HDMA3 and DRM can use 16-QAM or 64-QAM. The larger the QAM constellation the more data can be sent. Smaller QAM constellations are more robust. HDMA3 can also transmit QPSK, which is Quadrature Phase Shift Keying. The resultant waveform from QPSK is identical to 4-QAM.

Bringing it all together

Radio Romania International, 11,975 KHz 90 KW 7,530 KM path, English service to Western Europe and North America,

A DRM-modulated HF and MF transmitter uses both sidebands to transmit unique information. There is no carrier present but rather a few pilot frequencies for the receiver to lock onto.

WFAS White Plains, NY All digital HDMA-3 signal

I like the waterfall display available with many SDR software programs. It gives a good indication of modulation density. With WFAS HDMA-3, the area +/- 5 KHz of the carrier signal has more power than the areas that are +/- 5 to 10 KHz from the carrier.

An HDMA3-modulated MW carrier sends the same data on upper and lower sidebands, effectively halving the data rate of DRM. There is a full carrier present, which represents approximately 25% of the transmitted power and does not contain any data. Currently, there are four three HDMA-3 stations transmitting in the US.

Both systems can make pre-corrections to the modulated signal in the exciter to compensate for amplifier non-linearities. This can greatly improve the MER and SNR.

The other perceived technical issue with AM radio is sound quality. This has to do mostly with poor-quality receivers, although there are some AM stations that are transmitting reduced-quality audio as well. There is a false notion that anything “digital” sounds better than analog. I would posit; it depends on several factors. Low-bit-rate audio codecs can sound abysmal. That being said, the newer high-efficiency audio codecs can sound quite good, but there are limits. With HD Radio, there is only one codec available; HDC+SBR. With DRM there are several; xHE-AAC, HE-AAC. xHE-AAC is designed to work with voice and can use bit rates as low as 12 kbps. It is possible for a robustly transmitted low-bit-rate codec to sound good with voice. It can sound okay with music, but not as good as analog FM.


Can an all-digital modulation format work well on the Standard Broadcast Band? The answer is; it’s complicated. One of the big positives of AM is that it is a very simple and well-tested system. Adding many layers of encoding and decoding is a violation of the KISS principle. That being said, using a digital modulation method that has been refined for mobile use over the years is a step in the right direction. There still is an issue with digital receivers; both HD and DRM. From what I have read, both formats are currently being included in several radio chip sets, yet I do not find those options in most car radios. There is a lack of public awareness, at least in the United States about digital radio in general. When someone says digital, most people think of streaming. When I am driving a rental car, I seldom find HD Radio, I do find Sirius/XM and all types of internet connectivity via smartphone apps.