{"id":51906,"date":"2022-02-18T05:30:10","date_gmt":"2022-02-18T09:30:10","guid":{"rendered":"https:\/\/swling.com\/blog\/?p=51906"},"modified":"2022-02-17T11:41:44","modified_gmt":"2022-02-17T15:41:44","slug":"guest-post-calculate-station-distances-using-excel-formulas","status":"publish","type":"post","link":"https:\/\/swling.com\/blog\/2022\/02\/guest-post-calculate-station-distances-using-excel-formulas\/","title":{"rendered":"Guest Post: Calculate Station Distances Using Excel Formulas"},"content":{"rendered":"

\"\"<\/a>Many thanks to SWLing Post contributor,\u00a0Bob Colegrove<\/a>, who shares the following guest post:<\/strong><\/em><\/p>\n

\n

Calculate Station Distances Using Excel<\/b><\/h1>\n

By Bob Colegrove<\/strong><\/p>\n

Introduction<\/b><\/h2>\n

On occasion, I\u2019ve wanted to know just how far away a station was from my home.\u00a0 I\u2019ve never been much of a contester, but I know distance can play a part in the results.\u00a0 There are a number of Internet cites which let you enter latitude and longitude information and then calculate the distance across the surface of the earth.\u00a0 These are alright on an occasional basis, but I often wind up getting the data mixed for the two locations, and it is not handy when you want to make several measurements.\u00a0 Here\u2019s a way to generate the distance from your home to thousands of stations with just a little effort.<\/p>\n

Many years ago, armed with my faded knowledge of high school trigonometry, I used Excel to calculate the surface distance between any two points on earth.\u00a0 I managed to find the spreadsheet (file dated 1998) which has no fewer than 11 steps in the algorithm.\u00a0 Although it worked, when I came back to it a few months later to make a change, I couldn\u2019t remember my thought process.\u00a0 There are Internet sites which develop earth surface calculations in highly esoteric terms and heavy-duty math.\u00a0 But life is short, and I wanted to cut to the chase.\u00a0 There are, in fact, several formula variations which have somehow managed to distill all this down to a neat single-cell calculation, and they seem to work very well.<\/p>\n

Construction<\/b><\/h2>\n

The spreadsheet figure below is the simplest form used when you have decimal latitude and longitude data as input.\u00a0 The convention is to use negative numbers for the Western and Southern Hemispheres.\u00a0 Home is your reception location and all other locations are compared with that to determine the distances.\u00a0 If you\u2019re curious, the home location (yellow cells) used in these examples is the monument marking the geographic center of all 50 US states in Belle Fourche, South Dakota.\u00a0 Google Maps is one easy source to determine the exact latitude and longitude of any point on earth.<\/p>\n

\"\"<\/a><\/p>\n

To calculate the distance between any two points on earth, copy the formula below directly into a cell, then change the reference cell names as appropriate, and you\u2019re ready to go.<\/p>\n

=ACOS(COS(RADIANS(90-$B$5)) * COS(RADIANS(90-B9)) + SIN(RADIANS(90-$B$5)) * SIN(RADIANS(90-B9)) * COS(RADIANS($C$5-C9))) * 3959<\/b><\/p>\n

$B$5 and $C$5 are the cell references for your home address (yellow in the figure above).\u00a0 Of course, the dollar signs indicate these data remain fixed in each calculation.\u00a0 B9 and C9 are corresponding latitude and longitude for the example radio station, WTOP (green).\u00a0 Change these four cell locations as necessary.\u00a0 The constant, 3959, at the end of the formula is the average radius of the earth in miles.\u00a0 Use 6371 if you want kilometers.\u00a0 The data cells in Columns D and E are populated with the formula and produce the result. These values are dynamic and can be replicated down the columns for the rest of your station location data.<\/p>\n

Degrees, Minutes, and Seconds Format<\/b><\/h2>\n

The US FCC on-line database contains latitude and longitude tower locations for medium wave stations in Region 2, North, South, and Central America.\u00a0 However, coordinates are in degrees, minutes, and seconds format and must be converted to digital format for calculation of distances.\u00a0 The conversion process can also be done in Excel.<\/p>\n

\"\"<\/a><\/p>\n

In this case, the inclusion of the coordinate hemispheres, N or S, and E or W is important.\u00a0 Whereas, the hemispheres in the decimal example were signed + or -, the inclusion of the appropriate letters here is necessary.\u00a0 Cell L5 reads<\/p>\n

=IF(H5=”S”,-I5-(J5\/60)-(K5\/3600),I5+(J5\/60)+(K5\/3600))<\/b><\/p>\n

and cell Q5 is similar for longitude, except \u201cW\u201d is substituted for \u201cS.\u201d\u00a0 These formulas are then replicated in columns L and Q for each data item.\u00a0 Columns R and S contain the distance calculation formulas as described above.\u00a0 Line 14 is not necessary, but can be used to see if your formulas are correct; that is, the distance from home to home should be zero.<\/p>\n

Let Excel Get the Information for You<\/b><\/h2>\n

What follows is for anyone tired of copying cumbersome latitude and longitude data.\u00a0 Unfortunately, it only works on the current version of Microsoft 365 Excel, and apparently goes off into the big cloud in the sky to instantly download the information.<\/p>\n

\"\"<\/a><\/p>\n

    \n
  1. Enter the town followed by either the US state, Canadian province, or other country name (Column A).<\/li>\n
  2. Copy these locations to the next column (Column B).\u00a0 The cells in Column B will become temporary geography<\/i> cells.\u00a0 Note:\u00a0 As shown above, the data have already been converted to geography format (Step 4).<\/li>\n
  3. Make sure you have all the geography<\/i> cell locations selected (Column B).<\/li>\n
  4. On the Data<\/i><\/b> ribbon select Geography<\/i><\/b>.\u00a0 A map icon will appear at the left of each cell, and the state, province and country will be truncated.<\/li>\n
  5. For the first latitude (Cell C7), enter =B7.Latitude<\/i><\/b>; likewise, =B7.Longitude<\/i><\/b> in Cell D7.<\/li>\n
  6. The formulas in C7 and D7 can be replicated down your list.<\/li>\n
  7. Columns for miles and kilometers (E and F) can be added using the distance formula as described above.<\/li>\n<\/ol>\n

    The geography data (Column B) cannot be replicated.\u00a0 If you want to add data later, you will have to reapply the geography format for the new data.\u00a0 Or, latitude and longitude can still be inserted manually for any additional entries.\u00a0 The geography data (Column B) are not needed beyond this point and can be deleted or hidden.<\/p>\n

    Note:\u00a0 I logged on to my first mainframe computer in September 1976 and have never ceased to be amazed at what these confounded things can be made to do.\u00a0 I tried as best I could to trip the system with small, obscure towns in faraway places, as well as duplicate names.\u00a0 I finally succeeded with a relatively large city, Ulaanbaatar, Mongolia.\u00a0 To be fair, I tried to get it to accept alternate spellings.\u00a0 So, if you need that one, you\u2019ll have to enter it manually.<\/p>\n

    Medium Wave Example<\/b><\/h2>\n

    This example is for medium wave DXers in Region 2, the Americas.\u00a0 It makes use of the FCC AM database at https:\/\/www.fcc.gov\/media\/radio\/am-query<\/a>.\u00a0 The database currently contains more than 24,500 entries, many of these are duplicate entries for stations using different daytime and nighttime powers.<\/p>\n

      \n
    1. Download the database as a pipe-delimited text file.<\/li>\n
    2. Import the file into Excel.<\/li>\n
    3. Create additional columns to convert the latitude and longitude data from degree-minute-second format to decimal as described above.<\/li>\n
    4. Add some rows above and enter your home coordinates in decimal.<\/li>\n
    5. Create another column to calculate the distance from home to all the stations, again using the base formula above.<\/li>\n
    6. Hide any columns in the FCC database that you don\u2019t need.<\/li>\n
    7. Finally, by creating an Excel table from all of the data, except your home location, you can do some on-the-fly filtering.<\/li>\n<\/ol>\n

      The example below shows some of the stations near our example home in Belle Fourche, South Dakota.\u00a0 The Distance column on the right has a filter applied to limit the listing in the table to stations within a 150 mile radius, that is, it only lists potential daytime stations.\u00a0 You could also use the conditional formatting feature of Excel to highlight the same information in the unfiltered data.<\/p>\n

      \"\"<\/a><\/p>\n

      Shortwave Example<\/b><\/h2>\n

      The AOKI log, http:\/\/www1.s2.starcat.ne.jp\/ndxc\/<\/a>, has listings for all of the recent broadcasting cycles, B21, A21, etc.\u00a0 The Excel format files are zipped for download, and include the latitude and longitude of each station.\u00a0 Unfortunately the coordinates are not only in degrees, minutes and seconds, but they are all mashed together in one cell for each listing.\u00a0 Excel to the rescue again.\u00a0 Select Text to Columns<\/i><\/b> in the Data<\/i><\/b> Tools<\/i><\/b> portion of the Data<\/i><\/b> ribbon.\u00a0 This feature will allow you to divide the single column into four columns each for latitude and longitude, that is, degrees, minutes, seconds and hemisphere.\u00a0 Then you can use the conversion formula to change degrees-minutes-seconds to decimal.\u00a0 Note that the first three digits used for longitude are minutes (they go up to 180); the remaining numerical columns have two digits each (up to 60 or 90), and the hemisphere columns (alpha) one character each.<\/p>\n

      Accuracy<\/b><\/h2>\n

      Here are a few things affecting accuracy:<\/p>\n

        \n
      1. The constants 3959 or 6371 used in the formula for miles and kilometers are generally accepted averages for the earth\u2019s radius.\u00a0 The difference between the equatorial (longer) and polar (shorter) radii is about 13 miles.<\/li>\n
      2. If you are using town locations in your data, remember that the actual distance to the tower in that town is likely to be different.\u00a0 The FCC and AOKI data are assumed to be station tower locations.<\/li>\n
      3. Some decimal sources of latitude and longitude data have less resolution, which could lead to a slight error.<\/li>\n<\/ol>\n

        You\u2019re on Your Own<\/b><\/h2>\n

        You may have noticed the examples shown in the figures all have multiple station locations. My thought in doing this was provide some test for accuracy and secondly to provide a seed for developing the spreadsheet into a more inclusive log of stations. There is likely enough basic Excel knowledge among the folks gathered here, and each person will likely have an individual preference in designing a spreadsheet. Nevertheless, the spreadsheet shown in the figures can be downloaded by clicking this link<\/a>.<\/p>\n

        The first sheet shows Figures 1 and 2 from this article; and the second sheet, Figure 3. The link in Cell I2 of the second sheet describes how to use the geography feature of Microsoft 365 Excel. The third sheet is a recent copy of the FCC AM database (Figure 4). To facilitate storage and downloading, only stations from 530 kHz to 600 kHz are included. Numerous unused columns from the FCC AM database have been hidden; so you can still copy the full, pipe-delimited FCC database into Columns A through AH. The FCC database has been converted to an Excel table; the Home location is not part of the table. Try substituting your own location for Home (Cells AI2, latitude and AJ2, longitude) and setting a distance filter from your home in Cell AK4. In the example, the distance filter has been set limiting the list of stations to less than 600 miles from our example in South Dakota. Note also that the Conditional Formatting feature on the Home ribbon has been used to highlight stations less than 100 miles from home.<\/p>\n

        If you have any interest in developing your own spreadsheet, perhaps you can comment on what you have done, or provide the rest of us with something I have missed. Hopefully, I have provided enough information to get you started.<\/p>\n","protected":false},"excerpt":{"rendered":"

        Many thanks to SWLing Post contributor,\u00a0Bob Colegrove, who shares the following guest post: Calculate Station Distances Using Excel By Bob Colegrove Introduction On occasion, I\u2019ve wanted to know just how far away a station was from my home.\u00a0 I\u2019ve never been much of a contester, but I know distance can play a part in the […]<\/p>\n","protected":false},"author":15,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8,5935,3194,545,627,2436,433,3,43],"tags":[4097,9669,7988,9670,9667,4099,34,9668],"class_list":["post-51906","post","type-post","status-publish","format-standard","hentry","category-articles","category-dx","category-guest-posts","category-ham-radio","category-how-to","category-longwave","category-mediumwave","category-news","category-shortwave-radio","tag-am","tag-as-the-crow-flies-distance-calculation","tag-bob-colegrove","tag-distance-calculation-spreadsheet-formulas","tag-excel","tag-mediumwave","tag-shortwave","tag-speadsheets"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pn3uc-dvc","jetpack-related-posts":[{"id":60935,"url":"https:\/\/swling.com\/blog\/2024\/10\/responding-to-the-colegrove-inspiration\/","url_meta":{"origin":51906,"position":0},"title":"Responding to “The Colegrove Inspiration”","author":"Jock Elliott","date":"October 24, 2024","format":false,"excerpt":"By Jock Elliott, KB2GOM It was Bob Colegrove\u2019s post -- this one\u00a0-- that inspired me and set me on this path. Medium wave broadcast band DXing (MWBCB) has a certain fascination for me, but I am poor at it. Oh sure, I have read the advice: map the band at\u2026","rel":"","context":"In "AM"","block_context":{"text":"AM","link":"https:\/\/swling.com\/blog\/category\/am\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/10\/Satellit-on-1690-001-001-1024x662.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/10\/Satellit-on-1690-001-001-1024x662.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/10\/Satellit-on-1690-001-001-1024x662.jpg?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":62344,"url":"https:\/\/swling.com\/blog\/2025\/03\/bobs-radio-corner-pairing-a-phone-or-tablet-with-a-radio\/","url_meta":{"origin":51906,"position":1},"title":"Bob’s Radio Corner: Pairing a Phone or Tablet with a Radio","author":"Thomas","date":"March 15, 2025","format":false,"excerpt":"Pairing a Phone or Tablet with a Radio By Bob Colegrove The Internet is full of resources for radio listeners and hobbyists.\u00a0 There are numerous examples of general information, frequency, and schedule lists.\u00a0 There are also WebSDR sites, which allow you to compare reception experiences from around the world.\u00a0 All\u2026","rel":"","context":"In "AM"","block_context":{"text":"AM","link":"https:\/\/swling.com\/blog\/category\/am\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2025\/03\/Fig-6-U-Twente.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2025\/03\/Fig-6-U-Twente.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2025\/03\/Fig-6-U-Twente.jpg?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":60460,"url":"https:\/\/swling.com\/blog\/2024\/08\/u-twente-websdr-new-tablet-phone-interface-option\/","url_meta":{"origin":51906,"position":2},"title":"U Twente WebSDR: New Tablet\/Phone Interface Option","author":"Thomas","date":"August 25, 2024","format":false,"excerpt":"Many thanks to\u00a0SWLing Post\u00a0contributor,\u00a0Bob Colegrove, who shares the following guest post: U. of Twenty Site for Phones and Tablets by Bob Colegrove I\u2019m not sure if this is new.\u00a0 It may have been around for a while.\u00a0 Very recently, while accessing the WebSDR site at the University of Twente, it\u2026","rel":"","context":"In "News"","block_context":{"text":"News","link":"https:\/\/swling.com\/blog\/category\/news\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/08\/Figure.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/08\/Figure.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/08\/Figure.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":60370,"url":"https:\/\/swling.com\/blog\/2024\/08\/a-band-aid-for-the-xhdata-d-220\/","url_meta":{"origin":51906,"position":3},"title":"A Band Aid for the XHDATA D-220","author":"Thomas","date":"August 11, 2024","format":false,"excerpt":"Many thanks to\u00a0SWLing Post\u00a0contributor, Bob Colegrove, who shares the following guest post: A Band Aid for the XHDATA D-220 By Bob Colegrove In the olden days of analog radios, we would have generated a graph plotting frequency against a 0 to 100 linear bandspread scale. That permitted determination of a\u2026","rel":"","context":"In "Guest Posts"","block_context":{"text":"Guest Posts","link":"https:\/\/swling.com\/blog\/category\/guest-posts\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/08\/Fig-1.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/08\/Fig-1.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/08\/Fig-1.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/08\/Fig-1.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/08\/Fig-1.jpeg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":61368,"url":"https:\/\/swling.com\/blog\/2024\/12\/bobs-1968-radio-tirana-qsl-card\/","url_meta":{"origin":51906,"position":4},"title":"Bob’s 1968 Radio Tirana QSL Card","author":"Thomas","date":"December 12, 2024","format":false,"excerpt":"Many thanks to SWLing Post contributor Bob Colegrove, who writes: Kudos to Don Moore for his excellently crafted article on Albanian radio. [...]Perhaps some might be interested to see a 1968 QSL from R. Tirana. Regards, Bob Colegrove Thank you for sharing this excellent Radio Tirana QSL card, Bob!","rel":"","context":"In "News"","block_context":{"text":"News","link":"https:\/\/swling.com\/blog\/category\/news\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/12\/R-Tirana-Inside.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/12\/R-Tirana-Inside.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/12\/R-Tirana-Inside.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/12\/R-Tirana-Inside.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2024\/12\/R-Tirana-Inside.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":49864,"url":"https:\/\/swling.com\/blog\/2021\/08\/bob-colegrove-on-the-joys-and-challenges-of-tuning-analog-radios\/","url_meta":{"origin":51906,"position":5},"title":"Bob Colegrove on “The Joys and Challenges of Tuning Analog Radios”","author":"Thomas","date":"August 1, 2021","format":false,"excerpt":"Many thanks to SWLing Post contributor, Bob Colegrove, who recently shared this excellent article and has kindly allowed me to share it here in the the Post. Bob prefaced it by saying, \"Being a retired technical writer, I started the attached article some time ago for my own amusement, but\u2026","rel":"","context":"In "Books"","block_context":{"text":"Books","link":"https:\/\/swling.com\/blog\/category\/books\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2021\/08\/Bob-Colegrove-Book-1.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2021\/08\/Bob-Colegrove-Book-1.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2021\/08\/Bob-Colegrove-Book-1.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/swling.com\/blog\/wp-content\/uploads\/2021\/08\/Bob-Colegrove-Book-1.jpeg?resize=700%2C400&ssl=1 2x"},"classes":[]}],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/swling.com\/blog\/wp-json\/wp\/v2\/posts\/51906","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swling.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swling.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swling.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/swling.com\/blog\/wp-json\/wp\/v2\/comments?post=51906"}],"version-history":[{"count":0,"href":"https:\/\/swling.com\/blog\/wp-json\/wp\/v2\/posts\/51906\/revisions"}],"wp:attachment":[{"href":"https:\/\/swling.com\/blog\/wp-json\/wp\/v2\/media?parent=51906"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swling.com\/blog\/wp-json\/wp\/v2\/categories?post=51906"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swling.com\/blog\/wp-json\/wp\/v2\/tags?post=51906"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}