Excel®™ Addin SPATIAL FUNCTIONS

What are Spatial functions? Mathematical functions used to manipulate geographic locations. Examples include calculating the distance between two points and counting the number of locations within a radius.

Use these functions to bring spatial patterns and relationships to your spreadsheet. Gain insights into the spatial distribution of your data. "How close are my customers?" "How many people use this product within 5km of my store?"

Distance Functions

Calculates the distance between 2 sets of latitudes and longitudes

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. lat2 {The Latitude of the second point}

  4. lon2 {The Longitude of the second point}

  5. units {Set the units to display the result ("KM" for Kilometres or "MI" for Miles)}

Example

=MAPCITE.DISTANCETO(B2,C2,B3,C3,"KM")

Finds the furthest point in a table from a specified point and returns the distance. The coordinates are expressed as decimal latitude and longitude.

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. table_array {A table of data from which the coordinates will be retrieved}

  4. lat_col_index_num {The column number in the table_array which contains the latitude data}

  5. lon_col_index_num {The column number in the table_array which contains the longitude data}

  6. units {Set the units to display the result ("KM" for Kilometres or "MI" for Miles)}

Example

=MAPCITE.DISTANCETOFURTHEST(B2,C2,A1:R101,2,3,"KM")

Finds the nearest point in a table to a specified point and returns the distance. The coordinates are expressed as decimal latitude and longitude.

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. table_array {A table of data from which the coordinates will be retrieved}

  4. lat_col_index_num {The column number in the table_array which contains the latitude data}

  5. lon_col_index_num {The column number in the table_array which contains the longitude data}

  6. units {Set the units to display the result ("KM" for Kilometres or "MI" for Miles)}

Example

=MAPCITE.DISTANCETONEAREST(B2,C2,A1:R101,2,3,"KM")

Finds the furthest point in a table to a specified point and returns the entry in a selected column. The coordinates are expressed as decimal latitude and longitude.

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. table_array {A table of data from which the coordinates will be retrieved}

  4. lat_col_index_num {The column number in the table_array which contains the latitude data}

  5. lon_col_index_num {The column number in the table_array which contains the longitude data}

  6. lookup_col_index_num {The column number in the table_array which contains the data to be returned}

Example

=MAPCITE.FINDFURTHEST(B2,C2,A1:R101,2,3,1)

Finds the nearest point in a table to a specified point and returns the entry in a selected column. The coordinates are expressed as decimal latitude and longitude.

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. table_array {A table of data from which the coordinates will be retrieved}

  4. lat_col_index_num {The column number in the table_array which contains the latitude data}

  5. lon_col_index_num {The column number in the table_array which contains the longitude data}

  6. lookup_col_index_num {The column number in the table_array which contains the data to be returned}

Example

=MAPCITE.FINDNEAREST(B2,C2,A1:R101,2,3,1)

How to video - Distance Functions
Radius Functions

Finds the points contained within a radius of a specified point and returns the average of the values in a specified column. The coordinates are expressed as decimal latitude and longitude.

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. table_array {A table of data from which the coordinates will be retrieved}

  4. lat_col_index_num {The column number in the table_array which contains the latitude data}

  5. lon_col_index_num {The column number in the table_array which contains the longitude data}

  6. average_col_index_num {The column number in the table_array which contains the data to be averaged}

  7. radius {A number that relates to the radius of the circle around the specified point}

  8. units {Set the units of the radius ("KM" for Kilometres or "MI" for Miles)}

Example

=MAPCITE.AVERAGEINRADIUS(B2,C2,A1:R101,2,3,18,1000,"KM")

Finds the points contained within a radius of a specified point and returns the count of the rows found. The coordinates are expressed as decimal latitude and longitude.

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. table_array {A table of data from which the coordinates will be retrieved}

  4. lat_col_index_num {The column number in the table_array which contains the latitude data}

  5. lon_col_index_num {The column number in the table_array which contains the longitude data}

  6. radius {A number that relates to the radius of the circle around the specified point}

  7. units {Set the units of the radius ("KM" for Kilometres or "MI" for Miles)}

Example

=MAPCITE.COUNTINRADIUS(B2,C2,A1:R101,2,3,1000,"KM")

Finds the points contained within a radius of a specified point and returns the maximum value in those rows within a specified column. The coordinates are expressed as decimal latitude and longitude.

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. table_array {A table of data from which the coordinates will be retrieved}

  4. lat_col_index_num {The column number in the table_array which contains the latitude data}

  5. lon_col_index_num {The column number in the table_array which contains the longitude data}

  6. max_col_index_num {The column number in the table_array which contains the Max value data}

  7. radius {A number that relates to the radius of the circle around the specified point}

  8. units {Set the units of the radius ("KM" for Kilometres or "MI" for Miles)}

Example

=MAPCITE.MAXINRADIUS(B2,C2,A1:R101,2,3,18,1000,"KM")

Finds the points contained within a radius of a specified point and returns the minimum value in those rows within a specified column. The coordinates are expressed as decimal latitude and longitude.

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. table_array {A table of data from which the coordinates will be retrieved}

  4. lat_col_index_num {The column number in the table_array which contains the latitude data}

  5. lon_col_index_num {The column number in the table_array which contains the longitude data}

  6. min_col_index_num {The column number in the table_array which contains the Min value data}

  7. radius {A number that relates to the radius of the circle around the specified point}

  8. units {Set the units of the radius ("KM" for Kilometres or "MI" for Miles)}

Example

=MAPCITE.MININRADIUS(B2,C2,A1:R101,2,3,18,1000,"KM")

Finds the points contained within a radius of a specified point and returns the sum of the values in those rows within a specified column. The coordinates are expressed as decimal latitude and longitude.

Parameters

  1. lat1 {The Latitude of the first point}

  2. lon1 {The Longitude of the first point}

  3. table_array {A table of data from which the coordinates will be retrieved}

  4. lat_col_index_num {The column number in the table_array which contains the latitude data}

  5. lon_col_index_num {The column number in the table_array which contains the longitude data}

  6. sum_col_index_num {The column number in the table_array which contains the data to sum}

  7. radius {A number that relates to the radius of the circle around the specified point}

  8. units {Set the units of the radius ("KM" for Kilometres or "MI" for Miles)}

Example

=MAPCITE.SUMINRADIUS(B2,C2,A1:R101,2,3,18,1000,"KM")

How to video - Radius Functions

GET THE ADD-IN HERE

Maps in Excel made easy

Microsoft Office APP Store