## DataPilot 101

OOo Calc has a feature that is similar in functionality to the Excel pivot table. It is known as the DataPilot. A whole book could be written on this topic. We will just introduce the concept with a simple example.

Consider the table of data below. There are numerous ways we may want to analyse this da

We first select the table we wish to analyze. Then Data-DataPilot-Start as is illustrated below.

We first are presented with the popup dialog below. We have already selected the data we are interested in – so we just select OK.

The next dialog box allow us to choose the layout of the DataPilot table. In our example, we are interested in knowing how each broker performed in each exchange.

Normally, at least one of the columns in our data source is numerical data that will be processed against the variables we are interested in.

Finally, we get to decide how the data is processed against the input variables. By default, the data is SUMmed, but that may not necessarily always be the case. This dialog is activated by double-clicking on the data field button.

Finally, the output DataPIlot table (or Pivot Table in Excel speak). It isplaced by OOo Calc just below the original data in this example.

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

First, let us look at how INDIRECT works with the help of a few examples.

D3:D6 is an array of numbers. The values in B3:B6 will be used in the different INDIRECT examples.

B10 =SUM(INDIRECT(B5)) This is equivalent to =SUM(D3:D6)

B12 =SUM(INDIRECT(“D”&B3;&”:D”&B4;)) A more elaborate example of assembling a string from different sources to be converted by INDIRECT to a range reference. B3 and B4 contain the start and end rows respectively of the array wewish to SUM

B14 =INDIRECT(ADDRESS(3;4;4)) The ADDRESS function also can be combined with INDIRECT.ADDRESS takes row and column numbers as arguments – converting them to a string – just what INDIRECT needs.

B15 =SUM(INDIRECT(“Sheet”&B6;&”.”&B5;)) An indirect reference to another sheet in the same document.

The one major difference between OOo Calc and Excel with regard to the INDIRECT function is the handling of named ranges. Excel allows you to make an indirect reference to a named range. This is not the case with OOo Calc.

## My first OOo Calc addin : XNumbers

Any computer having hardware at 32-bit can perform arithmetic operations with 15 significant digits, at the most. The only way to overcome this finite fixed precision is to adopt special software that extends the accuracy of the native arithmetic

This preliminary version of Xnumbers for OOo Calc contains over 30 functions to assist In the processing of extended precision numbers.

Here is an example of one of the functions – xmult

Here, we calculate the sin, cos, and tan of an angle to 150 places…

Ultimately, Xnumbers for Calc will have 300+ functions. The existing functionality is a Solid foundation upon which the remaining functions can be developed.

The original XNumbers add-in was developed for Excel by Leonardo Volpi and the Foxes team.

## New OOo Calc Addin: Data Entry Wizard

This is an addin that you may or may not find useful. It can be downloaded here

It takes some knowledge and experience with macros to create a custom form for inputting data into spreadsheet tables. This Data Entry Wizard makes it easy by automatically figuring out all the fields in your table and generating a customized dialog for you.

It also features full support for dates – which was the most difficult feature to implement.

In order for the Data Entry Wizard to work, your table must be surrounded by blank cells. Select a single cell inside the table and invoke the wizard as shown below.

I’d like to think that using the Data Entry Wizard is easy and intuitive.

• New creates a new row at the bottom of the table.
• Insert creates a new row at the current location.
• Update updates the table based on the current values in the dialog.
• Delete removes the current row from the table.
• Next and Previous allow you to navigate throught the table

Also TAB moves you between fields.

Please send me your feedback/comments and especially requests for enhancement. This is open source so you can make your own changes if yu feel up to it.

In future versions, I’d like to add more error checking, sorting, date verification between bounds and selection lists.

## Conditional Formatting: Comparing two Lists

We will use conditional formatting to identify unique entries between two lists.

In the sheet below, we have two lists of US States. We would like to identify the list entries that are unique to each list. This technique can also be applied to two versions of the same list to identify additions/removals.

Step 1: Select B2:B19 and open the Conditional Formatting dialog – below. We have already defined a formatting style that has a darker background.

