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

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

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

Basic functions : SUMIF

This short tutorial illustrates the two basic ways that SUMIF can be used in a spreadsheet.

The SUMIF function takes two or three arguments.

For the two argument version, the condition is applied to each cell in the range being SUMmed. In the example below, C9 is the sum of all values in C3:C8 that are greater than 5.

In the three argument version, the condition is applied to a separate range of cells. This range can be numerical of textual. In the example below, the values corresponding to Tom are summed in C10

Read More

Basic functions : SUMIF

This short tutorial illustrates the two basic ways that SUMIF can be used in a spreadsheet.

The SUMIF function takes two or three arguments.

For the two argument version, the condition is applied to each cell in the range being SUMmed. In the example below, C9 is the sum of all values in C3:C8 that are greater than 5.

In the three argument version, the condition is applied to a separate range of cells. This range can be numerical of textual. In the example below, the values corresponding to Tom are summed in C10

Read More

Basic Functions: INDEX, MATCH

We have already seen a table lookup example using the OFFSET function.

Here, we perform a similar operation using INDEX and MATCH

The example below show how well INDEX and MATCH can work together.

MATCH will return the position of a particular value in an array, while INDEX returns the value of a particular position within an array. We see multiple examples of these functions in operation below.

PriceMatrix is C4:E6 Material is C3:E3 Item is B4:B6
Using these names in the formulas below makes for easier readability nad maintenance. For this reason, it is always good practice to take advantage of the ability to name arrays that will be used within formulas.

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

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

Basic Functions: FORECAST, TREND, GROWTH

In this tutorial, we will look at three related functions that can be used to predict future unknown values – based on existing data.

The FORECAST function takes a set of X-Y data pairs and returns a single Y value – given it’s corresponding X-value. The assumption is that the data is linear in behaviour.

In the example below, we have sales data for the first eight months of 2004. We want to predict (forecast) the sales figures for July 2005.

The first argument is the X value for which we need a forecast. The second and third arguments are the known Y values and known X values respectively.

The TREND function issimilar to FORECAST. A linear relationship is assumed between the X and Y data sets.

Howver, TREND is an array function and returns an array of unknown Y values.

The frst two arguments to TREND are the know X and Y data sets. The third argument is the array of X values for which we wish to predict the corresponding Y data.

As you can see from the chart below, OOo Calc ignore anomalous data as best it can in predicting future values for Y.

Like TREND, the GROWTH function returns an array of predicted values. The difference being that exponential growth is assumed. Otherwise the format is the same.

Again, by charting the known and predicted data, you can decide if OOo Calc has made a reasonable prediction based on the data provided to it.

Read More
  • 1
  • 2