We spent a lot of time looking at some Financial and Statistical functions. I don’t propose to go into the remaining types of function in nearly the same depth. That would draw out the series without benefit to most people. But I do want to highlight some of the functions in the other categories so that you have an idea of what is possible in Calc. Remember that if you need to know more about them Google is your friend.
These functions are all very similar. They take three arguments, Database, Database field, and Search criteria. Each one has a definition, and you can get a little more on this at https://help.libreoffice.org/Calc/Database_Functions.
- Database – the cell range defining the database
- Database Field – specifies the column where the function operates on after the search criteria of the first parameter is applied and the data rows are selected. To reference a column by means of the column header name, place quotation marks around the header name.
- Search criteria – is the cell range containing search criteria. If you write several criteria in one row they are connected by AND. If you write the criteria in different rows they are connected by OR. Empty cells in the search criteria range will be ignored. Many of these functions support regular expressions as search criteria.
So in brief these functions let you do a query through the search criteria, pull the rows that correspond to that query, and then do something. The something you can do can include:
- Standard Deviation
These functions let you ask questions about the contents of cells in your spreadsheet. In general you can imagine a long column of numbers and wanting to know something about each of the numbers. So in an adjacent column you put one of Information functions, point it at the first column, and drag-to-fill the column. If the number in the first column matches what you are looking for, the function will return a value of TRUE. Here are some of the things you can test for:
- ISBLANK – Is the cell blank?
- ISERROR – Does the cell contain an error value?
- ISEVEN_ADD – Is the number even?
- ISFORMULA – Does the cell contain a formula?
- ISNA – Returns a TRUE if the value in the cell equals “#N/A”
- ISNONTEXT – Returns TRUE if the value is not text
- ISNUMBER – Returns TRUE if the value is a number
- ISODD_ADD – Is the number odd?
- ISREF – Returns TRUE if the value is a reference
- ISTEXT – Returns TRUE if the value is text
These are functions where you can apply logical tests of some kind and get back an answer. For example, maybe you want to know about any rows where the value in column A is greater than 10 and the value in column B is less than 15. Those are logical tests. The form of these functions is to specify as many of these logical tests as you like (up to 30), and return a TRUE if all tests are passed. So imagine you have two columns of numbers (A & B) and you want to apply the example test. In column C enter the function AND in cell C1, and in Logical value 1 enter the test A1>10, and in Logical value 2 enter the test B1<15. Click OK, then drag-to-fill column C. This will test all the rows that you filled. here are the Logical functions of this kind:
- AND – Returns TRUE if all arguments are TRUE
- OR – Returns TRUE if any argument is TRUE
- XOR – Returns TRUE if an odd number of arguments is TRUE
You also have some interesting functions here for If/Then calculations. These functions all in general apply a test, then allow you to specify two values, one if the test is true, the other if the test is false. These are:
- IF – For this function you need to give it the test as the first argument. For example, if the value in column A is positive, enter one thing, if it is negative enter something else.
- IFERROR – Here the test is built into the function, it is to see if the cell in the column has an error. If it does, return one value, if it doesn’t, return a different value.
- IFNA – This test also is built into the function. It checks for an #N/A error in the cell, and depending on what is there it returns one of two values.
There are tons of mathematical functions in Calc, and I will just mention a few of the most common ones.
To begin with, there are all of the Trigonometric Functions:
- Sine, cosine, tangent, secant, cotangent, cosecant
- arcsine, arccosine, arctangent, arccotangent
- hyperbolic sine, hyperbolic cosine, hyperbolic tangent, hyperbolic secant, hyperbolic cotangent, hyperbolic cosecant, etc.
And you can convert degrees to radians, and vice-versa. The Random functions we have already discussed. And then there are exponentials, rounding, sums (including the sum of a series), absolute value and many more.
These include some of the standard linear algebra manipulations, such as finding the Determinant, inverting the array, multiplying two arrays, inner products, various summing of squares functions, and transposing rows and columns of an array. There are also functions to get regression results calculated as an array.
This is a bit of a grab bag. You have the lookup functions, applying a URL hyperlink, extracting data from a pivot table, and linking via DDE to an external data source. You can also apply a style to a cell or range of cells.
This another pretty miscellaneous group of functions. I will mention some the ones that you might want to use, but you can explore this further some time if you want to do a deep dive.
- Convert Arabic numerals to Roman numerals and vice-versa (ARABIC, ROMAN)
- Convert a Unicode number into the corresponding character, or find the number for a given character (UNICHAR, UNICODE)
- Convert text to uppercase or lowercase as needed (UPPER, LOWER)
- Concatenate several text strings (CONCATENATE)
- Compare two cells and see if they have the exact same text strings (EXACT)
- Find a text string (FIND)
- Find the first or last character of a text string (LEFT, RIGHT)
Remember that you can always drag-to-fill a column and apply these to many cells at once. For example, I have had occasion to compare tow long columns of numbers to see if any in one column were missing the other, and the EXACT function proved useful.
There are a lot of functions here that would be of use to a programmer.
- Various Bessel functions (BESSELI, BESSELJ, BESSELK, BESSELY)
- Conversion between Binary, Decimal, Hexadecimal, and Octal numbers (BIN2DEC, BIN2HEX, BIN2OCT, DEC2BIN, etc.)
- Various functions for dealing with imaginary and complex numbers (COMPLEX, IMAGINARY, IMARGUMENT, IMCOS, etc.
This concludes our look at functions in Excel Calc. I hope you agree that there is a lot of power in these functions, and that to be a proficient spreadsheet user you really need to have at least a few of them in your toolkit. Which ones you use will of course depend on what your interests are. There are functions for accountants, stockbrokers, statisticians, scientists, engineers, programmers, and so on. So probably no one uses every function. You need to learn the ones that are of interest to you or are useful in the work you are doing.
Listen to the audio version of this post on Hacker Public Radio!