OpenOffice Calc Tips

« September 2004 | Main | November 2004 »

October 26, 2004

Date & Time : Calculating the end-of-month

A commonly needed entity is the date of the last day of a particular month or a range of months, usually when payments are due or when interest is calculated. We show two approaches to this.

The EOMONTH function returns the last day of the month based on the specified date and offset value. For the current month, the offset is 0 and the offset is incremented by 1 for each subsequent month - as is illustrated below.



An alternative approach is to use the DATE function. A little known feature of the DATE function is that a zero in the day argument results in the last day of the previous month being output. The example below shows how this could be implemented.

Posted by Dave at 05:16 AM | Comments (0)

October 25, 2004

Basic Functions: LARGE, SMALL

SMALL returns the kth smallest value in a range of cells, LARGE returns the kth largest value in a range of cells.

In the example below, we see how the SMALL and LARGE functions repectively retrieve values based on their ranks in the selected range of cells.

Posted by Dave at 04:41 AM | Comments (0)

October 22, 2004

Basic functions : REPLACE, SUBSTITUTE

When do we use REPLACE and when do we use SUBSTITUTE? We will answer this question with some basic examples of both functions.

With REPLACE, we specify the position and lenght of the substring to be replaced, what that substring is, we don't know.
With SUBSTITUTE, we know what we are replacing, just not sure exactly where it is in the target string.

Posted by Dave at 04:25 AM | Comments (1)

October 21, 2004

Selecting an entire sheet

To select all the cells of a sheet, click in the space above the 1 and to the left of A - see below.

Posted by Dave at 04:18 AM | Comments (0)

October 20, 2004

Webquery : Importing HTML tables

With the help of the Web Page Query OpenOffice.org Calc import filter, you can insert tables from HTML documents in a OpenOffice.org Calc spreadsheet.

Let us step through the relatively straightforward process of importing data from tables in a HTML document.

The data for this example was obtained from an excellent website with tons of population data from all over the world : citypopulation.de

Here is what the source HTML file looks like : swiss.html

For this example, we will be importing awebpage from the local disk.

The External Data dialog is activated by Insert-External Data.



Once we fill in the path to the local copy of the HTML file and the table inside this file we wish to import - we hit OK and we are done - it is that easy.

Posted by Dave at 07:14 AM | Comments (2)

October 19, 2004

Hyperlinks : Linking to a webpage

Like the other Openoffice tools, it is possible to add hyperlinks to OOo Calc. In this tutorial, we will activate the Hyperlink toolbar and link to an external website from a spreadsheet.

The Hyperlink toolbar is activated as shown below.



The Hyperlink toolbar is outlined in red in the diagram below.



Having chosen a cell to which we wish to link to an external website, we fill in two combo boxes in the Hyperlink toolbar. The URL address is taken from the entry in the Internet URL combo box on the right, and the name of the link is taken from the URL Name combo box on the left. To complete the addition of the URL hyperlink to the spreadsheet, we clicking on the Hyperlink button on the right.



Cell B3 now has a hyperlink to an external website. By clicking on B3, a browser window is opened to the specified website.

Posted by Dave at 06:29 AM | Comments (0)

October 18, 2004

Goal Seek

Goal Seek allows you to perform a special analysis on a formula wherein you specify the result in order to determine the value of an input parameter required to achieve the specified result.

As with most features, an illustrated example of Goal Seek will hopefully explain clearly how it works.

In the example below, we see a simple formula - it is for illustrative purposes only.



The Goal Seek function is invoked as shown below - after selecting the cell with the formula we wish to analyze.



The Goal Seek dialog box is opened with the Formula Cell prefilled with the selected cell. It reaminas for us to fill in the Target value and Variable cell fields. The Target value is the desired value of our formula and the Variable cell is the cell whose value we wish to adjust wit ha view to obtaining the desired value.



When we press OK to close the Goal Seek dialog box, a confirmation dialog opens to indicate that a value was found for the Variable cell that satisfied the and asking us if we wish to insert this into the Variable Cell.



Our updated spreadsheet.



Download example spreadsheet

Posted by Dave at 04:39 AM | Comments (1)

October 15, 2004

Datasources : Spreadsheets

In OOo Calc, there exists the concept of Data Sources. A data source can take many forms - including MySQL database, text file, address book and spreadsheet. We will eventually examine each of these in more detail.

When we identify a data source that we would like to use, we will need to register it as such with Open Office Calc. This short tutorial will illustrate how to do this for an OOo Calc spreadsheet.

We first invoke the Data Source Adminstration dialog window - as shown below.



