OpenOffice Calc Tips

In the spreadsheet below, we wish to apply a formula to the values of a particular column - C and display the results in column D. However, the column is ’sparse’ in that not all cells in the column have values.

This would be problematic if the formula returns an error when applied to a blank cell. However, by incorporating the ISBLANK function
as shown below, we can gurantee that only non-blank entries are processed.

I’m not a big user of the OpenOffice Calc toolbars - but I was curious what it would look like if I activated them all…

You can enable/disable a particular toolbar with the View - Toolbars menu option. As you can see, it can get quite crowded if you anable them all! In my next installment, I’ll introduce you to some of them.

By combining INDEX and MATCH functions as shown below, we can create a lookup table whose data can be accessed by specifying the desired row and column values.

This is probably best applied where the lookup table is of fixed size - for example - calculation of sales commisions.

For tables that change on a regular basis - thus requiring constant updates of the lookup formulae, the Base application might be a better solution. Now if only I had more time to play around with that…

Here is a similar

example

I have been wanting to do this for the longest time - ever since I became aware of this puzzle last year. So for the past week, I’ve been busy putting together this spreadsheet that will assist you with solving a Sudoku puzzle.

Anyway, three days and 900 formulae later, I present… ooo_sodoku.

This is not a push-button solver. Perhaps the next version will allow that. Basically, for each square in the solution grid, the solver will determine all the possible values - saving you a lot of the drudge work associated with these puzzles.

As you manually enter the values in the solution grid - top left, the main grid in the center will display one of three colors for each square. Light blue indicates that square already filled in solution grid. Green indicates only one possible value - which has yet to be entered in the solution grid, and brown indicates more than one solution

Your comments, encouragement and feedback are always welcome. As I said earlier, there are 900+ formulae here - so bugs are possible.

Download this utility here

For each cell, you can define in advance what type of contents is valid for that cell. This allows you to guide users through data entry in OpenOffice.org Calc by restricting cells to receive specific values and ranges.

For selected cells, a drop-down list of possible values can be defined. In this tutorial, we show how this is done.

Step1: Select the required cell and open the data validation dialog as shown below.

Step2: In the Criteria tab of the validation dialog, the Allow option is itself a drop-down list. We select List.

Step3: With List slected, a text box appears which we populate with the list of allowed values for the selected cell(s). We are almost done.

After closing the validation dialog, we see that we now must select a value for our cell from the drop-down list we have just created - as shown below. It is also acceptable to leave it blank.

This example can be downloaded here.

The WEEKDAY function returns an integer that represents the day of the week. The usefulness of this function is in identifying weekends in a series of dates.

It is straightforward to use the filldown functionality of Openoffice Calc to create a column of consecutive dates. We can use the WEEKDAY function to only include the days of the week we want - for example excluding weekends.

The arguments to the function are :

WEEKDAY(Number; Type)
where

• Number, as a date value, is a decimal for which the weekday is to be returned.
• Type determines the type of calculation. For Type =1, the weekdays are counted starting from Sunday (this is the default even when the Type parameter is missing). For Type =2, the weekdays are counted starting from Monday =1. For Type = 3, the weekdays are counted starting from Monday = 0.

In the example below, we use the WEEKDAY function to identify weekend days.

We now use the WEEKDAY function to create a column of dates that exclude the weekends. An alternative (but similar) approach is shown

here

The sheet for this example can be downloaded here

The FIND, LEFT, MID, RIGHT and LEN text functions are extremely useful and versatile. We have seen
before how to split a person’s name into it’s component parts.

Here, we take a single cell containing a US address and split it into it’s City, State and Zip components. As long as the string we want to partition is well beahved - as is the case here, splitting should be relatively straightforward with the appropriate combination of text functions.

If, for example, we did not have a comma after the city, we could use the formulae below to extract the city and state components…

Also, if you want to delete column B, you will first need to preserve the columns you have just created by doing a Paste Special (values only) as shown
here

This worksheet can be downloaded here

One of the most useful and widely used financial functions available in OpenOffice Calc is PMT. We have discussed this
before.

It’s use is illustrated below in a simple example showing how to calculate the payments on a loan with fixed interest rate over a specified repayment term.

The advantage of using a spreadsheet in this manner is that we can plug different parameters into the equation and instantly see how it affects the payment we would need to make.

I will now extend this capability by showing how to create a table of payments for a range of loan amounts and interest rates. We first set up a blank table with the desired loan amounts on the side and the interest rates along the top.

In cell G4, we enter the following formula

We have already used this technique to create a multiplication chart
here

Using the ‘hybrid’ addressing mode results in OOo Calc generating the correct results when we autofill the formulae down the first column. More information on the autofill mechanism
here.

Likewise, we can autofill the complete table by dragging the first column across with the fill handle as shown below.

The above example spreadsheet can be downloaded
here
(ODF format)

A balloon mortgage is a short loan, lasting five to seven years, but the payments are based on a term of 30 years. Balloon loans often have a lower interest rate, and can usually be easier to qualify for than the traditional 30 year mortgage. At the end of your loan term you will need to pay off your outstanding balance. This usually means a refinance, a home sale or a conversion to a traditional mortgage at the current interest rates.

In the spreadsheet below, we use the PMT and FV functions to calculate the payments and final payment for a 7 year 8% balloon loan for $100,000.

Let us now revise this worksheet with a “what if” scenario - so easy with OOo Calc. let us sweeten the payment each month and see how it impacts our final payment due.

The worksheet for this example can be downloaded here (OO0 2.0)

To convert a time to the number of minutes past midnight, just multiply by 1440 - as shown below.

You also need to make sure the result cell is formatted as a number.