We want to answer these question: What is the average BT salary? What is the average non-BT salary?

Download the spreadsheet below.


STEPS:
Make data pretty and clean.
  • Just above the first row double click on a line separating the columns to make the headings readable.
  • Make the headings in Row 1 bold.
  • Center the data in columns F-I and M-N.
  • Freeze the first row of data so if can be seen when you scroll down.
  • Format columns L, M & O to currency.
  • OOPS! It looks like there was a type-o. Mr. Johnson's salary is incorrect.
    • To find the correct Mr. Johnson sort by last name then filter by subject.
    • Change his current salary from $33.00 to $133.00.

Now that our data is clean and pretty we can find the answer to our question.
  • Enter the formula to calculate the change in salary by the increase in column M.
  • Enter a formula to add the increase to the current salary.
  • Drag the formula cells down to apply it the formulas all rows.
  • Sort by BT column
  • In a column to the right of the data enter: "Average BT Pay"
    • In the cell to the right of "Average BT Pay" enter a formula to calculate the average Total Pay and include all the BTs.
  • n a column to the right of the data enter: "Average non-BT Pay"
    • In a column to the right enter a formula to calculate the average Total Pay and include all the non-BTs.

Print the answer to your question.
  • Select the 4 cells that contain the answer to your question.
  • Click on the "File" tab in the top left of the screen.
  • Select "Print"
  • Under "Settings" click on the first drop down menu and choose "Print Selection"
  • Make sure that the print preview is correct.
  • Click "print" (Optional)