20 Spreadsheets (3)
Resources |
Revision Questions |
Information Communication Technology ICT
Login to see all questions
Click on a question to view the answer
1.
A student is creating a spreadsheet to calculate the total cost of items. The price of each item is listed in cells D2 to D10, and the quantity of each item is listed in cells E2 to E10. The student wants to create a formula in cell F12 that calculates the total cost of all items. Write the formula using appropriate cell referencing (absolute or relative) to achieve this. Explain why you chose that referencing method.
The formula in cell F12 should be: =SUMPRODUCT(\$D\$2:\$D\$10 * \$E\$2:\$E\$10)
Explanation:
- SUMPRODUCT() is a function that multiplies corresponding components in the given arrays and returns the sum of those products.
- \$D\$2:\$D\$10 is an absolute reference to the range containing the prices of the items. The dollar signs ensure that this range remains fixed when the formula is copied.
- \$E\$2:\$E\$10 is an absolute reference to the range containing the quantities of the items. The dollar signs ensure that this range remains fixed when the formula is copied.
- The multiplication of the price and quantity for each item is performed, and then the SUMPRODUCT function calculates the sum of all these individual costs, giving the total cost.
- Using absolute references for the price and quantity ranges is crucial because the formula will likely be copied to other cells in the spreadsheet to calculate the total cost for different sets of items. We want to ensure that the price and quantity ranges always refer to the same data.
2.
Question 1: A student is creating a spreadsheet to track their weekly expenses. They want to easily refer to the total amount spent each week. They decide to name the cell containing the total weekly expenses as "WeeklyTotal". Explain, in terms of the benefits to the user, why it is advantageous to use named cells in a spreadsheet. Consider ease of use, accuracy, and potential for future changes.
Using named cells provides several advantages:
- Ease of Use: Instead of remembering the cell address (e.g., A1), the user can simply use the name "WeeklyTotal" in formulas. This makes formulas easier to read and understand.
- Accuracy: If the cell containing the total weekly expenses is moved, the formula referencing the named cell will automatically update to reflect the new location. This reduces the risk of errors caused by incorrect cell addresses.
- Future Changes: If the spreadsheet layout is changed (e.g., columns are added or deleted), the named cell reference remains valid, ensuring formulas continue to work correctly. This makes the spreadsheet more robust and easier to maintain.
In summary, named cells improve the readability, accuracy, and maintainability of spreadsheet formulas.
3.
A shopkeeper has the following prices for different items: Apples: £1.50 each, Bananas: £0.75 each, Oranges: £1.00 each. A customer buys 3 apples, 4 bananas, and 2 oranges. Calculate the total cost of the customer's purchase using appropriate functions. Show the formula and explain what each function does.
Formula:
- Cost of Apples:
=3 * 1.50 Explanation: This multiplies the number of apples (3) by the price of each apple (£1.50) to find the total cost of the apples. - Cost of Bananas:
=4 * 0.75 Explanation: This multiplies the number of bananas (4) by the price of each banana (£0.75) to find the total cost of the bananas. - Cost of Oranges:
=2 * 1.00 Explanation: This multiplies the number of oranges (2) by the price of each orange (£1.00) to find the total cost of the oranges. - Total Cost:
=SUM(3 * 1.50, 4 * 0.75, 2 * 1.00) Explanation: The SUM() function adds together the individual costs of the apples, bananas, and oranges to calculate the total cost of the customer's purchase.