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.
- Open the Ferrett Tabulation File named
Press 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 (Click figure to Enlarge)
- 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 press the "Enter" key.
- Highlight the column again, then select the "Decimal Format" button or the "Format" menu, then "Decimal" to bring up the decimal formatting dialog.
- Select the "One decimal place" option and press the "Ok" button.
- 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.