In the Conditional Formatting dialog, select the “Formula is”option from the drop down list on the left and enter the formula as displayed below. Choose the predefined style. Exit the dialog.

We apply similar conditonal formatting to the D2:D19 array. I will leave that as an exercise for the reader.

The result after the conditional formatting is applied. Those cells that are shaded dark do not have a matching entry in the other list.

## Converting time to minutes past midnight

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.

## Creating A Series Of Workdays

To create a series of dates in a range, with just weekdays (Monday through Fridays), you first enter your starting date in a cell (B2, in the example below), and then enter the following formula in the cell below that cell.

=IF(OR(WEEKDAY(B2+1)=1;WEEKDAY(A4+1)=7);B2+3;B2+1)

Then use the Fill Down utility to fill out your entire series of dates.

The formula to display the day of the week we have already met…

=CHOOSE(WEEKDAY(B2);”Sun”;”Mon”;”Tue”;”Wed”;”Thu”;”Fri”;”Sat”)

## Easter formula

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!

### Converting text to dates

You have lots of dates to enter in your spreadsheet. This can be tedious when you have to format it correctly. Here is a formula that allows to to quickly enter dates as text.

In the example below, we enter text in column B and convert to dates in column C. The conversion formula for C3 is
=DATEVALUE(LEFT(B3;2)&”/”&MID;(B3;3;2)&”/”&RIGHT;(B3;2))

## Custom Time Formatting for a timesheet

In the timesheet example below, to represent the total hours worked for the week, we use a custom time format [H]:MM

Cells E3:E7 are also formatted similarly. If we had used HH:MM, E3 for example would display 07:15 – which is not quite what we are looking for.

Cells C3:D7 are formatted HH:MM AM/PM

## Determining whether a year is a Leap Year

This tip explains how to determine whether the year in a date used in a OpenOffice Calc spreadsheet is a leap year.

In the Gregorian calendar, a normal year consists of 365 days. Because the actual length of a sidereal year (the time required for the Earth to revolve once about the Sun) is actually 365.25635 days, a “leap year” of 366 days is used once every four years to eliminate the error caused by three normal (but short) years. Any year that is evenly divisible by 4 is a leap year: for example, 1988, 1992, and 1996 are leap years.

However, there is still a small error that must be accounted for. To eliminate this error, the Gregorian calendar stipulates that a year that is evenly divisible by 100 (for example, 1900) is a leap year only if it is also evenly divisible by 400.

For this reason, the following years ARE NOT leap years 1700, 1800, 1900, 2100, 2200, 2300, 2500, 2600 because they are evenly divisible by 100 but NOT by 400.

The following years ARE leap years
1600, 2000, 2400
because they are evenly divisible by both 100 and 400.

The following formula will determine whether the year number entered into a cell (in this example, cell B3) is a leap year:

=IF(OR(MOD(B3;400)=0;AND(MOD(B3;4)=0;MOD(B3;100)<>0));”Leap Year”; “NOT a Leap Year”)

## Date & Time : Calculating the end-of-month

A commonly needed entity is the date of the last day of a particular month or a range of months, usually when payments are due or when interest is calculated. We show two approaches to this.

The EOMONTH function returns the last day of the month based on the specified date and offset value. For the current month, the offset is 0 and the offset is incremented by 1 for each subsequent month – as is illustrated below.

An alternative approach is to use the DATE function. A little known feature of the DATE function is that a zero in the day argument results in the last day of the previous month being output. The example below shows how this could be implemented.

## Date & Time : Calculating Dates of Holidays

This particular exercise in developing formulas for each of the major US holidays is a good way to develop expertise in OOo Calc date manipulation. US holidays follow simple guidelines for the dates on which they fall. Some holidays fall on specific dates , such as New year’s day on January 1st and independence day on July 4th. Other holidays fall on specific days within the month. For example, Memorial Day is the last Monday in May and Thanksgiving Day is the fourth Thursday in November.

The OOo Calc functions that will feature prominently in this exercise are

• DATE(year; month; day) Returns formatted date corresponding to specified year, month, and day values.
• WEEKDAY(dateValue; type) Returns a decimal value corresponding to the day of the week for the input date.

Before we tackle the US holidays problem, let us become more familar with the above functions and how they are used in OOo Calc formulas.

The DATE function allows us to define and manipulate a dates components – year, month, and day – independently. We have already seen this in an earlier tip

The WEEKDAY function returns a decimal value between 1 & 7 corresponding to the day of the week for the specified date. By invoking the TEXT with the appropriate formmating, we can convert the output of the WEEKDAY function to something more meaningful. In the example below, both formats are shown. The last two formulas show one approach to determining the first day-of-week after a specified date.

The table below generates the 10 major US holidays for a specified year – in cell C2

• New Year’s Day
=DATE(C2;1;1)
• Martin Luther King Jr. DayThis is the third Monday in January.
=DATE(C2;1;IF(2<WEEKDAY(DATE(C2;1;1));10-WEEKDAY(DATE(C2;1;1));3-WEEKDAY(DATE(C2;1;1)))+14)
• President’s Day This is the third Monday in February.
=DATE(C2;2;IF(2<WEEKDAY(DATE(C2;2;1));10-WEEKDAY(DATE(C2;2;1));3-WEEKDAY(DATE(C2;2;1)))+14)
• Memorial Day The last Monday in May, we subract 7 days from the first Monday in June.
=DATE(C2;6;IF(2<WEEKDAY(DATE(C2;6;1));10-WEEKDAY(DATE(C2;6;1));3-WEEKDAY(DATE(C2;6;1)))-7)

I leave the rest for you as an exercise!

## Date & Time Basics

Creating a column of consecutive dates is easy with the OOo Calc tool.

The formulas below illustrate how to increment a particular date by a given number of days, months, or years…

## Charting: Modifying the axis scaling

By default, OOo Calc will create a chart with the y-axis starting at 0 (zero). For most purposes, this is adequate, but it is quite straightforward to shift the axis up (or down) where desired.

For the Flash version of this tip, click here.

Consider the data set below, which we wish to chart. This has been covered before elsewhere.

The default chart. In this example, we will adjust the y-axis to start at 20.

We first select the chart for editing by double-left-clicking anywhere inside the chart area. A solid border should appear around the chart as shown below.

Now enter the y-axis editor with the right mouse button as shown below.

Make sure the scale tab is selected. Before we modify the axis scaling, we first deselect the automatic settings. The fields indicated by the red arrows need to be changed.

## Webquery : Importing HTML tables

With the help of the Web Page Query OpenOffice.org Calc import filter, you can insert tables from HTML documents in a OpenOffice.org Calc spreadsheet.

Let us step through the relatively straightforward process of importing data from tables in a HTML document.

The data for this example was obtained from an excellent website with tons of population data from all over the world : citypopulation.de

Here is what the source HTML file looks like : swiss.html

For this example, we will be importing awebpage from the local disk.

The External Data dialog is activated by Insert-External Data.

Once we fill in the path to the local copy of the HTML file and the table inside this file we wish to import – we hit OK and we are done – it is that easy.

## Database functions: DAVERAGE

In a prior example we saw how data filtering could be used to reduce the size of the data set before doing analysis.

The database functions perform basic data analysis – but they also have advanced data filtering built in.

The DAVERAGE function returns the average of the values in a database column satisfying a specified condition.

With the database functions – such as DAVERAGE, the criteria are defined in the spreadsheet. This is illustrated below in cells D13:E14. The criteria matrix normally has the same colums and headers as the main database table. Each row of the criteria matrix corresponds to a conditon to be applied to the database when filtering. Criteria in the same row are ANDed, while criteria in different rows are ORed

In the example below, we have configured the criteria to average rows whose age is <25 AND whose salaries are less than \$50,000. These criteria are both selected in the formula of B20. For the other example formulas, we select either the age or the salary condition, but not both.

In B18, we are averaging the filtered ages, but the other example formulas are averaging the salary.

## Conditional Formatting III

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.