Power Map?

[elsmith9035768]

I am in real estate and use Excel to import data from my local multiple listing service into Excel as a csv file. From a Google search, it appears I can map addresses in Excel using Power Map. What I want to do is download sales for an area and then have Excel separate the listings based on distance from a target address. So, the download may be for a 5-mile radius from a target address and then I want Excel to geocode the addresses so that I can separate addresses within one mile, two to three miles, and four to five miles. Anyone know if this is possible using Power Map or another geocoding software?

 

[Chihiro]

That's not really Power Map function. Power Map uses Zip, Lat/Long (geo-code), or city/country name (using centroid of city/country) with other statistical data to visualize data on maps.

https://support.office.com/en-us/ar...ower-Map-88a28df6-8258-40aa-b5cc-577873fb0f4a

What you are looking for sounds more like obtaining geo-code/distance info from list of addresses.

There's several stages involved.

1. Obtain Lat/Long of addresses.
This can be done via Map API, using VBA to send info (Google, BING etc). Or you may use software such as ArcGIS.

https://developers.google.com/maps/
https://www.microsoft.com/maps/choose-your-bing-maps-API.aspx
https://geonet.esri.com/welcome

2. Calculate distance from target address' Lat/Long to each address' Lat/Long. You can use haversine formula to calculate shortest travel distance (over earth's surface, ignoring altitude change) between points.

https://rosettacode.org/wiki/Haversine_formula

3. Chart data.

 

[Chihiro]

Here's User Defined Function for Haversine formula. Note that in Excel argument for Atan2 is in reverse order from most programming languages.

If calculating in miles. You can change 6371(km) to 3958.756(mile).

Public Const earthRadius = 6371
Public Function Haversine(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) As Double
Dim E As Double
Dim pi As Double

pi = 4 * Atn(1)
E = pi / 180

lat1 = lat1 * E
lat2 = lat2 * E
lon1 = lon1 * E
lon2 = lon2 * E

haver1 = Sin((lat2 - lat1) / 2) ^ 2 + Cos(lat1) * Cos(lat2) * Sin((lon2 - lon1) / 2) ^ 2
haver2 = 2 * WorksheetFunction.Atan2(Sqr(1 - haver1), Sqr(haver1))

Haversine = haver2 * earthRadius

End Function

 

[elsmith9035768]

Thanks,
This is over my head. I think I'll have to hire someone to complete this task.
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Peter Bartholomew]

This was an attempt from a couple of years ago. To be honest, it was more of an exploration of naming conventions than a serious attempt to produce a navigational aid. From memory, the result was not exact but then I had entered city centre locations rather than airport locations.