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.

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.

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.

Conditional Formatting II

Conditional Formatting I

Selecting an entire sheet

Looking up data in tables V

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.

Conditional Formatting I

Conditional formatting is a mechanism that allows the user to apply formatting to a cell or range of cells based on defined conditions. If the rules (conditions) are met, then the specified formatting is applied. In OOo Calc, you can apply up to 3 rules in a cell or range of cells.

In the example below, we have an array of numbers. To illustrate how conditional formatting works, we will apply different formatting to those cells whose values are greater than the average of the range.

We first need to define a new formatting style.

Apply the desired formatting to an isolated cell and invoke the Styles and Formatting dialog as shown below.

Here, we click on the New Style from Selection icon.

This allows us to add our own custom style to the Style catalog.

We will now make use of our newly defined style in the Conditional Formatting dialog, which is enabled as shown below.

We select Condition 1 and the settings are shown below. The Cell STyle drop down list allows to to select any of the styles from the catalog – including any custom style we have defined.

You must use absolute addressing when referencing cells and cell ranges.

The final result of applying conditional formatting on the selected range of cells.