June 2011 Paper 31 Walkthrough Task 1: Open new document in MS Word and save as EVIDENCE Task 2: Use a 3 column header to include the candidate details as requested Spreadsheets – MS Excel Task 3: In MS Excel, do File > Open to open J1GAMES.CSV Task 4:
Header: Insert > Header & Footer > Type in candidate details Click go to footer Footer: click on “File Path” icon
Task 5:
In Google do a search for “Olympic record Holders” Wikipedia is the most user friendly site for quickly finding records Men’s shot put:
Women’s shot put
It is best to copy and paste the information so as to avoid spelling mistakes When you enter the distance, it is IMPORTANT that you only enter a number and not the “m”
Task 6
Auto adjust the width of the columns A to F Click on page layout > Gridlines > Tick View > Tick Print Click on page layout > Headings > Tick view > Tick > Print Highlight cells A1:F4 Choose print > selection
Task 7
Make sure J1GAMES and J1Country are in the same folder Open both files and place them side by side In A8, do the following to construct your function o =VLOOKUP (as you know that you are looking up data vertically) o =VLOOKUP(C8 (as this is the cell that you are looking up – IRE) o =VLOOKUP(C8,J1country.csv!$A$1:$B$32 (you get the range by highlighting it) o =VLOOKUP(C8,J1country.csv!$A$1:$B$32,2,FALSE) (2 is the return column, FALSE is because you want an exact match) Replicate the function for the whole column
Task 8: =MAX(E8:J8) and replicate Task 9:
=SUMIF(E8:J8,"<>NT",E8:J8) Looks in the range E8:J8 for cells that are NOT NT and then adds up from the same range Formula also works if you forget to put in the second range (but you would lose a mark!)
June 2011 Paper 31 Walkthrough Task 10
=COUNTIF(E8:J8,"<>NT") Counts the number of cells in the range that are NOT “NT”
Task 11
If it says use L8 and M8 to help you, then you are expected to do this! =L8/M8 would give you the average You are required to ROUND to 1dp so will need to use the ROUND function =ROUND(L8/M8,1) This divides L8 by M8 and rounds the result to 1dp
Task 12
=IF(D8="Male",IF(K8>$D$3,"Yes","No"),IF(K8>$D$4,"Yes","No")) If D8 is Male then do the first IF statement o Checks if best throw is bigger than Male Olympic record o If it is then it puts yes, if it isn’t it puts no If D8 isn’t equal to Male then do the second IF statement o Checks if best throw is bigger than Female Olympic record o If it is then it puts yes, if it isn’t it puts no
NOTE: Manually check to see if the IF statement works. I assumed that it did and noticed later that I had entered D3 and D4 with the “m” for metres still there! Task 13: Highlight columns D to O and right align contents Task 14: Page Layout > Orientation > Landscape Task 15: Save file as ATHLETES (make sure it is excel NOT CSV) Task 16: Highlight columns B to J, right click > Hide Task 17:
Print showing formulae used o Click Formulas tab > Click show formulas o Auto adjust column widths Row and column headings o Click on page layout > Gridlines > Tick View > Tick Print o Click on page layout > Headings > Tick view > Tick > Print Two pages wide o Do print preview o Double check in preview that header and footer are showing
Task 18: Highlight column A and K > right click > unhide Task 19:
Firstly turn off show formulas Redo the auto-widths for the whole sheet Great Britain only o Highlight the contents of Row 6 and click on data > filter
June 2011 Paper 31 Walkthrough
o Use the drop down icon to select only Great Britain Create graph o Use CTRL to highlight only the names and Average throws (include the headings) o Insert Column > 2D
Task 20: Change the title to that given (be careful of capitalisation and spelling) Task 21: Double check that this is as requested; if it isn’t redo the graph Task 22:
To include your name, etc either copy and paste to word and add the details before printing or: Add an X axis and put the details in that (this is what I did)
Task 23: Open the “athletes” spreadsheet Task 24: Highlight columns L and M > right click > hide Task 25: Highlight rows six and 7 > make bold, italic and centred Task 26:
Highlight row 7 (not row 6) In the record breaking column only select “Yes”
Task 27:
Click anywhere in the data Click on data > sort (the data must still be filtered)
Task 28
Adjust the column widths so that everything fits on one page Check in print preview that data and data labels are visible Check that the header contains your candidate details
Websites Task 29
Create a folder called “WEBSITE” first Then download the files into it
Task 30: Open the image in Paint Task 31: Click on resize > pixels > tick “maintain aspect ratio” then add to evidence (remember to save with a new name)
June 2011 Paper 31 Walkthrough Task 32:
Open the newly resized image in GIMP Export it as a jpg as this will allow you to choose compression Make sure that you give it a new filename J1BG3 before compressing Compare the file sizes and place in your evidence document Although your original image may not be more than 100kb still do this task as marks are available.
Task 33: Open in Paint Task 34:
Click on rotate > flip horizontal Save with the new filename
Task 35: Open with notepad Task 36:
Open J1SHOT.htm in your browser Change the stylesheet number is notepad, save and then refresh the browser Decide which is the easiest to read (consider that some images may be changed) In this case J1style3.css is the most appropriate Save the file with this stylesheet attached
Task 37: resize browser window so that background image doesn’t tile (repeat) Task 38: Replace candidate details as instructed Task 39:
Find the correct image and include the following code: <img src ="J1Shot.jpg" width="90px"> By not including a height tag, the aspect ratio will be maintained
Task 40 / 41:
<TABLE width = "450px" id"=middle" cellpadding="3px" cellspacing="3px">
Task 42:
Change the display in your folder from list to icons as this will make it easier to see the images from which to choose
Save your work and check that the images are in the correct place
June 2011 Paper 31 Walkthrough Task 43:
Task 44:
Task 45:
Task 46: ď&#x201A;ˇ ď&#x201A;ˇ
Print a copy of the HTML source (do this from notepad File > Print) o Look at the end of the paper to see what you should highlight Open file in web browser and add screen print to evidence
Task 47: save and print your evidence document