First Page

• (Any) picture added to first page ✓
• Correct picture 1Music added ✓
• Picture wrapping/alignment changed to appear at top right-hand corner of page ✓

First Page

• Text box added (anywhere) to the first page ✓
• 'Elvis High School' added as text to the text box ✓

Styles

• New style 'Music' created ✓
• Font size formatted to 48 pt ✓
• Text centred ✓

Page Border

• Any page border added ✓
• First page only ✓

Watermark

• Watermark text: 'First Draft' added ✓
• Watermark text added only to first page ✓
  (Note to marker: Do not accept if 'Different First Page' has been selected and watermark has not been deleted from second page.)

Comment

• Comment deleted ✓

Text Effect

• Font colour orange ✓ gradient text fill effect ✓ applied to heading 'The Effect Music Has On Me'
  (Notes to marker:
• Allocate first mark if font colour was changed.
• Mark from dialogue box.)

Dropped Cap

• Dropped cap effect applied to the 'M' of 'Music' ✓
• Over 2 lines ✓
• 0.6 cm/16 pt away from text ✓

Line Spacing

• Second paragraph line spacing set to 'Multiple 1.15' ✓

Endnote

• Endnote inserted ✓ on text 'Rap and Rock'
• Appears in capital Roman number format 'I' ✓
• Text entered in endnote ✓ ('Adapted from an article by S Mariam')

Find and Replace

• 2 occurrences ✓ of manual line break (soft enter)
• Replaced with hard enters/paragraph marks ✓
  (Note to marker: Located at the end of the second and third paragraphs under the heading 'The Effect Music Has On Me'.)

Page numbering

• Page numbering (any style) inserted in the footer ✓
• Automatic page numbering inserted (as a field) ✓
• Page numbers are centred ✓
• First page is not numbered ✓
• Numbering starts as page 1 on the second page (after the table of contents page) ✓
  (Note to marker: Ignore if the numbering on the landscape pages from Question 2.6 onwards differs.)

Table of Contents

• Automatic table of contents inserted ✓
• Fancy style used (Small caps) ✓
• Two levels of headings appear ✓
• Solid line leader tab appears for page numbers ✓
  (Note to marker: Ignore if ToC appears twice.)

Styles and Table of Contents

• 'Heading 1' style applied to the heading ✓
• Table of contents reflects new entry (any style) ✓

Citations

• New interview source added ✓
• 'Steve Johnson' inserted ✓ as interviewee
• Text 'Placeholder1' replaced with automatic citation ✓

Columns

• Text appears in 3 columns ✓
• Spacing between columns is set at 1.1 cm ✓
• Column break ✓ applied to each sub-heading
• Text justified ✓
  (Note to marker: Award third mark for any column break.)

Page Orientation

• Page containing text in landscape orientation ✓
• All other pages remain portrait orientation ✓

Numbered and Bulleted List

• Level 1 numbering set to 1. 2. 3. ✓
• Level 1 numbering aligned to 1 cm ✓
• Level 1 text relatively aligned/indented to 2 cm ✓
• Level 2 set to ✓✓
• Level 2 bullet relatively aligned/indented to 2 cm ✓
  (Note to marker: Ignore if 1., 1 or 1) and type of ✓ bullet.)

Table

• Function inserted =AVERAGE(Above) ✓

Range A1:M1:

• Merge and centre ✓ applied to text in range A1:M1

Cell G2:

•  =TODAY() ✓ OR NOW()

Cell B3:
=COUNTA(B5:B103)
OR =COUNTIF(B5:B103,"*")

• COUNTA/COUNTIF function ✓
• Range✓: (B5:B103)
  (Note to marker: Accept COUNTA function on columns C, D, E, F, G, I or L OR COUNT function on columns D, E, F, G or I.)

Cell A5:
=UPPER(B5)

• UPPER function ✓
• Cell (B5) ✓

Print setting:

• Print area set ✓ to range B4:D103
• Print gridlines set to show ✓
  Notes to marker:
• Accept if column A has been included.
• Accept if 'Headings – Print' were selected in 'Sheet Options' and range is B5:D103.)

Column G:

• Format changed to yyyy-mm-dd ✓
  (Note to marker: Accept if just cell G5 formatted.)

Cell H5:
=CONCATENATE("011",I5) OR ="011"& I5

• CONCATENATE function OR & ✓
• "011" ✓
• I5 ✓

Cell K5:
=IF(J5="","Yes","No")
OR =IF(ISBLANK(J5),"Yes","No")
OR =IF(J5<>"","No","Yes")
OR =IF(COUNTA(J5)=0,"Yes","No")
OR =IF(LEN(J5)=0,"Yes","No")

• Testing if J5 is "blank" ✓
• If cellphone is not available: "Yes" ✓
• If cellphone is available: "No" ✓
• Function copied to cells K6:K103 ✓
  (Note to marker: Accept any suitable variations and if "Y" and "N" used instead of "Yes" or "No".)

