DTV – Engineering Radio

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:

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:

=(10*LOG(C6))+30

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:

=IF(C11>C9,"HIGH",IF(C11<C8,"LOW","OK"))

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:

=10^((B22-30)/10)

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:

=299792458/B10

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:

=(299792.458/B10)/360*B11

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:

=(20.05*(273.16+B11)^0.5)/B12

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:

=SQRT(B12/B11)

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:

=B11/0.3048

Where B11 is the length in meters

Convert feet to meters:

=B12*0.3048

Where B12 is the length in feet.

Convert degrees F to degrees C:

=(B11-32)/1.8

Where B11 is the degrees Fahrenheit

Convert degrees C to degrees F:

=(B12*1.8)+32

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:

=B11/3412.142

Where B11 is the BTU/hr

Example of an Air Conditioner load estimation:

=(B11*B12-B11)*3412.142

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.

Doing it with sound AND pictures

AKA: Television!

I am talking about the type of TV you need an antenna for. I have been installing a few of these low-power digital TV transmitters at various places and it is good work.

Anywave MPTV 2.2 KW digital TV transmitter, WCRN Boston

LPTV antenna side mounted on tower in Quincy, MA ERP is 15 KW.

Anywave MPTV unpacking, rolling into the building, Ellenville, NY

This transmitter’s dry weight is about 500 pounds, which was a little bit too much for our appliance hand truck, so we built a ramp. They have a nice set of wheels on the bottom, so they roll into place.

Carrier power after pre-correction files updated

There are a few differences in the way things are done. First of all, there is a different set of acronyms:

ASI- Asynchronous Serial Interface, format for MPEG transport stream, max speed 270 Mbps.
SDI – Serial Digital Interface – Similar to ASI but can run much faster, up to 12 Gbps.
TS – Transport Stream, Encoded video and audio streams into the exciter. Same idea as composite audio input on an FM exciter.
TSID – Transport Stream ID, a unique number assigned to each DTV station and encoded with the transport stream.
PSIP – Program System Information Protocol, carries program and system data about the transport stream.
ATSC 3.0 – recently updated ATSC standard that allows TV stations to do more with their transport streams than before. Will also change the modulation from 8VSB to COFDM.

Then, some things that look familiar are called by different names, BNC cable vs ASI cable… I am a neophyte in the TV world, so there are still many things to learn.

EAS is still EAS, but now there is a locally generated video to go along with the audio.

A while back, some fool wrote their congressman because THE COMMERCIALS ON THE TEE-VEE WERE TOO LOUD, so there is something called CALM Act compliance.

However, at the output connector on the exciter, through the amplifier, filters, directional coupler, etc; it is RF and behaves like RF. Even more interesting; Mr. Doherty’s name is used to describe the RF amp. Doherty amplifier or Doherty modulation was designed by William Doherty for Western Electric in 1936. It was not until Continental Electronics began using it in their AM (317B) and Shortwave (420A) transmitters that it became known broadly. Old things are new again:

Continental Electronics 420A 500 KW Shortwave transmitter, Greenville site B

That picture is from my visit to Greenville, NC in 2017. This is the control console, the transmitter is behind the glass and takes up half the building. It was installed when the site was built in the early 1960s. The new Doherty UHF amplifiers use LDMOS devices, notably the BLF888E in the Anywave units.

Antenna sweep, channel 30 UHF slot antenna, WCRN Boston

Antenna with channel band pass filter, Channel 30 WCRN Boston

The Channel Band Pass Filter is required by the FCC, basically, it ensures that the TV transmitter is staying in its allotted 6 MHz channel. They add phase rotation, as noted above. This is why pre-correction is needed to keep the ATSC signal linear across the entire 6 MHz bandwidth. RF is RF and we like RF.

WMHT’s former analog transmitter

During the digital TV conversion in the US, all broadcast television stations installed new transmitting equipment and antennas. Most stations ended up on a different frequency than their original analog channel. In Albany, New York, all of the TV stations moved to a common transmitter site and installed their antennas on a single tower.

Albany DTV tower, home of WRGB, WTEN, WNYT, WXXA, WMHT, and WCWN

For more on the Albany DTV site, check out the NECRAT page: www.necrat.us/albdtv.html

So, what happened to the old Analog TV sites in Albany?

For the most part, after the analog turn-off on June 12, 2009, the sites have sat empty. Such is the case with the former WMHT site.

Sign outside of former WMHT transmitter building

This old sign about sums up the end of analog television.

Former WMHT analog transmitter wide shot

The former transmitter operator desk. The maintenance log is still open. From the looks of things, they opened the circuit breakers and walked away. Everything remains intact from the antenna to the klystrons and exciters. It does appear that the coolant has been drained from the system. Other than that, it seems like the whole thing could be restarted with minimal effort.

Former WMHT Onan DFN 350 backup generator

There were two Onan DFN 350 backup generators. With a TV transmitter, running the cooling system after shutdown is vital. The idea here is that both generators in parallel could run the whole station, if one generator failed, then the cooling system would still run and cool the klystrons.

Former WMHT tower, wave guide and WVCR antenna — Former WMHT tower, waveguide and WVCR antenna

The former WMHT tower, which currently holds the WVCR-FM, WXL-34 (NOAA weather radio), and W44CT-D (Three Angles Broadcasting) Low power TV transmitter.

Current site occupants; WVCR-FM and W44CT-D

These equipment racks and the NOAA weather radio transmitter in the other room are the only active equipment at this site.

WMHT-TV Chanel 17 (488-494 MHz) was signed on 1962 from this site. The Comark transmitter was installed in 1984. The station’s analog ERP was 2000 KW visual, 200 KW aural.

It is an interesting site.