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.
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.
- Open the Ferrett Tabulation File named edw_trans_time_sf3_cell_calculation.ftf. Hit the "OK" button on the Ferrett Chosen Instance Warning dialog.
- "Go Get Data" to see the tabulation.
- 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:
- We can create a new column and replicate this formula.
- Highlight C6 by clicking in the gray column header.
- In the formula bar, enter the formula as- =COMP(C3/$C3$R2*100) -then hit the "Enter" key.
- Highlight the column again, then select the "Decimal Format"
button or the "Format" menu, then "Decimal" to bring up the decimal
- Select the "One decimal place" option and hit the "Ok" button.
- The percents in our new column (C6) should match the percents in column 2.