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

Basic Functions: COUNTBLANK

The COUNTBLANK function allows you to total the number of blank or empty cells in a specified range.

It is used as show n below in the example. Cells C3, B5 and D5 are empty – so the COUNTBLANK function in D7 returns 3.

You may have a situation where the array is quite large and it is important that each cell has a value. To make it easier to trap blank cells, you can combine the COUNTBLANK inside an IF function…

=if(COUNTBLANK(A1:A500)>0,”Blank Cell”,average(A1:A500))

Conditional formatting would also help you spot blank cells.


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.

Read More

Basic Functions: HLOOKUP

how the HLOOKUP could be used, we consider an example where we identify the minimum bid for a contract from a list of contractors.



The syntax for HLOOKUP is

=HLOOKUP(search_criteria;array;Index;sorted)

Search criterion is the value searched for in the first row of the array.

array is the reference, which is to comprise at least two columns.

index is the number of the column in the array that contains the value to be returned. The first column has the number 1.

sorted is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.

Below, we have a number of contractors and their corresponding bids in an array.

We first use the MIN function to place the minimum bid value in B10

Row 3 is used to flag the column with the minimum bid. The formula in
B3 is =IF(B5=$B$10; “P”;””) We use the Wingding font, which shows a flag symbol instead of the letter “P”.

The HLOOKUP function in B9 then searches for the flag returning the name of company below that flag.

Read More

Basic Functions: FREQUENCY

The following tips will show how the FREQUENCY function can be used in conjunction with the charting capabilities of OpenOffice Calc to create frequency plots of data.


In the example below, our data is in B4:D20. We wish to plot the frequency of occurence of the numbers listed in B26:B31.

The corresponding frequency tabe C26:C31 is created with the array function

{=FREQUENCY(INT(B4:D20);B26:B30)}

For some reason (OOo Calc bug?), you need to specify B26:B30 rather than B26:B31 – but it seems to work as entered here.

Read More

Checkerboard shading

A checkerboard shading effect is easily achieved with conditional formatting as shown below. The conditional formatting dialog is invoked with Format – Conditional Formatting

Please review earlier articles on conditional formatting here here and here.

When the condition (formula) evaluates to true, then the specified style is applied to the cell. We have seen this before. ISODD is a boolean function so is ideal in this situation.

The resulting pattern…

Read More

Controls : PushButton

The Form icon opens a floating toolbar with the tools and functions needed to create an interactive form.

Long-click the Form icon in order to open the floating toolbar. A short mouse click always activates the control field inserted last, which is shown as an icon.

To place a PushButton on the sheet – click the icon shown here. iconbutton.jpg
This control can be used to execute a command for a defined event, such as a mouse click.

To modify/view the properties of a selected form control select
Control The dialog shown below pops up. It has two tabs.

The General tab controls the appearance of the control.

The Events tab controls the actions associated with the control.

Read More
  • 1
  • 2