OpenOffice Calc Tips

A few examples showing how data from multiple cells can be combined - as well
as how data can be combined with text and with extra formatting. A useful
function is the TEXT()( function. We will look at some examples.

(more…)

There are almost a dozen available rounding functions available in OOo Calc. We will first look at ROUND(), ROUNDUP() and ROUNDDOWN()
(more…)

Scanning the Excel boards for a formula for Easter that is easily ported to Openoffice Calc.

=FLOOR(”5/” & DAY(MINUTE(B5 / 38) / 2 + 56) & “/” & B5; 7) - 34 + 1

where B5 is the year we wish to find the date on which Easter (Sunday) falls.

This generates American date format MM/DD/YYYY and I believe it is valid to 2075 - which is long enough for me!

The following tips will show how the FREQUENCY function can be used in conjunction with the charting capabilities of OpenOffice Calc to create frequency plots of data.

(more…)

An array formula I came across that lists the largest N values of an array.

In the example below, D1:D5 contains the five largest values from A1:A13

The INDIRECT function converts a string into a cell reference. With examples, I will show how useful this can be in certain applications.

(more…)

To determine the number of occurences of a particular letter in a
string - replace it with a empty character and compare the lenghts
of the original and modified strings.

To find the number of ‘g’s in A1…

=LEN(A1)-LEN(SUBSTITUTE(A1;”g”;”"))

In SUBTOTAL function allows you to perform basic spreadsheet operations on the visible cells in AutoFilter mode.

(more…)

A basic function that retrieves information on a specified cell - in particular the formatting.

The syntax is CELL(INFO_TYPE; REFERENCE)

Info_type is the character string that specifies the type of information. The character string is always in English. Upper or lower case is optional.

Reference is the position of the cell to be examined. If Reference is a range, the cell moves to the top left of the range. If Reference is missing, OpenOffice.org Calc uses the position of the cell in which this formula is located. Microsoft Excel uses the reference of the cell in which the cursor is positioned.

The examples below show the different type of information that can be obtained for a cell

There are two methods available for concatenating text.

The ampersand symol & can be used as a concatenation operator on text - as shown below. This is analogous to the + operator for numbers.

There is also a CONCATENATE function - which can be used on up to 30 strings. The CONCATENATE function is also shown in the example below.