Calc Guide

To get an idea of what nested functions can do, imagine that you are
designing a self-directed learning module. During the module, students
do three quizzes, and enter the results in cells A1, A2, and A3. In A4,
you can create a nested formula that begins by averaging the results of
the quizzes with the formula =AVERAGE(A1:A3). The formula then uses
the IF function to give the student feedback that depends upon the
average grade on the quizzes. The entire formula would read:
=IF(AVERAGE(A1:A3) >85; "Congratulations! You are ready to
advance to the next module"; "Failed. Please review the
material again. If necessary, contact your instructor for
help")
Depending on the average, the student would receive the message for
either congratulations or failure.
Notice that the nested formula for the average does not require its own
equal sign. The one at the start of the equation is enough for both
formulas.
If you are new to spreadsheets, the best way to think of functions is as
a scripting language. We've used simple examples to explain the
concept more clearly, but, through nesting of functions, a Calc formula
can quickly become complex.
Note
Calc keeps the syntax of a formula displayed in a tool tip next
to the cell as a handy memory aid as you type.
A more reliable method is to use the Function List (Figure 135).
Available from the Insert menu, the Function List automatically docks
as a pane on the right side of the Calc editing window. If you wish, you
can Control+double-click on a blank space at the top of the pane to
undock this pane and make it a floating window.
The Function List includes a brief description of each function and its
arguments; highlight the function and look at the bottom of the pane to
see the description. If necessary, hover the cursor over the division
between the list and the description; when the cursor becomes a two-
headed arrow, drag it upwards to increase the space for the
description. Double-click on a function’s name to add it to the current
cell, together with placeholders for each of the function’s arguments.
Using the Function List is almost as fast as manual entry, and has the
advantage of not requiring that you memorize a formula that you want
to use. In theory, it should also be less error-prone. In practice, though,
some users may fumble when replacing the placeholders with values.
Another feature is the ability to display the last formulas used.
Chapter 7 Using Formulas and Functions 199