Problemset

Bina Nusantara Skill Competition
Qualification Round
Sunday, 5 August 2007

Topic : Spreadsheet – Formula and Function

Please download the result and content in here

Instruction:
Save with filename: "Answer SS – Formula & Function.xls"
(reference to additional field that is not give is NOT ALLOWED, if there’s any of it, the formula that refer to additional field wouldn’t be marked)

Instruction :

• Rename sheet “Sheet1” into “Master” ; and “Sheet2” into “Result”
• In sheet “Master”, give name to table :
  • MsProduct       : B2:C11
  • Price                : B15:F24
• In sheet “Result”
  • Brand name is taken from table “MsProduct” based on first 2 digit of code, if it is not list in table “MsProduct”, fill it with “Others”
  • Categories is filled based on the third digit of Code, as follow :
    • 1 : GSM
    • 2 : CDMA
    • 3 : PDA
    • 4 : Others
• Amount is filled based on the last 2 digit of Code
• Price is taken from table “Price” based on its Brand and Categories, if it is not listed in table “Price”, the price will be Rp 1,000,000
• For every transaction that has even number of amount will get 7% discount, and for odd number of amount will get 5% from its total.
  Where total = amount * price
• Mark the minimum payment with “Rose” pattern and maximum payment with “Light Green” pattern automatically.
  (if the code, that will affect amount and price, is changed, it will change Total Payment too and the mark might adjust it automatically)
• Fill I37 with the total of all payments
• Fill I38 with maximum payment from all transactions
• Fill I39 with minimum payment from all transactions
• From row 43 to 52 :
  • Column H is filled with number of transaction that choose Brand in column C
  • Column I is filled with total payment for each brand in column C
• Format the column as it seen on the picture below :