LibreOffice Calc: Introduction to Functions

What is a Function?

My friend Charles in NJ who does the Mathematics series on Hacker Public Radio may explore this in more depth at some point, and it is an interesting topic to many of us, but I will offer up what the dictionary says (source is the online Merriam-Webster dictionary):

• a mathematical correspondence that assigns exactly one element of one set to each element of the same or another set
• a variable (as a quality, trait, or measurement) that depends on and varies with another ; also : result

My way of thinking about it in reference to LibreOffice Calc comes from the second of these. A function is something that takes an input from data already on the spreadsheet and returns a answer of some kind, sometimes but not always a numerical answer. There are a large number of functions available in Calc, so I don’t intend to discuss each one in depth, but I do want to give you an overview, and I will discuss some of the more common and useful functions. But first, what are the kinds of functions available here?

<h2>Calc functions</h2>

• Database – Calc can be used for a simple flat-file type of database if necessary. I prefer to use an actual database program for most of my database needs (and LibreOffice comes with one), but if your needs are simple Calc can get you by. And these functions can be used to do Database queries such as getting a count of the entries, the average, minimum, and maximum, and so on, of all the cells that match the search query.
• Date and Time – There are a lot of these functions which do things like return the number of days in the year in which a date occurs, or the number of days, months, or years between two dates.
• Financial – Calc has a lot of Financial functions, which you should expect given that spreadsheets were primarily adopted by companies to do financial management. If you ever wanted to figure out when your mortgage would be paid off, here is where you would go. There are also functions for finding the future value of an investment, depreciation over time, and various stock pricing functions.
• Information – These functions can be very useful for doing analysis of a large spreadsheet model. Many of the functions are logical functions, which means they return a value of TRUE if certain criteria are met. As examples, you can tell which cells contain formulas, which cells have errors, and so on.
• Logical – These functions allow you to do simple logical tests using operators like AND, IF, and XOR.
• Mathematical – These functions include all of the usual trigonometric functions (sine, cosine, etc.), factorials, radians, random numbers, and so on.
• Array – An Array is linked rectangle of cells, in its simplest form. Array functions let you work on the entire rectangle of cells with one function. This includes some basic matrix operations, such as inverting a matrix.
• Statistical – There is a reasonably large set of statistical functions in Calc, such as Beta distributions, Chi-square, F-test, mean, geometric mean, harmonic mean, Poisson, standard deviation and variance, and various regression functions. You could certainly teach a full semester course in Statistics using these functions.
• Spreadsheet – These functions allow you to insert hyperlink, do a lookup, apply a style, and so on.
• Text – These functions operate on cells like the contents are purely text strings. You can convert Roman numerals to Arabic, concatenate several items into one, compare two cells to see if they are the same, and even do a ROT13 on the contents.
• Add-in – These are very specialized functions that do things like let you operate on complex and imaginary numbers, use Bessel functions, and do conversions between binary, hex, octal, and decimal numbers. For anyone who does programming those conversion functions will come in handy.

Working With Functions

There are some general principles to use in working with the functions in Calc. When you select a function that you want to use in the Function Wizard window, you will see on the right a very brief description of the function, and usually in parentheses you will see the names of variables needed as inputs to the function. These are called arguments in mathematics, and that has nothing to do with people yelling at each other.  The Wikipedia definition goes:

A mathematical function has one or more arguments in the form of independent variables designated in the function’s definition, which can also contain parameters. The independent variables are mentioned in the list of arguments that the function takes, whereas the parameters are not.

When you use the function you need to enter something for each of these arguments, and it should be in the form of a cell address that contains the required information.

When you select the function, and know that you have all of the required information, click Next in the the Function Wizard. Each of the arguments will get an input field. You can type in directly, but it is usually better to click the roll-up button on the right of the field, then click a cell, and when you go back the cell address should be entered. Do this for each argument in turn, and you will see the formula being constructed in the box below. When you are done, click OK and see the result. If you want to apply the function repeatedly, like in a column, just click-and-drag to fill it. All of the cell addresses will increment normally.

Listen to the audio version of this post on Hacker Public Radio!