Cell M5:
=MID(L5,FIND("@",L5,1)+1,LEN(L5))
OR =RIGHT(L5,LEN(L5)-FIND("@",L5))

• To determine the position of the '@':
  
  • FIND function ✓
  • @' character ✓
  • In the correct cell: L5 ✓
• Extracting the domain:
  
  • Function to extract (MID or RIGHT) ✓
  • Correct cell used to extract from: L5 ✓
  • From after the position of the '@' sign ✓
  • To the end of the text/domain: LEN(L5), etc.✓
    (Note to marker: Check for 'building blocks' in other columns.)

Extract and copy:

• Two members born in the month of December ✓
• Copied to the Dec worksheet ✓
  (Notes to marker:
• 2 members expected (Killian R and Hassim T).
• Accept if not all the information was copied.)

Column B:

• All data is visible in column B ✓

Column G:

• Data in column G formatted to 2 decimal places ✓
  (Note to marker: Accept if only part of the column has been changed or if formatted as a currency format.)

Cell F2:
=VLOOKUP(A2,Code!$C$2:$D$4,2)

• Lookup value: A2 ✓
• Table array: Code_Kode!C2:D4 ✓
• Reference column: 2 ✓ or use of LOOKUP function
• Absolute cell reference: Code_Kode!C$2:D$4 ✓
• Copied to remaining cells ✓
  (Notes to marker:
• Ignore 'True' or 'False'.
• Also accept absolute cell reference $C$2:$D$4.)

Cell H2:
=IF(A2=1,G2*1.2,IF(A2=2,G2*1.25,G2*1.15))
OR
=IF(A2=1,G2+G2*20/100,IF(A2=2,G2+G2*25/100,G2+ G2*15/100))
OR
=IF(A2=1,G2*120/100,IF(A2=2,G2*125/100, G2*115/100))
OR
=IF(F2="CD",G2*120/100,IF(F2="DVD",G2*125/100, G2*115/100))

• Nested IF/Function-construct is correctly used ✓
• Condition CD OR Code 1 and value G2*1.2 ✓
• Condition DVD OR Code 2 and value G2*1.25 ✓
• Condition MP3 OR Code 3 and value G2*1.15 ✓
  (Note to marker: Accept if column F was used with 'MP3', etc. If the percentage was calculated without the increase, penalise conditions by one mark.)

Cell L2:
=COUNTIF($D$2:$D$612,K2)

• Absolute cell reference D$2:D$612 ✓
• '1990' replaced with cell reference K2✓
  (Note to marker: Accept ($D$2:$D$612) as range.)

Cell L21:
=SUMIF(E2:E612,"POP",G2:G612)

• SUMIF function ✓
• Criteria range: (E2:E612) ✓
• Criteria: "POP" OR K21 ✓
• Sum range: (G2:G612) ✓

Cell J2:

• Expected answer: 101 ✓
  Method J3:
• Removed duplicate ✓ owners ✓
  ---------------------------------------------------------------------------------------------------------------
  OR
• Sorted, deleted duplicate owners ✓ and counted ✓
  --------------------------------------------------------------------------
  OR
• Sorted owners ✓ and counted unique owners ✓
  (Notes to marker:
• Accept any other correct method e.g. =SUMPRODUCT((G2:G636<>"")/COUNTIF(G2:G636,G2:G636)).
• Allocate all three marks if the correct answer displays.)

Chart:

• Horizontal axis title ('Years') inserted ✓
• Horizontal axis labels changed to display years 2000 - 2005 ✓
• Legend changed to 'Number of songs' ✓
• Vertical axis label major unit set to fixed 50 ✓
• Data labels set (any position) to show values✓

Field: Gender

• Default value set to 'M' ✓

Field: Grade

• Set as required field ✓

Field: Class

• Display control: List box/Combo box ✓
• Row source type: Value list ✓
• Row source: A, B, C, D OR a, b, c, d ✓
  (Notes to marker:
• Allocate third mark if one option added.
• Also accept if separate table created and used with rows/source type set to Table/Query.)

Field: DOB

• Format
  Set to Medium Date ✓
• Input Mask symbol
  Placeholder changed to # via wizard: 00\->L<LL\-00;0;# ✓

Field: Cell

• Validation rule: Len ✓ ([Cell_Sel]) ✓ =10 ✓
• Validation text: Refers to 10 characters ✓

• Displays all 12 fields from Learners table ✓
• Font colour of the data in the Owner field appears as reddish ✓
• Combo box/List box added in details section ✓
• Row source based on Owner field ✓
• Row source: SELECT [Learners].[Owner] FROM Learners; ✓
• Label 'Find what' added ✓
• Horizontal line added to form footer ✓
  Notes to marker:
• Mark from Form view.
• Do not allocate fifth mark if selected record does not display.
• Accept any line inserted anywhere in form footer.)

• Fields displayed: Title, Artist, Year and Genre ✓
• Year criteria: 1996 ✓
• Genre criteria: 'DANCE' ✓
• Title field sorted ascending ✓
  (Note to marker: 1 record expected.)

• Cost field added ✓ and format set to any currency ✓
• Calculated field added: Inc: ✓ [Cost]  * 2  award two marks if *2 is omitted OR Inc: [Cost]+[Cost]

• Total row displayed ✓
• Grouped by Genre ✓
• Cost field: Total property set to Sum ✓

Address field criteria: Like "13 *Crescent"

• 13 ✓
• Compulsory space after 13 ✓
• *Crescent/*Cre* ✓
  (Note to marker: 3 records expected. Accept any alternative method.)

• Report display fields: Artist, Title, Year, Cost and Genre ✓
• Report grouped according to Year field ✓
• Sorted on Artist field ✓
• Report heading changed to 'Year released' ✓
• Function in Year footer/header ✓: =SUM([Cost])  and suitable label added ✓
• Function in report footer/header ✓: =MAX([Cost]) ✓
  (Note to marker: Do not penalise if label in the report footer is not given or not suitable.)

Title
<head>
<title>'60s Music</title>
</head>

• Title with title tags moved to head section of web page ✓

Image
<img src ="6Sixty.jpg"….>

• Tags added for image source ✓
• Image 6Sixty.jpg named/used correctly ✓

Alignment
<h1 align="center">Music</h1>

• Music' heading centred ✓

Horizontal line
<hr size="3" width = "70%"/>

• Size of horizontal line set to 3 ✓
• Width of horizontal line set to 70% ✓

Heading style
<h2>Before 1963</h2>

• Heading 2 style applied to text ✓

Bold
<b>The Beatles</b>

• Bold applied to text 'The Beatles' ✓

Italics
<p><i>To mention a few ...</i></p>

• Text 'To mention a few …' appears in italics ✓
• In a separate paragraph ✓

Table
<table bgcolor="orange" align="center" border="3">
<tr>
<th>Artist</th>
<th>Hits</th>
</tr>

• Table's background colour changed to orange ✓
• Row added at the top of the table ✓
• Two cell heading tags added ✓
• Cells contain headings 'Artist' and 'Hits' respectively ✓
  (Notes to marker:
• Allocate last mark if only one heading appears.
• Do not accept <td> instead of <th>.)

Numbered list
<ol>
<li>Surfin' USA</li>
<li>Good Vibrations</li>
</ol>

• The text 'Surfin' USA' and 'Good Vibrations' appear in a numbered list ✓
• Two list tags/items ✓
  (Note to marker: Allocate mark if only one list item appears. Accept ordered or unordered list.)

Link
<a href="#Top">Back to top</a>

• Text inserted 'Back to top' ✓
• Link created on the text 'Back to top' ✓
• Link moves to 'Top' ✓

Document properties

• Comment property reads 'CAT Exam' ✓

Name: Form field control

• Text form field inserted next to text 'Name' ✓

Surname: Text form field

• Text form field property set to display in uppercase ✓

Drop down list

• Drop down form field inserted ✓
• Options: Beatles, Elvis Presley, The Beach Boys ✓
  (Note to marker: Accept at least two options to allocate the second mark.)

Mail Merge

• 7Data used as the data source for the mail merge ✓
• Name and DOB fields inserted in correct positions ✓
• Merge completed ✓ (7Data)
  (Note to marker: 99 letters expected.)

Tables

• New table ✓ created called 7Comb
• Records from both tables combined (33 records) ✓
• Records sorted in descending order according to Name field ✓
  (Note to marker: Do not penalise if data in Code field has changed.)

• Accept any method that will yield the correct answer.
• One mark for the answer and one mark for the method. If there is only an answer and no method, allocate two marks if the answer is correct.

Which song charted in both the American and British charts on 1 August 1964?

• A Hard Day's Night ✓
  Method: ✓
• Filter on Date and Year
• Filter search 1964-08-01
• Sort on Date, Year, Chart

How many different or unique titles by Elvis Presley reached the charts?

• 9 ✓

Method: ✓

• Filter on Artist, count only unique values
• Remove duplicate songs, count on Artist
• COUNTIF and count unique values

Which chart featured the most titles?

• American ✓ (178)

Method: ✓

• COUNTIF British and American charts and compare
• Filter both and compare total on scroll bar
• Sort and count

Which year featured the most titles on the charts?

• 1964 ✓ (100)

Method: ✓

• Filter each and see count on scroll bar
• Filter, copy and count
• COUNTIF
• Sort, select years and see count on scroll bar
• Use SUBTOTAL function