OpenOffice Calc Tips

A few examples showing how data from multiple cells can be combined - as well
as how data can be combined with text and with extra formatting. A useful
function is the TEXT()( function. We will look at some examples.

(more…)

There are almost a dozen available rounding functions available in OOo Calc. We will first look at ROUND(), ROUNDUP() and ROUNDDOWN()
(more…)

Scanning the Excel boards for a formula for Easter that is easily ported to Openoffice Calc.

=FLOOR(”5/” & DAY(MINUTE(B5 / 38) / 2 + 56) & “/” & B5; 7) - 34 + 1

where B5 is the year we wish to find the date on which Easter (Sunday) falls.

This generates American date format MM/DD/YYYY and I believe it is valid to 2075 - which is long enough for me!

An introduction to the data consolidation functionality that is built in to OOo Calc. This is a useful feature when merging similar data from different souces.
(more…)

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.

(more…)

We continue our look at the application of conditional formatting in OOo Calc. Here, we wish to apply alternate coloring to even and odd rows. Prior to invoking the conditional formatting dialog, we select all cells in the sheet. This was covered in an earlier tip

The trick here is using the mod function to identify odd rows to which we apply the custom format.

The result after the conditional formatting is applied to the entire sheet.

Here is a Flash animation showing how to do this ….

See also:
Conditional Formatting II

Conditional Formatting I

Selecting an entire sheet

To illustrate the application of conditional formatting, we consider the need to highlight dates in a spreadsheet that are fast approaching or are already past.

The original spreadsheet without highlighting.

Before we apply conditional, we define any custom cell formats we may need. In this case, we have cells highlighted orange and cells highlighted yellow. We open the conditional formatting dialog and evaluate two conditions. The order is important here. In the conditional formatting dialog, once a cell evaluates TRUE, it will not be evaluated again. With that in mind, we first evaluate if a date is past due. We then evaluate if a date is fast approaching.

The modified spreadsheet with conditional formatting applied. Using this technique, the spreadsheet has become more readable and any key dates or deadlines can be more readily identified.

Conditional formatting is a mechanism that allows the user to apply formatting to
a cell or range of cells based on defined conditions. If the rules (conditions) are met, then the specified formatting is applied.
In OOo Calc, you can apply up to 3 rules in a cell or range of cells.

(more…)

An array formula I came across that lists the largest N values of an array.

In the example below, D1:D5 contains the five largest values from A1:A13

The INDIRECT function converts a string into a cell reference. With examples, I will show how useful this can be in certain applications.

(more…)