Excel Exercises – Introduction or revision

Most of these exercises are short. They are intended to highlight basic aspects of the use of Excel; (almost) nothing very advanced. The page numbers referred to in the ISD booklets are the page numbers at the foot of each page and NOT the page in the pdf file version.

Use of Formulae and functions (see Part 1 p23-35 and Part 2 p13-16)

Remember (a) that formulae in Excel cells start with an =, (b) to use brackets correctly, (c) the difference between relative (e.g. C6) and absolute (e.g. $C$6) referencing of cells.

1. Open the spreadsheet dept-student-numbers.xls.
   

   In the ByDept sheet enter a formula in cell B9 to calculate the total number of students.
   

   In the ByYear sheet enter a formula in cell B6 to calculate the number of students in 1990.
   

   Copy this formula into the adjacent cells (C6 through to O6).
   

1. Open bonus.xls
   
   

   Enter a formula to calculate Cruise's total salary (Basic & Bonus). Copy this into the cells below.
   

   If this doesn't work well, use absolute referencing for the reference to cell B3.
   

1. Find out about the count (COUNT, COUNTA, COUNTBLANK and COUNTIF) functions. Use them to count the number of cells in the block A3:C12 which (a) contain numbers; (b) contain anything; (c) are blank; and (d) where the salary is greater than £200.
   

1. Return to the ByYear sheet in the dept-student-numbers.xls file.
   

   Calculate the average, minimum, maximum and median student numbers for yoga and Sanskrit respectively.
   

1. Use Tools>Options>View to view the formulae, then revert to the normal view.
   

1. If you have time try Task 6 Part 2, p 16 using the spreadsheet florist.xls.
   

Use of Names (Part 2, p 18)

1. Giving a cell or group of cells a name can simply formulae and make them easier to follow. Use the spreadsheet florist-names.xls, based on florist.xls, to do the calculations with named cells and ranges of cells.
   

Paste Special (Part 2 p21-22)

1. When we copy and paste data in Excel the cell value or formula and any associated formatting are copied and pasted along with the value. Sometimes we wish to copy the cell value only without the formula and formatting, or to copy formatting without the value. These steps are achieved with the Paste Special command. Copy the data in the ByYear sheet in the spreadsheet dept-student-numbers.xls and transpose it, starting at cell A11.
   

Lists (sorting and filtering) (Part 2 p2-7)

1. Open the spreadsheet club.xls.
   

Sort the list in ascending order on the field Name.

Now re-sort the list in descending order on field Place and then by field Name.

Apply a filter to list any club member who lives in Coombe Martin.

Add to this a filter to extract club members who live in Coombe Martin
and were born before 01/01/1966. How many are there?

Remove all filters.

Subtotals (Part 2 p8-9)

1. Use the club.xls spreadsheet and sort by Place, then calculate subtotals based on the Paid to Date and Total Due fields.
   

   Use the – signs to collapse the display so that only rows containing subtotals are shown.
   

   Remove all subtotals.
   

Logical IF function (Part 2 p10)

1. The IF function is used to check if a cell's value satisfies a given criterion. It returns the value TRUE if the criterion is met and FALSE if it is not. Furthermore you can specify what happens if the result is TRUE – another logical IF test perhaps – and similarly if it's FALSE. The format of the function is =IF(logical test, do this if true, do this if false).
   

   Open the student-marks.xls
   spreadsheet. Use IF(logical-test, do this if true, do this if false) to enter "pass" in column E for marks of 50 or greater, and "fail" for marks below 50.
   

Charts (Part 2 p36-53)

1. When the data you want to plot is divided into categories, (e.g. student numbers by department, fees by course type), then a bar chart or column chart is probably what you need. However if you want to plot numerical data against each other to analyse a relationship between them a XYplot is needed. It may be tempting to use a line chart but this would be wrong. A line chart assumes that the data on the x-axis is organised in regular steps or equal increments. This is rarely the case in experimental data.
   

Choose an appropriate chart type for the following data:

• Student numbers by department on the ByDept sheet of dept-student-numbers.xls.
  
• Monthly sales for 2002 and 2003 in the monthly-sales-2002-3.xls file.
  
• Infant height dependence on age from baby-heights.xls.
  

1. Add y error bars to the baby-heights chart (see Task 16, p.51)
   

• Amend the chart to show error bars fixed at 5.5 cm above and below each data point for the Girls data.
  
• Add 6.1cm error bars for the Boys data.
  
• Amend the chart to display height values between 40 cm and 100 cm, with major units of 10 cm, and minor units of 5 cm.
  

Compound Pendulum

This is a simple exercise to get you to produce a well labelled graph of the kind you will be expected to do for your laboratory reports.

Using the data given in the spreadsheet comp_pendtask.xls, produce an XY (scatter plot) using T²h (s² m) values for the y-axis and h² (m²) values for the x-axis.

Add error bars to the plot using the data in column B for errors in h² and column D for errors in T²h (s² m).

Show a trend line (line of best fit) on the graph and display its equation.

Label the axis including the units (Note you will need to insert superscripts into the axes' labels).

When completed the plot should be similar to that shown below.