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. |

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.

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.

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.

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 existing style called
**Passes**(it has a green background) for students who passed the course, and - a new style with a light yellow background and bold, dark red font for students who
*otherwise*received more than 40 in the Exam.

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.

Cell is” condition type. Next, try to highlight the corresponding cells in column A using the option “ 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:

- All and only students with grade PS
- Top 10 students (based on their Total marks)
- Students whose names begin with N, using the
**Standard Filter**tool. - The students with the three smallest Labs Total mark (Standard Filter again).

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.

- Number of heart beats = number of times the voltage crosses the 3V threshold and is increasing

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:

- Conditional Functions: IF, COUNTIF, SUMIF
- Conditional Formatting
- Translating a condition expressed in words to a logical condition understood by Calc

-- end --