Computer Science: Spreadsheet Formulas to Model Real Problems
Using cell references, functions, and formulas to solve realistic problems
Computer Science: Spreadsheet Formulas to Model Real Problems
Using cell references, functions, and formulas to solve realistic problems
Computer Science - Grade 9-12
- 1
A student is tracking weekly study time. Cells B2 through B8 contain the number of hours studied each day from Monday through Sunday. Write a formula for cell B9 that calculates the total hours studied for the week.
Use a function that adds a range of cells.
The formula is =SUM(B2:B8). This adds all the daily study hours from Monday through Sunday. - 2
A class gradebook has homework scores in cells C2:C11. Write a formula that calculates the average homework score.
The formula is =AVERAGE(C2:C11). This finds the mean of all homework scores in the range. - 3
A store sells T-shirts for $18 each. Cell B2 contains the number of shirts sold. Write a formula for cell C2 that calculates total revenue from T-shirt sales.
Revenue equals quantity sold times price per item.
The formula is =B2*18. This multiplies the number of shirts sold by the price of each shirt. - 4
A spreadsheet tracks monthly electricity use. Cell B2 contains kilowatt-hours used, and cell C2 contains the cost per kilowatt-hour. Write a formula for D2 that calculates the total electricity cost.
The formula is =B2*C2. This multiplies the energy used by the price for each kilowatt-hour. - 5
A school fundraiser keeps item prices in column B and quantities sold in column C. Row 2 is for notebooks. Write a formula for D2 that calculates the money earned from notebooks.
Use the price cell and the quantity cell from the same row.
The formula is =B2*C2. This multiplies the notebook price by the number of notebooks sold. - 6
A budget spreadsheet lists income in B2 and expenses in B3. Write a formula for B4 that calculates the remaining balance.
The formula is =B2-B3. This subtracts total expenses from income to find the remaining balance. - 7
A cell phone plan costs $35 per month plus $0.10 per text message. Cell B2 contains the number of text messages used. Write a formula that calculates the total monthly cost.
The fixed cost is added once, and the message cost depends on the number of messages.
The formula is =35+B2*0.10. This adds the base monthly cost to the charge for all text messages. - 8
A temperature spreadsheet has Celsius values in column A. Write a formula for B2 that converts the Celsius value in A2 to Fahrenheit using the formula F = C*9/5 + 32.
The formula is =A2*9/5+32. This converts the Celsius temperature in A2 to Fahrenheit. - 9
A teacher wants column D to show Pass if a student's score in C2 is at least 70 and Retake if it is below 70. Write the formula for D2.
Use an IF function with a comparison.
The formula is =IF(C2>=70,"Pass","Retake"). This checks whether the score is at least 70 and returns the correct label. - 10
A sports tracker records a runner's distance in miles in B2 and time in hours in C2. Write a formula for D2 that calculates average speed in miles per hour.
The formula is =B2/C2. This divides distance by time to calculate average speed. - 11
A spreadsheet compares prices at two stores. Store A price is in B2 and Store B price is in C2. Write a formula that displays the lower price.
Use the function that finds the smallest value.
The formula is =MIN(B2,C2). This returns the smaller of the two prices. - 12
A student loan calculator has the loan amount in B2 and the annual interest rate as a decimal in B3. Write a formula that estimates one year of simple interest.
The formula is =B2*B3. This multiplies the loan amount by the annual interest rate to estimate one year of simple interest. - 13
A spreadsheet models the cost of ordering custom stickers. There is a $12 setup fee and each sticker costs $0.45. Cell B2 contains the number of stickers ordered. Write a formula for C2 that calculates the total cost.
This model has a fixed cost and a variable cost.
The formula is =12+B2*0.45. This adds the setup fee to the cost of all stickers ordered. - 14
A science club tracks plant growth. Cell B2 contains the plant height at the start of the week, and C2 contains the plant height at the end of the week. Write a formula for D2 that calculates the growth during the week.
The formula is =C2-B2. This subtracts the starting height from the ending height to find the amount of growth. - 15
A cafeteria inventory sheet has the number of sandwiches sold each day in cells B2:F2. Write a formula that calculates the greatest number of sandwiches sold on any one day.
Use the function that finds the highest value in a range.
The formula is =MAX(B2:F2). This returns the largest daily sandwich sales value in the range.