e-Consult | Revision Qns

1.

A teacher wants to create a table in a spreadsheet to display the cost of different items. The table should include the item name, the price per item, and the total cost if 5 of that item are purchased. Show how the teacher would set up the spreadsheet, including the cell references for the formulae. Present your answer as a table.

The teacher would set up the spreadsheet as follows:

Item Name	Price per Item	Total Cost (5)
Apple	1.00	=B2*5
Banana	0.50	=B3*5
Orange	0.75	=B4*5

Explanation:

Item Name: The name of the item is entered in column A (e.g., A2, A3, A4).

Price per Item: The price of each item is entered in column B (e.g., B2, B3, B4).

Total Cost (5): The total cost for 5 of that item is calculated in column C. The formula in cell C2 is =B2*5, which multiplies the price per item (B2) by 5. This formula is then copied down to the other rows (C3, C4) to calculate the total cost for the other items.

2.

A student is creating a spreadsheet to track exam results. They need to highlight the students who achieved a grade A. Describe, with specific instructions, how they could use formatting features in a spreadsheet program (e.g., Microsoft Excel, Google Sheets) to visually identify these students. Your answer should include details on the specific formatting options used and why they are appropriate.

To visually identify students who achieved a grade A, the student should use a combination of cell formatting options. Here's a detailed breakdown:

Select the cells containing the student names and their exam grades. This can be done by clicking and dragging the mouse over the relevant cells.

Use the 'Fill Color' (Shading) option: Select a distinct colour, such as green, to fill the background of the cells containing the grade A results. This immediately draws attention to these specific entries.

Apply 'Bold' formatting to the student names: This will make the names stand out from the grades, improving readability.

Consider using 'Text Colour' for emphasis: If the background colour is light, a darker text colour (e.g., dark blue or black) can improve contrast and readability.

Alternatively, use 'Underline' for the grade A entries: A thin underline can provide a subtle visual cue. This is often used in conjunction with other formatting options.

These formatting choices work together to quickly and easily identify the students who achieved a grade A, making the spreadsheet more user-friendly and easier to interpret.

3.

A teacher wants to analyse the results of a quiz. The quiz results are stored in a spreadsheet with the marks in column A. The teacher needs to calculate the average mark, the highest mark, and the lowest mark. Write a formula using appropriate functions to calculate these values. Show the formula and explain what each function does.

Formula:

Average Mark: =AVERAGE(A1:A20) Explanation: The AVERAGE() function calculates the arithmetic mean of a range of numbers. In this case, it calculates the average of the quiz marks in cells A1 to A20.

Highest Mark: =MAX(A1:A20) Explanation: The MAX() function returns the largest value in a set of numbers. Here, it identifies the highest mark from the range A1 to A20.

Lowest Mark: =MIN(A1:A20) Explanation: The MIN() function returns the smallest value in a set of numbers. It finds the lowest mark in the range A1 to A20.

20 Spreadsheets (3)