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.