Practice Time!
BEFORE YOU START!
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.
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.
Your Road Map
1. Create a new Google Sheet file.
2. You can do this by visiting sheets.google.com/create or clicking on new and then Google Sheet in your Google Drive.
BONUS!
Google Docs Quick Create is a Google Chrome Extension that allows you to create new Google Docs, Sheets, Slides, Forms, and Drawings from your browser tool bar.
SBAC SCORES
Collecting Data
- Download the SBAC Scores Excel file.
- Import the SBAC Scores Excel file into the new spreadsheet you created.
- Select insert new sheet(s) as your import location.
Organizing Data
- Add 1 column to the right of Column A.
- Use Text to Columns to remove CA- from the student id. *HINT* Think of what you can use as your separator value.
*HINT* Make sure you copy and paste the data to have this icon show up.
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
- Create a column chart of your student SBAC scores.
TEST SCORES
Collecting Data
- Click here to access the test scores Google Sheet.
- Use CopyTo to copy the data to your Google Sheet file.
Organizing Data
- Use Sort Range to sort your students first by period and then by last name.
- 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!
- 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
- Protect your Test Scores sheet.
- Share your completed Google Sheet with mrbradfordonline@gmail.com.
