OpenOffice Calc Tips

The OOo Calc graphical interface is remarkably configurable - as is often the case with open source projects. For the most part, the default configuration satisfies most users needs - but if you feel you must configure the GUI to your particular needs, it is relatively straightforward to do so.

(more…)

OOo Calc has a feature that is similar in functionality to the Excel pivot table. It is known as the DataPilot. A whole book could be written on this topic. We will just introduce the concept with a simple example.

(more…)

When you have a list of items, you can filter out records that meet a particular criteria. This is a quick introduction to some of the OOo Calc data filtering capabilities.

(more…)

Sometimes it may be desirable to filter out “0″ return values from functions and formulas to avoid messy and cluttered spreadsheets.

(more…)

Here is a simple trick to exclude zero-valued cells from functions where it might be desired - for example the AVERAGE function.

(more…)

For large spreadsheets that do not fit completely in the window, it is useful to be able to freeze the row and/or column headers to better view the data.

(more…)

The RAND function returns a random number with even distribution between 0 and 1. Here are examples of how to use this function…

(more…)

The Form icon opens a floating toolbar with the tools and functions needed to create an interactive form.

(more…)

You can define ranges as names from the Insert Menu. This makes subsequent enhancements and maintenance of the worksheet easy.

(more…)

By combining the different text functions available in OOo Calc, some common name transformations can be performed.

Here are a few examples…

Using “Harry Potter in F1…

=LEFT(F1;FIND(” “;F1)-1) returns “Harry”

Note that without the -1 in the above formula, we would get a trailing space - making life difficult for us later on.

=RIGHT(F1;LEN(F1)-FIND(”*”;SUBSTITUTE(F1;” “;”*”; LEN(F1)-LEN(SUBSTITUTE(F1;” “;”"))))) returns “Potter”

I think we need to explain the above formula a little bit. First off, when trying to develop formulas of this complexity - do not attempt to write it all at once.

This is what is known as a megaformula - a miniature program - squeezed into a single formula.

When extracting the last name like this - we use the RIGHT function - but it is not much use unless we know where the last name starts. It starts immediately after the last space character. We can find that with the FIND function. Howver FIND works from the left. If we could figure out how many space characters there are - we would be set. We do that by removing the space characters and seeing how much shorter the name is.

The whole procedure is illustrated below..

As an alternative to developing these megaformulas, you could also code this in Open Basic - but for that, we will wait another day.