Counting letters in a string

March 4th, 2005

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”;”"))

Posted in Function Tips | No Comments »

Macro : Sorting sheets

March 3rd, 2005

A simple macro for sorting sheets in a spreadsheet in ascending alphabetical order.

Sub SortSheets Dim oSheets Dim oSheet Dim i As Integer Dim swap As Boolean

oSheets = ThisComponent.Sheets

If oSheets.getCount() > 1 Then ‘No sorting for one sheet Do swap = False ‘We continue bubble sort passes until no more swaps For i = 0 to oSheets.getCount()-2 If oSheets.getByIndex(i).Name > oSheets.getByIndex(i+1).Name Then oSheets.moveByName(oSheets.getByIndex(i+1).Name,i) swap = True End If Next Loop Until swap = False End If

End Sub

Basic functions : subtotal

March 2nd, 2005

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

In the example below, we have sales figures for 4 salesmen - George, Paul, Ringo and John.

After we apply the AutoFilter to display only the sales related to Paul, we use SUBTOTAL as shown to average his sales.

The SUBTOTAL function takes two arguments:

SUBTOTAL(function; range)

Function is a number that stands for one of the following functions:

1 AVERAGE 2 COUNT

3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP

If you do not wish to use AutoFilter, you can use a combination of SUMIF and COUNTIF as shown below to achieve the same results - at least as far as averaging is concerned.

Filling a Cell Range with a Series

February 24th, 2005

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.

Converting text to dates

February 22nd, 2005

You have lots of dates to enter in your spreadsheet. This can be tedious when you have to format it correctly. Here is a formula that allows to to quickly enter dates as text.

In the example below, we enter text in column B and convert to dates in column C. The conversion formula for C3 is  =DATEVALUE(LEFT(B3;2)&”/”&MID(B3;3;2)&”/”&RIGHT(B3;2))

Tomorrow, we will look at how to do this in a macro.

Posted in Date & Time | No Comments »

Basic Functions : cell

February 16th, 2005

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

Posted in Function Tips | No Comments »