LibreOffice-Suite-Calc/C3/Formulas-and-Functions/English-timed

From Script | Spoken-Tutorial
Revision as of 15:37, 9 July 2014 by Pratik kamble (Talk | contribs)

Jump to: navigation, search

Resources for recording Formulas and Functions



Time Narration
00:00 Welcome to the Spoken Tutorial on Formulas and Functions in LibreOffice Calc.
00:07 In this tutorial we will learn about:
  • Conditional Operator
  • If..Or statement
  • Basic statistic functions
  • Rounding off numbers
00:19 Here we are using Ubuntu Linux version 10.04 as our operating system and LibreOffice Suite version 3.3.4.


00:30 We have already learnt to apply the basic arithmetic operators like addition,subtraction and average on data.
00:39 Now, let’s learn about a few other useful operators.
00:43 One of the most commonly used operator is the Conditional Operator.
00:51 Conditional Operators,

check for the condition applied on the data by the user

00:56 and then show results in boolean - TRUE or FALSE.
1:01 Let’s open “Personal-Finance-Tracker.ods”.
1:05 Here, under the heading “Cost”, we have listed the prices of several items.
1:11 Let’s apply conditional operators on them and analyse the results.
1:17 Let’s click on the cell referenced as “B10” and type “Condition Result” inside it.
1:24 Now, click on the cell referenced as “C10”.
1:28 The condition’s result will be applied and displayed in this cell.
1:33 Note, that the cost of “House Rent” is rupees 6,000.
1:38 The cost for the “Electricity Bill” is rupees 800.
1:43 The cost of “House Rent” is more than that of “Electricity Bill”.
1:48 We can apply different conditions on them and check the results.
1:54 Click on the cell referenced as “C10”.
1:57 In this cell, type the first condition as “is equal to C3 greater than C4 ” and press the “Enter” key.
2:09 Since the value in cell C3 is greater than the value in cell C4, the result we get is “TRUE”.
2:18 Now let us change this conditional statement to “is equal to C3 less than C4”
2:26 Press “Enter”.
2:28 The result we get is “FALSE”.
2:32 In the same manner, you can apply other conditional statements and study the results.
2:38 These statements are very useful when dealing with large amounts of data.
2:44 You can also use the “If and Or” condition on data
2:49 * to print the results
  • according to the condition
  • that holds TRUE.
2:55 Let’s click on the cell referenced as “C10” and type,
2:59 “ is equal to IF” and within braces, “C3 greater than C4” comma, within double quotes “Positive” comma and again within double quotes “Negative”.
3:16 This means if the value in cell C3 is greater than the value in cell C4, “Positive” will be displayed
3:25 or else “Negative” will be displayed..
3:28 Now press “Enter”.
3:31 Notice, that the result is “Positive” since rupees 6000 is greater than rupees 800.
3:39 Now, in the condition statement let’s change “greater than” to “less than” and press the “Enter” key.
3:47 Note, that the result is now “Negative”, as the value in cell C3 is greater than the value in cell C4.
3:57 You can also see the change in result, if we change the data in the cells C3 and C4.
4:04 The result which is displayed now is “Negative”.
4:09 Now, let us increase the value in cell C4 to “7000” and press the “Enter” key.
4:17 The result automatically changes to “Positive”.
4:22 Again, let’s decrease the value in cell C4 to “800”.
4:26 And press the “Enter” key.
4:29 The result again automatically changes to “Negative”.
4:34 Now, let us delete the changes made.
4:38 Next, let’s learn a few arithmetic and statistic functions.
4:43 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.
4:57 Now let’s perform some operations to check how the Sum, Product and the Quotient functions work.
5:05 First let’s select “Sheet 3”.
5:08 Enter the numbers “50”,”100” and ”150” within the cells referenced “B1”, “B2” and “B3” respectively.
5:19 Click on the cell “A4” and type “SUM”.
5:23 Click on the cell “B4”.
5:26 We shall compute the result in this cell.
5:30 Type “is equal to “SUM”, and within the braces, B1 comma B2 comma B3.
5:37 Press Enter.


5:39 Notice the result shows “300”.
5:43 You can also enter a range of cells like this
5:47 Click on “B4” again.
5:49 Now, within the braces, instead of B1 comma B2 comma B3, type B1 colon B3
5:58 Press Enter.


