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

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

plagiarism

Anonymous's picture

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

kpss's picture

Excellent article thank you

Not rounding my way

Matthew Oakey's picture

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

ag tile's picture

Well, I just have 2 words for whoever is responsible for this tread:
wipe-out the-spam.
thanks

fonts this is not spam! :-)

fonts's picture

fonts
this is not spam! :-)

One Sided

smarterthanyouthink's picture

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

tattoo designs's picture

Great review

Open Office the best office tool

Alexei Korolev's picture

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

Home Refurbish Course's picture

We think this is an inspiring article.

I choose my very sexy lades

My XXX Ladies's picture

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

ok now i get it...

Rob H.'s picture

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

FEL3232's picture

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

I can remember taking

Big Dog's picture

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

Blogger's picture

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

Dirbuzz's picture

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

Understanding functions

pozycjonowanie stron internetowych's picture

Thank you for this awesome explanation.

Regards.

Good article.

new thumbs daily's picture

Good article.

where's the confusion?

mac millan's picture

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

John Donne's picture

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

Laptop Den's picture

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

Very Interesting

AmericanRussianLove's picture

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

havainas's picture

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

HellAngels's picture

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

Weird Biz's picture

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

PS3 Advisor's picture

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

PS3 Advisor's picture

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

Goa's picture

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

InrecoLAN FuzzyMath

free fonts's picture

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

Anonymous's picture

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

Play Arcade Games's picture

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

Enchanted Sky's picture

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

Wiesław Generalczyk's picture

Could you tell me more advantages of using OpenOffice.org?
I have always used MS in math operations.

Fuzzymath

Greece Travel's picture

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

Skipper's picture

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

Joe Actimel's picture

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

Silverlight Demos

Why?..

Anonymous's picture

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?..

Anonymous's picture

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()/......

Anonymous's picture

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

Web Directory's picture

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

function is a pre-defined calculation

skuteczne pozycjonowanie's picture

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

Why do you idiots

Anonymous's picture

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

Formula Simplification

discoleo's picture

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?

Anonymous's picture

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

Peter's picture

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

design site's picture

Thanks for very interest article in this site.

I think I know this one...

Anonymous's picture

> 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

discoleo's picture

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

David Amormino's picture

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?

Dr Bill's picture

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

Bruce Byfield's picture

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)

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState