Welcome to the Spoken Tutorial on Formulas and Functions in LibreOffice Calc.
In this tutorial, we will learn about: Conditional Operator If..Or statement Basic statistic functions Rounding off numbers.
Here, we are using Ubuntu Linux version 10.04 as our operating system and LibreOffice Suite version 3.3.4.
We have already learnt to apply the basic arithmetic operators like addition, subtraction and average on data.
Now, let’s learn about a few other useful operators.
One of the most commonly used operator is the Conditional Operator.
Conditional Operators: check for the condition applied on the data by the user
and then show results in Boolean - TRUE or FALSE.
Let’s open “Personal-Finance-Tracker.ods”.
Here, under the heading “Cost”, we have listed the prices of several items.
Let’s apply conditional operators on them and analyze the results.
Let’s click on the cell referenced as “B10” and type “Condition Result” inside it.
Now, click on the cell referenced as “C10”.
The condition’s result will be applied and displayed in this cell.
Note that the cost of the “House Rent” is rupees 6,000.
The cost for the “Electricity Bill” is rupees 800.
The cost of “House Rent” is more than that of “Electricity Bill”.
We can apply different conditions on them and check the results.
Click on the cell referenced as “C10”.
In this cell, type the first condition as: is equal to C3 greater than C4 and press the Enter key.
Since the value in cell C3 is greater than the value in cell 'C4', the result we get is “TRUE”.
Now, let us change this conditional statement to: is equal to C3 less than C4.
Press Enter.
The result we get is FALSE.
In the same manner, you can apply other conditional statements and study the results.
These statements are very useful when dealing with large amounts of data.
You can also use the If and Or condition on data
to print the results according to the condition that holds TRUE.
Let’s click on the cell referenced as “C10” and type:
is equal to IF and within braces, C3 greater than C4 comma, within double quotes “Positive” comma and again within double quotes “Negative”.
This means, if the value in cell C3 is greater than the value in cell C4, “Positive” will be displayed
or else “Negative” will be displayed.
Now, press Enter.
Notice that the result is “Positive” since rupees 6000 is greater than rupees 800.
Now, in the conditional statement, let’s change “greater than” to “less than” and press the Enter key.
Note that the result is now “Negative”, as the value in cell C3 is greater than the value in cell C4.
You can also see the change in result, if we change the data in the cells C3 and C4.
The result which is displayed now is Negative.
Now, let us increase the value in cell C4 to “7000” and press the Enter key.
The result automatically changes to “Positive”.
Again, let’s decrease the value in cell C4 to “800”
and press the Enter key.
The result again automatically changes to “Negative”.
Now, let us delete the changes made.
Next, let’s learn a few arithmetic and statistic functions.
Basic arithmetic functions include:SUM for addition,PRODUCT for multiplication,QUOTIENT for division and many more which we have already learnt in the earlier tutorials.
Now, let’s perform some operations to check how the Sum, Product and the Quotient functions work.
First, let’s select “Sheet 3”.
Enter the numbers “50”, ”100” and ”150” within the cells referenced “B1”, “B2” and “B3” respectively.
Click on the cell A4 and type SUM.
Click on the cell B4.
We shall compute the result in this cell.
Type: is equal to “SUM” and within the braces B1 comma B2 comma B3.
Press Enter.
Notice the result shows “300”.
You can also enter a range of cells like this.
Click on B4 again.
Now, within the braces, instead of B1 comma B2 comma B3, type B1 colon B3
Press Enter.
Once again, the result shows “300”.
Now, let’s click on the cell “A5” and type: “PRODUCT”.
Click on the cell “B5”.
Here, type: is equal to “PRODUCT” and within the braces, B1 colon B3.
Press Enter.
Notice, the result shows “7,50,000”.
Now, let’s see how Quotient works.
Click on the cell referenced “A6” and type: “QUOTIENT”.
Now click on the cell “B6”.
We shall use this cell to compute the result.
And type: is equal to QUOTIENT and within the braces, B2 comma B1.
Press Enter.
You will get the result as “2”. That is because “100” divided by “50” gives 2.
Similarly, we can perform various arithmetic operations in Calc.
Now, let’s learn how to implement Statistic Functions.
Statistical functions are useful for analysis of data in spreadsheets.For example-functions like COUNT, MIN, MAX, MEDIAN, MODE and many more are statistical in nature.
First, let us click on sheet 1.
Let’s see how to find the minimum, the maximum and the median costs, using statistical functions.
Let’s click on the cell referenced as “C10” where we will be displaying the result.
Under the heading “Cost”, we have very few entries.
The minimum cost is rupees 300.
The maximum cost is rupees 6000.
These are the results which should be displayed when we use their functions.
In the cell “C10”, let’s type: is equal to MAX and within braces C3 colon C7.
Now, press the Enter key.
Notice that the result is “6000” which is the maximum value in the column.
Now, let’s replace the term “MAX” in the statement with “MIN”
and press the Enter key.
Note that the result is “300” which is the minimum amount in the Cost column.
To find the median value, replace the term “MIN” with “MEDIAN”
and press the Enter key.
The result shows “800” which is the median cost in the column.
Similarly, you can use other statistical functions on data and analyze them accordingly.
Let us delete the changes in this cell.
Now, let’s learn how to round off numbers.
Let us make few changes under the heading- “Cost”.
We shall change:“6000” to “6000.34”“600” to “600.4”, ”300” to “300.3”.
Now, click on the cell referenced as “B11” and type the heading “ROUNDING OFF”.
Click on the cell referenced as “C11” where we will find the total of the items under the heading “Cost”.
In the cell C11, let’s type: is equal to SUM and within braces C3 colon C7.
Now, press the Enter key.
Notice that the total is “9701.04”.
Now, suppose, we don’t want any decimal places in our result.
The best solution is to round off the result to the nearest whole number.
Let us click on the cell with the total “9701.04”.
Type: is equal to ROUND, open brace SUM and again within braces C3 colon C7.
Close the brace. Press the Enter key.
You see that the result is now “9701” which is “9701.04”, rounded off to the nearest whole number.
Rounding off can also be done to either the lower whole number or the higher number.
Let’s click on the cell with the result and edit the term “ROUND” to “ROUNDUP”.
Now, press the Enter key.
You see that the result is now “9702” which is the higher whole number.
In order to round off to the lower whole number, change the term “ROUNDUP” to “ROUNDDOWN”
and press the Enter key.
The result is now “9701” which is the lower whole number.
Let us undo these changes in order to get our “Personal-Finance-Tracker.ods” to its original form.
This brings us to the end of this Spoken Tutorial on LibreOffice Calc.
To summarize, we learned about: Conditional Operator If..Or statement Basic statistic functions Rounding off numbers.
Watch the video available at the following link.
It summarizes the Spoken Tutorial project.
If you do not have good bandwidth, you can download and watch it.
The Spoken Tutorial project team:
Conducts workshops using spoken tutorials.
Gives certificates for those who pass an online test.
For more details, please write to:contact at spoken hyphen tutorial dot org.
Spoken Tutorial project is a part of the Talk to a Teacher project.
It is supported by the National Mission on Education through ICT, MHRD, Government of India.
More information on this mission is available at:
spoken hyphen tutorial dot org slash NMEICT hyphen Intro.
This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd. Thanks for joining.