Question 3 – Spreadsheet The owners of The Tiger Canyon are…
Question 3 – Spreadsheet The owners of The Tiger Canyon are very interested in other wild animals as well. Open the 3Statistics_Statistieke spreadsheet and continue working in the Animals Hunted_Diere Gejag worksheet that summarises the hunting of wild animals in South Africa over five years. 3.1 Use a spreadsheet feature to remove the drop-down list from cell E4. (1) 3.2 Format the headings in row 3 to appear in the cells as shown in the screenshot: Right-click on this button and select the “Open link in new tab” option (1) 3.3 Format the cells in column K to display the top 3 in the range to a green fill with a dark green text. (2) 3.4 The animal types are stored in column A. Use the FIND function in cell Q18 to determine at which position the ‘i’ in the word ‘tiger’ in cell A18 appears. (3) 3.5 Use a function in cell K29 to calculate the total number of animals, more than 100, that were hunted in column K. (3) 3.6 Calculate the number of animals hunted that appear the most in cell K31. (2) 3.7 Format row 3 of the spreadsheet so that the top row will be visible while scrolling through the rest of the worksheet. (1) 3.8 Use a LOOKUP function in cell M4, and the data in the Table_Tabel worksheet, to display the availibility of the different wild. Make sure that this function can be copied down to cell M26 (6) 3.9 Calculate in cell K33 the % of tigers that were hunted over five years in comparison to the total amount of animals hunted. Round the function down to 2 decimals (4) 3.10 Calculate in cell K35 the highest total number of animals that were hunted in all provinces. (2) 3.11 A code is used to identify the different animals. The animal code is made up with the letters ‘WA’ that appears in cell B29, in lower case, followed by the first three letters of the name of the animal in column A, followed by the last two letters of the name of the animal in column A Example: The code for ‘Aardwolf’ would be waaarlf. Insert a function/formula in cell N5 to determine the code for ‘African Wild Cat’. (7) 3.12 The chart in the ‘Chart_Grafiek’ worksheet displays the number of animals hunted over five years in the different provinces. Format the chart so that it is displayed as in the example below: Right-click on this button and select the “Open link in new tab” option Change the chart type so that the chart is displayed as in the screenshot above. Change the colour of the pyramids in the chart so that each pyramid is displayed in a different colour. Minor tick marks must be displayed on the vertical axis of the chart. The title of the vertical axis of the chart must be displayed as shown in the screenshot. The ‘Chart_Grafiek’ worksheet must appear as the second worksheet in the spreadsheet. Right-click on this button and select the “Open link in new tab” option (1) (1) (1) (1) (1) 3.13 Continue working in the ‘Table_Tabel’ worksheet and create a hyperlink on the image of the animal to link with cell A24, in the ‘Animals Hunted_Diere Gejag’ worksheet. Ensure that a screen tip, ‘Dodos are extinct’, is displayed. (4) Save and close the 3Statistics_Statistieke spreadsheet.