zip Code Radius Help

ConnorForm

Hello Everyone!!! Hope my question makes sense. But I am trying to figure out if there is a formula that can provide me a Max Distance radius from Retailer formula that shows the max range in which we should set our radius targeting for these retailers. I have the retailers full address, Zip, Lat and Long so I feel like there should be a formula that shows a radius based off those values. Current problem we are facing is all retailers are set to a 15 mi radius, some cover too much area while others do not cover enough space

I have this view in Tableau where the dot is the Retailer location and the circle around the dot is the radius we are targeting. The shaded region are those retailers' "territories" which consit of mulitple Zip Codes linked back to that retailer Currently going to each dealer to see a 10-40 mi radius will cover each one accurately. hoping the formula could help put me in a good spot with that.

EX: Dealers 7604 and 7642 and others in that cluster will need 10-15 radius while ones like 7672,7608,7610 will need much larger to cover that area

Genevieve P.

Hey @ConnorForm

This is a really interesting question!

Smartsheet does not current have a direct integration with a map or map app, so there isn't a built in function to look at a location radius.

That said, if you can figure out the math as math, then we could use a formula within Smartsheet to represent that math. Or, if you have a map application that you're using, you could potentially use a third party application (such as Zapier) or the Smartsheet API to feed that data into your sheet.

Cheers,

Genevieve

ConnorForm

Yeah @Genevieve P. I am not sure what formula or how the math maths here hahaha. I think the problem is I want this formula to give me a radius in miles. however I am using a column with the full address of a dealer, a column with 1 zip code where the dealer is located and a column with a group of zip codes that surrond the dealers address/zip code. I think the problem is I need another column to show the total miles the 1 zip code column encompasses and then the total miles the group of zip codes to encompass. I think with that information we can then come up with some kind of formula to give us either an AVG Radius to target for those zip codes, or a max radius that encompasses the group of zip codes around that dealer. If that even makes sense.

Genevieve P.

This is fantastic idea, but I haven't come up with a way that we can do this solely in Smartsheet. Have you found another program that can track radiuses and zip codes that we could integrate with?

ConnorForm

I tried using Mapsly with a free trial but was unsuccessful! I have not been able to find any other routes to take as well!

marc4

You want to convert locations into latitude and longitude and then there are simple formulas to tell you distance.

/marc

ConnorForm

Hey @marc4 I have Lat and Long for the specific Dealer Locations. So I need to make the Lat and Long of these groups of Zip codes to a group of lats and longs? Then I can compare the distances?

marc4

Yes the formula is

=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*6371 (6371 is Earth radius in km.)

Just google "calculate distance between to latitude and longitude points"

/marc