# OpenOffice.org Calc functions, part 1: Understanding functions

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

is the same formula as `=2+(5*7)`

. However, when `SUM`

and `PRODUCT`

are used, then the relation is clearer.The fact that the `PRODUCT`

function comes after a semi-colon and in a set of parentheses for the `SUM`

function makes it clear that `PRODUCT`

is an argument for `SUM`

. In addition, the fact that the inner pair of parentheses is around `(5;7)`

makes clear that this operation is done before the one defined by the outer pair of parentheses.

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.

--

Bruce Byfield (nanday)

## Comments

## plagiarism

Bruce Byfield is a computer journalist > who copies word by word the OOo help files to make his articles...

see OOo Wiki (or the PDF) Calc guide, chap. 7 - Using Formulas and Functions

## kpss

Excellent article thank you

## Not rounding my way

Hello,

Great blog and info, but I still can't get this one to work:

When calculating "cost per unit" (dollar amount/# of pieces in pack as $3.99/4400)sometimes it is less than $o.oo and I want it to print in cell as $0.01 ?

I am using this formula: =IF(C83="";"";(IF(B83/C83="<0.00";"0.01";(SUM(B83/C83)))))

which works as long as the correct answer is $0.005 or greator? thats more like rounding and not my formula, I don't undertand?

In "options" or "format cell" it is set to 2 decimal places.

I have tried changing the variable for the "Less Than" and it has no effect...

It will only print out $0.01 when the calc'ed answer is $0.005 or more... (2.69/400)

help?

## This tread

Well, I just have 2 words for whoever is responsible for this tread:

wipe-out the-spam.

thanks

## fonts this is not spam! :-)

fonts

this is not spam! :-)

## One Sided

I think Blogger there has a point. There is much more to life than a formulaic expression. You just can't break it down like that.

J.L.Graham

Core Creative Group

http://www.corecreativegroup.com

## Great review

Great review

## Open Office the best office tool

Open Office the best office tool, no doubt. I even wrote some comparation with MS Office http://releasenotes.org/node/10

## We think this is an

We think this is an inspiring article.

## I choose my very sexy lades

I choose my very sexy lades and make sure their the best, just like this article.

## ok now i get it...

there have always been several ways to get to where am going using linux. mostly lucky 'shots' and several manipulation that i forget after...thanks for clearing my mind...

## Great help

Thanks, inspiring post, one day I'll be there myself.

## I can remember taking

I can remember taking Calculus in college and thinking to myself man this is never going to apply itself if the real world. I mean were not the Egyptions... were not building pyramids with our hands.

Anyhow, Thank God for formulas... because they make my life easy!

## It isnt the formulas that

It isnt the formulas that make your life easy. It is these tools that utilize the formulas and come up with 1 click applications that do it.

## I am new to OpenOffice and

I am new to OpenOffice and hope this article will be helpful.

## Understanding functions

Thank you for this awesome explanation.

Regards.

## Good article.

Good article.

## where's the confusion?

Probabaly the best explanation for the default radian mode is that it is the unit of measurement that works naturally in calculus. Using degrees when calculating a derivative requires a "chain rule" factor to compensate. Radian measure is actually a measure without units (examine the equation theta = s/r where s and r are lengths of the arc and radius, respectively), and so the rate of change with respect to theta in radians is one unit per unit, so to speak.

## I am trying to make a time

I am trying to make a time sheet that all I have to do is put in the time start and time finished with wor and have it put in the hours in another section, I have it set so that if I calculate the hours per day by hand it will give me the weekly hours I just can't get the spreadsheet to calculate the hours based on a military time format. if I put in 1900in one box then 0000 in another it will not come up with 5 for the hours. help me please

---

No man is an island, entire of itself; every man is a piece of the continent.

## Nice Article

Nice article.I never really new these programs could do so much.

## Very Interesting

I have just started using Excell alot to keep track of my business data.I am going to look at Open Office now to.There seems to be so many things you can do once you are conversant with the functions.

## Hands down one of the best programs

Open Office is hands dwon a lifesaver for any company. They still have a demo available to try it before you buy too!

## Calc Functions

I was a complete novice in regards to calc functions and spreadsheets several months ago.I think I was a bit intimidated as I was never really that great at maths.Now however I feel comfortable with most functions and I wonder how I ever did without them.

## I've always been a bit of a math weirdo

So I find this article pretty interesting. Calculators are pretty darn neat you know. Sometimes people take things for granted, I certainly do. Hm... But I suppose I'm getting a bit weird, that's how I roll though, so it's to be expected!

## Open Office

I have been thinking of giving Open Office a try now for a while and I think this article has made my mind up for me.

## Open Office

I have been thinking of giving Open Office a try now for a while now and I think this article has made my mind up for me.

## I second..

Its really nice article. I have never had idea before about its potential. Its indeed a really nice resource. Thanks

## InrecoLAN FuzzyMath

Now you can calculate with fuzzy math in OpenOffice.org Calc spreadsheets, thanks to the new InrecoLAN FuzzyMath component.

The component allows to use uncertain or approximate values in OpenOffice.org Calc. It means you can perform ordinary arithmetic operations and use ordinary mathematical and financial functions with uncertain values as if they are standard, or crisp, numbers. The component allows you to create and edit fuzzy numbers, use fuzzy numbers in formulas. You can also display the graphs from the fuzzy numbers and results of calculations.

## I just started using

I just started using OpenOffice, about 3 months back. The beginning transition was difficult, because I was so used to MS Office. The one immediate benefit I noticed was that my PC stopped becoming a heavy load and ran smoother. With MS Office, it felt like it was a memory hog. I think I will continue using it.

