Paste Special I

In a series of short tips, we introduce the Paste Special feature.

A quick and easy way to multiply an array of numbers by a constant without using a formula…

In the example below, we wish to multiply B5:B20 by 10. Temporarily enter 10 into an empty cell. Select it and then Cut or Copy

Select the target range, then click on Edit – Paste Special . In the Paste Special dialog, unselect Paste All and Formats. Also select Multiply. Now click OK …

We are done! The modified cell range.

Read More

Creating A Series Of Workdays4

To create a series of dates in a range, with just weekdays (Monday through Fridays), you first enter your starting date in a cell (B2, in the example below), and then enter the following formula in the cell below that cell.

=IF(OR(WEEKDAY(B2+1)=1;WEEKDAY(A4+1)=7);B2+3;B2+1)

Then use the Fill Down utility to fill out your entire series of dates.

The formula to display the day of the week we have already met…

=CHOOSE(WEEKDAY(B2);”Sun”;”Mon”;”Tue”;”Wed”;”Thu”;”Fri”;”Sat”)

Read More

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.

Read More

Paste Special II – Removing formulae

The paste special feature is useful for preserving the values in a spreadsheet – but removing the formulae that generated those values. This may be required to preserve proprietary equations.

To remove the formulae for a range of cells, select and copy the desired range. Do not unselect the range. Now click on Edit – Paste Special – see below.

Make sure the Formulas box is unchecked. After you click OK, the formula is removed but the value is preserved.

Read More

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.

Read More

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

Read More

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.

Read More

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.

Read More

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.

Read More

Secret StarWars games

Open Office Calc has an interesting Easter egg – a hidden feature that might not be obvious or documented. Programmers sometimes bury Easter Eggs in their programs or web sites to add extra depth and challenge users to find them.

In an empty cell, type the following formula..=GAME(“StarWars”) This is what you will see…

After you have selected your hero, the game begins. Another screen shot…

You can only play the game once. To play again – exit OOo Calc and the quickstarter before restarting.

Read More