<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thought I would share this with everyone (this is from the May 07
issue)...<br>
<br>
<h1 class="title">OpenOffice.org Calc functions, part 1: Understanding
functions</h1>
<!-- begin content --> <span class="submitted">By <a
href="http://interactive.linuxjournal.com/user/800902"
title="View user profile.">Bruce Byfield</a> on Fri, 2007-03-30 19:22.</span>
<p>A
function is a pre-defined calculation entered in a cell to help you
analyze or manipulate data in a spreadsheet. All you have to do is add
the arguments, and the calculation is automatically made for you.
Beginners might be content to use Calc for lists, but, for advanced
users, functions are the main reason for spreadsheets. If you
understand functions, then you can start to use the real power of a
spreadsheet. </p>
<p>In Part 1 of this article, I'll give a brief overview of functions
and how they operate. Then, in Part 2, I'll talk about how to work with
them, and give some examples of what they can do.</p>
<p><strong><br>
Understanding functions<br>
</strong></p>
<p>Calc includes over 350 functions to help you analyze and reference
data. Many of these functions are for use with numbers, but many others
are used with dates and times, or even text. A function may be as
simple as adding two numbers together, or finding the average of a list
of numbers. Alternatively, it may be as complex as calculating the
standard deviation of a sample, or a hyperbolic tangent of a number. </p>
<p>Typically, the name of a function is an abbreviated description of
what the function does. For instance, the <code>FV</code> function
gives the future value of an investment, while <code>BIN2HEX</code>
converts a binary number to a hexadecimal number. By tradition,
functions are entered entirely in upper case letters, although Calc
will read them correctly if they are in lower or mixed case, too. </p>
<p>A few basic functions are also represented by symbols. For instance,
<code>SUM</code>, which adds arguments, can also be entered as <code>+</code>
while <code>PRODUCTION</code>, which multiplies arguments, can also be
entered as <code>*</code>.</p>
<p>Each function has a number of arguments used in the calculations.
These arguments may or may not have their own name. As user, your job
is to enter the arguments needed to run the function. In some cases,
the arguments will have pre-defined choices, and you may need to refer
to the online help to understand them. More often, however, an argument
is a value that you enter manually, or one already entered in a cell or
range of cells on the spreadsheet. In Calc, you can enter values from
other cells by typing in their name or range, or --unlike the case in
some spreadsheets -- by selecting cells with the mouse. Should the
values in the cells change, then the result of the function is
automatically updated.</p>
<p>Strictly speaking, when all the arguments are entered and a function
is ready to run, it becomes a formula. These terms are sometimes used
interchangeably, but the distinction is worth preserving, because a
formula can use functions as an argument.</p>
<p>For compatibility's sake, both functions and their arguments in Calc
have almost identical names to their counterparts in MS Excel. However,
both Excel and Calc have functions that the other lack. Occasionally,
too, functions with the same names in Calc and Excel have different
arguments, or slightly different names for the same argument -- neither
of which can be imported to the other. However, maybe nine-tenths of
functions can be imported between Calc and Excel without any problems.</p>
<p><strong><br>
Understanding the Structure of Functions<br>
</strong></p>
<p>Except for simple functions such as <code>+</code> or <code>*</code>,
all formulas have a similar structure. If you use the right tool for
entering a formula (a subject for Part 2), you can escape learning this
structure, but it is still worth knowing for troubleshooting.</p>
<p>To give a typical example, the structure of a formula to find cells
that match an entered search criteria is:</p>
<p><code><br>
= DCOUNT (Database;Database field;Search_criteria)<br>
</code></p>
<p>Like most formula, this one starts with an equal sign. It is
followed by the <code>DCOUNT</code>, the name of the function. </p>
<p>After the name of the function comes its arguments. All arguments
are required, unless specifically listed as optional. </p>
<p>Arguments are added within the brackets, and separated by
semi-colons, with no space between the arguments and the semi-colons.
Many arguments are a number. A Calc function can take up to thirty
numbers as an argument. That may not sound like much at first. However,
when you realize that the number can be not only a number or a single
cell, but also an array or range of cells that contain several or even
hundreds of cells, then the apparent limitation vanishes. </p>
<p>Other arguments may be a column label, a mathematical constant, or a
value unique to that function.</p>
<p>Depending on the function, arguments may have to be entered with
straight quotation marks. However, this requirement is not consistent.
Otherwise similar formulas may differ only in this requirement, and no
simple rule tells you which is which. You simply have to know or check
the requirements in the online help.</p>
<p>The only exception to these structural rules are basic arithmetical
functions entered with symbols. For example, instead of entering <code>=SUM(2;3)</code>,
you can enter <code>=2+3</code>.</p>
<p><strong><br>
Advanced structure<br>
</strong></p>
<p>As well as being used on its own, a function can be an argument in a
larger formula. A formula, however, is limited by the fact that it can
only do one function at a time. And that means that you need to make
sure that functions are done in the right order if the formula is going
to work.</p>
<p>To help set the order for functions in a multiple function formula,
you use parentheses within parentheses. When the formula is run, Calc
does the innermost function first, then works outwards. For example, in
the simple calculation <code>=2+(5*7)</code>, Calc multiples <code>5</code>
by <code>7</code> first. Only then is <code>2/code> added to the
result to get <code>37</code>.</code></p>
<p>The placement of functions within sets of parentheses is called
nesting. Basically, it reduces a function that could run on its own to
an argument in the formula. For example, in <code>=2+(5*7)</code>, the
formula <code>(5*7)</code> is nested within the larger formula of <code>=2+(5*7)</code>.</p>
<p>In other words, the nested function becomes an argument of another
function. </p>
<p>This relation is more obvious when doing a calculation using a
function with a name. For all purposes,</p>
<p><code><br>
=SUM(2;PRODUCT(5;7))<br>
</code></p>
<p>is the same formula as <code>=2+(5*7)</code>. However, when <code>SUM</code>
and <code>PRODUCT</code> are used, then the relation is clearer.The
fact that the <code>PRODUCT</code> function comes after a semi-colon
and in a set of parentheses for the <code>SUM</code> function makes it
clear that <code>PRODUCT</code> is an argument for <code>SUM</code>.
In addition, the fact that the inner pair of parentheses is around <code>(5;7)</code>
makes clear that this operation is done before the one defined by the
outer pair of parentheses.</p>
<p>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 <code>=AVERAGE(A1:A3)</code>. 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:</p>
<p><code><br>
=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")<br>
</code></p>
<p>Depending on the average, the student would either receive the
message for congratulations of failure.</p>
<p>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
formula.</p>
<p><strong><br>
Conclusion<br>
</strong></p>
<p>If you are new to spreadsheets, the best way to think of functions
is as a scripting language. I've used simple examples in order to
explain more clearly, but, through nesting of functions, a Calc formula
can quickly become complex.</p>
<p>For instance, a few years ago, when I was thinking of writing a book
on OpenOffice,org, Kohei Yoshida offered to me the following as an
example of what a formula can do: </p>
<p><code><br>
=6367.5*ACOS(COS(E3*2*PI()/360)*COS(E8*2*PI()/360)*COS((E9-E4)*2*PI()/360)+SIN(E3*2*PI()/360)*SIN(E8*2*PI()/360))<br>
</code></p>
<p>This is a formula for calculating the distance between two locations
in kilometers, based on their longitudes and latitudes. In the formula,
cells E3 and E4 give the longitude and latitude respectively of one
location, while cells E8 and E9 give the same information for the other.</p>
<p>If you examine this formula, you'll soon understand that the
comparison of functions to a scripting language is not much
exaggerated. That's why an expert formula writer can earn money
comparable to an experienced programmer's. </p>
<p>Of course, you may not have such elaborate purposes. But, in Part 2,
I'll talk about how to work with functions and some of the simple uses
that any user may find for them.</p>
<em><br>
Bruce Byfield is a computer journalist who writes regularly for the
Linux.com and Linux Journal web sites.</em><br>
<pre class="moz-signature" cols="72">--
Robert Wolfe (MCP/Linux+) <a class="moz-txt-link-rfc2396E" href="mailto:rwolfe@shatterit.com"><rwolfe@shatterit.com></a>
Shatter I.T. Global Services, Buffalo, NY
<a class="moz-txt-link-freetext" href="http://www.shatterit.com">http://www.shatterit.com</a>
</pre>
</body>
</html>