Before starting the challenges I would suggest opening up the pages below in a new tab. To open a new tab on a Windows & Linux press Ctrl + t. If you are a Mac user press ⌘ + t.

Collecting Data Organizing Data Sharing Data

Each challenge is laid out in three parts. The parts of the challenge match each of the pages you opened. On these pages you will find written instructions and short gifs on how to complete each task. If you scroll to the bottom of this page I have also embedded an example of a completed sheets file. *HINT* If you are struggling with the writing formulas check out my completed sheet and compare and contrast the formulas.

Collecting Data

1. Download the SBAC Scores Excel file.
2. Import the SBAC Scores Excel file into the new spreadsheet you created.
3. Select insert new sheet(s) as your import location.

Organizing Data

1. Add 1 column to the right of Column A.
2. Use Text to Columns to remove CA- from the student id. *HINT* Think of what you can use as your separator value.

3. Conditional format Column F so Level 4 scores turn blue, Level 3 scores turn green, Level 2 scores turn yellow, and Level 1 scores turn red. *HINT* Check out the image below for help on choosing your format cells if value.

4. Use the countif formula to find the total number of students who scored Level 2, Level 3, and Level 4. *HINT* Think about the column where your data is. Is it easier to write F1:F14 or F:F?

5. Use the sum formula to find the total number of students who took the test.

Sharing Data

1. Create a column chart of your student SBAC scores.

Collecting Data

1. Click here to access the test scores Google Sheet.
2. Use CopyTo to copy the data to your Google Sheet file.

Organizing Data

1. Use Sort Range to sort your students first by period and then by last name.
2. Use the Concatenate formula to fill in Column D with student names. *HINT* Remember that " " in between cell names will add a space between values. Also there is a super cheat that you can use to quickly auto populate the formula! See if you can figure it out!
3. Conditional Format Columns F-H to match the criteria below.

4. Use text wrap to wrap the text in Column J.

5. Find the average for Test #2 and Test #3.

Sharing Data

1. Protect your Test Scores sheet.
2. Share your completed Google Sheet with mrbradfordonline@gmail.com.

Ready for the ultimate test? On your SBAC Scores sheet use VLookUp to populate Column D for Period.