ENGG1811 Lab 02*: Problem Solving with Spreadsheets

* labs are numbered according to the week. There is no Lab 1.


After completing this lab, students should be able to use a range of core functionality in OpenOffice.org Calc, including formulas, relative and absolute addressing and conditional formatting. Another objective is to learn to process some data collected from a real sensor; this will involve translating a description in words to a logical formula that the computer can process.

This lab uses a single OpenOffice.org spreadsheet lab02.ods with a different sheet for each exercise. It contains macros that provide additional services, in this case self-assessment buttons, which you can also use to demonstrate your work to your tutor. If you open the workbook at home, you may notice a security warning as shown below.

Click OK, then navigate to Tools - Options (or OpenOffice.Org - Preferences on the Mac), expand OpenOffice and click on the Security category. Press the Macro Security button and the Medium radio button. Optionally select the Trusted Sources tab, press Add and find the folder where you expect to do your work this semester. Click OK all the way back and exit the application. Now you can open files in a trusted location without a warning, and files elsewhere after accepting the risk.

Copy the workbook to your workspace, open it and type your name when requested. There are only two sets of exercises this week, later labs may have more so you should get into practice by working quickly.

Marking Scheme

Each lab will be marked out of 3 marks: 1 mark from an online multiple choice question and 2 marks from your tutor's assessment of your lab work. You will need to have everything done in good time, including some before the class unless you're a quick learner.

Remember you must be ready for some assessment half an hour before the end of the class, but should attempt the online assessment near the beginning.

Note: the Assess This Sheet function has been adapted for use with Calc for the first time this year. Don't worry if it claims you've made a mistake when you think you haven't, your tutor will make the final determination.

Preliminary Work: CSE Workspace Adjustments

Each week you will have two hours of formal lab work in a scheduled class for ENGG1811. You may use the CSE labs at other times, provided there are no classes in them.

IMPORTANT: please read information available in the "Lab Guide" to understand CSE lab environment, and customise it to your needs. Your tutor will help you if you have any problem. UNSW has already provided you with a zPass to access myUNSW and other services. You use your zID and zPass to log in to your CSE account.

Exercise 1: Markbook

The sheet Ex1_Marks in the lab workbook is similar to the one in the lecture example. It has a table of marks for 43 students.

Task 1: 

Your first task is to calculate the Total marks for all the students in column G using a formula that has this effect for each student:

Total = 2*(Session Mark)*(Exam)/(Session Mark + Exam)

Task 2:

Using an IF condition, display PS in column H if a student passes the course, and FL otherwise. Please note that to pass the course, a student must score at least 50 marks in the Exam, AND her/his Total mark must also be greater than or equal to 50.

Task 3:

Using “Conditional Formatting”, highlight rows using

The conditions should occur in the order stated. If you make a mistake with the style you can't edit or remove it (a restriction due to the spreadsheet being protected), but you can create a new style and assign it instead.

Hint: First highlight the cells in column H (H5:H47) only using the Passed style. That's easy because the cell itself contains the data you're interested in, so you can use the “Cell is” condition type.

Next, try to highlight the corresponding cells in column A using the option “Formula is ”. You also need to consider whether to use relative, absolute or mixed addressing in this formula, and you must know where the active cell is at the time you enter the formula. If you have problems understanding this point, please discuss it with your tutor.

Once you can highlight cells in column A, you can complete the exercise by highlighting the full range A5:H47, and entering both formulas relative to the active cell (remember, to highlight rows the row number in the formula must be relative, but the column letter is absolute). It doesn't matter what you call the new style, but it should apply background, font and font effects changes.

Task 4:

Using COUNTIF, display a number of students who passed the course and a number of students who failed the course in cells K2 and K3 respectively.

Completed worksheet after Task 4

Task 5:

Apply AutoFilter to the above table and display the following:

Exercise 2: Counting heart beats

The worksheet Ex2_Heart contains 2 columns of data obtained from a pulse oximeter sensor. Column A contains the times (in seconds) at which the measurements were taken. Column B is the voltage (in Volts) measured at the corresponding time in Column A. For example, at Row number 16, the data were collected at time 1.4s (Cell A16) with a measured voltage of 2.52V (Cell B16).

In the lecture, we counted the number of beats by counting the number of peaks whose value exceeds a threshold. In this exercise, you will use an alternative method to determine the number of heart beats. We will describe the alternative method is Task 3. First, we do some preliminary processing in Tasks 1 and 2.

Task 1 The first step of data analysis is very often to plot the data. Plot the voltage (y-axis) versus time (x-axis). Choose a line type so that you can see the oscillatory pattern of the heart beats. After plotting the graph, you can see that number of heart beats is fairly large. You can manually count them (if you really want to) but it is easy to make a mistake. What you really want to is to get the computer to count them for you.

Task 2 We will be using a threshold voltage of 3V. We have specified this value in Cell H1. In this task, you should define a named constant called "threshold" for Cell H1. In Task 3, you should use the named constant "threshold" instead of the value of 3. This will make the context clearer.

Task 3

In this exercise, you will use:

For example, the following plot shows the voltage from 21s to 24.4s. The red circles in the plot indicates the time instances that the voltage crosses the 3V threshold and is increasing. These are upwards crossing of a threshold.

The first job that you need to do is to translate the condition "the voltage crosses the 3V threshold and is increasing" into a logical condition that the spreadsheet can use to determine where these crossings have occurred. You may recall that we did something similar in the lecture where we translated the condition "Is it a peak?" into a logical condition. Ultimately, this is an exercise in translating a condition expressed in words to a logical expression that the spreadsheet understands.

In order to determine whether an upward crossing has occurred, you will need to look at two neighbouring data points at a time. (Note: for determining a peak, we need to look at three neighbouring data points at a time.) The following picture shows two neighbouring data points where an upward crossing has occurred. 

You may want to think about situations where "the voltage crosses the 3V threshold and is increasing" does not occur. You may want to draw these situations on a piece of paper. This will help you to see what the logical condition that you need to use to determine whether a condition has occurred.

After you have derived the logical condition, implement it in the worksheet. Note that you may need to put some intermediate calculations in some columns. It is always a good practice to check what you have derived is correct. Please do so.

After you have satisfied that your logical condition is correct, determine the number of heart beats and place the answer in cell H3. The answer is between 75 and 80; your tutor knows the exact answer.

Remark: You may ask whether you could have used the downward crossings instead. Yes, you could have. We have made an arbitrary choice for this Exercise.

At the End of the Lab

You should be able to show your tutor your properly completed spreadsheet, and demonstrate how to use the following functions/features in Calc:

-- end --