## Open Office

I've had openOffice recommended to me sometime back and have been dragging my feet, but reading some of the comments here, I may switch over...

## You should try it

You should really give it a try. For basic wordprocess and spread sheets its works great and beats the pants of the MS price of $200+.

## Could you tell me more

Could you tell me more advantages of using OpenOffice.org?

I have always used MS in math operations.

## Fuzzymath

The FuzzyMath component sounds excellent. I have been using OpenOffice for many years now as an alternative to MS Office and I have to admit that I have been more than impressed with it.

## Longitude and Latitude

that´s a funny formula, indeed. For me as a sailor, I enjoyed that very much and stored it in my OO-Calc. Hopefully, I can use it in practice while sailing. Thanks

Regards

## I dont get what that has to

I dont get what that has to do with this post!

Silverlight Demos

## Why?..

So... really! Why do you idiots approximate the earth as a sphere? Do you want to be terribly wrong in your estimation of distance?

## Why?..

So... really! Why do you idiots approximate the earth as a sphere? Do you want to be terribly wrong in your estimation of distance?

## =6367.5*ACOS(COS(E3*2*PI()/......

Content of this article is difficult to read... need scrolling left right...

Please put some space on the long formula

=6367.5*ACOS(COS(E3*2*PI()/......

## Yes, I am also having hard

Yes, I am also having hard time reading it while dealing with scroll bar all the time.

## function is a pre-defined calculation

Thanks for help, thats the answer of all my questions.

## Why do you idiots

Why do you idiots approximate the earth as a sphere? Do you want to be terribly wrong in your estimation of distance?

## Formula Simplification

The formula:

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

can be somewhat simplified to:

=6367.5*ACOS(COS(E3*PI()/180)*COS(E8*PI()/180)*

COS((E9-E4)*PI()/180)+SIN(E3*PI()/180)*SIN(E8*PI()/180))

But I still do not understand, why spreadsheet programs do NOT implement a version of the trigonometric functions that accepts the angle in 'degrees' as opposed to the standard 'radians' (thats why you multiply the 'degrees' by PI()/180, to get 'radians).

## Why radians?

The main reason is that the algorithms used to evaluate sin, cos, etc, can be expressed more readily in radians than in degrees, grads, etc. There are also approximations, such as the small angle assumption, that work quite nicely in radians, but not in degrees. A classic instance of this is the ODE describing a pendulum's motion in a uniform gravitational field (another approximation...). Without the small angle assumption, the ODE is non-linear, but the small angle assumption (as a approaches 0, sin(a) becomes approximately equal to a, when a is in radians).

## Degrees and Radians

OOo Calc has two functions that do the conversions for you: DEGREES and RADIANS.

The formula can be re-written as

=6367.5*ACOS(COS(RADIANS(E3))*COS(RADIANS(E8))*COS(RADIANS(E9-E4))+SIN(RADIANS(E3))*SIN(RADIANS(E8)))

Likewise a formula that gives, say, latitude and logitude can use the DEGREES function to convert the answers to a more readily recognised form.

## Interest and good news

Thanks for very interest article in this site.

## I think I know this one...

> But I still do not understand, why spreadsheet programs do NOT implement a version of the trigonometric functions that accepts the angle in 'degrees' as opposed to the standard 'radians' (thats why you multiply the 'degrees' by PI()/180, to get 'radians).

Problems usually are first converted to a so called "coherent system of units" -- that is, you convert all your measures and constants to the units in one such "coherent system" and bingo! From then on, you can work just with numbers and forget the units. The final result is guaranteed to be in a proper unit of the chosen unit system.

All the world has standardized on the "SI" (International System) of units for most scientific calculation. That system uses radians for angles. Not degrees.

But... in certain countries (actually, almost only in the US) other units have attained popular use, which creates confusion.

## Actually even sixth-grader learn in degrees

This is unfortunately not a problem of US vs SI-countries.

Back in school (not in the US), I first learned to compute the trigonometric functions using degrees and NOT radians. It was much later that I was introduced to radians. Almost every school will teach the trigonometric functions using degrees.

Actually, IF I speak of an angle of 90 degrees, many will know what this means. What IF I ask you about an angle of 1.57 radians. Can you tell me what this means. And what about 0.52 radians. Degrees are natural, whereas radians are something so abstract.

Even

Fortress(http://research.sun.com/projects/plrg/Publications/fortress1.0beta.pdf) supports using both radians and degrees (see p232, units outside the SI that are accepted).## Degrees has been a source of errors

We use both degrees and radians at our company. Degrees are typically used on drawing, blue prints, product specifications, and the like. Radians are used in more scientific areas like engineering and programming.

After years of doing both, it is clear that radians are much less prone to cause problem. This was no surprise as far as programming is concerned. But it is even true when the information is being used by humans. For this reason, we are moving toward standardizing all angular quantities in radians.

## Why is radians the default?

Probabaly the best explanation for the default radian mode is that it is the unit of measurement that works naturally in calculus. Using degrees when calculating a derivative requires a "chain rule" factor to compensate. Radian measure is actually a measure without units (examine the equation theta = s/r where s and r are lengths of the arc and radius, respectively), and so the rate of change with respect to theta in radians is one unit per unit, so to speak.....

regards to all....

## Comments

Let me cheerily admit that this discussion is way out of my league. But so long as everyone else is happy, who am I to complain?

--

Bruce Byfield (nanday)

--

Bruce Byfield (nanday)