Note:
You must sign in to an ArcGIS account to use the ArcGIS for Excel functions. If you are not signed in, a sign in error appears when you hover over the green triangle in the upper left corner of a selected cell. In Microsoft Windows Desktop or macOS, you will also see a yellow warning icon.
Microsoft Excel includes a set of functions or formulas that you can use to calculate information, from simple arithmetic sums to complex population of cells.
ArcGIS for Excel includes similar functions that you can use for geocoding, data enrichment, and performing route operations. Each function starts with an equal sign (=) in a cell followed by the function name and its input parameters, if any, in parentheses.
Tip:
As you write a function in a cell, you can use cell references by selecting a specific cell or cells in a column or row. This ensures data integrity. See the Microsoft Create or change a cell reference article for more information.
Note:
The geocoding, GeoEnrichment Service , and routing functions consume credits. Geometry functions do not consume credits. The SUGGESTADDRESS function consumes credits when you request more than 15 suggestions. Helper Functions do not consume credits. Your ArcGIS functions are cached to prevent additional credit consumption if and when the functions are refreshed. This cache increases the size of your Microsoft Excel file. To clear the ArcGIS for Excel functions cache, click the Clear cache button in the Settings pane.
You can copy the examples in this topic into an Excel worksheet to visualize how functions work. You can also use the ArcGIS for Excel functions in the ArcGIS Function Builder tool.
Some ArcGIS functions may fail with an error that the service is not available when running them within a cell in Excel. Contact your organization administrator to enable the service or get more information.
See the ArcGIS Enterprise and ArcGIS Online documentation for more information about services.
The following sections describe the ArcGIS for Excel functions and include the syntax, an example, and descriptions of the parameters.
Note:
In the function examples below, the actual result of the functions excludes the header. Run the ADDHEADERS function to request the headers.
GETCOORDINATES
This is a Geocoding function. Geocode a location. This returns possible locations including longitude, latitude, and, optionally, the address matched and the match score.
Syntax
ARCGIS.GETCOORDINATES(address, [spatial_reference], [match_details], [geocode_service])
Parameters
Parameter | Description | Parameter required |
---|---|---|
address | The location to be geocoded. This can be a street address, place-name, postal code, or POI. | Yes |
spatial reference | The spatial reference for the point location. This can be either a WKID (number) or WKT (text). See the list of valid WKID values. The default is 4326 (WGS84). | No |
match_details | If set to TRUE, this returns not just the longitude and latitude but also the address name of the matched point and the match score. The default is FALSE. | No |
geocode_service | Choose a geocoding service from the drop-down menu. The service lists all the locators available to members of your organization. | No |
Note:
In all the function examples, the actual result of the functions excludes the header. Run the ADDHEADERS function to request the headers.
Function example 1
=ARCGIS.GETCOORDINATES("380 New York Street, Redlands, CA, USA")
Result
Longitude (X) | Latitude (Y) |
---|---|
-117.19479 | 34.057265 |
Function example 2
=ARCGIS.GETCOORDINATES("380 New York Street, Redlands, CA, USA", 4326 , TRUE)
Result
Longitude (X) | Latitude (Y) | Address | Match score | Geolocator | Address type |
---|---|---|---|---|---|
-117.1948 | 34.057265 | 380 New York St, Redlands, CA, 92373, USA | 100 | World | PointAddress |
GETADDRESS
This is a Geocoding function. Determine the address at a particular longitude-latitude location through reverse geocoding.
Syntax
ARCGIS.GETADDRESS(longitude(X), latitude(Y), [spatial_reference], [all_data], [geocode_service])
Parameters
Parameter | Description | Parameter required |
---|---|---|
longitude(X) | The longitude of the point from which to search for the closest address. | Yes |
latitude(Y) | The latitude of the point from which to search for the closest address. | Yes |
spatial reference | The spatial reference for the point location. This can be either a WKID (number) or WKT (text). See the list of valid WKID values. The default is 4326 (WGS84). | No |
all_data | If set to TRUE, this returns not just the reverse-geocoded address name but also all address properties, such as City or Region. The default is FALSE. | No |
geocode_service | Choose a geocoding service from the drop-down menu. The service lists all the locators available to members of your organization. | No |
Function example 1
=ARCGIS.GETADDRESS(-70.251, 43.65808)
Result
Address |
---|
Standard Baking, 75 Commercial St, Portland, ME, 04101, USA |
Function example 2
=ARCGIS.GETADDRESS(-70.251, 43.65808, 4326, TRUE)
Result
Address matched | Full address name | Short address name | Address type | Type | Place name | Street number | Street name | Block | Sector | Neighborhood | District | City | Metropolitan area | Sub-region | Region | Region abbreviation | Territory | Postal code | Postal code extension | Country | Country code |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Standard Baking | Standard Baking, 75 Commercial St, Portland, ME, 04101, USA | Standard Baking | POI | Bakery | Standard Baking | 75 | 75 Commercial St. | Portland | Cumberland county | Maine | ME | 04101 | United States | USA |
ENRICHBYPOINT
This is a GeoEnrichment function. Get enriched demographic information about the people, places, and businesses in a specific area or within a certain distance or drive time from a location. The default distance is within a one-mile radius of the point.
Syntax
ARCGIS.ENRICHBYPOINT(longitude(X), latitude(Y), [spatial_reference], [distance], [unit],[travel_mode],[data_collections],[variables],[include_derivative_variables], [include_studyarea_details])
Parameters
Parameter | Description | Parameter required |
---|---|---|
longitude(X) | The longitude of the point of interest. | Yes |
latitude(Y) | The latitude of the point of interest. | Yes |
spatial_reference | The spatial reference for the point location. This can be either a WKID (number) or WKT (text). See the list of valid WKID values. The default is 4326 (WGS84). | No |
distance | The radius or distance from the point, which is used to compute the ring buffer area for the demographic data. For example, use 3 for a three-mile radius. The default is 1. | No |
unit | The unit of measurement for the distance parameter. This can be meters, kilometers, feet, yards, miles, or nautical miles. If the travel_mode parameter is set, this can also be seconds, minutes, or hours. The default is miles. | No |
travel_mode | If this is set, it will compute an area using travel distance from the point of interest. This can be walking, driving, or trucking. If left blank, a ring buffer area will be computed. | No |
data_collections | Preassembled lists of attributes that will be used to enrich the input features. Run the LISTDATACOLLECTIONS function to request a list of data collections. For example, use KeyUSFacts, KeyGlobalFacts. The default is KeyGlobalFacts. | No |
analysis_variables | Variables to be returned from the data collections—for example, KeyGlobalFacts.TOTPOP, AGE.MALE5. If left blank, all variables of the data collection KeyGlobalFacts are requested. Run the LISTDATACOLLECTIONS function to request a list of variables, or review the Data Collection Finder to see a list of variables. | No |
include_derivative_variables | Derivative variables to include in the output. This can be percent, index, average, or all. If left blank, no derivative variable will be included. | No |
include_studyarea_details | If set to TRUE, this will include the associated study area details, such as the radius and distance measurement method used to compute the relevant area around the point. Default is FALSE. | No |
Function example 1
=ARCGIS.ENRICHBYPOINT(-90.491, 38.777)
Result
Total population | Total households | Average household size | Male population | Female population |
---|---|---|---|---|
11464 | 4860 | 1.98 | 5745 | 5719 |
Note:
In all the function examples, the actual result of the functions excludes the header. Run the ADDHEADERS function to request the headers.
Function example 2
=ARCGIS.ENRICHBYPOINT(-90.491, 38.777, 4326, 5, "miles", "driving", "health", "X8001_X, X8021_X", "index", TRUE)
Result
Country code | Area type | Buffer units alias | Buffer radii | 2022 Healthcare | 2022 Healthcare: index | 2022 Dental services | 2022 Dental services: index |
---|---|---|---|---|---|---|---|
US | NetworkServiceArea | Drive Distance Miles | 5 | 240971277 | 99 | 15463985 | 99 |
ENRICHBYGEOGRAPHY
This is a GeoEnrichment function. Get information about the people, places, and businesses in specific named administrative boundaries—or levels of geography—of a map, such as districts, states and postal codes.
Syntax
ARCGIS.ENRICHBYGEOGRAPHY(area_id, geography_level_id, [data_collections], [variables], [include_derivative_variables], [include_geography_details])
Parameters
Parameter | Description | Parameter required |
---|---|---|
area_id | The FIPS ID of the area where demographic information will be obtained. Run the LISTGEOGRAPHIES function to request a list of area IDs for a particular geography level. For example, "06" is the State of California. | Yes |
geography_level_id | The ID of the geography level to which the area ID belongs. Run the LISTGEOGRAPHIES function to request a list of area IDs for a particular geography level. For example, "US.States" is the states in the United States. | Yes |
data_collections | Preassembled lists of attributes that are used to enrich the input features. Run the LISTDATACOLLECTIONS function to request a list of data collections. An example is "KeyUSFacts, KeyGlobalFacts". The default is KeyGlobalFacts. | No |
analysis_variables | Variables to be returned from the data collections—for example, KeyGlobalFacts.TOTPOP, AGE.MALE5. If left blank, all variables of the data collection KeyGlobalFacts are requested. Run the LISTDATACOLLECTIONS function to request a list of variables, or review the Data Collection Finder to see a list of variables. | No |
include_derivative_variables | Derivative variables to include in the output. This can be percent, index average, or all. If left blank, no derivative variable will be included. | No |
include_geography_details | If set to TRUE, this will include the associated geography details, such as country code, geography level, and area ID. The default is FALSE. | No |
Function example 1
=ARCGIS.ENRICHBYGEOGRAPHY("06","US.States")
Result
Total population | Total households | Average household size | Male population | Female population |
---|---|---|---|---|
39770476 | 13570050 | 2.86 | 19767455 | 20003021 |
Function example 2
=ARCGIS.ENRICHBYGEOGRAPHY("08019","ES.Municipalities", "IncomeTotalsAIS")
Result
2020 Total household income per year | 2020 Average household income per year | 2020 Average household income per month | 2020 Average annual income per capita | 2020 Average monthly income per capita |
---|---|---|---|---|
1.99E + 10 | 29898.9 | 2491.57 | 11938.06 | 994.84 |
Note:
In all the function examples, the actual result of the functions excludes the header. Run the ADDHEADERS function to request the headers.
Function example 3
=ARCGIS.ENRICHBYGEOGRAPHY("08019","ES.Municipalities", , "KeyGlobalFacts.AVGHHSZ, IncomeTotalsAIS.NINCHA")
Result
Average household size | 2020 Average household income per year |
---|---|
2.5 | 29898.9 |
Function example 4
=ARCGIS.ENRICHBYGEOGRAPHY("06","US.States","health", "X8001_X, X8021_X", "index", TRUE)
Result
Standard geography level | Standard geography name | Standard geography ID | Country code | 2022 Health care | 2022 Health care: index | 2022 Dental services | 2022 Dental services: index |
---|---|---|---|---|---|---|---|
U.S. States | California | 06 | US | 1.08894E+11 | 113 | 7.37E+09 | 119 |
ENRICHBYADDRESS
This is a GeoEnrichment function. Get enriched demographic information about the people, places, and businesses in a specific area or within a certain distance or drive time from an address. The default distance is within a one-mile radius of the point.
Syntax
ARCGIS.ENRICHBYADDRESS(address, [distance], [unit],[travel_mode],[data_collections],[variables],[include_derivative_variables], [include_studyarea_details])
Parameters
Parameter | Description | Parameter required |
---|---|---|
address | The location to be geocoded. This can be a street address, place-name, postal code, or POI. | Yes |
distance | The radius or distance from the point, which is used to compute the ring buffer area for the demographic data. For example, use 3 for a three-mile radius. The default is 1. | No |
unit | The unit of measurement for the distance parameter. This can be meters, kilometers, feet, yards, miles, or nautical miles. If the travel_mode parameter is set, this can also be seconds, minutes, or hours. The default is miles. | No |
travel_mode | If this is set, it will compute an area using travel distance from the point of interest. This can be walking, driving, or trucking. If left blank, a ring buffer area will be computed. | No |
analysis_variables | Variables to be returned from the data collections—for example, KeyGlobalFacts.TOTPOP, AGE.MALE5. If left blank, all variables of the data collection KeyGlobalFacts are requested. Run the LISTDATACOLLECTIONS function to request a list of variables, or review the Data Collection Finder to see a list of variables. | No |
include_derivative_variables | Derivative variables to include in the output. This can be percent, index, average, or all. If left blank, no derivative variable will be included. | No |
include_studyarea_details | If set to TRUE, this will include the associated study area details, such as the radius and distance measurement method used to compute the relevant area around the point. Default is FALSE. | No |
Function example 1
=ARCGIS.ENRICHBYADDRESS("380 New York Street, Redlands, CA, USA")
Result
Total population | Total households | Average household size | Male population | Female population |
---|---|---|---|---|
11464 | 4860 | 1.98 | 5745 | 5719 |
Note:
In all the function examples, the actual result of the functions excludes the header. Run the ADDHEADERS function to request the headers.
Function example 2
=ARCGIS.ENRICHBYADDRESS("380 New York Street, Redlands, CA, USA", 5, "miles", "driving", "health", "X8001_X, X8021_X", "index", TRUE)
Result
Country code | Area type | Buffer units alias | Buffer radii | 2022 Healthcare | 2022 Healthcare: index | 2022 Dental services | 2022 Dental services: index |
---|---|---|---|---|---|---|---|
US | NetworkServiceArea | Drive Distance Miles | 5 | 240971277 | 99 | 15463985 | 99 |
ENRICHBYPOLYGON
This is a GeoEnrichment function. Get information about people, places, and businesses in a specific area.
Syntax
ARCGIS.ENRICHBYPOLYGON (esrijson_polygon, analysis_variables, include_derivative_variables)
Parameters
Parameter | Description | Parameter required |
---|---|---|
esrijson_polygon | A polygon as an EsriJSON geometry, representing area for which demographic information will be obtained. | Yes |
analysis_variables | Variables to be returned from the data collections—for example, KeyGlobalFacts.TOTPOP, AGE.MALE5. If left blank, all variables of the data collection KeyGlobalFacts are requested. Run the LISTDATACOLLECTIONS function to request a list of variables, or review the Data Collection Finder to see a list of variables. | Yes |
include_derivative_variables | Output must contain derivative variables. The parameter value can be: Percentage, Index, Average, or All. If left blank, no derivative variable is included. | No |
FINDROUTE
This is a Routing function. Find the total travel distance (the default measurement unit is miles) and time (in minutes) between two locations using the best route found.
Syntax
ARCGIS.FINDROUTE(from_longitude(X), from_latitude(Y), to_longitude(X), to_latitude(Y), [spatial_reference], [travel_mode], [unit], [include_directions])
Parameters
Parameter | Description | Parameter required |
---|---|---|
from_longitude(X) | The longitude coordinates of the starting location of the route. | Yes |
from_latitude(Y) | The latitude coordinates of the starting location of the route. | Yes |
to_longitude(X) | The longitude coordinates of the destination location of the route. | Yes |
to_latitude(Y) | The latitude coordinates of the destination location of the route. | Yes |
spatial_reference | The spatial reference for the point location. This can be either a WKID (number) or WKT (text). See the list of valid WKID values. The default is 4326 (WGS84). | No |
travel_mode | The travel mode for the route. Instead of a ring buffer area, compute an area using a travel mode, for example, walking, driving, or trucking. For example, use Driving Distance or Walking Time. Run the LISTTRAVELMODES function to request a list of supported travel modes. If left blank, the default travel mode specified in your ArcGIS organization settings is used. See Configure travel modes to learn more about travel modes and parameters. | No |
unit | The measurement unit for the travel distance. This can be miles, kilometers, meters, feet, points, yards, or nautical miles. The default is miles. | No |
include_directions | If set to TRUE, the routing directions to the destination and the time and distance traveled at each step will be included in the results. The default is FALSE. | No |
Function example 1
=ARCGIS.FINDROUTE(-77.24488778, 38.92894278, -80.78300851, 35.1001845)
Result
Distance traveled (miles) | Time traveled (minutes) | Route shape |
---|---|---|
391.3764 | 401.6001295 | {"spatialReference":{"latestWkid":4326,"wkid":4326},"paths":[[[-77.244918653,38.92906864],[-77.2443203,38.9292242], ... |
Note:
In all the function examples, the actual result of the functions excludes the header. Run the ADDHEADERS function to request the headers.
Function example 2
=ARCGIS.FINDROUTE(-77.24488778, 38.92894278, -80.78300851, 35.1001845, 4326, "Driving Distance", "miles", TRUE)
Result
Description | Distance traveled (miles) | Time traveled (minutes) | Route shape |
---|---|---|---|
Total Distance/Time Traveled | 382.7767 | 408.1581 | |
Start at Location 1 | 0 | 0 | {"spatialReference":{"wkid":4326},"paths":[[[-77.24491862136172,38.92906862347136],[-77.24491862136172,38.92906862347136]]]} |
Go East | 0.03108 | 0.300162 | {"spatialReference":{"wkid":4326},"paths":[[[-77.24491862136172,38.92906862347136],[-77.24432026092327,38.92922419358836],[-77.2431644738714,38.92953551367221],[-77.24291516601454,38.92957141170151],[-77.24263625487299,38.92926048728733]]]} |
Turn left, then turn right on Leesburg Pike | 0.422316089 | 1.215881348 | {"spatialReference":{"wkid":4326},"paths":[[[-77.24263627476374,38.929260492606396],[-77.24232884852634,38.92937681604757], ... |
Finish at Location 2, on the left | 0 | 0 | {"spatialReference":{"wkid":4326},"paths":[[[-80.78293244317324,35.10024066538108],[-80.78293244317324,35.10024066538108]]]} |
FINDROUTEBYADDRESS
This is a Routing function. Find the total travel distance (the default measurement unit is miles) and time (in minutes) between two addresses using the best route found.
Syntax
ARCGIS.FINDROUTEBYADDRESS(from_address, to_address, [travel_mode], [unit], [include_directions])
Parameters
Parameter | Description | Parameter required |
---|---|---|
from_address | The address of the starting location of the route. | Yes |
to_address | The address of the destination location of the route. | Yes |
travel_mode | The travel mode for the route. Instead of a ring buffer area, compute an area using a travel mode, for example, walking, driving, or trucking. For example, use Driving Distance or Walking Time. Run the LISTTRAVELMODES function to request a list of supported travel modes. If left blank, the default travel mode specified in your ArcGIS organization settings is used. See Configure travel modes to learn more about travel modes and parameters. | No |
unit | The measurement unit for the travel distance. This can be miles, kilometers, meters, feet, yards, or nautical miles. The default is miles. | No |
include_directions | If set to TRUE, the routing directions to the destination and the time and distance traveled at each step will be included in the results. The default is FALSE. | No |
Function example 1
=ARCGIS.FINDROUTEBYADDRESS("380 New York Street Redlands CA", "Downtown Riverside")
Result
Distance traveled (miles) | Time traveled (minutes) | Route shape |
---|---|---|
14.32662 | 20.73435 | {"spatialReference":{"latestWkid":4326,"wkid":4326},"paths":[[[-117.194789317,34.057516991],[-117.1940741,34.057515], ... |
Function example 2
=ARCGIS.FINDROUTEBYADDRESS("380 New York Street Redlands CA", "Downtown Riverside", "Driving Distance", "kilometers", TRUE)
Result
Description | Distance traveled (kilometers) | Time traveled (minutes) | Route shape |
---|---|---|---|
Total Distance/Time Traveled | 22.9899 | 22.40177 | |
Start at Location 1 | 0 | 0 | {"spatialReference":{"wkid":4326},"paths":[[[-117.19478927505831,34.057516969635486],[-117.19478927505831,34.057516969635486]]]} |
Go East | 0.0660270315239986 | 0.396162189143992 | {"spatialReference":{"wkid":4326},"paths":[[[-117.19478927505831,34.057516969635486],[-117.19407404027919,34.057515005004625]]]} |
Turn left | 0.0665435791015625 | 0.3992919921875 | {"spatialReference":{"wkid":4326},"paths":[[[-117.19407405467044,34.057514980900905],[-117.19406685106797,34.05811489909636]]]} |
Finish at Location 2, on the left | 0 | 0 | {"spatialReference":{"wkid":4326},"paths":[[[-117.37829693421398,33.97863201286705],[-117.37829693421398,33.97863201286705]]]} |
VERTICESTOESRIJSON
This is a Geometry function. Create an EsriJSON polyline, or polygon geometry from a list of its vertices' coordinates.
Syntax
ARCGIS.VERTICESTOESRIJSON (xy_vertices, spatial_reference, geometry_type, y_then_x)
Parameters
Parameter | Description | Parameter required |
---|---|---|
xy_vertices | A cell range containing the vertices' coordinates. The vertices can be specified in either row or column format. | Yes |
spatial_reference | The spatial reference for the point location. This can be either a WKID (number) or WKT (text). See the list of valid WKID values. The default is 4326 (WGS84). | No |
geometry_type | The geometry type. The parameters value can be: Polyline or Polygon. Default is Polygon. | No |
y_then_x | If set to TRUE, it indicates that the coordinates specify the latitude (Y) first, then the longitude (X). Default is FALSE, and by default, the order of the coordinates is assumed to be X followed by Y. | No |
CONVERTTOESRIJSON
This is a Geometry function. Convert a geometry from a non-Esri format to an EsriJSON geometry.
Syntax
ARCGIS.CONVERTTOESRIJSON (geometry_to_convert)
Parameters
Parameter | Description | Parameter required |
---|---|---|
geometry_to_convert | The geometry to be converted into EsriJSON. This can be a WKT or GeoJSON geometry. | Yes |
GETPOLYGONAREA
This is a Geometry function. Calculates the area of a polygon.
Syntax
ARCGIS.GETPOLYGONAREA(esrijson_polygon, unit, measurement_type)
Parameters
Parameter | Description | Parameter required |
---|---|---|
Esrijson_geometry | An Esri JSON geometry. | Yes |
Unit | The unit of measurement for the distance parameter. This can be square milimeters, square decimeters, square meters, square kilometers, square inches, square feet, square yards, square miles, US square feet, acres, or hectares. If the travel_mode parameter is set, this can also be seconds, minutes, or hours. The default is miles. | Yes |
Measurement_type | The measurement type used to determine the distance. The parameter values can be:
Default is Geodesic. | Yes |
GETGEOMETRYLENGTH
This is a Geometry function. Calculates the length of the geometry.
Syntax
=ARCGIS.GETGEOMETRYLENGTH(esrijson_geometry, unit, measurement_type)
Parameters
Parameter | Description | Parameter required |
---|---|---|
Esrijson_geometry | An Esri JSON geometry. | Yes |
Unit | The unit of measurement for the distance parameter. This can be centimeters, decimeters, meters, kilometers, inches, feet, yards, miles, US feet, or nautical miles. The default is miles. | Yes |
Measurement_type | The measurement type used to determine the distance. The parameter values can be:
Default is Geodesic. | Yes |
GETGEOMETRYDISTANCE
This is a Geometry function. Calculates the distance between two geometries.
Syntax
=ARCGIS.GETGEOMETRYDISTANCE(esrijson_geometry1, esrijson_geometry2, unit, measurement_type)
Parameters
Parameter | Description | Parameter required |
---|---|---|
Esrijson_geometry1 | The first Esri JSON geometry. | Yes |
Esrijson_geometry2 | The second Esri JSON geometry. | Yes |
Unit | The unit of measurement for the distance parameter. This can be centimeters, decimeters, meters, kilometers, feet, yards, miles, US feet, or nautical miles. The default is miles. | Yes |
Measurement_type | The measurement type used to determine the distance. The parameter values can be:
Default is Geodesic. | Yes |
GETCENTROID
This is a Geometry function. Calculates the centroid for a geometry.
Syntax
=ARCGIS.GETCENTROID(esrijson_geometry)
Parameters
Parameter | Description | Parameter required |
---|---|---|
Esrijson_geometry | This is an Esri JSON geomtery. | Yes |
RELATEGEOMETRIES
This is a Geometry function. Compares a geometry to another geometry by performing a spatial relationship operation.
Syntax
=ARCGIS.RELATEGEOMETRIES(esrijson_geometry_or_XY1, topologocal_operation, esrijson_geometry_or_XY2)
Parameters
Name | Description | Parameter required |
---|---|---|
esrijson_geometry_or_XY1 | The Esri JSON geometry or coordinates of a point to compare. | Yes |
topologocal_operation | The topological spatial relationship operator to be used to compare the geometries. This parameter's value can be:
| Yes |
esrijson_geometry_or_XY2 | The Esri JSON geometry or coordinates of a point to be compared with. | Yes |
GENERALIZEGEOMETRY
This is a Geometry function. Generalizes a geometry using the Douglas-Peuker algorithm.
Syntax
=ArcGIS.GENERALIZEGEOMETRY(esrijson_geometry, max_deviation, max_deviation_unit, remove_degenerate_parts)
Parameters
Parameter | Description | Parameter required |
---|---|---|
Esrijson_geometry | An Esri JSON geometry. | Yes |
Max_deviation | The maximum allowed deviation from the generalized geometry, to the original geometry. If maxDeviation is equal to or less than zero, the operator returns the input geometry. | Yes |
Max_deviation_unit | The length unit of the maximum allowed deviation. An errors occurs if this value is set for Geographic Coordinate Systems. When the value is not set, the spatial reference unit of geometry is used. | Yes |
Remove_degenerate_parts | When set to TRUE, any parts of the generalized geometry with lower dimensionality than the input (such as a polygon generalized to a line) are removed from the output. Default value is FALSE. | Yes |
SUGGESTADDRESS
This is a Helper function. Suggest addresses based on a text input. This can be used as an interactive autocomplete address search.
Syntax
ARCGIS.SUGGESTADDRESS(search_term, [max_suggestions], [country], [geocode_service])
Parameters
Parameter | Description | Parameter required |
---|---|---|
search_term | The input text provided by a user, which is used to generate a list of possible matches. | Yes |
max_suggestions | The maximum number of suggestions to be returned. If this number is greater than 15, the suggestions will be made by the geocoding operation, which may consume additional credits and may lead to unexpected results. The default is 5. Maximum is 50. | No |
country | Limits the results to a particular country. For example, use United Kingdom. Run ARCGIS.LISTGEOGRAPHIES to request a list of countries. | No |
geocode_service | The maximum number of suggestions to be returned. If this number is greater than 15, the suggestions will be made by the geocoding operation, which may consume more credits and may lead to different results. Default is 5. Maximum is 50. | No |
Function example
=ARCGIS.SUGGESTADDRESS("Orange county",10, "USA")
Result
Orange County, CA, USA |
Orange County, FL, USA |
Orange County, IN, USA |
Orange County, NC, USA |
Orange County, NY, USA |
Orange County, TX, USA |
Orange County, VA, USA |
Orange County, VT, USA |
Orange County Great Park, Irvine, CA, USA |
Orange County Airport, 500 Dunn Rd, Montgomery, NY, 12549, USA |
GETGEOMETRY
This is a Helper function. Get a specific geography including its area ID and its geometry.
Syntax
ARCGIS.GETGEOMETRY (country, geography_level_id, search_term, exact_match, show_all_matches)
Parameters
Parameter | Description | Parameter required |
---|---|---|
country | The source country of the data collections. For example, use United States. Run the LISTGEOGRAPHIES function to request a list of countries. | Yes |
geography_level_id | The ID of the geography level to which the area ID belongs. Run the LISTGEOGRAPHIES function to request a list of area IDs for a particular geography level. For example, "US.States" is the states in the United States. | Yes |
search_term | The name of the geography, used as a search term. For example, use California, San Diego, CA. | Yes |
exact_match | Only return results matching your exact search term. | No |
show_all_matches | Return all results of the search term, if there are matches with multiple geographies. | No |
LISTGEOGRAPHIES
This is a Helper function. Depending on the input parameters, a list of countries, a list of geography levels (for example, districts or provinces) of a country, or a list of the area IDs for a geography level is returned. The maximum list item limit is 5,000.
Syntax
ARCGIS.LISTGEOGRAPHIES([country], [geography_level_id], [search_term], [exact_match])
Parameters
Parameter | Description | Parameter required |
---|---|---|
country | The source country of the data collections. For example, use United Kingdom. If a value is specified, all geography levels for that country are listed. If left blank, all countries and territories are listed. | No |
geography_level_id | The ID of a geography level. For example, use US.States for the states in the United States. If a value is specified, all areas of a geography level, such as California, and their FIPS IDs are listed. If left blank, the results are dependent on the country parameter. | No |
search_term | A search term that narrows the results by matching the search term with the geography names. | No |
exact_match | Only return results matching your exact search term. | No |
Function example
=ARCGIS.LISTGEOGRAPHIES()
Result
Country | Country code |
---|---|
Albania | AL |
Algeria | DZ |
Andorra | AD |
Angola | AO |
Anguilla | AI |
=ARCGIS.LISTGEOGRAPHIES("USA")
Result
ID | Name |
---|---|
US.WholeUSA | Entire Country |
US.States | States |
US.DMA | DMAs |
US.CD | Congressional Districts |
US.CBSA | CBSAs |
US.Counties | Counties |
US.CSD | County Subdivisions |
US.ZIP5 | Zip Codes |
US.Places | Cities and Towns (Places) |
US.Tracts | Census Tracts |
US.BlockGroups | Block Groups |
=ARCGIS.LISTGEOGRAPHIES("Spain","ES.Municipalities")
Result
ID | Name | Major geography subdivision |
---|---|---|
01001 | Alegría-Dulantzi | País Vasco |
01002 | Amurrio | País Vasco |
01003 | Aramaio | País Vasco |
01004 | Artziniega | País Vasco |
01006 | Armiñón | País Vasco |
=ARCGIS.LISTGEOGRAPHIES("Spain","ES.Municipalities","Barcelona")
ID | Name | Major geography subdivision |
---|---|---|
08019 | Barcelona | Cataluña |
LISTDATACOLLECTIONS
This is a Helper function. List the data collections available for a country.
Syntax
ARCGIS.LISTDATACOLLECTIONS(country, [data_collection_id], [search_term])
Parameters
Parameter | Description | Parameter required |
---|---|---|
country | The source country of the data collections. For example, use United Kingdom. Run the LISTGEOGRAPHIES function to request a list of countries. | Yes |
data_collection_id | The ID of a data collection. For example, use KeyFacts. If a value is specified, all variables of the data collection are listed. If left blank, all data collections for the specified country are listed. | No |
search_term | A search term that narrows the results by matching the search term with the data collection names and descriptions or variable names and descriptions. For example, use fast food. | No |
Function example
=ARCGIS.LISTDATACOLLECTIONS("USA")
Result
ID | Description | Example data |
---|---|---|
1yearincrements | The 1 Year Increments data collection includes population counts by 1-year age breaks up to age 84 for 2023 and 2028. The collection also includes 2010 population by single year increments up to age 21. The data tables include age for total population and age by gender. This collection contains data from the Esri 2023/2028 Updated Demographic database and Esri Census 2010 database. |
|
5yearincrements | The 5 Year Increments data collection includes population counts by 5-year age breaks for 2023, 2028, and 2010. The data tables include age for the total population and age by gender. This collection contains data from the Esri 2023/2028 Updated Demographic database, American Community Survey (ACS) database, and Census 2010 database. |
|
Age | This data collection includes age range data for males and females used for data pyramid infographics. This collection contains Esri 2023 Updated Demographic data. |
|
=ARCGIS.LISTDATACOLLECTIONS("USA","health")
Result
ID | Name | Description |
---|---|---|
REL65HI2OC | 2023 Pop 65+: Other Health Ins Combos REL (ACS 5-Yr) | 2023 Population 65+: Other Health Ins Combinations REL (ACS 5-Yr) |
ACSCIVNINS | 2023 Civ Noninstitutionalized Population (ACS 5-Yr) | 2023 Civilian Noninstitutionalized Population (ACS 5-Yr) |
MOECIVNINS | 2023 Civ Noninstitutionalized Population MOE (ACS 5-Yr) | 2023 Civilian Noninstitutionalized Population MOE (ACS 5-Yr) |
=ARCGIS.LISTDATACOLLECTIONS("USA", "health", "<19")
Result
ID | Name | Description |
---|---|---|
ACSCIVNI0 | 2023 Civ Noninstitutionalized Pop <19 (ACS 5-Yr) | 2023 Civilian Noninstitutionalized Pop <19 (ACS 5-Yr) |
ACS0ONEHI | 2023 Pop <19: 1 Type of Health Ins (ACS 5-Yr) | 2023 Population <19: One Type of Health Insurance (ACS 5-Yr) |
MOE0ONEHI | 2023 Pop <19: 1 Type of Health Ins MOE (ACS 5-Yr) | 2023 Population <19: One Type of Health Insurance MOE (ACS 5-Yr) |
Note:
In all the function examples, the actual result of the functions excludes the header. Run the ADDHEADERS function to request the headers.
LISTTRAVELMODES
This is a Helper function. List all travel modes of the route service supported by ArcGIS Online or configured for your organization.
Note:
This function requires no parameters. For a similar example, see the Microsoft RAND function article.
Syntax
ARCGIS.LISTTRAVELMODES()
Function example
=ARCGIS.LISTTRAVELMODES()
Name | Description | Details |
---|---|---|
Driving Time | Models the movement of cars and other similar small automobiles, such as pickup trucks, and finds solutions that optimize travel time. Travel obeys one-way roads, avoids illegal turns, and follows other rules that are specific to cars. When you specify a start time, dynamic travel speeds based on traffic are used where it is available. | Avoid Carpool Roads; Avoid Express Lanes; Avoid Gates; Avoid Private Roads; Avoid Unpaved Roads; Driving an Automobile; Roads Under Construction Prohibited; Through Traffic Prohibited; Vehicle Maximum Speed (km/h): TravelTime 0 |
Walking Time | Follows paths and roads that allow pedestrian traffic and finds solutions that optimize travel time. The walking speed is set to 5 kilometers per hour. | Avoid Private Roads; Avoid Roads Unsuitable for Pedestrians; Preferred for Pedestrians; Walking Speed (km/h): WalkTime 5; Walking |
Walking Distance | Follows paths and roads that allow pedestrian traffic and finds solutions that optimize travel distance. | Avoid Private Roads; Avoid Roads Unsuitable for Pedestrians; Preferred for Pedestrians; Walking Speed (km/h): WalkTime 5; Walking |
Note:
In all the function examples, the actual result of the functions excludes the header. Run the ADDHEADERS function to request the headers.
ADDHEADERS
Create a header row for the output for ArcGIS for Excel functions. This function is not applicable for some functions, such as some under the Helper functions category.
Note:
This function requires only one parameter.
Note:
In all the function examples, the actual result of the functions excludes the header. Run the ADDHEADERS function to request the headers.
Syntax
ARCGIS.ADDHEADERS(ARCGIS_function)
Parameters
Parameter | Description | Parameter required |
---|---|---|
ARCGIS_function | Input a cell reference to another ArcGIS for Excel function where you want to add headers. | Yes |
Function example
=ARCGIS.ADDHEADERS(A2)
This example will work if you have another function in cell A2. For example, if cell A2 contains the =ARCGIS.GETCOORDINATES("380 New York Street, Redlands, CA, USA") function.
Result
Longitude (X) | Latitude (Y) |
Comma, decimal point, and semicolon separators
If you are using a German, Dutch, Spanish, French, or Italian-language operating system, numbers written in decimal form may be formatted differently than for those using English-language operating systems. For some languages, numbers containing decimals are written or separated using a comma (,) instead of a decimal point (.).
Depending on the decimal separator, functions in Excel should be formatted differently.
- When a decimal point (.) separator is used, you must separate function parameter inputs using a comma (,), for example, =ARCGIS.GETADDRESS(-70.251, 43.65808).
- When a comma (,) decimal separator is used, you must separate function parameter inputs using a semicolon (;), for example, =ARCGIS.GETADDRESS(-70,251; 43,65808).
In Excel, the default decimal separator is the same as that of your operating system. When using Excel, you can also customize this setting.