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.

Read More

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.

Read More

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.

Read More

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.

Read More

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.

Read More

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.

See also:
Conditional Formatting II

Conditional Formatting I

Selecting an entire sheet

Read More

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.

Read More

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.

Read More

Data Validation: Drop down lists

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

Read More

Toolbar Crazy

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.

Read More