Calc allows you to use regular expressions (wildcards) in filters and functions. However, if you want to use wildcards in functions, this must be enabled via Tools -> Options-> OpenOffice.org Calc-> Calculate page

In List of regular expressions you can find the wildcards you can use in Calc.

When using an autofilter (data -> filter -> autofilter) you can quickly and easily make selections in a data range. However the use of common functions such as AVERAGE or COUNT will create a problem because in these functions you will need to define a range. The defined range will be averaged or counted even if certain cells are hidden by the use of a filter.

The solution (as you might have guessed from the title of this post) is the use of SUBTOTAL which will only count or average the selected cells.

You can download an example using SUBTOTAL and autofilter here: Population of countries.ods.

Also check out the SUBTOTAL article.

When you use Calc in several languages — I use the Dutch and English language version — you’re sometimes looking for a function name in an other language. You could open the file in the other language version of Calc to find the name to use, but it’s far easier to have a reference sheet.

With information from the excellent le Tableur Calc par l’exemple (French) I put the functions in seven languages (English, German, French, Dutch, Spanish, Italian, Polish) side by side in a Calc reference sheet.

Download Calc Multilingual Functions.ods

3 examples: sort order = 0 / sort order = 1 / 3. IF(ISNA())

VLOOKUP() offers the possibility to lookup criteria in an array and then return corresponding values from that array.

Syntax
=VLOOKUP(Search criterion;array;index;Sort order)

Search criterion is the value searched for in the first column of the array.

• array is the reference, which is to comprise at least two columns.
• index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
• Sort order is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.

Sort order = TRUE (1) (screenshot 1) Sort order = TRUE means that the search column needs to be sorted in order. When a search value can’t be found, the result will show the next closest result.

Sort order = FALSE (0) (screenshot 2) I usually set sort order = FALSE since I want to know when there is no exact match with the search column. When there is no exact match you will get the error message #N/A. This usually means that a row needs to be added to the array.

IF(ISNA()) (screenshot 3)The problem with using sort order = FALSE is that it gives the error message #N/A which is ugly, but — more importantly — will break other functions that reference the cell with the error message. The solution is to use the IF(ISNA()) function to look for the #N/A message to give a different result (i.e. “”) when VLOOKUP() returns an error.

Calculating subtotals in a spreadsheet is very common.

When calculating the Total value of several subtotals usually the subtotals are added with a formula similar to: =C7+C14. This works flawlessly unless you add subtotals; then you must remember to add the new subtotal cell to your ‘Total’ formula.

The more elegant solution is to use SUBTOTAL(). This formula gives you the possibility to sum several values, without summing any values calculated with SUBTOTAL(). This means you can use SUBTOTAL() to calculate subtotals and the Total value. For Total value, you simply select the entire range, including subtotals.

Syntax

SUBTOTAL(function; range)

function is a number that stands for one of the following functions:

1 - AVERAGE
2 - COUNT
3 - COUNTA
4 - MAX
5 - MIN
6 - PRODUCT
7 - STDEV
8 - STDEVP
9 - SUM
10 - VAR
11 - VARP

range is the range of included cells

Pay attention: if you hide rows or colums included in SUBTOTAL() the cells will be counted. Excel gives the option to ignore hidden rows by using function 109 instead of 9.