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.

Read More

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.

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

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.

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

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.

Read More

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

Read More

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.

Read More

Easter Egg: starcalcteam()

There is a new Spanish language OO related blog from Rafael Sanchez

He points out an interesting Easter Egg in the OpenOffice Calc program.

In any cell, type =starcalcteam()

The image below is displayed…

Read More