TRENDING
No Content Available

Text Formulas: Extracting First & Last Names

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.

Importing Tabular Data : CSV Files

If the various OOo Calc forums are an indicator of what gives Calc users the most grief, then importing data into Calc via CSV (coma separated values) files is up there with charting as one of the most error prone tasks.

Before you can perform data analysis with OOo Calc, you first need to import the data into the program. Supposedly, one of the more straightforward mechanisms for doing so is via CSV files. These are text files whose files have defined separators tab, comma, semicolon etc) that allow easy mapping from the CSV file into the cells of the spreadsheet.

In this tutorial, we will import some French population data into OOo Calc. The popdata.csv file is listed below.

From the File-Open dialog, we select File type TEXT CSV (*.csv *.txt) We then select the text CSV file we wish to import.

The Text Import dialog opens – which will give you a preview of your imported data. The default separator is comma, and fixed field width is unselected, so we do not need to change any of the settings in this window. Click OK

Now you can inspect the imported text data, making any formatting modifications that you deem necessary. Note that the width of the columns have been optimally sized by OOo Calc.

When it comes time to save your data and/or exit the OOo Calc application, you will be given the option of saving as a CSV file. If you do opt to save as a text CSV file, you will lose any formatting modifications that you made to the imported data.

Counting unique entries in a range

A good source of hints and tips for OOo Calc can be the numerous Excel blogs. One of the best is from Dick Kusleika.

This formula for counting the number of unique items in a range of cells was one of three suggested by Dick – but the only one that ported successfully to OOo Calc.

It is an array formula. Use Ctrl-Shift-Enter when entering it.

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);””); IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);””))>0;1))

where A1:A10 is the range being analyzed..

Array Formulas: Working with Matrices

An array is a linked range of cells on a spreadsheet containing values. A formula in which the individual values in a cell range are evaluated is referred to as an array formula.

Not only can an array formula process several values, but it can also return several values. The results of an array formula is also an array.

The OOoCalc functions that are introduced here are TRANSPOSE, MMULT, and MINVERSE.



As an introduction to array formulas – we introduce one of the builtin array functions – TRANSPOSE, which as the same suggests – transposes the rows and columns of the original array to the target array.

In the example below, we wish to transpose A1:D3 to B6:D9.

With B6 selected – enter =TRANSPOSE(A1:D3) – followed by Ctrl-Shift-Enter. OOo Calc takes care of the rest! Note that in the Input Window – the function is embedded within { } indicating an array function. These are automatically added by OOo Calc.

OOo Calc also has a builtin function for multiplying matrices – MMULT. The use of this function is demonstrated in the example below.

We use Insert->Names->Define to define the two product matrices as Matrix1 and Matrix2. Using defined names to define and manipulate matrices is good practice – as is highlighting the matrices with different background colors.

To calculate the inverse of a matrix, we use the MINVERSE function. This is illustrated below.

We can now apply our knowledge of matrix manipulation in OOo Calc to solve for a system of linear equations. The technique is illustrated in the example below.

Webquery: Scraping stock quotes from MSN

In a previous entry we imported data from an HTML file on the local disk. In this example, we will extract stock quotes from an MSN site and import the data into OOo Calc spreadsheet.

We will import the stock quote information from this MSN webpage. A screen shot is shown below.

For this example, we import the data into the spreadsheet as before with Insert – External Data . The URL is http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&Symbol;=INTC and the table we need to select is HTML_15.

You also need to make sure your proxy server settings are correct under Tools – Options (Internet)

Now, we can choose to have OOo Calc import this data at predefined regular intervals. This is useful – given the constantly changing nature of the stock prices. Also, each time that the user opens the document, OOo Calc will prompt to update the external links. Basically, what we have created is a ‘window’ to an external document. It can be a snapshot – or it can regularly monitor the target – updating accordingly.

Having extracted the data from the external website in it’s’raw’ form, we can use VLOOKUP to grab any piece of data we want. This is shown below.

Conditional Formatting II

To illustrate the application of conditional formatting, we consider the need to highlight dates in a spreadsheet that are fast approaching or are already past.

The original spreadsheet without highlighting.

Before we apply conditional, we define any custom cell formats we may need. In this case, we have cells highlighted orange and cells highlighted yellow. We open the conditional formatting dialog and evaluate two conditions. The order is important here. In the conditional formatting dialog, once a cell evaluates TRUE, it will not be evaluated again. With that in mind, we first evaluate if a date is past due. We then evaluate if a date is fast approaching.

The modified spreadsheet with conditional formatting applied. Using this technique, the spreadsheet has become more readable and any key dates or deadlines can be more readily identified.

Conditional Formatting: Comparing two Lists

We will use conditional formatting to identify unique entries between two lists.

In the sheet below, we have two lists of US States. We would like to identify the list entries that are unique to each list. This technique can also be applied to two versions of the same list to identify additions/removals.

Step 1: Select B2:B19 and open the Conditional Formatting dialog – below. We have already defined a formatting style that has a darker background.

In the Conditional Formatting dialog, select the “Formula is”option from the drop down list on the left and enter the formula as displayed below. Choose the predefined style. Exit the dialog.

We apply similar conditonal formatting to the D2:D19 array. I will leave that as an exercise for the reader.

The result after the conditional formatting is applied. Those cells that are shaded dark do not have a matching entry in the other list.

Regression Analysis II : Basic functions, charting

In a continuation from the previous tip, we look at some basic built-in functions for determining trendline coefficients of a X-Y plot.

For a series of X-Y values that we suspect have a linear relationship, we can determine the slope and y-intercept values of the linear approximation using the builtin functions SLOPE() and INTERCEPT as showb below. Compare these values to those we obtained when performig our regression analysis without using the builtin functions.

Now, plotting X-Y data is straightforward. Adding a trendline takes a little more work. We start off with the basic X-Y plot.

First, select the chart for editing by right-clicking anywhere inside the chart boundary. Now, before we add the trendline, we need to select the data series for editing. Move the cursor over one of the data points – you will see popup info about the nearest data point.

Left-click and the data series is now selected (below)

The Data Series dialog opens up. Select the Statistics tab (below). Select the Linear Regression curve and click OK

The modified X-Y plot with the newly added trend line.

DataPilot 101

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.

Consider the table of data below. There are numerous ways we may want to analyse this da


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.

Advanced Functions: INDIRECT

The INDIRECT function converts a string into a cell reference. With examples, I will show how useful this can be in certain applications.

First, let us look at how INDIRECT works with the help of a few examples.

D3:D6 is an array of numbers. The values in B3:B6 will be used in the different INDIRECT examples.

B10 =SUM(INDIRECT(B5)) This is equivalent to =SUM(D3:D6)

B12 =SUM(INDIRECT(“D”&B3;&”:D”&B4;)) A more elaborate example of assembling a string from different sources to be converted by INDIRECT to a range reference. B3 and B4 contain the start and end rows respectively of the array wewish to SUM

B14 =INDIRECT(ADDRESS(3;4;4)) The ADDRESS function also can be combined with INDIRECT.ADDRESS takes row and column numbers as arguments – converting them to a string – just what INDIRECT needs.

B15 =SUM(INDIRECT(“Sheet”&B6;&”.”&B5;)) An indirect reference to another sheet in the same document.

The one major difference between OOo Calc and Excel with regard to the INDIRECT function is the handling of named ranges. Excel allows you to make an indirect reference to a named range. This is not the case with OOo Calc.

My first OOo Calc addin : XNumbers

Any computer having hardware at 32-bit can perform arithmetic operations with 15 significant digits, at the most. The only way to overcome this finite fixed precision is to adopt special software that extends the accuracy of the native arithmetic

This preliminary version of Xnumbers for OOo Calc contains over 30 functions to assist In the processing of extended precision numbers.

Here is an example of one of the functions – xmult

Here, we calculate the sin, cos, and tan of an angle to 150 places…

Ultimately, Xnumbers for Calc will have 300+ functions. The existing functionality is a Solid foundation upon which the remaining functions can be developed.

The original XNumbers add-in was developed for Excel by Leonardo Volpi and the Foxes team.

New OOo Calc Addin: Data Entry Wizard

This is an addin that you may or may not find useful. It can be downloaded here

It takes some knowledge and experience with macros to create a custom form for inputting data into spreadsheet tables. This Data Entry Wizard makes it easy by automatically figuring out all the fields in your table and generating a customized dialog for you.

It also features full support for dates – which was the most difficult feature to implement.

In order for the Data Entry Wizard to work, your table must be surrounded by blank cells. Select a single cell inside the table and invoke the wizard as shown below.

I’d like to think that using the Data Entry Wizard is easy and intuitive.

  • New creates a new row at the bottom of the table.
  • Insert creates a new row at the current location.
  • Update updates the table based on the current values in the dialog.
  • Delete removes the current row from the table.
  • Next and Previous allow you to navigate throught the table

Also TAB moves you between fields.

Please send me your feedback/comments and especially requests for enhancement. This is open source so you can make your own changes if yu feel up to it.

In future versions, I’d like to add more error checking, sorting, date verification between bounds and selection lists.

Conditional Formatting: Comparing two Lists

We will use conditional formatting to identify unique entries between two lists.

