Contents

Key terms
The power and functions of spreadsheets
Uses of spreadsheets
Beyond spreadsheets
Spreadsheet AssignmentsIntroduction to Databases
Databases and Privacy
Database Practical
Solutions to Privacy Issues


Key Terms

Open the file below and correct it by matching the correct term with the appropriate definition.


Absolute and Relative Cell References - Important

Many students struggle to understand the difference between absolute and relative cell references. The guide below distinguishes between the two very well:




Activity 1 - Beginning to understand the power and function of a spreadsheet


Use the following worksheet and complete the activities below:


1. Make columns A and B text format columns

2. Two students have scored half marks for a question for the latitude and longitude test. Format the cells in this column so that all students tests can be shown to one decimal place.

3. Calculate the average score for the class in both the latitude and longitude test and the distance and direction test.

4. Shade the cells that refer to the Term 1 mark, Term 1 Grade and Term 1 effort another colour, so that they stand out.

5. Add in a column next to the student names that has the date of their enrolment (make the dates up) and apply the correct formatting

6. Create a template for a class grade book with Student first name, Student surname, Date of enrolment, Test 1, Test 2, Test 3 and Test 4, Term Mark, Term Grade and Term Effort as column labels. Set up a formula in the Term Mark column, so that each Test is worth 25% of the Term Mark and when added together give a score out of 100.

7. Now enter four students with enrolment dates, give them fictional test scores.

8. In Column I (Term Grade), In the cell for student number 1, enter the following formula =IF(I2<=100,"A"). Note what happens. Fill down for the other students. See if you can write the remainder of the formula to ensure that A's are given to students with 85% or above, B's to students with 70-84%, C's to students with 55-69%, D's to students with 40-54% and E grades to students who score below 40%.
*Note cell I2 works only when the first student is entered in the second row of the spreadsheet.

9. Now add a column and label it "Pass / Fail". Use an IF statement to determine students who pass or fail. Students with 50% and over should be assigned a pass.

10. Create a bar graph to show the individual test scores for each student. Make sure that there are no gaps between the bars on your graph, give the graph a scale between 0 and 100% and add the correct labels. Make your graph become an individual sheet in your workbook.

11. Label the two tabs on the workbook: - Term 1 results, Term 1 results graph.

12. Create a pictograph to show how the students compare in their Term 1 mark. Label this worksheet tab in the workbook.

14. . Label a new column "Certificate". Use the IF command to assign certificates to students who have Term marks over 90%.

15. You now have the overall results for Term 1; the mark, the grade and who has passed or failed. Hide the Test scores in your worksheet. Perhaps think why you may wish to do this. Now go back and unhide the columns.

16. Sort the students into alphabetical order without displacing the grades assigned to each individual student by using the sort function.

17. Rather than have the first name and surname in different column you wish to join them. Use the CONCATENATE function to do so. Replace columns A and B with the new concatenated first name / surname column.

18. One of the students did poorly in a test and you want to add notes as to why this happened. Use the comment command to add the details why this particular student performed so poorly in this specific instance. SAVE your file.

20. Your manager would like to use the grade book that you have set up but would like to view it with the tasks down the side, as rows, and the students along the top as columns. Select the all the data, copy it, then use the paste special TRANSPOSE feature.

How the grade book might look when completed:

What spreadsheets can be used for


Homework

Draw a mind map to show the various uses of spreadsheets and include their functionality and people who would use them.

Student Calendar

Class Attendance Roll

Monthly Calendar

Yearly Calendar

Travel Expense Report

Family Budget Planner

Employee Timesheet

Personal Monthly Budget

Loan Calculator

Wedding Checklist

Shift Schedule

Fuel Mileage Log Book





Activity 2 - understanding the power and function of a spreadsheet in a business setting


Complete the assignment below using the associated files:




Practice Examination Questions





Beyond Spreadsheets

While spreadsheets are extremely versatile in what they do, they are not necessarily perfect for every task that you may wish to carry out. As a consequence there is a range of financial software, organisational software, scientific visualisation software and mathematical and statistical software in the marketplace to cater for those users who wish to have a high-end specific product.


Assignment Task 1
Describe some software packages that deal with finance, organisation, scientific visualisation and mathematical / statistics in a better way than spreadsheets. In essence, detail some of the features that these programs have that make them far superior to a spreadsheet program such as Microsoft Excel.

Use Computer Confluence, Pages 204-206 to begin this task.

Introduction to Databases




Databases and Privacy and Security of Information

The use of databases prompts many social and ethical issues relating to reliability of the data, privacy and anonymity of the information contained in databases, security of the data, policies and standards that govern the use of data and also people and machines, as to whether databases are actually needed and are appropriate.

The following ethical analyses prompt important discussion about using databases. Each student of the class has been assigned an ethical issues assignment with the exception of Jacob who may choose any of the four!
Angus
Andrew
Alistair
Jeremy

Australia has ten National Privacy Principles (NPP's), which relate to the Privacy Act (1988), subsequently amended in 1990 and 2000.
National Privacy Principles

More about Privacy - Google Street View

Google's 'Street View' has stirred up a lot of controversy since it's launch here in Australia last June. Some claim it is an invasion of privacy:
All Seeing Google Street View Prompts Privacy Fears
Google Zooms In Too Close For Some

There are some very interesting things that you can see on Google Street View:
10 Bizarre Sights in Google Street View


"Big Brother is Watching You"


Is it really an invasion of privacy? Listen to what this podcast has to say:



Database Practical


Complete the database practical activity below using the associated files.


Here's the solution to the Adelaide Climate Database Question:


Solutions to Issues of Loss of Privacy and Databases