{"id":6943,"date":"2024-08-12T03:00:00","date_gmt":"2024-08-12T07:00:00","guid":{"rendered":"https:\/\/www.both.org\/?p=6943"},"modified":"2024-08-07T18:38:38","modified_gmt":"2024-08-07T22:38:38","slug":"libreoffice-calc-functions-to-make-your-work-easier","status":"publish","type":"post","link":"https:\/\/www.both.org\/?p=6943","title":{"rendered":"LibreOffice Calc functions to make your work easier"},"content":{"rendered":"
\r\n
\r\n \r\n <\/i>\r\n <\/a>\r\n <\/span>\r\n<\/div><\/div>\n

LibreOffice is an outstanding open source office suite. I especially love the LibreOffice Calc spreadsheet, which is a powerful alternative to proprietary spreadsheets.<\/p>\n\n\n\n

I find all kinds of uses for spreadsheets. As an undergraduate physics student, years ago, I relied on spreadsheets every day to analyze lab data. Today, I use spreadsheets for all kinds of things. I\u2019m a consultant, and I use spreadsheets to track my budget and expenses. I teach university courses, and I use spreadsheets to calculate and assign grades. I also use spreadsheets to plan major purchases.<\/p>\n\n\n\n

If you know the right function to apply, spreadsheets like LibreOffice Calc make it easy to analyze and reduce data. Here are the LibreOffice Calc functions that I use all the time to get my work done.<\/p>\n\n\n\n

Columns and rows<\/h2>\n\n\n\n

Before I show the functions I use the most, it might help to start with a few notes about how spreadsheets track data.<\/p>\n\n\n\n

All spreadsheets since the days of VisiCalc (1979) use letters<\/em> for columns and numbers<\/em> for rows. You address each cell with a combination of a letter and number, such as A1<\/code> for the cell in column A and row 1.<\/p>\n\n\n\n

To specify a range<\/em> of values, you need to give the cell address for the start<\/em> and end<\/em> of the range. For example, to indicate the first 5 cells in the first two columns, you would write A1:B5<\/code> because the \u201cupper left\u201d end of the range starts in cell A1<\/code> and the range ends in the \u201clower right\u201d in cell B5<\/code>.<\/p>\n\n\n\n

If I\u2019m going to work on data in rows and columns, I\u2019ll use AutoFill to repeat a calculation into adjacent cells. For example, I might have a set of values in column A, and I want to perform calculations with those numbers in column B. For B1<\/code>, I might use =A1*2<\/code> to double the value. \u201cDragging\u201d the block in the lower-right corner of B1<\/code> allows me to use AutoFill to do the same calculation for each cell in column B.<\/p>\n\n\n\n

But to reference a range<\/em> of values, I might not want AutoFill to \u201ctranslate\u201d these references for me. That\u2019s when I find it helpful to \u201clock off\u201d a column or row reference with the $<\/code> prefix operator<\/em>. For example, to always access the range A1:B5<\/code>, even when using AutoFill, I can \u201clock\u201d the row references with $<\/code> like A$1:B$5<\/code> so AutoFill will continue to access the same range when filling to adjacent rows. To \u201clock\u201d column references, use $<\/code> before the column reference, such as $A1:$B5<\/code> so AutoFill will access the same range when filling to adjacent columns.<\/p>\n\n\n\n

You can also use named ranges<\/em>, but I find the $<\/code> is enough for most of what I do in spreadsheets. You\u2019ll see this come up in several of my examples.<\/p>\n\n\n\n

Basic calculations<\/h2>\n\n\n\n

I use these basic functions the most to analyze data. The MIN<\/code> function returns the smallest value in a range; similarly, the MAX<\/code> function returns the largest value. You can use SUM<\/code> to add the values in a range, and COUNT<\/code> to return the number of items<\/em> in a range. In theory, you could divide SUM<\/code> by COUNT<\/code> to get the average, but it\u2019s easier and more reliable to use AVERAGE<\/code>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Applying conditions<\/h2>\n\n\n\n

Sometimes, it\u2019s helpful to perform a calculation on a range while ignoring certain data. That\u2019s when these \u201cIf\u201d functions become super useful. SUMIF<\/code> adds the values in a range, depending on a condition. COUNTIF<\/code> will count the items, depending on a condition. And AVERAGEIF<\/code> calculates the average, depending on a condition.<\/p>\n\n\n\n

You enter the range as you would with SUM<\/code> or COUNT<\/code> or AVERAGE<\/code>, and provide the condition in quotes. For example, if you only wanted to consider values above zero, enter \">0\"<\/code> for the condition.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

At other times, you might only need to compare a value. That\u2019s where the logical functions become useful. The IF<\/code> function performs a test and returns a true or false value. For example, =IF(A1>0)<\/code> returns TRUE<\/code> if the value in cell A1<\/code> is a number that\u2019s greater than zero, and FALSE<\/code> otherwise.<\/p>\n\n\n\n

You can combine tests with the AND<\/code> and OR<\/code> functions. These are functions<\/em>, not operators. That means you can\u2019t combine comparisons with AND<\/code>; instead, you need to use AND<\/code> as a function. Include each test as separate function arguments.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Random numbers<\/h2>\n\n\n\n

Spreadsheets help to model data. But data doesn\u2019t have to be static, you can provide a \u201cfuzz factor\u201d by introducing a random value. Use the RAND<\/code> function to generate a random number from 0 to 1, although the random number never quite gets to 1. The random number will change every time you update the spreadsheet, such as entering data in a new cell.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Rounding numbers<\/h2>\n\n\n\n

When I model random values using RAND<\/code>, I usually multiply the random number by some other number to get a random value in a range, such as =RAND()*10<\/code> to get a number from 1 to 10. At the same time, I usually don\u2019t want the stuff after the decimal point. For that, I need to cut off the extra digits.<\/p>\n\n\n\n

Spreadsheets provide two handy functions to do that: INT<\/code> will return just the number before the decimal point, creating an integer<\/em> value. ROUND<\/code> will round a number to a certain number of decimal places. For example, use =ROUND(A1,1)<\/code> to round the value in A1<\/code> to just one decimal place; use 0 to generate an integer value.<\/p>\n\n\n\n

One thing to remember with ROUND<\/code> is it applies the standard rules about rounding numbers: if the next number is less than five, it \u201crounds down\u201d – if it\u2019s 5 or more, it \u201crounds up.\u201d A calculation like =ROUND(3.12,1)<\/code> will round the value to 3.1, because the first decimal place gets \u201crounded down.\u201d But a calculation like ROUND(3.89,1)<\/code> will give 3.9, because the 9 in the next decimal place means the 8 gets \u201crounded up.\u201d<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Table lookups<\/h2>\n\n\n\n

The table lookup feature in spreadsheets is a huge time-saver. Tables can make it easy to transform one value into another, depending on values stored in columns or rows.<\/p>\n\n\n\n

I use vertical lookups to calculate final grades when I teach classes. I start by defining a lookup table, with the data sorted in ascending order. In the first column, I give the \u201cbottom\u201d value of each grade level, such as 0% for F, 60% for D, 70% for C, 80% for B, and 90% for A. In the second column, I list the corresponding letter grade.<\/p>\n\n\n\n

To find values with VLOOKUP<\/code>, give the value to locate in the table, then the range for the full table, and the column (counting from 1 for the first column in the table) that holds the result that you want to return from the lookup. If your data is sorted in the table, also give a TRUE<\/code> value as the fourth option.<\/p>\n\n\n\n

For example, here is a sample gradebook calculation that looks up the letter grade for five students:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

A shortcut to vertical lookups is the LOOKUP<\/code> function. To look up values with this simpler function, give the value to locate in the sorted table, then the range of the first column (the column with the number values) and the range of the second column (the column with the result).<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If your data is in columns instead of rows, you can use the HLOOKUP<\/code> function to do a horizontal lookup. The usage is otherwise the same as VLOOKUP<\/code>: start with the value to find in the table, then the range of the full table, and the column number with the result. If the data is sorted in the table, give a TRUE<\/code> value in the fourth option.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Let the spreadsheet do the work<\/h2>\n\n\n\n

Spreadsheets are an invaluable tool to work with data. Don\u2019t worry about memorizing every possible function<\/em> in the spreadsheet; most people tend to stick to a few functions that help them get their work done. I find these 17 functions will help me tackle most data that I need to work with.<\/p>\n\n\n\n

Use this as a starting point to explore the other spreadsheet functions available in LibreOffice Calc. Tap the F1 key in LibreOffice Calc to enter the Help system, and click on the link to list functions by category. Or visit the Functions by Category<\/a> page on the LibreOffice.org website for a full reference.<\/p>\n","protected":false},"excerpt":{"rendered":"

Here are the LibreOffice Calc functions that I use all the time to get my work done.<\/p>\n","protected":false},"author":33,"featured_media":4259,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_lmt_disableupdate":"","_lmt_disable":"","footnotes":""},"categories":[237],"tags":[133],"class_list":["post-6943","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-libreoffice","tag-libreoffice"],"modified_by":"Jim Hall","_links":{"self":[{"href":"https:\/\/www.both.org\/index.php?rest_route=\/wp\/v2\/posts\/6943","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.both.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.both.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.both.org\/index.php?rest_route=\/wp\/v2\/users\/33"}],"replies":[{"embeddable":true,"href":"https:\/\/www.both.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6943"}],"version-history":[{"count":2,"href":"https:\/\/www.both.org\/index.php?rest_route=\/wp\/v2\/posts\/6943\/revisions"}],"predecessor-version":[{"id":6957,"href":"https:\/\/www.both.org\/index.php?rest_route=\/wp\/v2\/posts\/6943\/revisions\/6957"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.both.org\/index.php?rest_route=\/wp\/v2\/media\/4259"}],"wp:attachment":[{"href":"https:\/\/www.both.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6943"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.both.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6943"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.both.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6943"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}