Another program that comes installed in the Ubuntu 13.10 standard install is LibreOffice Calc.
As you probably know, the functionality of spreadsheets has grown tremendously in the past few years. Features added have included sorting and filtering data, text and number formatting, and even data processing functions built into your standard spreadsheet program. LibreOffice Calc is strong in all of these areas. We will cover some of those areas for the purpose of comparison to other, more costly, spreadsheets available to the home office computer user.
Let’s start with the customizable toolbars. The buttons that can be made visible contain features such as alignment, text and cell coloring, insert and delete, merge, styles, and many more text formatting options. As a sample we will create a spreadsheet demonstrating some of these formats.
One nice feature of this spreadsheet that has not happened in earlier spreadsheet versions is automatic height adjustment of the rows based on the size of the font. In the next shot you will see a couple of tables. This table has been sorted using the numbers in row 2 and row 1 as a secondary sort, both ascending.
This is the same table using row 1 (descending) as the primary sort column and row 2 (ascending) as the secondary sort. Notice that quite a few of the names are underlined; even your spreadsheet performs spell check!!
Now let’s do some filtering on that same table. First, we will filter for the “2” items in row 1.
Next let’s do a little more complex filtering. We will view only the names that contain an “r.”
As with sorts, filtered data can use multiple criteria. Are you impressed yet with LibreOffice Calc? If the answer to that is no, you will be after looking at some of the functions designed to make your business day more efficient. Would you care to know how much your expensive equipment will depreciate for each period of its useful life? Use the DB() function.
I am not a financial wizard. Since you also may not be, I will explain the numbers shown above. You have a piece of equipment that costs $239,000 and it has an expected life of ten years. The salvage value at the end of that decade is $27,000. In the 8th month of use you will depreciate $3,788.36 while you will depreciate $616.03 for the final month before entering its last year of depreciation.
After the complexity of that last function we will go with a few easy guys – countif, lcm, and trunc.
What is all of this? (you may be thinking). The first function in the right column counted all of the numbers from the left column that were greater than 30. That would be three. The really large number displayed below that calculated the least common multiple of all of those numbers. If only a few of the numbers had been selected the result would have been much less. For example, the LCM of 12,8, and 40 would be 120. Truncate would normally be used to truncate a number of decimal places. When you use a negative number, as it is here, it truncates that number of digits. In this example we truncated 46 with a -1 to return 40.
For the final function example we will use DSUM. This will sum all values that match a certain criteria. Let’s say that we want to know how many pounds of apples and bananas we have sold along with the average weight of grapes. We will use a table such as the example below.
The average for the grapes used the TRUNC(DAVERAGE(),2) function to bring it down to two decimals.
Have you become familiar with Microsoft Excel, Appleworks, or maybe even Google Spreadsheets? Is your wallet becoming thinner because of spending so much money on software programs? You could use LibreOffice Calc just as efficiently and not have to worry about the cost. If you are worried about file type compatibility, there is no need. This file that we have been working on was saved as XLSX, emailed, and opened in Excel 2010 without any hiccups.
(note that there are more screenshots but articles are limited to five, the full article is available in my blog at http://www.linkemup.us/support-articles