Commonwealth Computer Navigator's Certificate

Module 4 : Spreadhseets using OpenOffice 3.0 Back to Module 3 : Word Processing using OpenOffice 3.0 Writer

Getting to know the application | Working with and in cells | Managing Worksheets | Working with Formulas and Functions | Formatting cells | Creating charts and graphs | Preparing the spreadsheet for distribution Go to Module 5 : Database Management using OpenOffice 2.0 Base

Section 4.4: Working with Formulas and Functions Section Overview We are now at the stage where we can undertake the main tasks that spreadsheets are designed to support, namely to automate numeric operations and to store the output of those operarions for later use. Such operations are needed in a range of realworld situations. For example, teachers may be required to do a simple analysis of the performance of their students in end-of-semester examinations. Alternatively, school administrators may be required to devise some basis for distributing a limited number of laptops to students and would need to generate numbers of students who meet some pre-determined criteria. Overall spreadsheets allow for the generation of quantitative data in a variety of contexts. Two of the primary tools used for this purpose are formulas and functions. Outcomes Upon completion of this tutorial the learner will: Explain the two main tools for doing calculations, namely formulas and functions. Define the realtionship between formulas and functions. Generate formulas for doing calculations using the four basic arithmetic operations. Identify and describe error values arising from inaccurate or inappropriate use of formulas. Explain cell referencing and distinguish among relative, absolute and mixed referencing. Use the three types of referencing when doing calculations with formulas. Generate formulas based on the five basic functions. Use functions to summarise data in a spreadsheet. Perform calculations based on formulas generated from the logical IF function, including the CountIF function. Perform calculations using the logical IF function that also require cell referencing. The following sub-sections are covered in this tutorial: Arithmetic formulas Error values when using formulas Cell referencing Working with functions Using the logical function IF