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

NOTE:
-

Question

QUESTION 4: SPREADSHEET

NOTE:
- Use formulae and/or functions for ALL calculations in the spreadsheet.
- Use absolute cell references ONLY where necessary to ensure that formulae are correct when you copy them to other cells in the same column (copy down).
- Insert formulae and/or functions in such a manner that the correct results will still be obtained even if changes are made to the existing data.
- Should you need to use building blocks, use the allocated space in the spreadsheet.

The details of sponsors and their donations are stored in a spreadsheet. As sponsors may make a single or monthly donation, the card numbers from which payments are made, is recorded.

Open the 4Donate spreadsheet and work in the Details worksheet.

4.1 Insert a function in cell F3 to determine the value that appears most often in column D.

4.2 Several sponsors donated the same highest amount (column D).
Insert a function/formula in cell F4 to determine the total value of the highest amount donated.

4.3 Change the SUMIFS function in cell F5 to determine the single donations (column C) that were made in 2019 (column E).

4.4 MSF will use the data in column C to determine which type of message should be sent to the sponsors.

NOTE: A bank card has expired if the expiry date (column G) is before the date in cell H4.

Use the donation type (column C) and the expiry date (column G).
Insert a function/formula in cell H8 to display the following:
- The word 'Expired' if the bank card of a monthly sponsor has expired OR
- The word 'Remind' if the bank card of a monthly sponsor is still valid and a transaction will take place OR
- A '#' character if the card was used for a single donation as he/she will not receive a message.

Ensure that the correct results will display if the function were to be copied to the rest of the column.

Work in the Code worksheet.

4.5 MSF allocates a code to each country to keep track of how many donations come from each country.

The code is created by moving the first two characters of the name of the country to appear as the last two characters of the name.

Example: China would be displayed as 'inaCh'.

Insert a function of functions in cell C3 to display the code for Argentina using this coding system.

Save and close the 4Donate spreadsheet.

QUESTION 4: SPREADSHEET NOTE: - Use formulae and/or functions for ALL calculations in the spreadsheet - NSC Computer Application Technology - Question 4 - 2021 - Paper 1

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

Insert a function in cell F3 to determine the value that appears most often in column D.

Insert a function/formula in cell F4 to determine the total value of the highest amount donated.

Change the SUMIFS function in cell F5 to determine the single donations (column C) that were made in 2019 (column E).

Insert a function/formula in cell H8 to display the message based on card expiration and donation type.

Insert a function of functions in cell C3 to display the code for Argentina.

Join the NSC students using SimpleStudy...