QUESTION 4: SPREADSHEET
NOTE:
- Use formulae and/or functions for ALL calculations in the spreadsheet - NSC Computer Application Technology - Question 4 - 2018 - Paper 1
Question 4
QUESTION 4: SPREADSHEET
NOTE:
- Use formulae and/or functions for ALL calculations in the spreadsheet.
- Use absolute cell references ONLY where necessary to ensure... show full transcript
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 - 2018 - Paper 1
Step 1
Format row 1 as follows:
96%
114 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To format row 1, you will:
Merge cells A1:M1. This can be done by selecting the range and choosing 'Merge Cells' from the formatting options.
Ensure that the text in A1:M1 is NOT centered.
Increase the font size to 32.
Step 2
The e-mail addresses in column C should NOT include upper case letters:
99%
104 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In column D, to convert the email addresses in column C to lower case, use the formula:
=LOWER(C3)
Apply this function to the rest of the cells in column D.
Step 3
Use an appropriate LOOKUP function in cell F3:
96%
101 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell F3, use the HLOOKUP function to find the description based on the 'Type code' in column E. The formula will be:
=HLOOKUP(E3, Code_Kode!$A$1:$K$2, 2)
This function searches for the value in E3 within the specified range in the Code_Kode worksheet.
Step 4
Use a function in cell H4 to determine the commission percentage:
98%
120 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
You can use nested IF functions to check the conditions based on the expense code in column G. The formula will look like:
=IF(G4=3,0.20, IF(G4=2,0.25, IF(G4=1,0.33)))
This formula assigns the commission percentage based on the specified codes.
Step 5
Use a function in cell K5 to determine the date:
97%
117 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To calculate the date when the artist's art pieces will be removed, use:
=EDATE(J5,I5)
This will add the number of months (from column I) to the start date (from column J).
Step 6
Change the format of the values in column L to currency:
97%
121 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
Select the range in column L, and change the format to currency via the formatting options.
Step 7
Insert a formula in cell M6 to calculate the selling price:
96%
114 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell M6, to calculate the selling price, use:
=L6 + (L6 * H6)
This adds the commission to the artist's price.
Step 8
Use conditional formatting to shade the cells in column K:
99%
104 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
Set a condition to check if the date in column K is before the current date using:
=K3 < TODAY()
Choose a colour to apply to the cells meeting this condition.
