NSC Computer Application Technology Uses of common applications: QUESTION 4: SPREADSHEET

The 4Je

Question

QUESTION 4: SPREADSHEET

The 4Jewel_Juweel spreadsheet contains the jewellery collection of a young investor.

NOTE:

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 A4 would be ivBracelet.

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

| Metal_Metaal | Quality levels _Kwaliteitsvlakke |
| Level1_Vlak1 | Level2_Vlak2 | Level3_Vlak3 | Level4_Vlak4 |
| Palladium | 900 | 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.

Copy the function in cell F4 to the rest of the cells in column F.

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.

QUESTION 4: SPREADSHEET The 4Jewel_Juweel spreadsheet contains the jewellery collection of a young investor - NSC Computer Application Technology - Question 4 - 2016 - Paper 1

Worked Solution & Example Answer:QUESTION 4: SPREADSHEET The 4Jewel_Juweel spreadsheet contains the jewellery collection of a young investor - 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 the asterisk condition.

Join the NSC students using SimpleStudy...