Charting: Editing charts : part 1

In this series of tutorial, we will take a newly created chart and make use of the various chart editing tools provided by OOo Calc to adjust the appearance of the chart to our satisfaction.

In the example below, the chart has just been created from the data in B2:C11 However, we would like to make some changes – making the chart background transparent, adjusting the orientation of the x-axis labels, changing the format of the y-axis numbers and correcting the label on the right hand side.

The first task is the chart background. We would like to make it transparent. We first select the chart by double (left) clicking inside it’s borders. Then we invoke the Chart Area dialog by (right) clicking and selecting Chart Area as shown below.

With the Transparency tab selected, we make the desired change as shown below. Click OK

The end result is much more pleasing to the eye. But we are not finished yet with our chart modifications. Next, we will adjust the orientation of the x-axis labels.

Read More

Macros: Getting Cell Information

Invariably, macros written for use within the Calc application will need to access the contents of the cells on a spreadsheet. This tip is an introduction to the various available methods.

The three methods we will look at are getCellByPositiongetCellRangeByPosition and getCellRangeByName

The function that is first encountered for most people is getCellByPosition. In the sample below, we access cell A1 (on Sheet1)


Sub getCellInfo
‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Get value of Cell A1
A1_value = xSheet.getCellByPosition(0,0).value

print A1_value

End Sub

The second example shows the use of getCellRangeByName and may be easier to use – because the cells are referenced by the traditional column/row identifiers that are displayed along each axis. However, for applications requiring looping through an array of cells, getCellByPosition is easier to use.

Sub getCellInfo
‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Get value of Cell A3
A3_value = xSheet.getCellRangeByName(“A3”).value

print A3_value

End Sub


The next example shows how getCellInfo grabs an array of cells – myTable. A subsequent call to getCellByPosition for the myTable object is relative to the origin of this array.

Sub getCellInfoByRange
Dim myTable as Object

‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Grab array A3:A5
myTable = xSheet.getCellRangeByName(“A3:A5”)
A5_value = myTable.getCellByPosition(0,2).value
print A5_value

End Sub

The final method that needs discussion is getCellRangeByPosition and the example below illustrates it’s use. It is equivalent in functionality to the previous example.

Sub getCellInfoByRange
Dim myTable as Object

‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Grab array A3:A5
myTable = xSheet.getCellRangeByPosition(0,2,0,4)
A5_value = myTable.getCellByPosition(0,2).value
print A5_value

End Sub

Read More

Copying Formulas while preserving references

When you cut and paste formulas from a range of cells, the cell references within the formulas will be automatically adjusted. Here, we show how to work aroud this ‘feature’.

For the purposes of illustration – we consider the simple example below – with the four formulae in B7:E7

WIth B7:E7 selected, we select Edit – Find & Replace We will be replacing the “=” characters with “#” in each formula. Now, we have a range of text values that will be unchanged in a cut/paste or copy/pastee operation.

We can now copy/paste on the range of text values.

The last step is to use Find & Replace to restore the original formulae by replacing “#” with “=”, reversing the previous Find & Replace operation.

Read More

Filling a Cell Range with a Series

A quick way to fill a range of cells with a series of values that increase/decrease in a geometric fashion.

Let us consider the simple example below. We wish to complete the series of values atrting at B3 (shaded).

The Fill Series dialog is invoked with Edit – Fill – Series as shown below.

OOo Calc makes it’s best guess regarding the continuation of the series.

In this example, we will elave everything at the default settings and close the dialog with OK

The completed series! Next,we will look at handling dates.

Read More

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

Paste Special III: Merging rows

It has been decided to merge the Mungits and Wingdings product lines. We will use Paste Special to merge the sales totals in the spreadsheet below.

Step 1: Select C6:F6 and Edit – Copy

Step 2: Select C5:F5 and Edit – Paste Special

The Paste Special dialog opens. In this situation, we apply the ADD operator to the paste operation which has the effect of adding the pasted data to the existing contents of the selected cells.

Having merged the data, we can safely delete the Wingdings row in the table.

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

Basic functions: TEXT

At first glance, the TEXT function may not sound very useful. Here are a few simple applications.

In the top example, without the TEXT function, the total is not formatted correctlt (as a currency). However, the TEXT function allows us to apply the correct format to the number component of the string.

In the second example, we see how the TEXT function allows us to reformat existing data – such as dates.

Read More
  • 1
  • 2