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.
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.
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.
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.
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.
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…
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.
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.