Join our WikiEducator discussion group or Register now for free skills training

From WikiEducator

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