Text manipulation 1 : Concatenation

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.

Read More

Regression analysis I : Basic linear formulas

As requested by Joerg in Germany, I am going to cover the topic of regression analysis over the next few days – including formulas, built-in functions and charting with trendlines.

Consider a set of data point pairs – which suggests a possible linear relationship between the two variables.

The equations below are used to calculate the slope m and y-intercept point b for a given set of data, as well as the correlation coefficient r.

Given the formula above, it is a straightforward process to extract the linear coefficients for the given set of data points.

Next, we will look at buil-in OOo Calc functions and the plotting of the trendline for this linear regression example.

Read More

Rounding to the nearest nickel: MROUND()

The MROUND function allows you to round to any value you want and is particularly useful in financial/commerce applications where rounding to the nearest nickel/dime/quarter is required. This function is only available if Analysis AddIn is installed. It rounds the target value to any multiple that you specify.

To round the value in A1 to the nearest nickel we use =MROUND(A1; 0.05)

Unlike the equivalent Excel function, MROUND in Calc allows negative numbers.

In fact, MROUND() is so useful – it can even be used to round the time to the nearest quarter hour. An example of this is shown below.

Read More

Regular Expressions 101

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.

Read More

Datapilot Revisited

Version 2.0 of OOo Calc will feature significant improvements to the DataPlot functionality. For this reason, we will revisit this powerful feature over the next few days.

First, the basics…

Consider the table of data below. There are numerous ways we may want to analyse this data. By using the DataPilot functionality in OOo Calc, we can perform the different analyses quite easily – with a bit of practice.

We first select the table we wish to analyze. Then Data-DataPilot-Start as is illustrated below.

We first are presented with the popup dialog below. We have already selected the data we are interested in – so we just select OK.

The next dialog box allow us to choose the layout of the DataPilot table. In our example, we are interested in knowing how each broker performed in each exchange.

Normally, at least one of the columns in our data source is numerical data that will be processed against the variables we are interested in.

Finally, we get to decide how the data is processed against the input variables. By default, the data is SUMmed, but that may not necessarily always be the case. This dialog is activated by double-clicking on the data field button.

Finally, the output DataPIlot table (or Pivot Table in Excel speak). It isplaced by OOo Calc just below the original data in this example.

Read More

Macros: A Lotto Number Generator

I have plans to implement a Keno game in the OOo basic language – but first a simple Lotto number generator. If you go into the source, you can configure to your own requirements. Generating a random number between 1 and 49 is really simple. Making sure that number has not already been picked slightly complicates matters.

Read More

Using names for cell ranges

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

First, select the range of cells to which you wish to assign a name. Then INsert->Names->Define (below)

A dialog will appear as is shown below..

You can then use the defined names to specify a range in a table as shown in this example.

If you change the Range name, the formulas will adjust automatically.

Read More

Financial Functions 3 : Complex Accumulation

In this example, we consider a more complex accumulation – wherein we are making regular payments as well as earning interest on our initial investment.

Here, we define names for the cells containing the main variables – so that the formulas are more readable.

The purpose of the month-by-month breakdown of the account also serves to confirm the accuracy of our original formula.

Read More

Introduction to the Status Bar

How many of us have paid any attention to the row of small windows at the bottom of the OpenOffice Calc window? In this article, we will explore the Status Bar in more depth.



The Status Bar displays information about the current sheet. It is shown below in the default configuration with the different fields tagged.

The fields in the default configuration are:

  • 1 : Position The number of the current sheet and the total number of sheets in the document.
  • 2 : Page Style The Page Style of the currently open sheet. You can double-click in this field to call up the Page Style Editor.
  • 3 : Zoom The current page display zoom factor.
  • 4 : Insert Mode The current insert mode. This field is active when the cursor is in the input line of a formula or cell. By clicking in this field, you can toggle between INSRT = insert and OVER = overwrite.
  • 5 : Selection Mode The current selection mode. By clicking in this field, you can toggle between STD = Standard (default), EXT = Extend and ADD = Add.
  • 6 : Modified Flag If modifications to the current document have not been saved, a * is displayed here.
  • 7 : Formula A basic evaluation of a simple formula for the currently selected range is displayed here. Default is SUM

The Status Bar can be customized by clicking Tools – Configure and selecting the Status Bar tab. The Status Bar configurtion dialog window is shown below.

Read More

Naming Multiple Cell Ranges

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.

Read More