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 usually calculations 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.

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. Hit 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:
   
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 hit 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 hit the "Ok" button.
9. The percents in our new column (C6) should match the percents in column 2.

-- BillHazard - 04 Oct 2007

• edw_trans_time_sf3_cell_calculation.ftf: Ferrett table definition showing a calculation using a single cell in the formula