OpenOffice Calc Tips

On the right side of the statistics, you will find a hidden gem of a feature - unless you are a former Excel user.

Select an array of numbers and the sum of the numbers appears in the bar - which you may or may not have noticed. However, if you right-click on the bar, you will see a choice of basic statistical operations to choose from - neat!

In the example below, we have chosen to display the average of the selected range.

Another quick tip illustrating the versatility of the Paste Special function.

To insert an empty row into an existing table…

Step 1: Select and Copy a single empty cell in your table.

Step 2: Select the row above which you wish to insert an empty row - then select Edit - Paste Special - below…

The key setting has been circled. Existing selection is shifted down while blank cell is copied across the table

The end result is shown below.

I have plans to implement a Keno game in the OOo basic language - but first a simple Lotto number generator. If you go into the source, you can configure to your own requirements. Generating a random number between 1 and 49 is really simple. Making sure that number has not already been picked slightly complicates matters.

Download it here

Sub Lotto
Dim i
Dim j
Dim val
Dim match
oSheet = ThisComponent.Sheets(1)
For i = 1 To 6
oCell = oSheet.getCellByPosition(1,i)
Do
match = False
val = int(rnd()*49) + 1
For j = 1 To i
If val = oSheet.getCellByPosition(1,j).getValue() Then
match = True
End If
Next j
Loop Until (match = False)
oCell.setValue(val)
Next i
End Sub

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”)

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.

In this tutorial, we will see how to restrict the value of a cell or range of cells to a specified list.

In the example below, we would like to restrict the values of two fields - State and Sex.

First select the column with the State values. Invoke the Validity dialog with Data - Validity as shown below.

There are three tabs in the Validity dialog. First, we define the criteria to be applied to the selected cells.

By default, all values are allowed. We will define a list which will be displayed when the user selects the cell for input.

Because the list of valid states will be displayed from which the user can choose, we can dispense with an input help message. The Error Alert tab allows us to define an error message/action to be generated if erroneous value is inputted.

We click OK to exit the Validity dialog. We are now ready to see how effective this feature is in validating the data being input in the table.

As you can see, selecting a cell in the state column results now in a drop-down list from which the user selects the desired value.

We can also define other lists where desired/required - for example the Sex column - as shown below.

In this tutorial, we will look at three related functions that can be used to predict future unknown values - based on existing data.

The FORECAST function takes a set of X-Y data pairs and returns a single Y value - given it’s corresponding X-value. The assumption is that the data is linear in behaviour.

In the example below, we have sales data for the first eight months of 2004. We want to predict (forecast) the sales figures for July 2005.

The first argument is the X value for which we need a forecast. The second and third arguments are the known Y values and known X values respectively.

The TREND function issimilar to FORECAST. A linear relationship is assumed between the X and Y data sets.

Howver, TREND is an array function and returns an array of unknown Y values.

The frst two arguments to TREND are the know X and Y data sets. The third argument is the array of X values for which we wish to predict the corresponding Y data.

As you can see from the chart below, OOo Calc ignore anomalous data as best it can in predicting future values for Y.

Like TREND, the GROWTH function returns an array of predicted values. The difference being that exponential growth is assumed. Otherwise the format is the same.

Again, by charting the known and predicted data, you can decide if OOo Calc has made a reasonable prediction based on the data provided to it.

I was intrigued to see that OOo Calc supported the creation of candestick charts.

What follows is not so much a tutorial on how to effectively make use of the feature in OOo Calc - but rather a summary of my experiences in trying to generate some meaningful charts from historical stock data available on the Web.

You are probably familiar with candlestick charts. One of the attractions is that in a single graphic, four useful stock metrics are presented, opening price, closing price, intraday hgh, and intraday low.

With no information or documentation to work with, I figured that in order to create a candlestick chart, I would need these four aforementioned metrics for my selected stock over a timeperiod that was of interest to me - say the last two months. This information is readily available online at sites like Yahoo - see below.

Step 1: From the Yahoo page below, I selected all columns of the historical prices table - except adjusted closing price - for Freescale (FSL) - about two months worth of data. Even though Volume is not required for the candlestick chart, I grabbed it anyways as it is a key metric in technical analysis of stocks.

Step 2: I pasted the table into Ooo Calc as shown below. I use OOo 2.0 on Win XP and this direct copy/paste manouever from browser to spreadsheet went without a hitch. On other systems, your mileage may vary. Ultimately, we would want some mechanism whereby the stocks would be updated automatically at the close of trading each day. This would certainly be possible with some macro programming.

Step 3: I noticed that the table was arranged with the most recent data at the top. This would map into a candlestick moving from left to right - which is incorrect. I selected the table and then Data - Sort to bring up the Sort dialog. I then chose to sort by Column B - Ascending. The imported and flipped data is shown below.

Step 4: Select all columns except the volume and open the chart wizard with Insert - Chart

First page: Both first row and first column selected as labels…

Second page: We select the Stock chart type…

Third page: The variant of the stock chart type is Stock Chart 2 - otherwise known as the candlestick chart. I also selected Y-axis grid lines for better legibility. A shortcoming of OOo Calc is the lack of control one has with the X-axis. More about that later.

Fourth page: I added a basic title anddisabled the legend and axis titles for the moment.

My first pass. What needs immediate attention is the X-axis - which is useless in it’s present state. Let us fix that…

The one way to ‘fix’ the x-axis is to create an alternate column in our table that will be converted more legibly in the final chart. With a bit of experimentation, I came up with the arrangement below. When regenerating the chart, ignore the column on the far left and use the manually created column to it’s right.

The result of my first attempt at a candlestick chart. This leaves a lot to be desired - but it is certainly promising. Certainly, we would like to automate this whole process by using macros. An automated daily update of the table and chart would also be very useful. Stay tuned!

It has been decided to merge the Mungits and Wingdings product lines. We will use Paste Special to merge the sales totals in the spreadsheet below.

Step 1: Select C6:F6 and Edit - Copy

Step 2: Select C5:F5 and Edit - Paste Special

The Paste Special dialog opens. In this situation, we apply the ADD operator to the paste operation which has the effect of adding the pasted data to the existing contents of the selected cells.

Having merged the data, we can safely delete the Wingdings row in the table.

Unlike Excel, OOo Calc does not have random sort feature. It is straightforward to work around this.

Consider the table below - we wish to randomly sort the rows.

Step 1: Add a random number for each row in a seperate column as shown below.

Step 2: Select all columns and invoke the sort dialog as shown below…

We will be sorting our original two columns based on the column of random numbers…

The final result. You can delete the column of random numbers - they have served their purpose.

If you are familiar with project management and the various tools that are used to schedule deadlines and resources, you may be familiar with Gantt charts.

In this particular tutorial, we look at using stacked bar charts

First, we need to tabulate our project data. The numbers in columns D & E are total days completed & estimated days remaining in each task respectively.

Selecting the above table, we invoke the chart wizard with Insert - Chart.

Both the first row and column are selected as labels..

Select the Bar Chart type - below…

Of the different bar chart types, we choose the stacked variant - below…

Here, we add a meaningful title..

The ‘finished’ chart. We have some postprocessing further to perform before it looks like a Gantt chart. Select it for editing…

The blue bar above represents the start date of each task. We will make it disappear. Fist double click on any blue bar to invoke the Data Series editor below. Set the area fill to None and remove the border.

This is what the chart looks like with the blue bars removed …

Now select the Y axis for editing as shown below…

We now want to adjust the Scale settings for the Y-axis based on the timelines defined in the initial table.

The major interval (61.0) is approximately 2 months. The minor interval (1.0) is one day.

On the Label tab, we rotate the text to almost 270 degrees as shown below…

The finished Gantt chart…