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

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

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

OOoStat First Menu

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

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

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.

Tips and tricks for openoffice.org

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.

Related articles by Zemanta

• Microsoft Office Banned at IBM – Office Alternatives (integralwebsolutions.co.za)

more extensions to use with OpenOffice.org release 3

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

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.

Powered by ScribeFire.

Related articles by Zemanta

• Free PDF to Word converter
• OpenOffice.org 3.0 is an incremental improvement
• OpenOffice 3 Is Available
• OpenOffice.org 3.0 trips up community’s Web site

first impressions from openoffice.org 3.x

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

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

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.

Powered by ScribeFire.

Related articles by Zemanta

• OpenOffice 3 includes native version for Mac OS X
• OpenOffice 3.0 released amid fears of development stagnation
• A First Look at OpenOffice.org 3.0 [Screenshot Tour]
• Scramble For Open Office 3.0 Causes Crash

odf in office (the other way round)

Image via Wikipedia

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.
=

Related articles by Zemanta

• ODF Addins For Microsoft Office Suite
• Goodbye Office 2007 and Good Riddance!
• OpenOffice.org 3 beta lands
• ODF wins the office document format war?

experimental pdf editing in openoffice 3

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.

Related articles

• OpenOffice 3.0 beta: Can it finally replace Microsoft Office? [via Zemanta]
• OpenOffice.org 3.0 beta goes native on the Mac [via Zemanta]
• OpenOffice 3.0 beta released [via Zemanta]

yet another ooffice 3.x dev snapshot available

As reported in the gullfoss a new development snapshot for the upcoming open office 3 has become available through the usual channels.

In past postings, I have also mentioned that upcoming releases will be available for the amd64 platform, too, and this release is no exception.

Go ahead and download and of course enjoy!

I.

Powered by ScribeFire.

new dev build and 64bit too

It has just been reported at gullfoss, that there is a new dev build release of openoffice 3 (this would be developer snapshot build DEV300_m16).

In addition to bringing us one step closer to the much expected OOo version 3, this dev build can be installed side by side to the “regular” OOo release, for someone adventurous enough to try new and exciting things. In addition, there is a 64-bit version for the people who like to run on the 64-bit way of life.

Although the release link does not say much, the 64-bit version is there along with the 32-bit version. It can be downloaded from many servers around the world.

Enjoy,

I.

Useful additions to OpenOffice.org suite

Image via Wikipedia

The following links may be useful to the many users of OpenOffice.org, the free, open source Office Productivity Suite. To say more about the suite itself would be an exercise in futility. I urge interested readers to visit and to explore the huge openoffice site to find out more about OpenOffice.org.

One of my favorite places for openoffice.org stuff is the following: Lingucomponent. This is where you can download several dictionary, hyphenation and thesaurus files for several languages. Another such interesting site is this one with extensions to improve the functionality of the Openoffice.org suite.

To install these extensions (which come as single .oxt files), we open OpenOffice, select the Tools menu, then select Extension Manager (see image below).

We will get a new window showing two entries: MyExtentions and OpenOffice.org Extensions. The important button is the Add which allows to import the extension of our choice into the system.

Here is a brief list of extensions I have already installed from the site:

1. pagination does what its name implies; it provides several methods for inputing page numbers by adding an extra entry under the Insert menu.
2. Set of templates for various purposes. According to the site, “… each template makes creating personalised and individual OpenOffice.org documents a breeze with built-in images and text styles”.
3. Writertools. I have written in the past about this extension. Here is another site for the same extension. The extension adds a new menu (next to the original Tools menu) and adds to the functionality of OpenOffice extensively.

While talking about extending the abilities of OpenOffice.org, it is important to mention that according to SUN, there will developmental builds for amd64 platforms starting very soon. Head over to GullFOSS for more information and news about openoffice.

I.Related articles

• Smooth the transition to OpenOffice.org [via Zemanta]
• Enhance OpenOffice.org with free extensions and templates [via Zemanta]
• New version of OpenOffice.org fixes critical bug [via Zemanta]
• Open Office 3.0 Dev [via Zemanta]

docx in openoffice revisited

Image via Wikipedia

From the pages of this blog, I wrote in the past about the ability of openoffice to open and save in the notorious docx (and xlsx and pptx) format the new MS Office 2007 introduced. My focus at the time was (and still is) how this can be done in various linux platforms. However, several people have asked about the possibility that Openoffice offers even for win32 platforms. So, here is, once again, a list of options (either web-based or platform-independent) for dealing with the mess that is docx (apparently, now an iso standard).

First, there are a couple of options that allow this translation from the web. You simply upload the file and get back the translated copy. I have not tried such options and I don’t know whether I would like to share with some other people personal files.

Zamzar online converter (all files). It allows for the translation of all types of files (docx, xlsx, pptx and more).

docx2doc online converter for docx2 files. Apparently, they offer a desktop conversion tool in partnership with Amazon. This tool can be found here: docXconverter, standalone, desktop conversion tool. Please be aware that this tool requires a fee.

Moving from web-choices to desktop ones, it should be said that the upcoming OpenOffice 3 will natively save (and open) docx, xlsx, pptx files. In the mean time, another option is to use Word 2003 viewer which can be downloaded from this site: word viewer. In order to use the new docx files, you will need this compatibility pack for older office versions and the job is done. Note, that you can install word viewer in linux under wine (why you would want to do this, though, is beyond me) 🙂 Also note, that the compatibility pack is necessary for office 97, 2000 etc.

If someone is still bound and tied to the MS office platform, this odf converter translator add-in for MS Office will make it smart enough to save and open odf files produced by OpenOffice. Of course, the option to use OpenOffice.org under Windows is also viable and should be promoted, as it offers enhanced capabilities to the end user.

Another indirect way from docx to odf may be via this conversion tool: docx2rtf. Docx2Rtf is a freeware file converter for MS Word 2007 and OpenOffice Sxw and Odt files. According to the site: “Docx2Rtf does not require Office 2007 or OpenOffice installed in order to work. Word 2007 docx files and OpenOffice files will be converted with formatting, but no images.” Understandably, this may not be what many people have in mind.

In the oooninja site, there are instructions and downloads in order to do the translation from docx to odf both under windows and linux using a cli (command line interface).

The key in both cases is the installation of an oxt file (an openoffice extension), which can be installed under the tools -> Extensions Manager. There are a couple of places where this oxt file can be downloaded from. First, from Novell or from go-oo.org.

A couple of interesting notes: Novell also produces its own version of open office, which incorporates this extension. Go-oo.org offer their own version of openOffice, with “… new features in development and […] functionality not yet accepted up-stream (from their website).

One last thing: If you get a docx, xlsx, pptx file via email, do what I do: return it to the sender and ask them to send you a check for so many $$ in order for you to buy a copy of MS Office. Also, inform them of the ability of OpenOffice and last, as a courtesy, ask them to be kind enough to use the File -> Save As choice and save their work as doc/xls/ppt files.

I.
PS. If this list is incomplete (and it most likely is), please let me know and I will add what’s missing.
PS2. If you like the article, please click the button below:

Related articles

• Office 2007 winning converts, survey says [via Zemanta]
• ODF wins the office document format war? [via Zemanta]
• Red Hat Summit panel: Who ‘won’ the OOXML battle? [via Zemanta]