Logic functions

column or row of a range.

ERROR(value)

Provides support for regular expressions.

When I started writing this article, I thought it would be a short note for beginners. But in the process of writing, I found quite a lot of information that is worth covering within this topic. As a result, the article grew to a fairly large size. And I hope that the material presented here will be useful not only to people starting their journey in using Calc, but also to people who have been using spreadsheets for a long time.

What are we going to talk about

Estimate of amount

If you just need to “estimate” the amount of a certain range, then LibreOffice Calc you can use the built-in feature. There is a field in the program status bar that says “Amount=0” by default. If you select cells on a sheet with numeric values, then instead of zero, this field will reflect the sum of these numbers. In order to select cells spaced apart from each other, or ranges of cells, you can select them by holding down the key Ctrl. The disadvantage of this method is that it only counts the amount from the current sheet. By right-clicking on the field, you will see the list shown in the screenshot.

I won’t discuss the list now, if you want, you can just experiment with it, and I think you’ll figure it out.

Sum

In LibreOffice Calc, the sum function has the same form as in others spreadsheets ah, such as Excel or Apache OpenOffice. Its syntax looks like this:

In this case, we simply list the numbers separated by semicolons. Enumeration of numbers can be explicit (1;2;3;...), can be references to cells (A1;C6;AZ190), or can be mixed (1; A1; C6;3). The function will "take out" a numeric value and use it.
You can use the function like this:

In this case, we must specify the first and last cell of the range. I would like to note, for people starting their journey in spreadsheets, if our range occupies more than one row and one column, then the beginning will be the top left cell, and the end will be the bottom right one. The formula will automatically determine the range rectangle and calculate the amount.
You can even use it like this:

I see the third method very rarely, but it is often convenient. Select the range, go to the main menu Data → Set Range… In the Name field, enter a name for the new range. Notice the “Headings in first row” and “Contains a total row” checkboxes, the first of which is checked by default. Don't forget to remove or mark them according to your situation. Click OK. Now you can substitute this name into formulas as an argument instead of selecting the range with the mouse.
Another way is to use column or row headings:

I see this method even less often, maybe because this feature is disabled by default. If you want to use it, go to the main menu Tools → Options and in the section LibreOffice Calc → Calculations Check the “Automatically detect column and row headers” checkbox. This method is useful when the range spans one column or row, but you don't know how large it will be. But you need to remember that the range should not be interrupted; for LibreOffice Calc, an empty row for the entire width or height of the range indicates that the range has ended, and everything that follows is another range. As of today (version 5.0.0), the header search functionality is not fully developed; for example, it does not support range dragging.
Although there are many methods, no one forbids using all methods at the same time, just do not forget that when specifying a range between the reference to the first and last cell in Russian localization, a colon is placed by default, and a semicolon is placed between function arguments. And I think there is no point in reminding that links can be not only to ranges in the current sheet, but also to other sheets, and even to other files, no matter local computer they are located or somewhere online.

Autosum in LibreOffice Calc

AutoSum in LibreOffice Calc can autosum only a contiguous range of a column or row. At the same time, it will search for a range vertically up and horizontally to the left. But since it essentially just inserts a SUM formula with a range as an argument and leaves it open for editing, this function is often handy, especially if you prefer to work with the mouse. Its use is the same as in other modern spreadsheet programs. The formula bar has an icon with the Greek letter sigma (Σ). Clicking this icon activates AutoSum.
Let's look at an example with a vertical range of two columns:

By selecting the cell under the second column and clicking the sum button, we see that the function found a continuous range in the column, but did not determine full range, and remained in the editing state. The screenshot shows small blue squares at the corners of the range frame. If you drag these squares with the mouse, you can change the range of values ​​involved in the calculations of the formula. That is, if we need to make sure that two columns are involved in the calculations, then we can pull the upper or lower left square to the left to include the second column. Also, we can reduce the vertical range if not the entire vertical range should be included in our calculations. Naturally, we can edit the formula directly, as we did before, no one will prohibit us from doing this. For horizontal ranges everything will look exactly the same. When you are satisfied with the range, press Enter on your keyboard, or press the green checkmark on the formula bar.

SUM as an array formula and summation by condition

In the help for the program (go to the main menu Help → LibreOffice Help, or press the key F1) or on the online help site you can find an example of a non-standard use of the SUM function. In my opinion, this is a very successful example, and I would like to retell it to you in my own words.
Let's say we have a table: in its first column there are dates, and in the second - our expenses for this day, because we can make such a table, it will last us for 2845 years :). But we want to know how much we spent in a particular month, or week, or year. That is, we need to sum up expenses for a certain period. I'm attaching a file with an example so you can see how it all works. And here I will explain everything in words. This is what the table looks like:

