Datapilot Revisited

Version 2.0 of OOo Calc will feature significant improvements to the DataPlot functionality. For this reason, we will revisit this powerful feature over the next few days.

First, the basics…

Consider the table of data below. There are numerous ways we may want to analyse this data. By using the DataPilot functionality in OOo Calc, we can perform the different analyses quite easily – with a bit of practice.

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.

Read More

Basic Functions: FORECAST, TREND, GROWTH

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.

Read More

Macros: A Lotto Number Generator

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.

Read More

Basic Functions: COUNTBLANK

The COUNTBLANK function allows you to total the number of blank or empty cells in a specified range.

It is used as show n below in the example. Cells C3, B5 and D5 are empty – so the COUNTBLANK function in D7 returns 3.

You may have a situation where the array is quite large and it is important that each cell has a value. To make it easier to trap blank cells, you can combine the COUNTBLANK inside an IF function…

=if(COUNTBLANK(A1:A500)>0,”Blank Cell”,average(A1:A500))

Conditional formatting would also help you spot blank cells.


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

Using names for cell ranges

You can define ranges as names from the Insert Menu. This makes subsequent enhancements and maintenance of the worksheet easy.

First, select the range of cells to which you wish to assign a name. Then INsert->Names->Define (below)

A dialog will appear as is shown below..

You can then use the defined names to specify a range in a table as shown in this example.

If you change the Range name, the formulas will adjust automatically.

Read More

Financial Functions 3 : Complex Accumulation

In this example, we consider a more complex accumulation – wherein we are making regular payments as well as earning interest on our initial investment.

Here, we define names for the cells containing the main variables – so that the formulas are more readable.

The purpose of the month-by-month breakdown of the account also serves to confirm the accuracy of our original formula.

Read More

Basic Functions: HLOOKUP

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.

Read More
  • 1
  • 2