OpenOffice Calc Tips

Plotting the frequency distribution of our random data set is quite straightforward and allows us to visually evaluate how symmetric about the mean our data is.

We will also look at a function that measures how symmetric a data set is.

We have already met the FREQUENCY function in a previous
tip

In the example below, we set up our intervals in B22:B30

and the FREQUENCY array function is added to C22:C30

The final step is to create a chart from the newly created frequency table.

<

A function that measures how symmetrically distributed a set of data is is SKEW. For perfectly symmetric data, the SKEW function returns 0.

In the plot below, the data is tailing off to the right - giving a positive skew. This is confirmed by the value of the SKEW function.

In the next tip, we will show how to generate a skewed set of random data - known as the Lognormal distribution

In previous tips, we showed how to generate random input data that was distributed according to our requirements. In particular, we looked at the Normal Distribution here and here

There is an even easier way to generate our normally distributed data - and that is to use the NORMINV function. NORMINV returns the inverse of the normal cumulative distribution. Given a cumulative probability, a mean, and a standard deviation, NORMINV returns the value that cuts off the cumulative probability.
The arguments to the function are :

NORMINV(number, mean, stdev)
where

• number represents the probability value used to determine the inverse normal distribution.
• mean represents the mean value in the normal distribution.
• stdev represents the standard deviation of the normal distribution.

By using the RAND function with multiple calls to NORMINV, we can obtain a nrmally distributed input data set.

In the example below, we set the mean to 0.0 and the stdev to 1.0. As you can see, the generated data set comes pretty close to our requirements.

Next, we will plot a frequency graph of our data.

In OpenOffice Calc, the FIXED function returns a text representation of a number rounded to a specified number of decimal places.

(more…)

The paste special feature is useful for preserving the values in a spreadsheet - but removing the formulae that generated those values. This may be required to preserve proprietary equations.

To remove the formulae for a range of cells, select and copy the desired range. Do not unselect the range. Now click on Edit - Paste Special - see below.

Make sure the Formulas box is unchecked. After you click OK, the formula is removed but the value is preserved.

In a series of short tips, we introduce the Paste Special feature.

A quick and easy way to multiply an array of numbers by a constant without using a formula…

In the example below, we wish to multiply B5:B20 by 10. Temporarily enter 10 into an empty cell. Select it and then Cut or Copy

Select the target range, then click on Edit - Paste Special . In the Paste Special dialog, unselect Paste All and Formats. Also select Multiply. Now click OK …

We are done! The modified cell range.

Open Office Calc has an interesting Easter egg - a hidden feature that might not be obvious or documented. Programmers sometimes bury Easter Eggs in their programs or web sites to add extra depth and challenge users to find them.

In an empty cell, type the following formula..=GAME(”StarWars”)
This is what you will see…

After you have selected your hero, the game begins. Another screen shot…

You can only play the game once. To play again - exit OOo Calc and the quickstarter before restarting.

There are many mathematical and statistical constants in use that are needed in spreadsheets. For OpenOffice Calc, only PI has been hardwired as a function call - PI()

Using defined names is a quick and easy way to define constants in your spreadsheet that will also make your formulae more legible and maintainable. There will also be no need to have hidden sheets where the constants are defined. In this example, we will define Euler’s constant - e

Open the Define Names dialog as shown below.

Instead of assigning/associating the name with a particular cell, assign a fixed value as shown below. Exit the dialog

The named constant can now be used in formulae as shown below. Note that Names defined in this way do not appear in the name box - which is just above the cells area on the left.

Here is a simple macro that creates a new sheet in the Calc document with a listing of all the notes (comments) found.

Notes can be added to any cell with Insert - Note - see below.

The Basic code for gathering all of these notes and adding them to a new sheet is given below. Items in the listing I’d like to draw your attention to..

• Creating new sheets with the insertNewByName method.
• The PrintableAddressOfCell and ColumnNumberToString routines convert row and column cell offsets to human readable notation - and were written by Andrew Pitonyak.
• The notes (annotations) for each sheet are traversed using a For loop in the exact same way as the sheets of the document.

The newly created comment sheet is shown below.

The CHOOSE function comes in handy when there are a number of options for a particular value based on the result of an expression or result. It saves having to construct a complex formula involving nested-ifs.

In the example below, the CHOOSE function is used to select the text form for the day of the week - according to the numerical value returned by the WEEKDAY function

A checkerboard shading effect is easily achieved with conditional formatting as shown below. The conditional formatting dialog is invoked with Format - Conditional Formatting

Please review earlier articles on conditional formatting here , here and here.

When the condition (formula) evaluates to true, then the specified style is applied to the cell. We have seen this before. ISODD is a boolean function so is ideal in this situation.

The resulting pattern…