Retrieve the value of a statistical variable at a given place and time
The=DCGET
formula returns the measurements of a specified statistical variable at a given place and optional time based on a list of parent place DCIDs. A complete list of variables can be found in the Statistical Variable Explorer.
Formula
=DCGET(dcids, variable, [date])
Required arguments
dcids
: A single place node or range of cells represening place nodes, identified by their DCIDs.variable
: The statistical variable whose measurements you want to query.
Optional arguments
date
: The date or dates of interest. If this argument is not specified, the API returns the latest variable observation. You can specify this argument as a single value, row, or column. All dates must be in ISO 8601 format (such as 2017, “2017”, “2017-12”) or as a Google sheets date value.
Returns
The value of the variable at those places on the specified date or on the latest available date, if no date is specified.
Examples
This section contains examples of using the =DCGET
formula to returns the values of statistical variables such as Count_Person
and Median_Income_Person
.
Note: Be sure to follow the instructions for for enabling the Sheets add-on before trying these examples.
Example 1: Get the total population of Hawaii in 2017
To get the total population of Hawaii in 2017:
- Place your cursor in the desired cell.
- Enter the formula
=DCGET("geoId/15", "Count_Person", 2017)
. The value1425763
populates the cell.
Example 2: Get the population of five Hawaii counties in 2017
To get the population of the five counties in 2017:
- Place your cursor in the desired cell; in this case A2, and enter the DCID of Hawaii, namely
geoId/15
. - In cell B2, enter the formula
=DCPLACESIN(A2, "County")
. The DCIDs of the Hawaii counties populate column B. - (Optional) In cell C2, enter
=DCGETNAME(B2:B6)
to retrieve the names of the counties in column C. -
In cell D2, enter the formula
=DCGET(B2:B6, "Count_Person", 2017)
.The values populate column D.
Example 3: Get the median income of a single place in multiple years
This example shows how to get the median income in Hawaii for the years 2011 - 2013, with dates as columns:
- In a new sheet, in row 1, create cells with the headings shown in the image below.
- In cell A2, enter
Hawaii
, and in cell B2,geoId/15
. -
Select cells C2 to E2, and enter the formula
=DCGET(B2, "Median_Income_Person", C1:E1)
.The values populate C2, D2 and E2.
Example 4: Get the median age of multiple places in multiple years
The following examples demonstrate how to retrieve the median age of five counties in Hawaii for the years 2011 - 2015.
To get the results with the counties in rows and the dates in columns, do the following:
- In a new sheet, in row 1, create cells with the headings shown in the image below, with columns for each year 2011 to 2015.
- In cell A2, enter
Hawaii
, and in cell B2,geoId/15
. - In cell C2, enter the formula
=DCPLACESIN(, "County")
. The county DCIDs populate column C. - In cell D2, enter the formula
=DCGETNAME(C2:C6)
. The county names populate column D. - Place your cursor in cell E2 and enter the formula
=DCGET(C2:C6, "Median_Age_Person", E1:I1)
. The ages for each county and year appear in columns E to I.
To get the results with the counties in columns and the dates in rows, do the following:
- In a new sheet, in column A, create cells with the headings shown in the image below.
- In cell B1, enter
Hawaii
, and in cell B2,geoId/15
- Manually enter the DCIDs for each county, in cells B3 to F3, as shown in the image below.
- Place your cursor in cell B4 and enter the formula
=DCGETNAME(B3:F3)
. The county names populate column D. -
Place your cursor in cell B5 and enter the formula
=DCGET(B3:F3, "Median_Age_Person", A5:A9)
.The ages for each county and year appear in rows 5 to 9.
Error responses
The =DCGET
formula returns a blank value under the following circumstances:
- A DCID does not exist (e.g.
geoId/123123123
) - You provide a nonexistent statistical variable (e.g.
Count
) - You provide an incorrectly formatted date (e.g.
July 12, 2013
)
For example, because the geoId/123123123
DCID does not exist, no value is returned to cell B1 in the following sheet for the formula =DCGET(A1, "Count_Person")
:
If you fail to provide all required arguments, you will get a response of #ERROR!
: