OpenOffice Calc Tips

« October 2004 | Main | December 2004 »

November 30, 2004

Charting: Editing charts : part 1

In this series of tutorial, we will take a newly created chart and make use of the various chart editing tools provided by OOo Calc to adjust the appearance of the chart to our satisfaction.

In the example below, the chart has just been created from the data in B2:C11 However, we would like to make some changes - making the chart background transparent, adjusting the orientation of the x-axis labels, changing the format of the y-axis numbers and correcting the label on the right hand side.



The first task is the chart background. We would like to make it transparent. We first select the chart by double (left) clicking inside it's borders. Then we invoke the Chart Area dialog by (right) clicking and selecting Chart Area as shown below.



With the Transparency tab selected, we make the desired change as shown below. Click OK



The end result is much more pleasing to the eye. But we are not finished yet with our chart modifications. Next, we will adjust the orientation of the x-axis labels.



To be continued...

Posted by Dave at 03:28 AM | Comments (1)

November 29, 2004

Basic functions: REPT

The REPT function can be used to create simple charts without the use of the OOo Calc charting tool - although it is not clear if that was the original intent of the function.

REPT is a text function that repeats a specified string or character a specified number of times.

In the example below, char(10)is a special character that appears as a square symbol in most fonts. The resulting effect is that of a bar-chart in column C based on the corresponding number in column B.



Taking the idea behind the above example a bit further, we get a bit more elaborate in the spreadsheet below - with some "charts" based on the sine function.

In column A we have
    =REPT(CHAR(10);INT((SIN(ROW()/8)+1)*25))
and in column B, we have
    =CONCATENATE(REPT(" ";INT((SIN(ROW()/8)+1)*25));"*")

Posted by Dave at 04:08 AM | Comments (0)

November 26, 2004

Autocorrect

When entering text in OO Calc cells, you may notice that the program makes assumptions about what you are typing and you get unwanted corrections.

These corrections are completely configurable throught the AutoCorrect dialog.

The AutoCorrect dialog is invoked by selecting Tools - AutoCorrect as shown below.



The Options tab presents some optional corrections that OOo Calc can perform on any text entered by the user. From the identifier of each option, it should be easy to understand what OOo Calc plans to do - if that option is enabled.



In particular, the user can make use of a replacement table of common mispellings. The replacement table is fully configurable via the Replace tab - shown below.For example, if "acn" is a string you use often (a name of a company perhaps) OOo Calc will always insist on replacing it with "can". By removing this particular entry from the replacement table, OOo Calc will leave "acn" alone.



The Exceptions tab - see below - allows the user to prevent OOo Calc from correcting certain 2-letter initial cap combinations.



Finally, the Custom Quotes tab allows the user to replace single/double quotation marks with any characters of his/her choosing.

Posted by Dave at 03:14 AM | Comments (1)

November 24, 2004

Macros: Getting Cell Information

Invariably, macros written for use within the Calc application will need to access the contents of the cells on a spreadsheet. This tip is an introduction to the various available methods.

The three methods we will look at are getCellByPosition, getCellRangeByPosition and getCellRangeByName

The function that is first encountered for most people is getCellByPosition. In the sample below, we access cell A1 (on Sheet1)



The second example shows the use of getCellRangeByName and may be easier to use - because the cells are referenced by the traditional column/row identifiers that are displayed along each axis. However, for applications requiring looping through an array of cells, getCellByPosition is easier to use.



The next example shows how getCellInfo grabs an array of cells - myTable. A subsequent call to getCellByPosition for the myTable object is relative to the origin of this array.



The final method that needs discussion is getCellRangeByPosition and the example below illustrates it's use. It is equivalent in functionality to the previous example.


For more information.. http://api.openoffice.org/docs/common/ref/com/sun/star/table/XCellRange.html

Posted by Dave at 03:09 PM | Comments (1)

November 22, 2004

Charting: Pie charts

A step by step - and hopefully easy-to-follow - guide to creating a basic pie chart.

The table below is a good candidate for generating a pie-chart.



As before, we select Insert - Chart and the first Autoformat Chart dialog box appears. If you have not already selected the table, you can manually enter the range at this point.



Once we select the pie-chart option, we get a small preview of the finished chart in the dialog box.



For pie-charts, there are four variants for us to choose from.



The next dialog window allows us to add a title to the pie-chart.



Our first pie-chart! It still needs cleaning up - but this will be covered in a later tip.

Posted by Dave at 04:43 AM | Comments (0)

November 17, 2004

Basic Functions: ISERR

It is useful to be able to control the appearance of error values in your spreadsheet. The example shows how to do this for string searches.

In the sample spreadsheet below - we see two ways to use the SEARCH function in a spreadsheet. If SEARCH does not produce a match, the we get #VALUE! in the cell - perhaps not acceptable from an aesthetic point of view. However, by usinf the ISERR function, we can filter out the #VALUE! entries - as illustrated.

Posted by Dave at 05:56 AM | Comments (0)

November 15, 2004

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.

Posted by Dave at 08:27 AM | Comments (0)

November 12, 2004

Macros: A simple clock

Here is another simple macro that turns an OOo Calc spreadsheet into a real time clock.

The OpenOffice Basic builtin function Now returns the current date and time as a Date value. By embedding this function inside an infinite WHILE loop, we simulate a clock. The code is shown below.



The output to the spreadsheet is shown below.

Posted by Dave at 04:57 AM | Comments (0)

November 11, 2004

Basic Functions: INDEX, MATCH

We have already seen a table lookup example using the OFFSET function.

Here, we perform a similar operation using INDEX and MATCH

The example below show how well INDEX and MATCH can work together.

MATCH will return the position of a particular value in an array, while INDEX returns the value of a particular position within an array. We see multiple examples of these functions in operation below.

PriceMatrix is C4:E6 Material is C3:E3 Item is B4:B6
Using these names in the formulas below makes for easier readability nad maintenance. For this reason, it is always good practice to take advantage of the ability to name arrays that will be used within formulas.

Posted by Dave at 04:42 AM | Comments (0)

November 10, 2004

Adding notes to cells

Adding notes is straightforward. Follow these illustrated examples on making the most of this Calc feature

Adding a note: Select the cell you wish to annotate and click Insert - Note as is shown below.



You can now add a comment in the newly created box. To finish adding a comment and close the box, select another cell.



Cells with attached notes have a tiny red mark visible in the upper right corner - and the note can be viewed by moving the cursor over the cell.

Posted by Dave at 05:35 AM | Comments (2)

November 09, 2004

Calculating weighted average

We will look at how to use the SUMPRODUCT function to determine the weighted average value for some sales data.

In the example below, averaging the prices of each product does not give a correct average sale. We need to use the SUMPRODUCT function as illustrated to determine the weighted average of the sales.


Posted by Dave at 06:19 AM | Comments (1)

November 08, 2004

Webquery: Scraping FOREX quotes from Yahoo

As a follow on from the previous tip, I show how to grab FOREX quotes from the Yahoo Finance website.

Here is the link to the Yahoo FOREX quote for the US dollar against the Japanese yen. A screen shot is shown below. At any given time, we are interested in two values for each currency pair, the bid and the ask.

The URL in this example is http://finance.yahoo.com/q?s=USDJPY=X and the table is HTML__yfncsumtab.





By selecting Edit - Links, you will get the Links popup editor.



With this, you canreview and modify existing Webquery links in your spreadsheet.

Posted by Dave at 04:07 AM | Comments (0)

November 05, 2004

Webquery: Scraping stock quotes from MSN

In a previous entry we imported data from an HTML file on the local disk. In this example, we will extract stock quotes from an MSN site and import the data into OOo Calc spreadsheet.

We will import the stock quote information from this MSN webpage. A screen shot is shown below.



For this example, we import the data into the spreadsheet as before with Insert - External Data . The URL is http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&Symbol=INTC and the table we need to select is HTML_15.

You also need to make sure your proxy server settings are correct under Tools - Options (Internet)

Now, we can choose to have OOo Calc import this data at predefined regular intervals. This is useful - given the constantly changing nature of the stock prices. Also, each time that the user opens the document, OOo Calc will prompt to update the external links. Basically, what we have created is a 'window' to an external document. It can be a snapshot - or it can regularly monitor the target - updating accordingly.



Having extracted the data from the external website in it's'raw' form, we can use VLOOKUP to grab any piece of data we want. This is shown below.

Posted by Dave at 03:30 AM | Comments (6)

November 04, 2004

Basic Functions: HLOOKUP

HLOOKUP is similar to the more often used VLOOKUP. To illustrate how the HLOOKUP could be used, we consider an example where we identify the minimum bid for a contract from a list of contractors.

The syntax for HLOOKUP is

=HLOOKUP(search_criteria;array;Index;sorted)

Search criterion is the value searched for in the first row of the array.

array is the reference, which is to comprise at least two columns.

index is the number of the column in the array that contains the value to be returned. The first column has the number 1.

sorted is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.

Below, we have a number of contractors and their corresponding bids in an array.

We first use the MIN function to place the minimum bid value in B10

Row 3 is used to flag the column with the minimum bid. The formula in B3 is =IF(B5=$B$10; "P";"") We use the Wingding font, which shows a flag symbol instead of the letter "P".

The HLOOKUP function in B9 then searches for the flag returning the name of company below that flag.

Posted by Dave at 05:09 PM | Comments (0)

November 03, 2004

Charting : Introduction

A picture tells a thousand words. In this introduction to OOo Calc charting capabilities, we will create a simple chart as an illustration of the operation of the charting AutoPilot.

Anytime you create a chart object in OpenOffice Calc, the AutoPilot is invoked.

After you select the data for which you wish to create a chart, there are two ways to proceed - both illustrated below.

You can select Insert - Chart



... or you can use the Insert Object floating toolbar.



When the charting autopilot is invoked, it has default settings already assigned which can be used to create a sheet at any stage of the process.

Normally, however, you will need to tweak the settings a little to achieve the desired chart layout and contents.

The first dialog allows us to define the data range, the labels and the target sheet for the chart.

When you are satisfied with the settings, hit Next to move to the next dialog, or Create to proceed immediately to chart creation. Until you hit Create in these series of dialogs, you will be able to return to previous dialogs for further adjustments.



The second dialog is straightforward. You can choose the chart type and also be able to see a preview of the output.



Based on the chart type you have selected, the third dialog allows you to refine certain aspects of the output chart. By selecting/unselecting the different options available you can observe how these effect the appearance of the chart.



Finally, we get to add titles to our chart and any axes we also wish to label.



Once created, we can move and scale the chart.



To be continued...

Posted by Dave at 06:03 AM | Comments (1)

November 02, 2004

Average a range without highest and lowest values

For statistical purposes, you may want to calculate the average of a data sample without its highest and lowest values.

The formula to do this is
=(SUM(range)-MAX(range)-MIN(range))/(COUNT(range)-2)

The use of this formula is illustrated below.

Posted by Dave at 05:24 AM | Comments (0)

November 01, 2004

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

Posted by Dave at 05:05 AM | Comments (0)