Using #Excel to Find How Far the Event is from Your Location

Note: It is important to note that this Excel model can be used any place in the world to look at the direction from storms or any other event.

 The destructive nature of hurricanes has been dominating the news recently in the Atlantic basin. But, many times it is difficult to get information about how far a storm is from your specific location. This excellent Excel technique will allow you to answer that question. All that you will need is your address (or an address of interest) and the current storm coordinates. Key parts of this technique were found at the following links.

https://chandoo.org/forum/threads/using-excel-to-find-lat-and-long-from-a-street-address-geocode.9793/

https://www.mrexcel.com/forum/excel-questions/541185-convert-number-deg-direction-text-n-nne.html

In the worbook provided, enter your address information in cell B1and the storm coordinates in C4 and D4. The formulas used to calculate your result are:

LocationXML =WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&Address&”+,+&sensor=false”)

Lat_1 =FILTERXML(LocationXML,”//result/geometry/location/lat”)

Lng_1=FILTERXML(LocationXML,”//result/geometry/location/lng”)

Distance=ACOS(SIN(Latitude_1*PI_DIV180)*SIN(Latitude_2*PI_DIV180)+COS(Latitude_1*PI_DIV180)*COS(Latitude_2*PI_DIV180)*COS((Longitude_2*PI_DIV180)-(Longitude_1*PI_DIV180)))*3959

PI_DIV180=PI()/180

Direction =DEGREES(ATAN2(COS(RADIANS(Latitude_1))*SIN(RADIANS(Latitude_2))-SIN(RADIANS(Latitude_1))*COS(RADIANS(Latitude_2))*COS(RADIANS(Longitude_2-Longitude_1)),SIN(RADIANS(Longitude_2-Longitude_1))*COS(RADIANS(Latitude_2))))

TextDirection=CHOOSE(1+ROUND(IF(Direction<0,360+Direction,Direction)/22.5,0),”N”,”NNE”,”NE”,”ENE”,”E”,”ESE”,”SE”,”SSE”,”S”,”SSW”,”SW”,”WSW”,”W”,”WNW”,”NW”,”NNW”,”N”)

The final formula for displaying the desired information (in cell A7) is:

=ROUND(Distance,0)&” miles “&TextDirection&” of “&Address

The result can be viewed here.

DirectionFromStorm1

I hope that you find this useful. Download the example file:

DirectionFromStorm

 

Advertisements

2 thoughts on “Using #Excel to Find How Far the Event is from Your Location

  1. Pingback: #Excel Super Links #149 – Shared by David Hager | Excel For You

  2. Pingback: Get Latest Storm Information in #Excel Using Only An Address and Storm Name | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s