CS: Spreadsheets: Organizing and Analyzing Data
Using rows, columns, formulas, sorting, filtering, and charts
CS: Spreadsheets: Organizing and Analyzing Data
Using rows, columns, formulas, sorting, filtering, and charts
CS - Grade 6-8
- 1
A spreadsheet has snack sale quantities in cells B2 through B6: 12, 9, 15, 6, and 18. Write a formula that finds the total number of snacks sold.
Use a function that adds a range of cells.
The formula is =SUM(B2:B6). It adds all the values from cell B2 through cell B6 to find the total number of snacks sold. - 2
In a spreadsheet, column A lists student names and column B lists their quiz scores. Scores are in cells B2 through B11. Write a formula to find the average quiz score.
The formula is =AVERAGE(B2:B11). It calculates the mean of all quiz scores in cells B2 through B11. - 3
Explain the difference between a row, a column, and a cell in a spreadsheet.
Think about how a cell address uses both a letter and a number.
A row goes horizontally across the spreadsheet and is usually labeled with a number. A column goes vertically and is usually labeled with a letter. A cell is one box where a row and a column meet, such as B4. - 4
A class has a spreadsheet of library books with columns for Title, Author, Genre, and Year Published. What column would you sort by to put the books from oldest to newest?
You would sort by the Year Published column in ascending order. Ascending order places the smallest year first, which lists the oldest books before newer books. - 5
A spreadsheet lists school clubs with columns for Club Name, Meeting Day, and Room. You only want to see clubs that meet on Wednesday. What spreadsheet tool should you use, and what setting should you choose?
This tool hides rows that do not match your chosen condition.
You should use a filter on the Meeting Day column. The filter should be set to show only rows where Meeting Day is Wednesday. - 6
The numbers 4, 8, 10, 10, 13, and 15 are in cells C2 through C7. Write a formula to find the largest number in the list.
The formula is =MAX(C2:C7). It returns 15 because 15 is the largest value in the range. - 7
A spreadsheet stores birth dates in the format month/day/year. Why is it better to store them as date values instead of plain text?
Think about what happens if the computer needs to compare two dates.
It is better to store birth dates as date values because the spreadsheet can sort them correctly, calculate ages, and use date functions. Plain text may look like a date but cannot always be analyzed correctly. - 8
A table shows favorite after-school activities: Sports 18, Art 9, Music 12, Gaming 15, Reading 6. What type of chart would clearly compare the number of students in each activity?
Use a chart that makes category sizes easy to compare.
A bar chart would clearly compare the number of students in each activity. Each bar can show one activity and its student count. - 9
Cell B2 contains a price, and cell C2 contains a quantity. Write a formula for cell D2 that calculates the total cost for that row.
The formula is =B2*C2. It multiplies the price in B2 by the quantity in C2 to calculate the total cost for the row. - 10
A spreadsheet formula in E2 is =B2*C2. If you copy it down to E3, what will the formula usually change to, and why?
Relative references move with the formula.
The formula will usually change to =B3*C3. This happens because spreadsheet formulas use relative cell references by default, so the row numbers adjust when the formula is copied down. - 11
You want every row in a spreadsheet to use the tax rate stored in cell F1. What formula should you use in D2 if B2 is the price and C2 is the quantity?
Use dollar signs to lock the tax rate cell.
A good formula is =B2*C2*$F$1. The dollar signs make F1 an absolute reference so the tax rate stays fixed when the formula is copied to other rows. - 12
A class survey spreadsheet has two rows for the same student because the student submitted the survey twice. Why should you remove or handle the duplicate before analyzing the data?
You should remove or handle the duplicate because it can make the results inaccurate. Counting the same student's response twice could change totals, averages, and percentages. - 13
A spreadsheet has temperatures in column B. Write an IF formula for cell C2 that displays Hot if B2 is greater than 85 and Not hot otherwise.
An IF function has a test, a value if true, and a value if false.
The formula is =IF(B2>85,"Hot","Not hot"). It checks whether the temperature in B2 is greater than 85 and returns the correct label. - 14
A student collected step counts for five days: Monday 6000, Tuesday 7500, Wednesday 5000, Thursday 9000, Friday 8500. Which day had the highest step count, and how could a spreadsheet help you find it?
Thursday had the highest step count with 9000 steps. A spreadsheet could help by sorting the step counts from largest to smallest or by using the MAX function. - 15
A spreadsheet has 200 rows of data with column headings in row 1. Name one formatting or view feature that would make the spreadsheet easier to use, and explain why.
Think about what becomes hard to see when you scroll down a large spreadsheet.
Freezing the top row would make the spreadsheet easier to use because the column headings would stay visible while scrolling. This helps users remember what each column means.