3

Subtask 1a (5 Marks)

  1. Click on the Marks sheet. It contains marks for 26 students, along with their student IDs and names. There are also incomplete fields for Total mark and Grade. The Total column is yellow and the Grade column is pale green. Change this so that the Grade column is yellow and the Total column is pale green. To do this, click on the Home tab, go to the Font group, and click on the arrow just right of the pouring bucket icon.

  2. Next, change the colour of the text in the row of column headers to standard colour Blue, and the background of this row to Red, Accent 2, Lighter 80%. Change the style of the row headers to bold. Change the font to Times New Roman and the font size to 11.

  3. One of the column headers is truncated. Adjust the column width so that the whole header can be seen.

  1. Now you need to compute the Total Mark for each student. The formula to use is

    total = 4*(labs+midsession)*exam/(labs+ midsession+exam)
    (this is the harmonic mean of session and exam marks, scaled up to marks out of 100).

    Click in Cell F2 in the Total column, and enter this formula, typing C2 for labs, D2 for midsession, and E2 for exam. Press enter when you have typed the formula. The result, about 94.9052632, should appear in cell F2. This has too many decimal places, so select the whole column from F2 to F27, go to the Number group on the Home tab, and click on the rightmost icon enough times to reduce the number of decimal places to 1. Save the workbook.

  2. Now click in F3, and enter the formula again, but this time click on C3, D3, and E3 rather than typing them, wherever you need them in the formula. The answer should be 77.0 this time.

  3. Now copy the formula down the rest of the column, by clicking in F3, moving the mouse into the bottom right corner of the cell until the cursor changes to a small black +, then click and drag down the column to F27.

  4. Now you have to compute the grades of the students: HD, DN, CR, PS, GP, and FL, using the usual UNSW criteria. To do this, you need to set up a lookup table. Click in A29, and type Base Mark, hit enter. Click in B29, type Grade and hit enter.
    Click in A30, type 0, press tab, type FL, hit enter.
    Click in A31, type 46, press tab, type GP, hit enter.
    Click in A32, type 50, press tab, type PS, hit enter.
    Click in A33, type 65, press tab, type CR, hit enter.
    Click in A34, type 75, press tab, type DN, hit enter.
    Click in A35, type 85, press tab, type HD, hit enter.
    Click in A29 again, drag across B29, and make the headings bold. Save the workbook.

  5. Now we have our lookup table. (Lookup tables are described in the textbook starting on page 152.) Click in G2, the first data cell in the Grade column, then click Insert function (looks like fx) on the formula bar, click the select a category arrow, scroll to Lookup&Reference and click. In the select a function window, scroll to the last item, VLOOKUP, click it once, read the text, then double click it. You are now looking at a Function Arguments dialogue box. Type F2, the location of the cell with the first Total in it, into the Lookup-value parameter box. Drag the mouse across the region A30 to B35: A30:B35 appears in the Table-array parameter box. This describes the "range" in which the data of the lookup table that we just created is located. We want this to be an absolute range (doesn't change when we apply the formula to F3, F4, etc.) so we need to change it to $A$30:$B$35. This can be done manually, or by pressing the F4-key (top row of keyboard). Type 2 into Col_index_num signifying that the result of the lookup comes from the second column of the lookup table, and click OK. Anna gets an HD.

  6. Now click in G2, and copy the formula down the column to G27.

  7. Select the column (from G2 to G27) and centre the grades in the column by clicking the "centre" icon in the lower row of the Alignment group of the Home tab. Save the workbook.

  8. In column H type in the heading Supp Exam . Make sure it is formatted like the other headings in the row and that the column is wide enough.

  9. We would like to display the text Yes in column H for each student that is eligable for the supplementary exam. We would like the column to remain empty for all other students. Students are eligable to do the supplementary exam if their Total mark was less than 50 and their Exam Mark was 20 or higher. Create a formula using an IF function and a nested AND function to achieve this. Type your formula into cell H2 and copy it down the column. You should end up with Yes on rows 9, 16 and 24. The rest of rows should remain empty in this column.

    If you are having trouble doing this, have a look at the final demo excel workbook posted with lecture notes for week 1. There is a similar example on the sheet named "StuRec_list" of the book except it uses "***" instead of "Yes" and different conditions.

  10. In an empty cell of your own choice, enter a formula to calculate the number of students eligable for the prac exam. To do this, use the COUNTIF function to count how many cells in Column H contain the string "Yes". You should get a result of 3.

That's the end of subtask 1a. If you are uncertain whether you have done it right, show it to your lab demonstrator now. Otherwise, go on to subtask 1b.