statistical analyses with openoffice.org calc

I don’t know why, but, occasionally, people may have to do minimal or even considerable statistical calculations using Calc, the OpenOffice.org‘s spreadsheet part of the Office Productivity Suite. Although, I wouldn’t recommend doing so since there are other, better software packages to complete simple and complex statistical analyses, such as R, a statistical programming language and environment with excellent analysis and graph capabilities. R can be found at one of its many mirrors.

However, for those who need or have to (or … whatever), here are some very very basic info on using statistical functions in Calc. Calc has a considerable variety of functions documented in the openoffice wiki. of statistical (and other types of) functions that you can use. There is also OOo_Stat, an external add-in, that you can include to assist you with your analyses. More on that later on.

MInd you, I am not going to provide a full “Statistics using Calc” guide here, I just want to suggest a few statistical functions for basic descriptive and inferential statistics.

A. Basic Descriptive Statistics
I. Central Tendency

  1. AVERAGE returns the mean average of its list of arguments
  2. MEDIAN returns the median value (the value at the 50th percentile) of its respective list of arguments
  3. MODE returns the most frequent value of its list of arguments

There are other functions, like GEOMEAN, HARMEAN, and TRIMMEAN which give the geometric mean, the harmonic mean and the trimmed mean (excluding a portion of highest and lowest values) of their arguments.

II. Variation

  1. KURT estimates the kurtosis of the distribution of values (ie, how flat or how tall the distribution is)
  2. SKEW estimates the asymmetry of the distribution (ie, how different from the symmetrical normal distribution it is)
  3. STDEVestimates the standard deviation of the distribution
  4. VAR estimates the variance (ie, the square of the standard deviation) of its list of arguments

Note, that there are functions STDEVP and VARPP for the standard deviation and variance of the population where the sample has come from.

B. Some (not all) inferential statistics
I. Simple tests

  1. CHITEST estimates the chisquare test of goodness-of-fit
  2. FTEST estimates the likelihood that two samples have the same variance.
  3. TTEST estimates Student’s t-test of one or two samples

II. Correlations, and simple regression

  1. CORREL estimates the Pearson product-moment correlation coefficient between two arrays of values
  2. PEARSON is identical to CORREL
  3. INTERCEPT estimates the intercept b_0 of the regression y = b0 + b1*x
  4. SLOPE estimates the slope b1 of the same regression equation
  5. STEYX estimates the residual standard error for the regression
  6. RSQ estimates the R^2, the square of the correlation coefficient between x and y>
  7. LINEST is a different kind of story. It provides a linear estimation for the data at hand. Basically it summarizes the regression analysis. It is an array function which means it must be called like this {=LINEST(arg1;arg2;arg3;arg4)}.
Linest Function Output

Linest Function Output

Data in the above example come from the R program and its cars dataset which “… give the speed of cars and the distances taken to stop” according to the help files. R help files cites Ezekiel’s 1930 Methods of Correlation Analysis published by Wiley as the source of the data and D. R. McNeil’s Interactive Data Analysis also published by Wiley in 1977) as a reference source for the data.

The output of the LINEST function consists of eight cells between F18 (upper left) and G22 (lower right) cells. Unfortunately no explanation is provided. I have tried to write in the adjacent cells some labels about each cell in the output of LINEST. Please note, that the third line of output (F21:G21) shows only the F-criterion for the fitting of data and the denominator degrees of freedom. In statistical parlance, F(1,48) = 89.57, p < 1.49e-012, which can easily be verified by using another Calc function FDIST.

There is additional output above the F18:G22 range of cells, basically the output of the CORREL, PEARSON, RSQ, INTERCEPT, SLOPE, STEYX commands from top to bottom, which all verify the output of the LINEST function.

C. OOo_STAT
An add-in macro for Calc, it comes with its own instructions on how to install it (you must enable macros) and then appears either as and additional top-level menu or as an item under Tools. OOo_STAT will not appear in Writer. It includes functions for graphics and advanced inferential statistics not available through other Calc functions. It is still in 0.5 version (which would make it a beta) but works pretty well.

After install, OOoStat gives three choices: Applications, Basic Stats, and Multivariate Statistics as shown below

OOo Stat macro

OOo Stat macro

The Applications menu allows for visualization of data via histograms and other manipulations.

OOoStat First Menu

OOoStat First Menu

The Basic Stats deals with one- and two- way ANOVA, Correlation Coefficients and Multiple Regression.

OOo_Stat Basic Stats

OOo_Stat Basic Stats

The Multivariate Analysis menu involves Principal Components Analysis, Correspondence Analysis, Multivariate Analysis of Variance (MANOVA), analyses which may be better completed using a dedicated statistical analysis package.

OOo_Stat Multivariate Statistics

OOo_Stat Multivariate Statistics

All in all, Calc provides a rather extensive set of tools (either through functions or through the OOo_Stat addin) which may assist users in their statistical analysis. However, beware, the user should always be in control of the analysis tools and not the other way round.

Enjoy,

I.

Enhanced by Zemanta
Advertisements

Tips and tricks for openoffice.org

OpenOffice.
Image via Wikipedia

<Update> Soon after I hit the PUBLISH button, I received via email, the OpenOffice.org newsletter, with an extensive list of openoffice.org related blogs.</Update>

Although there is an abundance of information available at the official openoffice.org site regarding the usability and how to make openoffice.org, the free open productivity suite, work better for your needs, there are hidden treasures of information, tricks, and tips all over the place waiting to be discovered.  Here are some, in no particular order of importance or significance.

The wordpress.com site.
The trick here is to look for the tags you (or somebody else) assign to a post they publish in a wordpress.com blog.  Be as imaginative and creative as possible.  Use combinations of words.  For example, office, open, xml, lead to this page, while open, office, xml lead to this different page. Try other languages, too, and see where the hunt takes you  (if you are versed in that particular language).  For example, the same tags in german will lead you to another page, altogether.

The sun.com site
Try the sun.com site for openoffice guides and white papers.  After all, they are one of the largest contributors to openoffice.org and they still hold the license for staroffice, the suite that started all once upon a time.  Note that to retrieve white papers and guides you may have to register with a username and a valid email address.  The following two links are interesting:
Migrating from MS Office to Openoffice.org or StarOffice,
Creating large documents with Openoffice.org writer

Other blogs (of course)!!!!
Try some or all of the following (I am not affiliated with them, nor do I know their owners/editors etc.  I simply read them occasionally).
Openoffice.org engineering at sun.
Tips and training for openoffice.org.
The lifehacker page and its openoffice tips.

Finally, something that I also found while searching around:
1) Open a new spreadsheet in Calc
2) Type the following in any cell
=GAME(“StarWars”)
enjoy the openoffice version of space invaders.

For more easter eggs, … well, just look around 🙂

I.

Powered by ScribeFire.

Reblog this post [with Zemanta]

more extensions to use with OpenOffice.org release 3


AddThis Social Bookmark Button

With the new release of the OpenOffice.org free application suite, the use of extensions enhancing the capabilities of the suite is easier than ever.

As a matter of fact, the use of extensions is almost required in version 3.0 as the dictionaries and other linguistic aspects of the suite are handled via extensions. By default, you will go under the Tools menu, select the Extension manager and you will see a few (locked) extensions pre-installed.

openoffice extension manager

openoffice extension manager

The english, french and spanish language files are pre-installed and apparently locked in the version I downloaded from the Openoffice.org servers (I assume these files are locked in other flavours of openoffice). I like to fiddle with my installation so I downloaded a series of tools/extensions. Here they are with few bits of information about each one:

1. Pagination. This extension adds a simple “insert page number” menu under Insert and facilitates the application of page numbers, styles etc. Perhaps its usefulness lies with its simplicity.

2. PDF Import. This extension allows users to import pdf files in openoffice Draw and complete simple editing (as images). Nothing fancy, but if you want to simple clean up a couple of things (typos etc) and sources are not available, this extension may do the trick.

3. Template Pack by Sun. This is an English language version, although there are packages for other languages, including french, italian etc. It allows for the creation of specialized and professionally looking files, documents, letters, presentations etc.

4. Report Builder also by Sun. This extension provides tools for customized reports drawn from the openoffice.org database files.

5. Writer tools. This extension creates a separate Writer’s Tools top-level menu (next to the native Tools menu) and offers various tools and tips for writers using OpenOffice.org’s writer. Very nifty!!!

6. Writer to LaTeX. This extension takes a writer (odt) file and transforms it into the proper LaTeX file to be typeset by the finest typesetting system available (LaTeX of course).

It also offers an additional extension to translate the writer file to xHTML files.

Finally, the last extension of the list is the Greek hyphenation/thesaurus/spelling dictionary file (it’s all Greek to you, I know 🙂 ).

What I also like is the update button which allows for periodic checkups and updates for each and every extension. Some extensions require you to scroll through their license agreement, but that’s acceptable, I guess.

Do you have any extensions that you use and you recommend? Post a comment

I.


AddThis Social Bookmark Button

Powered by ScribeFire.

Reblog this post [with Zemanta]

first impressions from openoffice.org 3.x


AddThis Social Bookmark Button

By now, most of the world knows that the new version of Openoffice.org, the free office application suite, is out.

Earlier, servers at openoffice.org were busy serving the various choices for win, linux, and Mac OS X (and at some point, they were so slow with incoming traffic). This is the first openoffice version which runs natively in Mac OS X. OpenOffice starts with a splash screen and then a central menu appears which allows the user to select from a variety of tasks.

openoffice version 3 start screen

openoffice version 3 start screen

In addition, it offers a variety of new goodies,
but I am going to mention only the following two:

First, it allows the import (natively) of .docx files, as well as .xlsx and .pptx files. These are the files in Microsoft’s new ooxml format that apparently even MS will no longer support 🙂 I have written extensively in the past about the docx saga and I have noted that the best choice maybe the odf converter (at version 1.1.7 as of this moment). However, with the native import filters, this extension is no longer functional and the system will respond with error messages if you try to install it. However, the functionality is there and you can open the docx/xlsx/pptx files, although you cannot save them in this format, too. Maybe that’s a nice (and not-so-intrusive) way of saying to your co-workers who use MS Office 2007 to use a format that the majority of users can also apply.

Second, it offers a much easier way to deal with multiple language dictionaries and spelling support. In previous versions (notably in versions 2.x), users had to install dictionaries via some semi-automatic scripts or manually by tweaking the dictionary.lst file and adding the appropriate hyphenation, spelling and thesaurus files. Now, things happen differently.

All we have to do is select the extensions manager and install the proper files which are conveniently located at the extensions server. By default, english language files and additional files are pre-installed, but you can add your choice of languages. A close-and-open-again trick is needed in order to apply the proper files.

extensions in openoffice

extensions in openoffice

While you are at it, you may also want to give a few more extensions a try. Download the beta pdf import tool which allows partial pdf file editing. And of course, Dmitri’s writer tools, a wonder set of choices which creates a special “writer tools” menu next to the default tools menu in openoffice.

Openoffice 3 is a great improvement and definitely worth trying for serious work. What is your reaction and opinion regarding this new release? Post a comment and digg it if you like it.

I.


AddThis Social Bookmark Button

Powered by ScribeFire.

Reblog this post [with Zemanta]

odf in office (the other way round)

OpenOffice.Image via Wikipedia


AddThis Social Bookmark Button

From the pages of this blog I have written about how to open a docx (office 2007) file in openoffice. This happens frequently with a client, a customer, a colleague who just updated (or was forced to update) to the newest version of Microsoft’s Office suite. And many readers have commented that often they received (as have I) docx files as attachments and don’t know what to do with them.

(Begin diversion)
I recently received such a file, an invitation to join a social club. I returned it with a polite note asking to receive it in doc, pdf or txt.
(End diversion)

But, what happens if someone (you, perhaps) started sending odf files to all those MS Office users? How, could the recipient(s) of such a file work with it, open it, edit it and then save it back as odf?

There are many possibilities, all free (isn’t it funny that you can work in an expensive, proprietary world with free software?):

  1. The SUN ODF plugin for Office. This is a SUN product which allows users of Office (versions 2000, XP, 2003, 2007 SP1) to read, edit and save an odf file.
  2. The Open XML/Odf Converter for Office. This is a sourceforge project with similar capabilities to the previous plugin. A command-line translator is also available for batch processing jobs.
  3. Microsoft Corp. According to this press release, the upcoming SP2 for Office 2007 will offer the ability to work with many additional formats including odf.
  4. OpenOffice.org. Educate the person who received the file that there is a free alternative to the MS Office way that does what MS Office and more…

If you know of additional methods of working or educating people who receive odf file in an Office environment, please let me know. What has your experience been in this area?

I.
=

AddThis Social Bookmark Button

Reblog this post [with Zemanta]

experimental pdf editing in openoffice 3

AddThis Social Bookmark Button

OpenOffice.org Impress

Image via Wikipedia

An experimental extension to edit pdf documents in open office is now available at the extensions repository. It is only available for the beta version of openoffice (ie, it will not work with openoffice 2.x) and is fine if you want to manipulate small pieces of the pdf text (and the source is not available). This is done in the Impress part of the OpenOffice.org suite.  The extension is available for many platforms, including solaris, mac os, windows and linux.

More information is available at gullfoss and in this article available at the free your media site.

Enjoy!

I.

Powered by ScribeFire.

AddThis Social Bookmark Button

Related articles

Zemanta Pixie