6:00 Once again, the result shows “300”
6:03 Now let’s click on the cell “A5” and type “PRODUCT”.
6:08 Click on the cell “B5”.
6:10 Here type “is equal to “PRODUCT”, and within the braces, B1 colon B3.
6:18 Press Enter.
6:20 Notice the result shows “7,50,000”.
6:26 Now let’s see how Quotient works.
6:29 Click on the cell referenced “A6” and type “QUOTIENT”.
6:34 Now click on the cell “B6”.
6:37 We shall use this cell to compute the result.
6:40 And type “is equal to QUOTIENT”, and within the braces, B2 comma B1.
6:47 Press Enter.
6:49 You will get the result as “2”. That is because “100” divided by “50” gives 2.
6:59 Similarly, we can perform various arithmetic operations in Calc.
7:05 Now, let’s learn how to implement Statistic Functions.
7:09 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.
7:27 First, let us click on sheet 1.
7:30 Let’s see how to find the minimum, the maximum and the median costs, using statistical functions.
7:37 Let’s click on the cell referenced as “C10” where we will be displaying the result.
7:44 Under the heading “Cost”, we have very few entries.
7:48 The minimum cost is rupees 300.
7:51 The maximum cost is rupees 6000.
7:55 These are the results which should be displayed when we use their functions.
8:00 In the cell “C10” let’s type “is equal to MAX” and within braces “C3” colon “C7”.
8:10 Now press the “Enter” key.
8:13 Notice, that the result is “6000”, which is the maximum value in the column.
8:20 Now, let’s replace the term “MAX” in the statement with “MIN”.
8:25 And press the “Enter” key.
8:28 Note, that the result is “300” which is the minimum amount in the Cost column.
8:34 To find the median value, replace the term “MIN” with “MEDIAN”.
8:40 And press the “Enter” key.
8:43 The result shows “800”, which is the median cost in the column.
8:50 Similarly, you can use other statistical functions on data and analyze them accordingly.
8:58 Let us delete the changes in this cell.
9:02 Now, let’s learn how to round off numbers.
9:05 Let us make few changes under the heading, “Cost”.
9:09 We shall change:

“6000” to “6000.34”

“600” to “600.4” ”300” to “300.3”.

9:23 Now, click on the cell referenced as “B11” and type the heading “ROUNDING OFF”.
9:31 Click on the cell referenced as “C11” where we will find the total of the items under the heading “Cost”.
9:39 In the cell C11 let’s type “is equal to SUM” and within braces “C3” colon “C7”.
9:49 Now, press the Enter key.
9:53 Notice, that the total is “9701.04”.
9:59 Now suppose we don’t want any decimal places in our result.
10:04 The best solution is to round off the result to the nearest whole number.
10:09 Let us click on the cell with the total “9701.04”.
10:15 Type “is equal to ROUND”,open brace “SUM” and again within braces “C3” colon “C7”.
10:25 Close the brace. Press the Enter key.
10:29 You see, that the result is now “9701”, which is “9701.04” rounded of to the nearest whole number.
10:44 Rounding off, can also be done to either the lower whole number or the higher number.
10:52 Let’s click on the cell with the result and edit the term “ROUND” to “ROUNDUP”.
10:59 Now, press the “Enter” key.
11:02 You see that the result is now “9702” which is the higher whole number.
11:10 In order to round off to the lower whole number, change the term “ROUNDUP” to “ROUNDDOWN”
11:17 And press the “Enter” key.
11:19 The result is now “9701” which is the lower whole number.
11:28 Let us undo these changes in order to get our “Personal-Finance-Tracker.ods” to its original form.
11:37 This brings us to the end of this Spoken Tutorial on LibreOffice Calc.
11:43 To summarize, we learned about:
  • Conditional Operator
  • If..Or statement
  • Basic statistic functions
  • Rounding off numbers
11:55 Watch the video available at the following link
11:58 It summarises the Spoken Tutorial project
12:01 If you do not have good bandwidth, you can download and watch it
12:06 The Spoken Tutorial Project Team
12:08 Conducts workshops using spoken tutorials.
12:11 Gives certificates for those who pass an online test
12:15 For more details, please write to contact at spoken hyphen tutorial dot org
12:21 Spoken Tutorial Project is a part of the Talk to a Teacher project
12:26 It is supported by the National Mission on Education through ICT, MHRD, Government of India
12:34 More information on this Mission is available at
12:37 spoken hyphen tutorial dot org slash NMEICT hyphen Intro
12:45 This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd

Thanks for joining

Contributors and Content Editors

Minal, PoojaMoolya, Pratik kamble, Sandhya.np14