Use Single Cell in Formula

Example 6 - Using a single cell as part of a formula instead of the entire row or column

Other features illustrated: Column number formatting (decimals shown)

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.

Goal: Create a table that calculates the percentage of drivers within commute time ranges for a set of counties, while still showing the numbers of drivers within each commute time.

Steps:

  1. Open the Ferrett Tabulation File named edw_trans_time_sf3_cell_calculation.ftf. Press the "OK" button on the Ferrett Chosen Instance Warning dialog.
  2. "Go Get Data" to see the tabulation.
  3. Column 2 was created using a formula that calculates the percent of commuters by travel time ranges. The formula in this case does not use Row 2 as the denominator, but instead just uses cell C3, R2 designated as $C3$R2 - see the image for the entire formula. Screenshot of table with a formula using a cell
    Screenshot of table with a formula using a cell (Click figure to Enlarge)
  4. We can create a new column and replicate this formula.
  5. Highlight C6 by clicking in the gray column header.
  6. In the formula bar, enter the formula as- =COMP(C3/$C3$R2*100) -then press the "Enter" key.
  7. Highlight the column again, then select the "Decimal Format" button or the "Format" menu, then "Decimal" to bring up the decimal formatting dialog.
  8. Select the "One decimal place" option and press the "Ok" button.
  9. The percents in our new column (C6) should match the percents in column 2.

By creating a formula using a single cell as the denominator, we are able to create a column showing percents while also maintaining all the other data columns.