In the sheet below, we have two lists of US States. We would like to identify the list entries that are unique to each list. This technique can also be applied to two versions of the same list to identify additions/removals.

Step 1: Select B2:B19 and open the Conditional Formatting dialog – below. We have already defined a formatting style that has a darker background.

In the Conditional Formatting dialog, select the “Formula is”option from the drop down list on the left and enter the formula as displayed below. Choose the predefined style. Exit the dialog.

We apply similar conditonal formatting to the D2:D19 array. I will leave that as an exercise for the reader.

The result after the conditional formatting is applied. Those cells that are shaded dark do not have a matching entry in the other list.

Charting: Modifying the axis scaling

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.

Database functions: DAVERAGE

In a prior example we saw how data filtering could be used to reduce the size of the data set before doing analysis.

The database functions perform basic data analysis – but they also have advanced data filtering built in.



The DAVERAGE function returns the average of the values in a database column satisfying a specified condition.

With the database functions – such as DAVERAGE, the criteria are defined in the spreadsheet. This is illustrated below in cells D13:E14. The criteria matrix normally has the same colums and headers as the main database table. Each row of the criteria matrix corresponds to a conditon to be applied to the database when filtering. Criteria in the same row are ANDed, while criteria in different rows are ORed

In the example below, we have configured the criteria to average rows whose age is <25 AND whose salaries are less than $50,000. These criteria are both selected in the formula of B20. For the other example formulas, we select either the age or the salary condition, but not both.

In B18, we are averaging the filtered ages, but the other example formulas are averaging the salary.

Conditional Formatting III

We continue our look at the application of conditional formatting in OOo Calc. Here, we wish to apply alternate coloring to even and odd rows. Prior to invoking the conditional formatting dialog, we select all cells in the sheet. This was covered in an earlier tip

The trick here is using the mod function to identify odd rows to which we apply the custom format.

See also:
Conditional Formatting II

Conditional Formatting I

Selecting an entire sheet

Looking up data in tables V

Here is another simple application of the VLOOKUP function.
Consider a sales person who gets paid commissionson sales based on a sliding scale.

For sales up to $50, a comission of 25% is paid, between $50 and $100, the commission drops to 22.5% – and so on. By using the lookup table and VLOOKUP, the correct rate of commission can be applied to each sale.

Conditional Formatting I

Conditional formatting is a mechanism that allows the user to apply formatting to a cell or range of cells based on defined conditions. If the rules (conditions) are met, then the specified formatting is applied. In OOo Calc, you can apply up to 3 rules in a cell or range of cells.

In the example below, we have an array of numbers. To illustrate how conditional formatting works, we will apply different formatting to those cells whose values are greater than the average of the range.

We first need to define a new formatting style.

Apply the desired formatting to an isolated cell and invoke the Styles and Formatting dialog as shown below.

Here, we click on the New Style from Selection icon.

This allows us to add our own custom style to the Style catalog.

We will now make use of our newly defined style in the Conditional Formatting dialog, which is enabled as shown below.

We select Condition 1 and the settings are shown below. The Cell STyle drop down list allows to to select any of the styles from the catalog – including any custom style we have defined.

You must use absolute addressing when referencing cells and cell ranges.

The final result of applying conditional formatting on the selected range of cells.

Data Validation: Drop down lists

For each cell, you can define in advance what type of contents is valid for that cell. This allows you to guide users through data entry in OpenOffice.org Calc by restricting cells to receive specific values and ranges.

For selected cells, a drop-down list of possible values can be defined. In this tutorial, we show how this is done.

Step1: Select the required cell and open the data validation dialog as shown below.

Step2: In the Criteria tab of the validation dialog, the Allow option is itself a drop-down list. We select List.

Step3: With List slected, a text box appears which we populate with the list of allowed values for the selected cell(s). We are almost done.

After closing the validation dialog, we see that we now must select a value for our cell from the drop-down list we have just created – as shown below. It is also acceptable to leave it blank.

Toolbar Crazy

I’m not a big user of the OpenOffice Calc toolbars – but I was curious what it would look like if I activated them all…

You can enable/disable a particular toolbar with the View – Toolbars menu option. As you can see, it can get quite crowded if you anable them all! In my next installment, I’ll introduce you to some of them.

Data Filtering : Advanced Filter

The OOo Calc Advanced Filter is similar in functionality to the AutoFilter wherein lists are filtered according to a combination of multiple criteria.

What is different is the mechanism by which we define these criteria.

Sometimes, the Autofilter does not meet the requirements due to the limited number of criteria that can be applied. With Advanced Filter, the criteria are defined in the spreadsheet. This is illustrated below in cells B2:D4 The criteria matrix normally has the same colums and headers as the main database. Each row of the criteria matrix corresponds to a conditon to be applied to the database when filtering. Criteria in the same row are ANDed, while criteria in different rows are ORed