In the Data Source Adminstration dialog window, we select the General tab.



At the top left. we select New Data Source. In the Name box, we enter "popdata". The Database type is a drop-down list of different options - we will choose spreadsheet. The Data source URL allows us to specify the path to our external spreadsheet data source.

Next click on the Tables tab. By default, all sheets/tables in the spreadsheet will be tagged as visible. At this time, we will make no changes. Click OK. We have completed the registration of our OOo Calc spreadsheet as a data source.



To confirm that we have indeed added a new data source, we can browse our data sources as shown below.



By clicking on Sheet1 under popdata, we can view the contents of that particular table.



To be continued...

Posted by Dave at 05:18 AM | Comments (1)

October 14, 2004

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.

Posted by Dave at 05:59 AM | Comments (1)

October 13, 2004

Data Filtering : Standard Filter

In the previous article on Data Filtering - we looked at the AutoFilter option.

We will now look at the Standard Filter.

For this example, we will be using the same data as the AutoFilter option. The Standard Filter is invoked by Data-FIlter-Standard FIlter - as is shown below.



The Standard Filter dialog box opens up. In the first simple example, we will just select the rows where Sales exceeded $9000. The Field name, Condition and Value fields are filled by selecting the desired values from the drop down lists presented. In the case of the Value field, you can also enter your own desired value in place of one of the drop down options.



Once you've filled in the required fields, hit OK for the filters to take effect - see below. Please note the row numbers indicate that we have filtered, not deleted the unwanted data - at least for the purposes of this particular analysis.

Posted by Dave at 06:53 AM | Comments (2)

October 12, 2004

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.

Name, Abr., Capital, A (km2), C 1982-03-04, C 1990-03-05, C 1999-03-08
Alsace, ALS, Strasbourg, 8280, 1566048,1624372, 1734145
Aquitaine, AQU, Bordeaux, 41308, 2656544, 2795830,2908359
Auvergne,AUV, Clermont-Ferrand, 26013, 1332678 ,1321214, 1308878
Basse-Normandie, BAS, Caen, 17589, 1350979 ,1391318, 1422193
Bourgogne, BOU, Dijon, 31582, 1596054 ,1609653, 1610067
Bretagne ,BRE, Rennes, 27208 ,2707886 ,2795638, 2906197
Centre, CEN, Orléans, 39151 ,2264164, 2371036, 2440329
Champagne - Ardenne, CHA,Châlons-sur-Marne, 25606, 1345935, 1347848, 1342363
Corse, COR, Ajaccio, 8680 ,240178, 250371, 260196
Franche-Comté, FRA, Besançon, 16202, 1084049, 1097276, 1117059
Haute-Normandie, HAU ,Rouen, 12317, 1655362, 1737247, 1780192
Île-de-France, ILEk, Paris, 12012, 10073059, 10660554, 10952011
Languedoc - Roussillon, LAN ,Montpellier, 27376 ,1926514, 2114985, 2295648
Limousin , LIM, Limoges, 16942, 737153, 722850, 710939
Lorraine, LOR, Nancy, 23547 ,2319905, 2305726, 2310376
Midi - Pyrénées, MID, Toulouse, 45348, 2325319, 2430663, 2551687
Nord - Pas-de-Calais, NOR, Lille, 12414,3932939 ,3965058, 3996588
Pays-de-la Loire, PAY, Nantes, 32082, 2930398, 3059112, 3222061
Picardie ,PIC, Amiens, 19399 ,1740321, 1810687, 1857481
Poitou - Charentes, POI, Poitiers, 25810, 1568230, 1595109, 1640068
Provence - Alpes - Côte d'Azur, PRO, Marseille, 31400, 3965209, 4257907, 4506151
Rhône - Alpes, RHO, Lyon, 43698, 5015947, 5350701, 5645607

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.

Posted by Dave at 04:38 AM | Comments (6)

October 11, 2004

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 backgorund 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.

Posted by Dave at 05:00 AM | Comments (1)

October 08, 2004

Basic Functions: SUM, AVERAGE

First time spreadsheet users tend to encounter these functions first - as they are the most frequently used and the easiest to understand. Updated. Let us look at an example featuring SUM first.

Here - we have six sales regions - and in order to detmine the total sales for each year across all regions, we use the SUM function as is illustrated below.



It should be pointed out that the range of cells to which the SUM function is applied need not necessarily be a single column or row.

OOo Calc offers an alternate way of SUMming a range of numbers. There is a SUM icon in the formula bar (below)



