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

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

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

Auto Filling Cells

Use this procedure to automatically increment data as you fill down a column or across a row.

1.Select the cell range whose existing contents you want to extend to more cells. Then release the mouse button.

2.Move the mouse pointer on top of the automatic fill handle in the lower right of the selected cell. The mouse cursor turns into a target cross.
3.Click and hold down the left mouse button on the automatic fill handle, and drag in the desired direction of the fill, selecting all the cells you want to contain data.

Here are a few examples of how the automatic fill sequence works:
The sequence 1, 2 becomes 1, 2, 3, 4, and so on
The sequence 1, 3 becomes 1, 3, 5, 7, and so on
The cell content “Quarter 1” becomes Quarter 1, Quarter 2, Quarter 3, and so on
Automatic fill only increments data that OpenOffice.org Calc recognizes, such as numbers, dates and cell references. If Calc does not recognize data, automatic fill duplicates the data exactly. However, you can create sort lists that contain lists that Calc will recognize and automatically increment. To create a sort list, choose Tools – Options – Spreadsheet – Sort Lists

If you just want to copy the range without changing the values, hold down the Ctrl key while dragging.

Read More