OpenOffice Calc Tips

Sometimes, it is useful to display the result of a division as the ratio of two numbers - the dividend and the divisor.
(more…)

With OOo, it is a straightforward task to generate a “data table” that is basically just multiplying a row by a column. So we have percentages running across the top row and dollar amounts down the first column, everything in between we’d like to be the product of that % and dollar amount.

Step 1: Enter the desired dollar amounts in column B and the percentage values in row 2. Enter the following formula in C3: =$B3*C$2. Pay close attention to the notation in the formula.

Often, we need to sum up a set of numbers that occupy the same position over a range of sheets. This is straightforward as shown below.

We have a sheet for each month as well as a Totals sheet. For illustration we add up the SUM of the A1 cells for each sheet from January to April with the formula =SUM(January.A1:April.A1). Note that this is slightly different from the corresponding Excel formula - =SUM(January:April!A1)

A good source of hints and tips for OOo Calc can be the numerous Excel blogs. One of the best is from Dick Kusleika.

This formula for counting the number of unique items in a range of cells was one of three suggested by Dick - but the only one that ported successfully to OOo Calc.

(more…)

Here’s a silly trick to split your string into separate characters across a range of cells.

You just need to enter the formula in C2 and then copy across the row as shown.

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.

Computing the rank order of entries in an array of data is needed in many applications.

There are a number of builtin OOo functions that deal with the ranking and ordering of data. We will look at four - RANK, PERCENTRANK, PERCENTILE, and LARGE

In the example below, we see two variants of RANK wherein the entries can be ordered in descening or ascending order. The first two arguments of RANK are the value to be ranked and the array that contains the data. By default, with these two arguments, the entries will be ranked in descending order. The optional third argument - when set to 1 - ranks the entires in ascending order.

The PERCENTRANK function assigns a percentage value to a given value based on a 100% range between the minumum and maximum values of the specified range. If you don’t format these results as a percentage value, the output from PERCENTRANK will lie betwen 0 and 1.

The PERCENTILE function accepts a percentage value as input and returns the percentile of data values in an array. A percentile returns the scale value for the array which goes from the smallest (%age=0) to the largest value (%age=1) of the array. The 50% percentile of an array is the same as the MEAN.

For OOo 2.0, enhanced functionality seems to have been introduced with respect to using array formulas for conditional summation. Consider the example below, where we wish to total numbers in a column corresponding to a particular month.

Contrast this with the use of SUMIF in a previous tip. We have also covered SUMPRODUCT before

Now, the SUM IF and SUMPRODUCT constructs shown below accept a function of an array. MONTH normally accepts a single date as a parameter - but within an array formula as in the examples below, it makes more sense to be able to apply an array of dates to MONTH. This has always been possible in Excel.

It seems, OOo Calc continues to match and/or surpass Excel in functionality - which can onloy be a good thing for those who wish to migrate from Excel.

The MROUND function allows you to round to any value you want and is particularly useful in financial/commerce applications where rounding to the nearest nickel/dime/quarter is required. This function is only available if Analysis AddIn is installed. It rounds the target value to any multiple that you specify.

(more…)

Here is another simple application of the VLOOKUP function.
Consider a sales person who gets paid commissionson sales based on a sliding scale.

For sales up to $50, a comission of 25% is paid, between $50 and $100, the commission drops to 22.5% - and so on. By using the lookup table and VLOOKUP, the correct rate of commission can be applied to each sale.