7: More Formulas

In this section, you will

• • use built-in formula functions (e.g., AutoSum)

  • use the Subtotal function

  • select correct ranges of data

  • print formulas

  • use absolute, relative, and mixed references

  • sort data

  • filter data

To successfully complete this section, read the information in the links for key topics listed above (linked) and watch the “Google Sheets- Sort and Filters” video in EdPuzzle.

Instructions /9

Complete the following steps in order:

Step 1: Open More Formulas. Save your copy as More Formulas - Firstname Lastname in your INF1060 SS1 folder.

/1 Step 2: In cell F4 of the More Formulas A worksheet, calculate Carina’s total income for the four months. Use the Sum function; then use the fill handle to calculate the total income of the other three people (notice, the cell references change when you do this to reflect that the formula is the same, but the references adjust for the new column or row - you do NOT need to type in a new formula each time!). Format the cells for currency if needed.

/1 Step 3: In cell B9 calculate the total income of the four people in January. Use the Subtotal function (use the code for addition: 9 or 109) in your formula, and be sure to include ONLY the required cells in your formula. Then calculate the total income for the other three months. Format the cells for currency if needed.

/3 Step 4: In cell G2 insert 30% as the income tax rate. Then in column G calculate the income tax amount for each person using an absolute cell reference in your formula (this will be a multiplication formula!). This is very important because you want this calculation to be correct even if, for example, you later want to change the income tax rate AND you want the cell reference to the income tax to remain constant even when you drag the formula to other cells. You want it to autocorrect rather than having to go through and redo calculations. You should be able to type in the first formula, then use the fill handle to drag the formula down for the remaining people. 

If you want the fill handle to keep a cell stationary (eg. G2 to stay on the percentage), then use: $G$2 which will allow you to use the fill function without having to adjust each cell after.

/1 Step 5: Use the fill handle to insert a new column heading called “Income Tax” to match the rest of the table. Using the fill handle carries over formatting.

Step 6: Change the view on the More Formulas A worksheet to display the formulas. Select “View” and then “All Formulas” to reveal the formulas rather than the calculations. This is helpful at a glance to see if you have any incorrect formulas.

Step 7: Open the More Formulas B worksheet, and complete the following steps in this worksheet.

/1 Step 8: Sort by city from A to Z.

• • Select the whole table

  • Select in the right corner of the title, the three horizontal lines. You should be able to sort from A --> Z

/1 Step 9: Filter out all Calgary locations.

• Select the Data menu, then Filter, then Create New Filter View. This will create filter dropdowns on all active columns

• Using the filter menu on the City column, filter out Calgary