Basic arithmetic operations and simple functions in Calc

    From The Document Foundation Wiki
    Other languages:

    Foreword

    The videos shows how "Basic arithmetic operations and simple functions in Calc - LibreOffice" can be done and links to this page here, which offers further information and details.

    Links to the Videos

    Link to the Video (in German [DE]):

    Grundrechenarten in Calc - LibreOffice

    Limk to the Video (in German [DE]):

    Einfache Funktionen in Calc - LibreOffice

    Basic arithmetic operations in Calc

    This video shows how that Basic arithmetics Overview in Calc can be applied (+, -, x, ÷).

    Open Calc and the file "Basic arithmetic operations in Calc V6.ods".

    Operators

    They are located in the "Basic arithmetic operations" table.

    The colored table shows the operators in the school view and how they are used in Calc.

    Basic arithmetics Overview in Calc

    Calculate values directly in the formula

    The number 40 is in cell E9.

    If you click in this cell, you see in the "Formula bar" the formula that calculates the displayed value.

    "=30+10" was entered in the cell, thus a formula with directly entered values.

    It is a simple addition of two numbers.

    Corresponding calculations of direct values with subtraction "= 30 - 10", multiplication "=30*10" and division "= 30 / 10" are contained in cells E10 to E12.

    Calculate values from other cells

    In cell G9 the same numbers are added, but with values from other cells.

    In the formula bar you now see the formula "= I11 + I9".

    Click above in the formula bar and the two reference cells are displayed blue-red marked.

    Leave the formula bar with Esc(ape).

    The cells G10 to G12 are again corresponding calculations "= I11 - I9", "= I11 * I9", "= I11 / I9" with values from other cells.

    Generate totals with the function “Sum”

    Sums can be calculated over several contiguous cells that have a number content.

    This can be done in a column or row.

    Place the cursor in cell I14 and click on the "Sum" symbol.

    The numbers above in I9 to I13 blue marked are displayed.

    Press ↵ Enter.

    The sum of the cells, I9 to I13, is displayed in I14.

    It should be noted that negative numbers, as in I13, are also calculated accordingly.

    Now place the cursor in N9 and click the ∑ symbol (sum) again.

    The numbers I9 to M9 to the left of it are shown in blue on the line.

    Press ↵ Enter.

    Copy sum formula

    In the next step you copy the sum formula in I14, to K14, with Ctrl + C and Ctrl + V.

    The sum of the fields K9 to K13 is now displayed in cell K14.

    Business rounding

    Alleged wrong rounding, example:

    One adds (or by sum formula)

    1,728 + 22,656 = 24,384

    which is correct.

    If we now format to 2 decimal places, i.e.

    1.73 + 22.66 results in 24.38 and not 24.39

    In principle correct, but still wrong.

    Calc calculates with the full decimal places and rounds accordingly.

    You can limit the number of decimal places.

    This changes the appearance of the numbers on the screen, not the value in the cell - in the background Calc still calculates with three decimal places.

    Then the result is 24.38, as described above.

    What is the correct way to do business rounding?

    You can round numbers correctly:

    Calc offers the function =ROUND() [ (=RUNDEN() in German ] with two parameters:

    • the first is the number to be rounded
    • the second is the number of digits

    If you now add up, Calc calculates 24.39 as desired.

    Example - Formulas
    Example - Values

    Further possibility

    In the menu Tools ▸ Options ▸ LibreOfficeCalc ▸ Calculate you can check the box "Precision as shown".

    You have to do this with all Calc documents in which you calculate with currency amounts, i.e. with money.

    This setting is not recommended, however, because in this case all tables in the document are affected, even where you do not want to round - so it is a rather radical method.

    "Options LibreOffice Calc Calculate" dialog

    Simple functions in Calc

    This video shows how simple functions can be used in Calc.

    Open Calc and the file "Simple functions in Calc V6.ods".

    Function assistent:

    Function assistent

    Function: CODE

    Click on the tab "F = CODE".

    Click in cell "B4" and see in the "Formula bar" the formula that leads to the displayed value.

    The "CODE" function supplies the code of the first character of a text.

    In this example, the first character from cell "C4" is an "M" from "Martha".

    Functions: ROMAN and ARABIC

    Below you select the tab "F = ROMAN and ARABIC".

    The function "ROMAN" converts a number into a Roman number.

    This is the case in cells "B4" and "C4".

    The "ARABIC" function calculates the value of a Roman number.

    You see this in cells "B6" and "C6".

    Function: DATEVALUE

    Click on the tab "DATE VALUE" and in the cell "B4".

    The function determines an internal number from a text in a possible date format.

    Cell "B4" shows the internal number for "04/01/2018".

    And cell "C4" shows the internal number for the "day zero" this is the "12/30/1899", so the default value in Calc.

    Function: ISBLANK

    Click on the tab "ISBLANK" and in the cell "B4".

    The "ISBLANK" function queries whether the specified cell is empty and displays "TRUE" if this is the case.

    Function: ISFORMULA

    Click on the tab "ISFORMULA" and in the cell "B4".

    The "ISFORMULA" function queries whether the specified cell is a formula cell and displays "TRUE" if this is the case.

    In this example, cell "C4" is a formula cell or a cell that contains a formula.

    Function: NOW

    Click on the tab "NOW" and in the cell "B4".

    The "NOW" function shows the current time of the computer.

    With "F9" the time in the cell is updated.

    The prerequisite is that the computer time has the correct time zone.

    With modern computers, the time is usually set via the Internet.

    Function: WEEKNUM

    Click on the ″WEEKNUM" tab and in cell "D4".

    The "WEEKNUM" function shows the calendar week for a given date.

    In this example, "D4" shows "13" for 04/01/2018.

    Insert a function (UPPER)

    Click on the "Insert function" tab.

    In the cell "C4" you write "anna".

    Insert into the one function by clicking on the function assistant in the calculation bar.

    The dialog "Function Assistant" opens and in the "Functions" tab you can select a function under "Function".

    Select the "UPPER" function by double-clicking on "UPPER".

    In the "Text" field you enter C4 and OK.

    The Function assistant in Calc offers a wealth of functions for the calculation.

    Take a look around

    Have a look around the functions and try out which functions can help you with the calculations in Calc.

    Further informations

    Working files

    File:"Basic arithmetic operations in Calc V6.ods"

    File:"Simple functions in Calc V6.ods"

    File:"7512xx_Calc Kaufmännisch runden.ods!

    Documentation / Manuals

    Here you will find the Documentation / Manuals:

    Documentation / Manuals

    Any questions?

    For questions on this topic go to:

    Ask.LibreOffice

    Get Involved

    Join us today and help us to make it even better!

    Get Involved

    LibreOffice is Free Software and is made available free of charge.

    Your donation, which is purely optional, supports our worldwide community.

    If you like the software, please consider a Donation.