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.
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, but press the Session button first and select Gnome, as this will give you the easiest environment to work with.
You should spend the first few minutes customising Gnome to suit you, especially
Your tutor will help, and more information is available in the lab guide on the class menu.
Your tutor will ask you to post a message introducing yourselves on the course forum.
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.
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)
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.
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.
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
Apply AutoFilter to the above table and display the following:
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 a different method to determine the number of heart beats. We will describe the alternative method in Task 3. First, we do some preliminary processing in Tasks 1 and 2.
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 --