Using Formula for Numbers (Addition, Subtraction, Multiplication & Division)

A formula is an expression that calculates the value of a cell. Functions are predefined formulas and are already available in LibreOffice Calc.

You can enter formulas in two ways, either by using the function wizard or by typing directly into the cell or the input line. All formulas begin with an equal sign. When typing in directly, you need to start a formula with one of the symbols: =, +, or -.

Operators in Formulas-

The formula in calc is like mathematical equations, you can use a combination of numbers, cell references, and operators to perform simple to complex calculations. There is the following operator used in LibreOffice calc.

Operator

Name

Example

+

Addition

=1+1

-

Subtraction

=5-2

-

Negation

=-12

*

Multiplication

=6*8

/

Division

=45/9

%

Percentage

25%

^

Exponentiation

2^3

 Order of operation: the order of operation in calc follows the same rules you learned in math class. A common technique for remembering the order of operation or calculation is the abbreviation (or, more properly, the “acronym”) “PEMDAS”.

  • P operations enclosed in parentheses
  • E exponential calculation (3^2, for example)
  • Md multiplication and division, whichever comes first
  • As addition and subtraction, whichever comes first.

For example, the formula 3^2*2+1 is evaluated in the following order.

3^2*2+1 #3^2

9*2+1 # 9*2

18+1

19

The avoid mistake in formulas it’s a good practice to use parenthesis when you are not certain about the order of operation or calculations.

Concatenation operator

It is common for users to place text in spreadsheets. a lot of times we need to join pieces of text found in different cells. for this purpose calc has the concatenation operator & in the following example text in the argument, columns are concatenated to the result columns.

Notice that when you use text in formulas you must surround it with quotation marks.

Here are a few examples of LibreOffice calc formulas:

=a1+10

Displays the content of cell a1 plus 10

=a1*16%

Displays 16% of the content of a1

=a1 * a2

Displays 16% of the content of a1

=rounds(a1,1)

Displays the content of cells a1 rounded to one decimal place

=effect (5%,12)

Calculate the effective interest for 5% annual nominal interest with 12 payments a year.

=bb-sum (b10: b14)

Calculate b8minus the sum of the cells b10 to b14

=sum (b8; sum (b10: b14))

Calculate the sums of cells b10 to b14 and adds the value to b8.

It is also possible to nest functions in formulas, as shown in the above example. You can also nest a function within a function the function wizard assists you with nested functions.

There are the following function categories available in the Function Wizard-

  1. Text Function
  2. Math Function
  3. Date & Time Function
  4. Database Function
  5. Logical Function

Text functions

Left: This function is used to display the specified number of characters from the left side of a text string. =left(“Bella”,3)

 Right: this function is used to display the specified numbers of characters from the right side of a text string.

Mid: this function is used to display the specified numbers of characters from the middle of a text string, given a starting position and length.

Len: this function is used to display the length of a text string (number of characters in a text string with scape) spaces are counted as characters.

Proper: this function is used to convert the first letter of each word in a text string to the upper case just like the title case and the remaining letter to lower case.

Rept: function is used to repeat the given text to a specified number of times.

Lower: function is used to convert all upper case in the text string to lower case.

Upper: function is used to convert all lower-case letters in a text string to uppercase.

Math functions

Sum (range of cells): it is used to add the cells’ value or range of cells.

Round: it is used for rounding the number to a specified number of digits.

Roundup: it round a number up, away from 0.

Round down: it round a number down; toward 0. = round down (2345.5678,2). Output 2345.56.

Sumif is used to sum the value according to the condition. This function requires three fields as given below:

For example

Range (cell range from which criteria would search)

Criteria (the value that you want to sum)

Sum range (cell range from where the similar value would be sum)

Item

Price

 

soap

25

Tea

270

Soap

25

coffee

300

Tea

350

Total

970

 

Product: this multiplies given numbers.

Power: it returns the result of a number raised to a given power.

Mod: this function returns the remainder of a division.

Trunc: - it truncates a number to an integer by removing the decimal or fractional, part of the number.

Statistical functions:

Count: it is used to count the numeric value in the given cell range.

Count: it counts the number of cells in a range that is not empty.

Countif: - it is used to count the number of cells within a range that meet the given condition.

Minimum: function is used to give the minimum value in the given range of cells. =min(a1:d5)

Maximum: this function is used to give the maximum value in the given range of cells. = max(a1:d5)