In the example below, we have configured the criteria to select rows whose Year is 2000 AND whose sales exceeds $5,000. We have yet to apply these criteria to the database.

The Advanced Filter dialog is opened with Data-FIlter-Advanced Filter – after selecting the database cells we wish to filter and having created our criteria matrix (see above)

In the Advanced Filter dialog, we just need to identify the criteria matrix and then press OK

The filtered data is shown below.

Finally, we see an example where the same original data is filtered with a different criteria matrix. Here, with the conditions on different rows, they are ORed.

Autocorrect

When entering text in OO Calc cells, you may notice that the program makes assumptions about what you are typing and you get unwanted corrections.

These corrections are completely configurable throught the AutoCorrect dialog.

The AutoCorrect dialog is invoked by selecting Tools – AutoCorrect as shown below.

The Options tab presents some optional corrections that OOo Calc can perform on any text entered by the user. From the identifier of each option, it should be easy to understand what OOo Calc plans to do – if that option is enabled.

In particular, the user can make use of a replacement table of common mispellings. The replacement table is fully configurable via the Replace tab – shown below.For example, if “acn” is a string you use often (a name of a company perhaps) OOo Calc will always insist on replacing it with “can”. By removing this particular entry from the replacement table, OOo Calc will leave “acn” alone.

The Exceptions tab – see below – allows the user to prevent OOo Calc from correcting certain 2-letter initial cap combinations.

Finally, the Custom Quotes tab allows the user to replace single/double quotation marks with any characters of his/her choosing.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Who needs DATEDIF() ??

I recently came across a blog in Indonesian – which had useful pointers related to OpenOffice alternatives to the Excel DATEDIF() function. Here is the link: http://maseko.com/

The DATEDIF() function is a strange beast in Excel – so much so that it isn’t even implemented in OpenOffice Calc.

The syntax for DATEDIF is as follows:

=DATEDIF(Date1,Date2,Interval)

Where

Date1 is the first date, in standard Excel serial-date format.
Date2 is the second date, in standard Excel serial-date format.
Interval indicates the unit of time that =DATEDIF is to return is result.

There are sixdifferent interval codes in Excel DATEDIF() and we will cover them all with alternatives in OpenOffice Calc.

For example, to calculate someones age (in years) in Excel, you would use =DATEDIF(A1,NOW(),”Y”)

In OpenOffice CALC, we can use =YEARS(A1;NOW();0)

For calculating number of months in Excel, we use =DATEDIF( A1,NOW(),”M”)

In OpenOffice CALC, we use = MONTHS(A1;NOW();0)

For calculating number of days using DATEDIF, we use  =DATEDIF( A1,NOW(),”Y”)

In Openoffice CALC, we can use.. =INT(DAYS(now();A1))

Create Links Between Cell Ranges (Quickly)

The Paste Special feature can be used to quickly link to range of cells from another sheet.

In the example below, we have a table on the sheet labelled First. We want to link to the bottom row from the sheet labelled Second

Select the desired range for linking b9:D9, then select Edit – Copy

Now go to the sheet from where we will be linking. Select the start cell of the new range (corresponding to B9 )

Now select Edit – Paste Special.

The Paste Special dialog is shown below. Select the Links option on bottom left.

The end result. The equations are for illustration. This may seem like overkill for a few cells – but can be very useful if yu have a very large range of cells that you need mapped to another spreadsheet.

Auto Filling Cells

Use this procedure to automatically increment data as you fill down a column or across a row.

1.Select the cell range whose existing contents you want to extend to more cells. Then release the mouse button.

2.Move the mouse pointer on top of the automatic fill handle in the lower right of the selected cell. The mouse cursor turns into a target cross.
3.Click and hold down the left mouse button on the automatic fill handle, and drag in the desired direction of the fill, selecting all the cells you want to contain data.

Here are a few examples of how the automatic fill sequence works:
The sequence 1, 2 becomes 1, 2, 3, 4, and so on
The sequence 1, 3 becomes 1, 3, 5, 7, and so on
The cell content “Quarter 1” becomes Quarter 1, Quarter 2, Quarter 3, and so on
Automatic fill only increments data that OpenOffice.org Calc recognizes, such as numbers, dates and cell references. If Calc does not recognize data, automatic fill duplicates the data exactly. However, you can create sort lists that contain lists that Calc will recognize and automatically increment. To create a sort list, choose Tools – Options – Spreadsheet – Sort Lists

If you just want to copy the range without changing the values, hold down the Ctrl key while dragging.