[nflug] OoO Artile from LinuxJournal

Wed Apr 4 05:42:28 EDT 2007

Thought I would share this with everyone (this is from the May 07 issue)...

  OpenOffice.org Calc functions, part 1: Understanding functions

By Bruce Byfield <http://interactive.linuxjournal.com/user/800902> on 
Fri, 2007-03-30 19:22.

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.

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.

*
Understanding functions
*

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.

Typically, the name of a function is an abbreviated description of what 
the function does. For instance, the |FV| function gives the future 
value of an investment, while |BIN2HEX| 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.

A few basic functions are also represented by symbols. For instance, 
|SUM|, which adds arguments, can also be entered as |+| while 
|PRODUCTION|, which multiplies arguments, can also be entered as |*|.

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.

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.

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.

*
Understanding the Structure of Functions
*

Except for simple functions such as |+| or |*|, 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.

To give a typical example, the structure of a formula to find cells that 
match an entered search criteria is:

|
= DCOUNT (Database;Database field;Search_criteria)
|

Like most formula, this one starts with an equal sign. It is followed by 
the |DCOUNT|, the name of the function.

After the name of the function comes its arguments. All arguments are 
required, unless specifically listed as optional.

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.

Other arguments may be a column label, a mathematical constant, or a 
value unique to that function.

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.

The only exception to these structural rules are basic arithmetical 
functions entered with symbols. For example, instead of entering 
|=SUM(2;3)|, you can enter |=2+3|.

*
Advanced structure
*

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.

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 |=2+(5*7)|, Calc multiples |5| by |7| first. Only 
then is |2/code> added to the result to get |37|.|

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 |=2+(5*7)|, the formula |(5*7)| 
is nested within the larger formula of |=2+(5*7)|.

In other words, the nested function becomes an argument of another 
function.

This relation is more obvious when doing a calculation using a function 
with a name. For all purposes,

|
=SUM(2;PRODUCT(5;7))
|

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 either receive the message 
for congratulations of 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 formula.

*
Conclusion
*

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.

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:

|
=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))
|

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.

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.

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.

/
Bruce Byfield is a computer journalist who writes regularly for the 
Linux.com and Linux Journal web sites./

-- 
Robert Wolfe (MCP/Linux+) <rwolfe at shatterit.com>
Shatter I.T. Global Services, Buffalo, NY
http://www.shatterit.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.nflug.org/pipermail/nflug/attachments/20070525/dce7ee6d/attachment.html
-------------- next part --------------
_______________________________________________
nflug mailing list
nflug at nflug.org
http://www.nflug.org/mailman/listinfo/nflug