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.

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

DirectionFromStorm