In cell I2 the formula is written

SUM((A3:A300>=F1)*(A3:A300<=F2)*B3:B300)

The first part A3:A300>=F1 is the condition above which date the numbers will be summed, the second part A3:A300<=F2 — ниже какой даты. A3 — это начало диапазона дат, A300 — это конец диапазона, для примера 300 даже много, в реальности это будет предполагаемая ячейка, докуда вы хотите вести свой диапазон. Перемножая эти две части мы получаем матрицу истинности, где те числа, которые соответствуют необходимым датам будут равны 1, а остальные 0. В третьей части мы имеем массив с расходами и естественно, если мы умножаем на ноль, то в ответе будет ноль, а если на единицу, то значение будет равно значению ячейки. Таким образом мы получаем матрицу с нулями и необходимыми значениям. То есть, если это всё представить в развернутом виде, то будет примерно так SUM(0;0;…;0;отобранные значения; 0; 0; …; 0) . Для того чтобы эта формула начала работать, мы должны её сделать формулой массива, для этого, вместо обычного нажатия Enter we need to press the combination Ctrl+Shift+Enter. A sign that you did everything correctly will be enclosing the formula in curly brackets
(=SUM((A3:A300>=F1)*(A3:A300<=F2)*B3:B300)}

Do not set the brackets yourself - this will not help, you must complete the entry of the formula correctly. If you use automatic search of headings in columns and rows, then this formula can be rewritten as follows:

SUM((("Date")>=F1)*(("Date")<=F2)*("Расходы"))

Having completed the introduction Ctrl+Shift+Enter, we will get the same result, but we won’t have to worry about the size of the range, and most importantly, it’s easy to change it and automatically get an updated result. The effect of this example, searching across multiple criteria, can also be achieved using the SUMIFS formula. Let me give you another example. In early 2011, bug report 35636 was opened due to the fact that the SUMIF formula did not accept empty ("") as a condition. This error was corrected only in April 2015. One way around this problem was to use the SUM formula as an array formula, like this:

SUM((Range_to_sum)*(Range_to_check=""))

I think it is clear that in the second parameter a truth matrix will be created, multiplying by which all unnecessary values ​​​​turns to zero, and, as a result, we get the sum of the values ​​​​corresponding to empty cells.

Automatic range expansion when adding a cell to the end

LibreOffice Calc, when adding a cell to the middle of a range, automatically expands it in the formula parameter. But if we sum these cells at the end of the range, for example, using atomic sum, and then try to add a cell to the end of the summed range, Calc will not include it in the sum. To get around this limitation, you can use the OFFSET function to refer to the end of the range. I will not dwell on this function in detail, I will only provide its syntax and explanations necessary for this example.

A reference is the cell or range of cells from which the offset begins. Lines is the offset in lines from the reference, positive numbers will move down, negative numbers will move up. Columns - column offset from the link, positive to the right, negative to the left. The remaining parameters are not important to us. In our example, the link is the cell in which the summation formula is written. Offset in rows we point to the last cell in the summed range. Columns, in this example, we do not touch. As a result, we get a formula like this:

SUM(A2:OFFSET(A6,-1,0))

Using automatic search by column or row headings does not work quite as expected. You cannot place a formula close to a range. In addition, you must add a row or cell not from the formula bar, but from an empty cell between the formula and the range. As you understand, this is a flaw that does not bring the expected result and does not allow you to use the tool intuitively. For our example, the formula will look like this:

Cumulative total amount

The cumulative total is one of the most frequently solved problems, especially in investment project models. It can be done in different ways, but in my opinion the most convenient way is to fix part of the range in the SUM function and stretch it. Let's say we have a cash flow - money given out for pocket expenses to a child (unplanned operating expenses :)

In cell B4 we enter the formula, indicating the boundaries of the range B3:B3, and fix the first part by clicking Shift+F4 on the keyboard (when the cursor is positioned on the first part): =SUM($B$3:B3) When dragging, the first cell indicating the beginning of the range will remain in place, and the second (end of the range) will automatically change, forming the sum of all previous values.

Summation of 3D ranges (end-to-end summation)

When a number of sheets in our spreadsheet workbook have the same layout, we can use a three-dimensional range to sum, sometimes called "end-to-end sum" on forums. For three sheets, adding 3 cells A1 looks like this:

SUM(Sheet1.A1:Sheet3.A1)

If we add the ranges, it will look like this:

SUM(Sheet10.A1:B2:Sheet11.A1:B2)
Please note that the sheets for which the summation is being carried out must be side by side. The leftmost sheet should be listed first in the formula, and the rightmost sheet of the range of sheets to be summed last.

SUMIF - summation by condition

If we need to add numbers that meet any given condition, then the easiest way is to use the SUMIF() function. The syntax of the function is as follows:

SUMIF(Range_for_condition; Condition; Sum_range)

The range for the condition is the range in which we will look for values ​​​​and compare them with the condition. The condition is the condition itself. By the way, it supports regular expressions, which significantly expands the possibilities of using this formula. The summation range is the range from which a sample will be summed according to a given condition. A simple example, let’s say there is a range with cash flow, positive numbers are profits, negative numbers are expenses, you need to find out how much came in, that is, add only positive numbers from the range:

SUMIF("Cash Flow",">0","Cash Flow")

Or the very popular question “how to fold empty cells” (this question always baffles me :). The point is that we look for empty cells in one range, and in the second we add their corresponding values:

SUMIF(Range to search;"";Sum range)

As you understand, we can set a range in all possible ways: search by title, named range, simply indicating the boundaries of the range, even if the range is in another file somewhere on the Internet.

Using regular expressions in a condition

Search by condition in LibreOffice Calc supports regular expressions. Writing regular expressions is different from writing them in Excel and is slightly different from the usual way of writing them in the shell. Regular expressions are discussed in detail on the help page, and I also touched on them when I talked about using the VLOOKUP and HLOOKUP functions.
Most often you have to count the amount of something of one type (for example, balances, receipts or sales of pencils, notebooks). Let's make a simple table, with headings Product and Quantity, for the name of the goods and their quantity, respectively.

The task is to count pencils (27 pieces, but we don’t count them in our heads, we count them using formulas :). So, from the above it is clear that we need to use regular expressions in the condition. The common piece of text for all pencil names is the word pencil. And since we have characters before and after the common fragment, we must indicate this. The regular expression will look like this: ".*pencil.*" . A dot (.) here will mean any character, an asterisk (*) any number of characters. The dot-asterisk order is mandatory; if you forget to put a dot, an error will be thrown, this is the specificity of regular expressions in Calc. The formula for such a table would look like:

SUMIF(A2:A6;".*pencil.*";B2:B6)

A more advanced method would be a regular expression with a link to the cell in which the keyword will be specified (entered manually, or selected from the list). Let's assume that the desired value is specified in cell D1:

SUMIF(A2:A6;".*"&D1&".*";B2:B6)

One note. Currently, there is a bug 93510, which does not allow the use of regular expressions in conjunction with the automatic search of headings in columns and rows if the range consists of words. Regardless of the values ​​in the sum range, the function always returns 0. This situation most likely occurs because LibreOffice Calc does not prioritize headers correctly. You can work around this error by having a blank line between the header and the beginning of the values, or a more elegant method is to use merged cells for the headers.

SUMIFS - search by many conditions

Unlike the SUMIF function, the SUMIFS function allows you to specify from one to 30 conditions. Its syntax:

SUMIFS(Sum_range;Range_for_condition1; Condition1;...;Range_for_condition30; Condition30)

I think you understand that the summation range will contain a range of values ​​from which we want to add numbers. The condition range is where we will look. The condition and summation ranges can be the same or different. The condition is what we are looking for (don't forget to enclose the expression in straight quotes (""). The formula allows for an excessive number of conditions, I think 30 ranges and conditions are enough for all occasions. The example I gave when I talked about SUM as an array formula can be rewritten like this:

SUMIFS("Expenses";"Date";">=""Date";"<="&F2)

SUBTOTAL - sum of visible cells or subtotals

Sometimes there is a need to calculate the sum of selected (visible) cells using an autofilter. The easiest way in LibreOffice Calc, in my opinion, is to use the SUBTOTAL() function. This feature allows you to do a lot more, but for the purposes of this article I will only show the amount. The rest of its functionality can be found in the help. Let's say we have a large table in which we make a selection and want to get the sum. For example, we’ll take a fairly simple option:

Of course we can use SUMIF, SUMIFS or even an array formula, but if we are using filters, then the easiest way is to use SUBTOTAL. Its syntax looks like this:

SUBTOTAL(Function_number; Range)

The function number is the number to which the function used for the range is assigned. For our case, it is 9. Range is the range to which you want to apply the function. In our case, this is the range for summation. As a result, the formula entered in cell B10 will look like this:

SUBTOTAL(9;B2:B9)

If you are adding rows to a range, you can use the advice above for the SUM formula to automatically expand the range when you add a cell to the end. Now, when we filter the required rows, their sum will appear in the cell with the formula. The only note: do not forget to leave a tick on the “empty” item, otherwise the result itself will be hidden.
It's not stated in the help, but it actually works: when we use SUBTOTAL without a filter, the function with the codes specified in the help does not exclude hidden rows from the calculation. To exclude them, add 10 before the function code. That is, for the sum, code 9 will not exclude hidden rows, but 109 will. After hiding, recalculate the result by pressing Ctrl+Shift+F9. Please note that the SUBTOTAL function does not include cells containing preliminary totals in the calculation (the same function). Another way to solve these problems would be to use the AGGREGATE function.

AGGREGATE - summation, skipping hidden cells and cells with errors

The previous example showed how to sum a range, excluding hidden cells, but there are situations when you also need to exclude cells with errors or with the SUBTOTAL and AGGREGATE functions itself. The AGGREGATE function has even more capabilities than SUBTOTAL. Below I give a description of it, but since some parameters of this function are not related to the topic of this article, I will not touch on them. And since, unfortunately, it is still not described in the help, if you need to clarify something about it, then it would be better to use Excel help for now (well, we should at least receive something useful from MS Corporation :) .
For the purposes of this article, we are interested in the following function syntax:

AGGREGATE(Function_number; Parameters; Range)

function_number is the number to which the function used for the range is assigned. For the purposes of this article, this is number 9—sum.
Parameter is a numeric value that determines which values ​​should be skipped during the calculation. All parameters are numbered from 1 to 7. The following list shows the number and a brief description of the function:

• 0 or omitted—skip nested SUBTOTAL and AGGREGATE functions.
• 1 - skip hidden rows and nested SUBTOTAL and AGGREGATE functions.
• 2 - skip error values ​​and nested functions SUBTOTAL and AGGREGATE .
• 3 - skip hidden rows, error values, and nested SUBTOTAL and AGGREGATE functions.
• 4 - don't skip anything.
• 5 - skip only hidden lines.
• 6 - skip only error values.
• 7 - skip hidden lines and error values.

Further reading and sources:

• Calc section on the Forum supporting users of open office suites
• Special thanks to Rami for

Spreadsheet functionis a predefined calculation.

Calc has the advanced analysis, charting, and decision-making capabilities expected of high-quality spreadsheets. It includes more than 300 functions, including financial, statistical and mathematical operations.

In the environment Openoffice.org Calc there are many functions. It is worth dividing them into 12 sections:

1. Mathematical functions

2. Text functions

3. Logic functions

4. Date and time functions

5. Database functions

6. Number conversion functions

7. Information functions

8. Spreadsheet Functions

9. Functions of complex numbers

10. Static functions

11. Financial functions

12. Array functions

The most popular in spreadsheets are mathematical functions, which are divided into arithmetic, trigonometric, hyperbolic and logarithmic.

Below is a table of commonly used mathematical functions Calc:

Also, they are widely used logical functions.

Below is a table of logical functions Calc:

Let's look at a few examples of using simple functions.

In order not to write functions into cells manually, there is Function Wizard.

Rice. 9. Function Wizard in Openoffice.org Calc.

Let's look at its step-by-step application using a simple example of using a mathematical function ABS.

Rice. 10. Input window.

Rice. 11. Using the Function Wizard.

3. To any of the selected red fields must be entered number, cell address, or simply select a cell/range of cells left mouse button. Next, press the button OK(yellow highlight). In our case, in the cell B1 the function itself will be executed, and the value will be taken from the cell A1.

Rice. 12. Using the Function Wizard.

4. The result of executing the function will be the following window:

Rice. 13. Result of the ABS function.

Now let's look at the use logical functions using the example of a function like IF.

We will need knowledge of several mathematical functions.

First, let's make captions for future columns:

Cell A1 - Number 1;

Cell B1 - Number 2;

Cell C1 - Amount;

Cell D1 - IF Function.

1. Cells A2:A12 - numbers from -5 to 5;

2. Cells B2:B12 - random numbers from 1 to 5 (for simplicity, we will use the mathematical function RANDBETWEEN, it will turn out to be a recording=RANDBETWEEN(1;5), then stretch the formula to the remaining cells);

Suppose you have two numbers in cells<В1>And<С2>and want to show
the sum of both of them in a cell<А1>
Do the following:

1. Place the cursor in a cell<А1>and press the "=" key. The Formula panel appears automatically. On the far left side of the panel you can see the coordinates of the selected cell.
2. Click in a cell<В1>. The ID of this cell is automatically displayed in the formula bar and inserted into the cell<А1>.
3. Click on the "+" key.
4. Click on a cell<С2>. You can see the final formula "=sum +" displayed in both the selected cell and the formula bar.
5. Press the Enter key.

A template is a template that you use to create other documents. For example, you can create a business template that has your company logo on the first page. All new documents created using this template will have your company logo on the first page.

Using templates to create a document:

1. From the main menu, select File > New > Templates and Documents.
2. A dialog box will open Templates and Documents.
3. In the box on the left, select the Templates icon if it is not already selected. A list of template folders appears in the center field.
4. Open ( double click) folder containing the template you want to use. A list of all templates contained in this folder appears in the central field.
5. Select the template you want to use.

To create a template from a document:

1. Open a new one or existing document, from which you want to make a template.
2. Add the content and styles you want.
3. From the main menu, select File > Templates > Save. The Templates dialog box opens.
4. In field New template Enter a name for the new template.
5. In the Categories list, select the category to which you want to assign a template. Category is simply the template folder in which you want to save the template.
6. Click OK. Writer will save the new template.

To create a template using the wizard:

1. From the main menu select File > Wizard > type of template required
2. Follow the commands on the wizard pages. The process is slightly different for each template type, but the format is similar for all of them.
3. IN last section wizard, you can specify a name and location to save the template. The default location is your custom templates directory, but you can choose a different location as you prefer.
4. Finally, you have the option of creating a new document from your template immediately, or manually changing the template.

Compound documents are typically used to create large documents such as a book, dissertation, or long report.

A master document is especially useful when you use a lot of graphics, spreadsheets, or other material that takes up a lot of space and makes the document's file size very large.

A composite document is also used when different people write different chapters or other parts of a complete document.

A composite document combines the contents of individual documents into one larger document, and results in consistent formatting, table of contents, bibliography, index, and other tables and lists.

Fields are an extremely useful feature of Writer. They are used for data that can change in a document, such as The current date or total number of pages. To quickly turn field shading on or off: View >Shading Fields or press Ctrl+F8 . You can quickly insert the most commonly used fields into your document by using Insert > Fields from the menu bar and selecting the required field from the list.

Writer provides several places where you can store information referenced by fields. We'll look at some of them here.

Seven document properties (Time, Date, Title, Subject, Author, Page Number, and Number of Pages) are found in the Insert > Fields menu. To insert one of these fields, click on it in the menu.
Other document properties are located on the Document Information and Document tabs of the Fields dialog box, accessed via Insert > Fields > Advanced.

Although these fields are often used to store changeable information, you can make the content permanent by selecting the Commit content when inserting a field check box. If necessary, you can return to this dialog later and uncheck this checkbox to make the field editable again.

Here are some handy keyboard commands to use when working with fields:
Ctrl+F2 Open the Fields dialog box.
Ctrl+F8 Enable/disable field shading.
Ctrl+F9 Show/hide field names.
F9 Update fields.

51 Note. The annual interest rate is divided by 12, since compound interest is calculated monthly. For example, the monthly payment on a four-year loan is 10,000 54 rubles. at 12 percent per annum will be 263.33 rubles. As the value of the payout argument, you need to enter the number -263.33 into the formula. Cross-check shows that 48 x 500 rubles + 20,000 rubles = 44,000 rubles. The difference between this amount and the invested amount equal to RUB 35,000 is the interest paid. last parameter are optional, you can leave parameters 3 and 4 or parameter 4 empty. It is not possible to leave parameter 3 empty. Example 1 After how many months will a deposit of 1000 rubles be will reach a value of 10,000 rubles if the annual interest rate on the deposit is 12% and accrual is made monthly. Every month a payment of 100 rubles is made. Payments are made at the beginning of each month. , which is currently equivalent to a number of future BS payments (optional): The required value of the future value or balance of funds after the last payment. If the BC argument is omitted, then it is assumed to be equal to 0 (for example, the BC for a loan is 0) Type (optional): payment period at the beginning or end of the period. The due date is the coupon expiration date. For example, suppose a 30-year bond was issued on January 1, 2008 and was purchased by the buyer six months after its issue. The issue date will be January 1, 2008, the agreement date will be July 1, 2008, and the maturity date of such bond will be January 1, 2038, that is, a date 30 years after the issue date.