Click to Print this page

Tips and Tricks

1. Co-tabulation, Recode Aggregate Variables

Did You Know?

  • You can join different datasets using a common variable.
  • You can recode two or more aggregate variables into one.
  • You can use formulas that combine variables from different datasets.

Background: Some datasets may have variables whose value sets are defined alike (comparable). Examples include demographic variables (e.g. gender, race, marital status) and geographic variables (e.g. state code, county code). Co-tabulation allows you to tabulate information from the different datasets side by side using the comparable variable as a hinge. See the following example for instructions on how to use these features. View example

 

2. Spreadsheet Tricks, Averages Over Time

Did You Know?

  • You can combine two or more variables into a single recode variable.
  • You can calculate percents with the click of a button.
  • You can sort data on a column with the click of a button.
  • You can create averages over time and make a time series graph with the click of a button.
  • You can nest variables in your table in different ways with or without totals.

Background: In microdata datasets, you can create new variables with values based on the combinations of multiple variables' values. The following example demonstrates how to do this and then several tricks you can use to examine your data in meaningful ways. View example

 

3. Map Address Points, View Underlying Data

Did You Know?

  • You can create create maps that combine address points and thematic data from different datasets.
  • You can view the individual underlying data records for your table.

Background: If a dataset has correctly-defined address point information, points can be mapped on top of a thematic map from a separate dataset. You can also get more detail from the data by exploring the underlying records. Example 3 walks you through how to do this step-by-step.
View example

 

4. Complex Functions

Did You Know?

  • The DataFerrett spreadsheet does complex functions like arithmetic calculations, weighted ranking and if conditions.
  • You can hide columns and rows in the spreadsheet.

Background: The following example shows how you can rank several variables and apply weights to those ranks in order to answer questions like "What is the best county for ...?" Other spreadsheet functionalities are demonstrated as well. View example

 

5. Only Records Meeting Certain Criteria

Did You Know?

  • You can show a list of only records meeting a set criterion.

Background: You can have your table only show results for the records that meet your criteria. The following example demonstrates what to do if you want just the counties within a select number of states that have a population within a certain size. You can list them in a table and include additional information from the dataset. View example

 

6. Use Single Cell in Formula

Did You Know?

  • You can use a single cell as part of a formula instead of the entire row or column

Background: The DataFerrett spreadsheet typically does row and column calculations since calculations normally apply to all cells in the row or column equally. However, sometimes you want to create a calculation that uses one specific cell as part of the formula and not the entire column or row. View example

 

7. Calculate Medians, IF Conditions

Did You Know?

  • You can calculate medians (or any percentile) on-the-fly
  • You can use IF conditions in your formulas

Background: The DataFerrett spreadsheet allows you to calculate medians (or any percentile) on-the-fly by creating your own bins (microdata) or utilizing income distribution counts (aggregate data), and using standard formulas to calculate the linear percentile you have defined within a given distribution. This example demonstrates how this can be done. View example