First, select a cell, then the SUM icon. If the selected cell is at the bottom of a column of cells, OOo Calc will make an intelligent guess of the range - which you just have to accept or override.



As an illustration of the AVERAGE function, consider 12 months of temperature data from 3 cities. The average for the full year is easily obtained for each city - as is shown below in the example.



There is also the AVERAGEA function - exactly the same as AVERAGE with the only difference being that the value of a text cell is considered to be 0. The AVERAGE function ignores text cells.

Posted by Dave at 06:35 PM | Comments (1)

October 07, 2004

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.

Posted by Dave at 05:55 AM | Comments (1)

October 06, 2004

Financial Functions 2: Accumulation

As a follow on from the mortgage example earlier, we now consider a small amount of money invested over a number of years at a fixed rate of interest.

The function we will use will be FV(rate, nper. pmt, pv)

Posted by Dave at 02:36 PM | Comments (0)

October 04, 2004

Date & Time : Calculating Dates of Holidays

This particular exercise in developing formulas for each of the major US holidays is a good way to develop expertise in OOo Calc date manipulation. US holidays follow simple guidelines for the dates on which they fall. Some holidays fall on specific dates , such as New year's day on January 1st and independence day on July 4th. Other holidays fall on specific days within the month. For example, Memorial Day is the last Monday in May and Thanksgiving Day is the fourth Thursday in November.

The OOo Calc functions that will feature prominently in this exercise are

• DATE(year; month; day) Returns formatted date corresponding to specified year, month, and day values.
• WEEKDAY(dateValue; type) Returns a decimal value corresponding to the day of the week for the input date.

Before we tackle the US holidays problem, let us become more familar with the above functions and how they are used in OOo Calc formulas.

The DATE function allows us to define and manipulate a dates components - year, month, and day - independently. We have already seen this in an earlier tip

The WEEKDAY function returns a decimal value between 1 & 7 corresponding to the day of the week for the specified date. By invoking the TEXT with the appropriate formmating, we can convert the output of the WEEKDAY function to something more meaningful. In the example below, both formats are shown. The last two formulas show one approach to determining the first day-of-week after a specified date.



The table below generates the 10 major US holidays for a specified year - in cell C2

• New Year's Day
  =DATE(C2;1;1)
• Martin Luther King Jr. DayThis is the third Monday in January.
  =DATE(C2;1;IF(2<WEEKDAY(DATE(C2;1;1));10-WEEKDAY(DATE(C2;1;1));3-WEEKDAY(DATE(C2;1;1)))+14)
• President's Day This is the third Monday in February.
  =DATE(C2;2;IF(2<WEEKDAY(DATE(C2;2;1));10-WEEKDAY(DATE(C2;2;1));3-WEEKDAY(DATE(C2;2;1)))+14)
• Memorial Day The last Monday in May, we subract 7 days from the first Monday in June.
  =DATE(C2;6;IF(2<WEEKDAY(DATE(C2;6;1));10-WEEKDAY(DATE(C2;6;1));3-WEEKDAY(DATE(C2;6;1)))-7)

I leave the rest for you as an exercise!

Posted by Dave at 04:58 AM | Comments (1)

October 01, 2004

Financial Functions 1 : Mortgage calculation

At some point, you will want to use OOo Calc to perform some sort of financial analysis. Like other topics I cover, you could write a whole book on this. As an introduction to the topic of financial functions in OOo Calc, we will look at a home mortgage example. The PMT function calculates the regular payments needed to pay off a specified loan at a specified interest rate in a specified number of payments periods. The arguments to the function are :

PMT(rate, nper, pv, fv, type) where

• rate The interest rate for each of the tune periods in the nper argument. (see below)
• nper The number of time periods between now and the end of the loan. For a standard 30-year ,ortgage - with monthy paymnets - this is 360.
• pv The present value (or initial value) of the loan. Also known as the principal fv The future value of the loan (desired) at the end of the nper payment periods. For mortgage payment calculations, this would normally be 0.
• type Optional argument which controls whether payments are made at the start of a period or the end.

In the example below, we have a 30-year mortgage, $100k to be paid off monthly at an APR of 8%.



The monthly payment on this loan turns out to be $733.76. What we would also like to know is how our principal decreases over time. Each monthly payment is split between interest and principal. Early on in the repayment schedule, a greater percentage of the payment is set aside for interest.

The formula for calculating the principal balance at the end of each period (D10:D369) is as follows.

=FV(rate; nper; pmt; pv)

In this case, we are calculating the future value of loan one time period in the future, based on the present loan value and interest rate.

Posted by Dave at 09:34 PM | Comments (2)