NSC Computer Application Technology Software enhancing accessibility, efficiency: 4. SPREADSHEET

Use formulae and

Question

4. SPREADSHEET

Use formulae and/or functions for ALL calculations in the spreadsheet.
Use absolute cell references ONLY where it is required by the question to ensure that formulae are correct when you copy it to other cells in the spreadsheet.
All formulae and/or functions should be inserted in such a manner that the correct results will still be obtained, even if changes are made to the existing data.

Open the 4Jewel_Juweel spreadsheet and work in the Jewel_Juweel worksheet.

4.1 Apply text wrapping to the headings in row 3.

4.2 Each piece of jewellery has a jewellery ID, which is obtained as follows:
- Three letters of the precious metal in column C, starting from the second letter, followed by
- The jewellery type in column B

Insert a function in cell A5 to determine the jewellery ID for the item in row 5.
EXAMPLE: The jewellery ID for the bracelet in cell C4 would be ivBracelet.

4.3 Each item listed has four possible quality levels, as shown in the table below.

Metal_Metal             Quality levels_Kwaliteitsvlakke
Level1_Vlak1  Level2_Vlak2  Level3_Vlak3  Level4_Vlak4
Gold          14 carat     22 carat     24 carat     999
Palladium     500          950          970          999
Platinum      850          900          950          999
Silver        800          925          958          999

EXAMPLE: The four different quality levels for silver are 800, 925, 958 and 999.

Insert a VLOOKUP function in cell D5 in the Jewel_Juweel worksheet as follows:
- Randomly generate a level from 1 to 4 to represent a quality level.
- Use the randomly generated level to look up the appropriate quality level for the metal in cell C5 by using the quality-level table in the Qual_Kwal worksheet.

NOTE: You may use building blocks to answer this question.

4.4 Column F should ONLY display an asterisk ('*') if the metal in column C is silver and there is a value for the stone type in column H.

In all other cases the cell must be left blank.

Insert a function in cell F4 to implement this.

4.5 Insert a function in cell M6 to determine the total value of all the rings and earrings in the collection.

4.6 Insert a function in cell M7 to determine the number of pieces of jewellery that have any stone (column H).

4.7 Insert a function in cell M8 to determine the number of pieces of jewellery that have any stone (column H).

Save and close the 4Jewel_Juweel spreadsheet.

4. SPREADSHEET Use formulae and/or functions for ALL calculations in the spreadsheet - NSC Computer Application Technology - Question 4 - 2016 - Paper 1

Worked Solution & Example Answer:4. SPREADSHEET Use formulae and/or functions for ALL calculations in the spreadsheet - NSC Computer Application Technology - Question 4 - 2016 - Paper 1

4.1 Apply text wrapping to the headings in row 3.

4.2 Insert a function in cell A5 to determine the jewellery ID for the item in row 5.

4.3 Insert a VLOOKUP function in cell D5.

4.4 Insert a function in cell F4 to implement this.

4.5 Insert a function in cell M6 to determine the total value of all the rings and earrings in the collection.

4.6 Insert a function in cell M7 to determine the number of pieces of jewellery that have any stone (column H).

4.7 Insert a function in cell M8 to determine the number of pieces of jewellery that have any stone (column H).

Join the NSC students using SimpleStudy...