OpenOffice Calc Tips

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 third solution to our linear curve fitting problem is to use the LINEST function.

LINEST is an array function - returning an array of parameters extracted from the provided X-Y data.

In the example below, we opt for a minimized set of return parameters
and we get the slope and y-intercept values as shown. C29:C31 are the original formulae we saw before. C35:D35 is the array output from LINEST. Remember to use Ctrl-Shift-Enter when applying this formula.

If array functions make you break out in sweat, you can embed LINEST inside an INDEX to extract whatever return parameter you want. This is also include in the example in C37:C38. Note that INDEX is not an array function - but it likes to work with array functions.

n a continuation from the previous tip, we look at some basic built-in functions for determining trendline coefficients of a X-Y plot.

For a series of X-Y values that we suspect have a linear relationship, we can determine the slope and y-intercept values of the linear approximation using the builtin functions SLOPE() and INTERCEPT as showb below. Compare these values to those we obtained when performig our regression analysis without using the builtin functions.

Now, plotting X-Y data is straightforward. Adding a trendline takes a little more work. We start off with the basic X-Y plot.

First, select the chart for editing by right-clicking anywhere inside the chart boundary. Now, before we add the trendline, we need to select the data series for editing. Move the cursor over one of the data points - you will see popup info about the nearest data point.

Left-click and the data series is now selected (below)

Now right-click and select Object Properties (below)

The Data Series dialog opens up. Select the Statistics tab (below). Select the Linear Regression curve and click OK

The modified X-Y plot with the newly added trend line.

As requested by Joerg in Germany, I am going to cover the topic of regression analysis over the next few days - including formulas, built-in functions and charting with trendlines.

Consider a set of data point pairs - which suggests a possible linear relationship between the two variables.

The equations below are used to calculate the slope m and y-intercept point b for a given set of data, as well as the correlation coefficient r.

Given the formula above, it is a straightforward process to extract the linear coefficients for the given set of data points.

Next, we will look at buil-in OOo Calc functions and the plotting of the trendline for this linear regression example.

Here is another application of conditional formatting. In a prior tip, we reviewed the ISERR function and how it could be used to hide error conditions in any cell in the Calc document.

We can achieve the same effect with conditional formatting as follows.

Original spreadsheet with this pesky error messages.

As before, we need to create a custom cell format prior to opening the conditional formatting dialog. We set the font color to be the same as the backgorund color and we label this style as blank

Now select the cells whose errors we wish to mask and open the Conditional Formatting dialog (below) We still use the ISERR function - this time embedded in the condition applied to the selected cells.

The final result. Whichever method you apply to mask the error message is your decision. The advantage of the conditional formatting approach is that the original cell formulas are not complicated with the ISERR calls and it is easier to turn the conditional formatting on and off.

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.

A useful quantity to know for a given loan is the total interest payable over the history of the loan - or the total interest paid to a certain point. There exists a built in OOo Calc function specifically for that purpose - CUMIPMT. A companion function - CUMPRINC calculates the total principal paid to a certain point.

The sample spreadsheet below illustrates the use of CUMIPMT and CUMPRINC

D3:D6 are the input parameters. Change any of these values to examine the effect on the results.

For this example, CUMIPMT and CUMPRINC are calculated for the last payment of the loan - and as expected, CUMPRINC equals the loan amount. Also, the total payments over the histoy of the loan is just the sum of CUMIPMT and CUMPRINC

The next example shows how CUMIPMT and CUMPRINC can be calculated at any point during the loan. The formulae in C13:F13 are

=-IPMT($D$8;B13;$D$7;$D$2)

=-PPMT($D$8;B13;$D$7;$D$2)

=-CUMIPMT($D$8;$D$7;$D$2;1;B13;0)

=-CUMPRINC($D$8;$D$7;$D$2;1;B13;0)

These formula can be dragged down to fill the remaining rows.

Note that without the “-” in front of the above formulae, you would see negative numbers - a correct reflection of the direction of flow of funds.

The above sheet can be downloaded
here (OOo 2.0 beta)

In this tip, we show how to determine the Principal and Interest components of a loan repayment using the built in OOo Calc functions IPMT and PPMT

For a standard loan or mortgage with fixed interest rate over the lifetime of the loan, a single regular payment can be calculated with the PMT function. This has been discussed in a previous tip.

However, as the amount of the outstanding balance is reduced over time, the Principal and Interest components of the loan change.

OOo Calc provides two functions that calculate the Interest and Principal components of any loan payment.

The arguments to the functions are :
IPMT(rate, per, nper, pv, fv, type)
and
PPMT(rate, per, nper, pv, fv, type) where

• rate The interest rate for each of the time periods in the nper argument.
• per The number of the payment period. The first payment is 1 and the last is nper.
• nper The number of time periods between now and the end of the loan. For a standard 30-year mortgage - with monthly payments - this is 360.
• pv The present value (or initial value) of the loan. Also known as the principal fv The future value of the loan (desired) at the end of the nper payment periods. For mortgage payment calculations, this would normally be 0.
• type Optional argument which controls whether payments are made at the start of a period or the end.

In the example below, the loan parameters are defined in C3:C5
and the loan payment in C6 is calculated as
=-PMT(C3;C4;C5)

The Principal component formula in C9 is calculated as
=-PPMT($C$3;B9;$C$4;$C$5)

and the Interest component in D9 is calculated as
=-IPMT($C$3;B9;$C$4;$C$5) For subsequent periods, the only argument to these functions that changes is the period number.

In case you need further convincing, the IPMT and PPMT components are summed in column E, and are constant throughout the life of the loan - as expected.