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?"

NEW! CO2e Functions

Calculates an estimate of the kg CO2e emission created over a defined distance.

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. kilogram {The weight of the item/s being transported}

  6. travelType {Set the travel type of the item/s, Select between}

    1. 'VAN' (Max 3500kg)

    2. 'HGV'

    3. 'FREIGHTFLIGHT'

    4. 'RAIL'

    5. 'CARGOSHIP'

Example

=MAPCITE.CO2EFREIGHT(B2,C2,B3,C3,10,"VAN")

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. travelType {Set the travel type, Select between}

    1. 'CAR'

    2. 'MOTORBIKE'

    3. 'TAXI' (Per Vehicle)

    4. 'BUS'

    5. 'COACH'

    6. 'RAIL'

    7. 'FERRY'

    8. 'FLIGHT' (Per Passenger)

Example

=MAPCITE.CO2EBUSINESSTRAVEL(B2,C2,B3,C3,"CAR")

"There are seven main GHGs that contribute to climate change, as covered by the Kyoto Protocol: carbon dioxide (CO2), methane (CH4), nitrous oxide (N2O), hydrofluorocarbons (HFCs), perfluorocarbons (PFCs), sulfur hexafluoride (SF6) and nitrogen trifluoride (NF3). Different activities emit different gases and you should report on the Kyoto Protocol GHG gases produced by your particular activities."

"As a minimum, for each activity there is a factor that can be used to calculate emissions of all relevant GHGs combined (kg CO2e per unit activity).
Additionally, for many activities, this factor is then split into separate factors for each gas (that is, kg CO2e of CO2/CH4/N2O per unit activity) which sum to the total kg CO2e per unit activity. These gas-specific factors can be used if desired."

(Source: https://www.gov.uk/government/publications/greenhouse-gas-reporting-conversion-factors-2020)

How to video - Distance Functions
youtube-video-thumbnail
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
youtube-video-thumbnail
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
youtube-video-thumbnail

GET THE ADD-IN HERE

Maps in Excel made easy

Microsoft Office APP Store