OpenOffice Calc Tips

To convert a time to the number of minutes past midnight, just multiply by 1440 - as shown below.

You also need to make sure the result cell is formatted as a number.

There are a number of situations where it is desirable to take advantage of the sheet/cell protectection features of OOo Calc.

Perhaps you want to prevent the values for a range of cells from being overwritten. Perhaps, you want to protect sensitive financial formulae but share the results.

In order for protection settings to be enabled for a range of cells, the document or sheet must be protected. Protecting a sheet or document prevents the format of the cells from being changed - and because cell protection settings are part of the format, we apply this first before protecting the sheet.

By default, all cells are unprotected and all formulae are visible (below)

With the desired cells selected, open the Cells Format dialog - Cell Protection tab - shown below. Select Protected to prevent changes to the contents or the format of a cell. Select Hide Formula to hide formulas.

With the correct protection settings having been applied to the selected cells, we must now protect the sheet or document. This is done as shown below…

A password is required - don’t forget it!!

This is the message that you get when attempting to modify the contents of a cell.

This is a topic that you will best learn by trying out stuff for yourself - but here I summarize the basics…

There are a number of builtin functions that make use of regular expressions. Here, we use COUNTIF to illustrate some of the possibilities.

Regular expressions are a system for matching patterns in strings. They provide a very powerful set of tools for finding particular words or combinations of characters.

Based on my investigation, the following is a summary of the allowable constructs in OOo Calc.

• Most characters match themselves. There are exceptions - see below.
  
  COUNTIF(A1:A100; “foo”) will return all the instances of the string “foo” in the specified range.

• “.” matches any single character.
  
  COUNTIF(B2:B18; “..”) counts all cells with exactly two characters.

• “*” is a special character that matches zero or more occurences of the previous expression.
  
  COUNTIF(B2:B18; “.*e”) counts all cells that end in “e”.

• “+” is a special character that matches one or more occurences of the previous single character.
  
  COUNTIF(B2:B18; “.+m.+”) includes “Tmmy” and “name”, but not “my”.

• A string of characters enclosed in square brackets ([]) matches any one character in that string.

  COUNTIF(B2:B18; “[efg].*”) counts all cells starting with e, f, or g.

From the company that seems to do everything right…

Google Analytics allows you to take a closer look at the folks that visit your website.

One of the coolest features is the ability to create a distribution map - below is the visitors to openofficetips.com in the past two days…

So - to all the folks in Catalunya, Vladivostok, Hanoi, Trondheim, Madeira, Bermuda, Costa Rica, Honolulu, Cape Town, Moscow, Fairbanks, Jakarta, Singapore, etc, etc thanks for stopping by!

By default, OOo Calc will create a chart with the y-axis starting at 0 (zero). For most purposes, this is adequate, but it is quite straightforward to shift the axis up (or down) where desired.

For the Flash version of this tip, click here.

Consider the data set below, which we wish to chart. This has been covered before elsewhere.

The default chart. In this example, we will adjust the y-axis to start at 20.

We first select the chart for editing by double-left-clicking anywhere inside the chart area. A solid border should appear around the chart as shown below.

Now enter the y-axis editor with the right mouse button as shown below.

Make sure the scale tab is selected. Before we modify the axis scaling, we first deselect the automatic settings. The fields indicated by the red arrows need to be changed.

When you have completed the desired modifications to the y-axis settings, close the dialog and deselect the chart. The final modifed chart is shown below.

Consider the simple example below - a table with various columns of data. Any formulas we create would be much more readable if we assigned names to the columns of the table.

With the full table selected, choose Insert - Names - Create to open the Create Names dialog.

The Create Names dialog gives you four options for defining the table range names. In our example, we will name each column according to the top row of the table.

With our newly defined names associated with the table, we can rewrite our formulae into more meaningful expressions.

The SUBTITUTE function replaces a sequence of characters with a different sequence of characters in a text string.

The arguments to the functions are :
SUBSTITUTE(text, old_text, new_text [,instance_num])
where

• text The text for which you want to substitute characters.
• search_text The text you want to replace.
• new_text The text you want to replace search_text with.
• occurence Optional: The occurrence number you want to replace.

This function should be used when you want to replace all the instances of some text regardless of their location in the text. If “occurence” is left blank, every occurrence of “search_text” is changed to “new_text”. The SUBSTITUTE function is very similar to the REPLACE function.

See also this tip for a clever application of the SUBSTITUTE function.