Difference between revisions of "LibreOffice-Suite-Calc/C3/Formulas-and-Functions/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
 
(8 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
'''Resources for recording'''
 
'''Resources for recording'''
 
[[Media:Formulas and Functions.zip |Formulas and Functions]]
 
[[Media:Formulas and Functions.zip |Formulas and Functions]]
 
 
 
  
  
Line 12: Line 9:
 
|-
 
|-
 
||00:00
 
||00:00
||Welcome to the Spoken Tutorial on Formulas and Functions in LibreOffice Calc.  
+
||Welcome to the '''Spoken Tutorial''' on '''Formulas and Functions''' in '''LibreOffice Calc'''.  
  
 
|-
 
|-
 
||00:07
 
||00:07
||In this tutorial we will learn about:
+
||In this tutorial, we will learn about: '''Conditional Operator''' '''If..Or statement''' '''Basic statistic   functions''' '''Rounding off numbers'''.
 
+
* Conditional Operator
+
 
+
* If..Or statement
+
 
+
* Basic statistic functions
+
 
+
* Rounding off numbers  
+
  
 
|-
 
|-
 
||00:19
 
||00:19
||Here we are using Ubuntu Linux version 10.04 as our operating system and LibreOffice Suite version 3.3.4.
+
||Here, we are using '''Ubuntu Linux''' version '''10.04''' as our '''operating system''' and '''LibreOffice Suite''' version '''3.3.4'''.
 
+
  
 
|-
 
|-
 
||00:30
 
||00:30
||We have already learnt to apply the basic arithmetic operators like addition,subtraction and average on data.  
+
||We have already learnt to apply the basic arithmetic operators like addition, subtraction and average on data.  
  
 
|-
 
|-
Line 41: Line 29:
 
|-
 
|-
 
||00:43
 
||00:43
||One of the most commonly used operator is the Conditional Operator.
+
||One of the most commonly used operator is the '''Conditional Operator'''.
  
 
|-
 
|-
 
||00:51
 
||00:51
||Conditional Operators,
+
||Conditional Operators: check for the condition applied on the data by the user
 
+
check for the condition applied on the data by the user
+
  
 
|-
 
|-
 
||00:56
 
||00:56
||and then show results in boolean - TRUE or FALSE.
+
||and then show results in Boolean - '''TRUE''' or '''FALSE'''.
  
 
|-
 
|-
||1:01
+
||01:01
||Let’s open “Personal-Finance-Tracker.ods”.  
+
||Let’s open “'''Personal-Finance-Tracker.ods'''”.  
  
 
|-
 
|-
||1:05
+
||01:05
||Here, under the heading “Cost”, we have listed the prices of several items.
+
||Here, under the heading “'''Cost'''”, we have listed the prices of several items.
  
 
|-
 
|-
||1:11
+
||01:11
||Let’s apply conditional operators on them and analyse the results.  
+
||Let’s apply conditional operators on them and analyze the results.  
  
 
|-
 
|-
||1:17
+
||01:17
||Let’s click on the cell referenced as “B10” and type “Condition Result” inside it.  
+
||Let’s click on the '''cell''' referenced as “B10” and type “'''Condition Result'''” inside it.  
  
 
|-
 
|-
||1:24
+
||01:24
||Now, click on the cell referenced as “C10”.
+
||Now, click on the '''cell''' referenced as “'''C10'''”.
  
 
|-
 
|-
||1:28
+
||01:28
 
||The condition’s result will be applied and displayed in this cell.  
 
||The condition’s result will be applied and displayed in this cell.  
  
 
|-
 
|-
||1:33
+
||01:33
||Note, that the cost of “House Rent” is rupees 6,000.
+
||Note that the cost of the “House Rent” is rupees 6,000.
  
 
|-
 
|-
||1:38
+
||01:38
||The cost for the “Electricity Bill” is rupees 800.  
+
||The cost for the “'''Electricity Bill'''” is rupees 800.  
  
 
|-
 
|-
||1:43
+
||01:43
||The cost of “House Rent” is more than that of “Electricity Bill”.
+
||The cost of “'''House Rent'''” is more than that of “'''Electricity Bill'''”.
  
 
|-
 
|-
||1:48
+
||01:48
 
||We can apply different conditions on them and check the results.  
 
||We can apply different conditions on them and check the results.  
  
 
|-
 
|-
||1:54
+
||01:54
 
||Click on the cell referenced as “C10”.  
 
||Click on the cell referenced as “C10”.  
  
 
|-
 
|-
||1:57
+
||01:57
||In this cell, type the first condition as “is equal to C3 greater than C4 and press the “Enter” key.
+
||In this cell, type the first condition as: '''is equal to C3 greater than C4 ''' and press the '''Enter''' key.
  
 
|-
 
|-
||2:09
+
||02:09
||Since the value in cell C3 is greater than the value in cell C4, the result we get is “TRUE”.  
+
||Since the value in cell '''C3''' is greater than the value in cell 'C4', the result we get is '''“TRUE”'''.  
  
 
|-
 
|-
||2:18
+
||02:18
||Now let us change this conditional statement to “is equal to C3 less than C4”
+
||Now, let us change this conditional statement to: '''is equal to C3 less than C4'''.
  
 
|-
 
|-
||2:26
+
||02:26
||Press “Enter”.
+
||Press '''Enter'''.
  
 
|-
 
|-
||2:28
+
||02:28
||The result we get is “FALSE”.
+
||The result we get is '''FALSE'''.
 
 
 
|-
 
|-
||2:32
+
||02:32
 
||In the same manner, you can apply other conditional statements and study the results.
 
||In the same manner, you can apply other conditional statements and study the results.
  
 
|-
 
|-
||2:38
+
||02:38
 
||These statements are very useful when dealing with large amounts of data.  
 
||These statements are very useful when dealing with large amounts of data.  
  
 
|-
 
|-
||2:44
+
||02:44
||You can also use the “If and Or” condition on data
+
||You can also use the '''If''' and '''Or''' condition on data
  
 
|-
 
|-
||2:49
+
||02:49
||* to print the results
+
|| to print the results according to the condition that holds '''TRUE'''.  
 
+
* according to the condition  
+
 
+
* that holds TRUE.  
+
  
 
|-
 
|-
||2:55
+
||02:55
||Let’s click on the cell referenced as “C10” and type,
+
||Let’s click on the cell referenced as '''“C10”''' and type:
  
 
|-
 
|-
||2:59
+
||02:59
||is equal to IF” and within braces, “C3 greater than C4” comma, within double quotes “Positive” comma and again within double quotes “Negative”.  
+
||'''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
+
||03:16
||This means if the value in cell C3 is greater than the value in cell C4, “Positive” will be displayed
+
||This means, if the value in cell '''C3''' is greater than the value in cell '''C4''', “Positive” will be displayed
  
 
|-
 
|-
||3:25
+
||03:25
||or else “Negative” will be displayed..  
+
||or else “'''Negative'''” will be displayed.  
  
 
|-
 
|-
||3:28
+
||03:28
||Now press “Enter”.  
+
||Now, press '''Enter'''.  
  
 
|-
 
|-
||3:31
+
||03:31
||Notice, that the result is “Positive” since rupees 6000 is greater than rupees 800.  
+
||Notice that the result is “Positive” since rupees 6000 is greater than rupees 800.  
  
 
|-
 
|-
||3:39
+
||03:39
||Now, in the condition statement let’s change “greater than” to “less than” and press the “Enter” key.  
+
||Now, in the conditional statement, let’s change “greater than” to “less than” and press the '''Enter''' key.  
  
 
|-
 
|-
||3:47
+
||03:47
||Note, that the result is now “Negative”, as the value in cell C3 is greater than the value in cell C4.  
+
||Note that the result is now “Negative”, as the value in cell '''C3''' is greater than the value in cell '''C4'''.  
  
 
|-
 
|-
||3:57
+
||03:57
||You can also see the change in result, if we change the data in the cells C3 and C4.  
+
||You can also see the change in result, if we change the data in the cells '''C3''' and '''C4'''.  
  
 
|-
 
|-
||4:04
+
||04:04
||The result which is displayed now is “Negative”.  
+
||The result which is displayed now is '''Negative'''.  
  
 
|-
 
|-
||4:09
+
||04:09
||Now, let us increase the value in cell C4 to “7000” and press the “Enter” key.  
+
||Now, let us increase the value in cell '''C4''' to “7000” and press the '''Enter''' key.  
  
 
|-
 
|-
||4:17
+
||04:17
 
||The result automatically changes to “Positive”.  
 
||The result automatically changes to “Positive”.  
  
 
|-
 
|-
||4:22
+
||04:22
||Again, let’s decrease the value in cell C4 to “800”.
+
||Again, let’s decrease the value in cell '''C4''' to “800”
  
 
|-
 
|-
||4:26
+
||04:26
||And press the “Enter” key.  
+
||and press the '''Enter''' key.  
  
 
|-
 
|-
||4:29
+
||04:29
 
||The result again automatically changes to “Negative”.  
 
||The result again automatically changes to “Negative”.  
  
 
|-
 
|-
||4:34
+
||04:34
 
||Now, let us delete the changes made.  
 
||Now, let us delete the changes made.  
  
 
|-
 
|-
||4:38
+
||04:38
 
||Next, let’s learn a few arithmetic and statistic functions.  
 
||Next, let’s learn a few arithmetic and statistic functions.  
  
 
|-
 
|-
||4:43
+
||04:43
||Basic arithmetic functions include
+
||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.  
 
+
* SUM for addition,
+
 
+
* PRODUCT for multiplication,
+
 
+
* QUOTIENT for division and
+
+
* many more which we have already learnt in the earlier tutorials.  
+
  
 
|-
 
|-
||4:57
+
||04:57
||Now let’s perform some operations to check how the Sum, Product and the Quotient functions work.  
+
||Now, let’s perform some operations to check how the '''Sum, Product''' and the '''Quotient''' functions work.  
  
 
|-
 
|-
||5:05
+
||05:05
||First let’s select “Sheet 3”.  
+
||First, let’s select “Sheet 3”.  
  
 
|-
 
|-
||5:08
+
||05:08
||Enter the numbers “50”,”100” and ”150” within the cells referenced “B1”, “B2” and “B3” respectively.  
+
||Enter the numbers “50”, ”100” and ”150” within the cells referenced “B1”, “B2” and “B3” respectively.  
  
 
|-
 
|-
||5:19
+
|05:19
||Click on the cell “A4” and type “SUM”.  
+
||Click on the cell '''A4''' and type '''SUM'''.  
  
 
|-
 
|-
||5:23
+
||05:23
||Click on the cell “B4”.
+
||Click on the cell '''B4'''.
  
 
|-
 
|-
||5:26
+
||05:26
 
||We shall compute the result in this cell.  
 
||We shall compute the result in this cell.  
  
 
|-
 
|-
||5:30
+
||05:30
||Type “is equal to “SUM”, and within the braces, B1 comma B2 comma B3.
+
||Type: '''is equal to “SUM”''' and within the braces '''B1 comma B2 comma B3'''.
  
 
|-
 
|-
||5:37
+
||05:37
||Press Enter.
+
||Press '''Enter'''.
 
+
  
 
|-
 
|-
||5:39
+
||05:39
 
||Notice the result shows “300”.
 
||Notice the result shows “300”.
  
 
|-
 
|-
||5:43
+
||05:43
||You can also enter a range of cells like this  
+
||You can also enter a range of cells like this.
  
 
|-
 
|-
||5:47
+
||05:47
||Click on “B4” again.
+
||Click on '''B4''' again.
  
 
|-
 
|-
||5:49
+
||05:49
||Now, within the braces, instead of B1 comma B2 comma B3, type B1 colon B3
+
||Now, within the braces, instead of '''B1 comma B2 comma B3''', type '''B1 colon B3'''
  
 
|-
 
|-
||5:58
+
||05:58
||Press Enter.
+
||Press '''Enter'''.
 
+
  
 
|-
 
|-
||6:00
+
||06:00
||Once again, the result shows “300”  
+
||Once again, the result shows “300”.
  
 
|-
 
|-
||6:03
+
||06:03
||Now let’s click on the cell “A5” and type “PRODUCT”.
+
||Now, let’s click on the cell “A5” and type: “PRODUCT”.
  
 
|-
 
|-
||6:08
+
||06:08
 
||Click on the cell “B5”.  
 
||Click on the cell “B5”.  
  
 
|-
 
|-
||6:10
+
||06:10
||Here type “is equal to “PRODUCT”, and within the braces, B1 colon B3.
+
||Here, type: '''is equal to “PRODUCT”''' and within the braces, '''B1 colon B3'''.
  
 
|-
 
|-
||6:18
+
||06:18
||Press Enter.  
+
||Press '''Enter'''.  
  
 
|-
 
|-
||6:20
+
||06:20
||Notice the result shows “7,50,000”.  
+
||Notice, the result shows “7,50,000”.  
  
 
|-
 
|-
||6:26
+
||06:26
||Now let’s see how Quotient works.  
+
||Now, let’s see how '''Quotient''' works.  
  
 
|-
 
|-
||6:29
+
||06:29
||Click on the cell referenced “A6” and type “QUOTIENT”.  
+
||Click on the cell referenced “A6” and type: “QUOTIENT”.  
  
 
|-
 
|-
||6:34
+
||06:34
 
||Now click on the cell “B6”.  
 
||Now click on the cell “B6”.  
  
 
|-
 
|-
||6:37
+
||06:37
||We shall use this cell to compute the result.
+
||We shall use this '''cell''' to compute the result.
  
 
|-
 
|-
||6:40
+
||06:40
||And type “is equal to QUOTIENT”, and within the braces, B2 comma B1.
+
||And type: '''is equal to QUOTIENT''' and within the braces, '''B2 comma B1'''.
  
 
|-
 
|-
||6:47
+
||06:47
||Press Enter.  
+
||Press '''Enter'''.  
  
 
|-
 
|-
||6:49
+
||06:49
 
||You will get the result as “2”. That is because “100” divided by “50” gives 2.
 
||You will get the result as “2”. That is because “100” divided by “50” gives 2.
  
 
|-
 
|-
||6:59
+
||06:59
||Similarly, we can perform various arithmetic operations in Calc.
+
||Similarly, we can perform various arithmetic operations in '''Calc'''.
  
 
|-
 
|-
||7:05
+
||07:05
 
||Now, let’s learn how to implement Statistic Functions.  
 
||Now, let’s learn how to implement Statistic Functions.  
  
 
|-
 
|-
||7:09
+
||07:09
||Statistical functions are useful  
+
||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.  
 
+
* for analysis of data in spreadsheets.
+
 
+
* For example,
+
 
+
* functions like COUNT, MIN, MAX, MEDIAN, MODE
+
 
+
* and many more are statistical in nature.  
+
  
 
|-
 
|-
||7:27
+
||07:27
||First, let us click on sheet 1.
+
||First, let us click on '''sheet 1'''.
 
 
 
|-
 
|-
||7:30
+
||07:30
 
||Let’s see how to find the minimum, the maximum and the median costs, using statistical functions.  
 
||Let’s see how to find the minimum, the maximum and the median costs, using statistical functions.  
  
 
|-
 
|-
||7:37
+
||07:37
 
||Let’s click on the cell referenced as “C10” where we will be displaying the result.  
 
||Let’s click on the cell referenced as “C10” where we will be displaying the result.  
  
 
|-
 
|-
||7:44
+
||07:44
 
||Under the heading “Cost”, we have very few entries.  
 
||Under the heading “Cost”, we have very few entries.  
  
 
|-
 
|-
||7:48
+
||07:48
 
||The minimum cost is rupees 300.  
 
||The minimum cost is rupees 300.  
  
 
|-
 
|-
||7:51
+
||07:51
 
||The maximum cost is rupees 6000.
 
||The maximum cost is rupees 6000.
  
 
|-
 
|-
||7:55
+
||07:55
 
||These are the results which should be displayed when we use their functions.  
 
||These are the results which should be displayed when we use their functions.  
  
 
|-
 
|-
||8:00
+
||08:00
||In the cell “C10” let’s type “is equal to MAX” and within braces “C3” colon “C7”.  
+
||In the cell “C10”, let’s type: '''is equal to MAX''' and within braces '''C3 colon C7'''.  
  
 
|-
 
|-
||8:10
+
||08:10
||Now press the “Enter” key.  
+
||Now, press the '''Enter''' key.  
  
 
|-
 
|-
||8:13
+
||08:13
||Notice, that the result is “6000”, which is the maximum value in the column.  
+
||Notice that the result is “6000” which is the maximum value in the column.  
  
 
|-
 
|-
||8:20
+
||08:20
||Now, let’s replace the term “MAX” in the statement with “MIN”.
+
||Now, let’s replace the term “MAX” in the statement with “MIN”
  
 
|-
 
|-
||8:25
+
||08:25
||And press the “Enter” key.  
+
||and press the '''Enter''' key.  
  
 
|-
 
|-
||8:28
+
||08:28
||Note, that the result is “300” which is the minimum amount in the Cost column.  
+
||Note that the result is “300” which is the minimum amount in the '''Cost''' column.  
  
 
|-
 
|-
||8:34
+
||08:34
||To find the median value, replace the term “MIN” with “MEDIAN”.
+
||To find the median value, replace the term “MIN” with “MEDIAN”
  
 
|-
 
|-
||8:40
+
||08:40
||And press the “Enter” key.  
+
||and press the '''Enter''' key.  
  
 
|-
 
|-
||8:43
+
||08:43
||The result shows “800”, which is the median cost in the column.  
+
||The result shows “800” which is the '''median cost''' in the column.  
  
 
|-
 
|-
||8:50
+
||08:50
 
||Similarly, you can use other statistical functions on data and analyze them accordingly.  
 
||Similarly, you can use other statistical functions on data and analyze them accordingly.  
  
 
|-
 
|-
||8:58
+
||08:58
 
||Let us delete the changes in this cell.  
 
||Let us delete the changes in this cell.  
  
 
|-
 
|-
||9:02
+
||09:02
||Now, let’s learn how to round off numbers.
+
||Now, let’s learn how to '''round off''' numbers.
  
 
|-
 
|-
||9:05
+
||09:05
||Let us make few changes under the heading, “Cost”.  
+
||Let us make few changes under the heading- “Cost”.  
  
 
|-
 
|-
||9:09
+
||09:09
||We shall change:
+
||We shall change:“6000” to “6000.34”“600” to “600.4”, ”300” to “300.3”.  
 
+
“6000” to “6000.34”
+
 
+
“600” to “600.4” ”300” to “300.3”.  
+
  
 
|-
 
|-
||9:23
+
||09:23
 
||Now, click on the cell referenced as “B11” and type the heading “ROUNDING OFF”.  
 
||Now, click on the cell referenced as “B11” and type the heading “ROUNDING OFF”.  
  
 
|-
 
|-
||9:31
+
||09:31
 
||Click on the cell referenced as “C11” where we will find the total of the items under the heading “Cost”.  
 
||Click on the cell referenced as “C11” where we will find the total of the items under the heading “Cost”.  
  
 
|-
 
|-
||9:39
+
||09:39
||In the cell C11 let’s type “is equal to SUM” and within braces “C3” colon “C7”.
+
||In the cell '''C11''', let’s type: '''is equal to SUM''' and within braces '''C3 colon C7'''.
  
 
|-
 
|-
||9:49
+
||09:49
||Now, press the Enter key.  
+
||Now, press the '''Enter''' key.  
  
 
|-
 
|-
||9:53
+
||09:53
||Notice, that the total is “9701.04”.
+
||Notice that the total is “9701.04”.
  
 
|-
 
|-
||9:59
+
||09:59
||Now suppose we don’t want any decimal places in our result.
+
||Now, suppose, we don’t want any decimal places in our result.
  
 
|-
 
|-
Line 465: Line 425:
 
|-
 
|-
 
||10:15
 
||10:15
||Type “is equal to ROUND”,open brace “SUM” and again within braces “C3” colon “C7”.
+
||Type: '''is equal to ROUND''', open brace '''SUM''' and again within braces '''C3 colon C7'''.
  
 
|-
 
|-
 
||10:25
 
||10:25
||Close the brace. Press the Enter key.  
+
||Close the brace. Press the '''Enter''' key.  
  
 
|-
 
|-
 
||10:29
 
||10:29
||You see, that the result is now “9701”, which is “9701.04” rounded of to the nearest whole number.
+
||You see that the result is now “9701” which is “9701.04”, rounded off to the nearest whole number.
  
 
|-
 
|-
 
||10:44
 
||10:44
||Rounding off, can also be done to either the lower whole number or the higher number.  
+
||Rounding off can also be done to either the lower whole number or the higher number.  
  
 
|-
 
|-
 
||10:52
 
||10:52
||Let’s click on the cell with the result and edit the term “ROUND” to “ROUNDUP”.
+
||Let’s click on the cell with the result and '''edit''' the term “ROUND” to “ROUNDUP”.
  
 
|-
 
|-
 
||10:59
 
||10:59
||Now, press the “Enter” key.  
+
||Now, press the '''Enter''' key.  
  
 
|-
 
|-
Line 497: Line 457:
 
|-
 
|-
 
||11:17
 
||11:17
||And press the “Enter” key.  
+
||and press the '''Enter''' key.  
  
 
|-
 
|-
Line 505: Line 465:
 
|-
 
|-
 
||11:28
 
||11:28
||Let us undo these changes in order to get our “Personal-Finance-Tracker.ods” to its original form.  
+
||Let us '''undo''' these changes in order to get our “Personal-Finance-Tracker.ods” to its original form.  
  
 
|-
 
|-
 
||11:37
 
||11:37
||This brings us to the end of this Spoken Tutorial on LibreOffice Calc.
+
||This brings us to the end of this '''Spoken Tutorial''' on '''LibreOffice Calc'''.
  
 
|-
 
|-
 
||11:43
 
||11:43
||To summarize, we learned about:
+
||To summarize, we learned about: '''Conditional Operator''' '''If..Or statement''' Basic '''statistic functions''' '''Rounding off''' numbers.
 
+
* Conditional Operator
+
 
+
* If..Or statement
+
 
+
* Basic statistic functions
+
 
+
* Rounding off numbers  
+
  
 
|-
 
|-
 
||11:55
 
||11:55
||Watch the video available at the following link  
+
||Watch the video available at the following link.
  
 
|-
 
|-
 
||11:58
 
||11:58
||It summarises the Spoken Tutorial project  
+
||It summarizes the Spoken Tutorial project.
  
 
|-
 
|-
 
||12:01
 
||12:01
||If you do not have good bandwidth, you can download and watch it  
+
||If you do not have good bandwidth, you can download and watch it.
  
 
|-
 
|-
 
||12:06
 
||12:06
||The Spoken Tutorial Project Team
+
||The Spoken Tutorial project team:
  
 
|-
 
|-
Line 545: Line 497:
 
|-
 
|-
 
||12:11
 
||12:11
||Gives certificates for those who pass an online test  
+
||Gives certificates for those who pass an online test.
  
 
|-
 
|-
 
||12:15
 
||12:15
||For more details, please write to contact at spoken hyphen tutorial dot org  
+
||For more details, please write to:'''contact at spoken hyphen tutorial dot org'''.
  
 
|-
 
|-
 
||12:21
 
||12:21
||Spoken Tutorial Project is a part of the Talk to a Teacher project  
+
||'''Spoken Tutorial''' project is a part of the '''Talk to a Teacher''' project.
  
 
|-
 
|-
 
||12:26
 
||12:26
||It is supported by the National Mission on Education through ICT, MHRD, Government of India  
+
||It is supported by the National Mission on Education through ICT, MHRD, Government of India.
  
 
|-
 
|-
 
||12:34
 
||12:34
||More information on this Mission is available at  
+
||More information on this mission is available at:
  
 
|-
 
|-
 
||12:37
 
||12:37
||spoken hyphen tutorial dot org slash NMEICT hyphen Intro  
+
||'''spoken hyphen tutorial dot org slash NMEICT hyphen Intro'''.
  
 
|-
 
|-
 
||12:45
 
||12:45
||This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd  
+
||This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd. Thanks for joining.
 
+
Thanks for joining
+

Latest revision as of 14:44, 23 March 2017

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.
01:01 Let’s open “Personal-Finance-Tracker.ods”.
01:05 Here, under the heading “Cost”, we have listed the prices of several items.
01:11 Let’s apply conditional operators on them and analyze the results.
01:17 Let’s click on the cell referenced as “B10” and type “Condition Result” inside it.
01:24 Now, click on the cell referenced as “C10”.
01:28 The condition’s result will be applied and displayed in this cell.
01:33 Note that the cost of the “House Rent” is rupees 6,000.
01:38 The cost for the “Electricity Bill” is rupees 800.
01:43 The cost of “House Rent” is more than that of “Electricity Bill”.
01:48 We can apply different conditions on them and check the results.
01:54 Click on the cell referenced as “C10”.
01:57 In this cell, type the first condition as: is equal to C3 greater than C4 and press the Enter key.
02:09 Since the value in cell C3 is greater than the value in cell 'C4', the result we get is “TRUE”.
02:18 Now, let us change this conditional statement to: is equal to C3 less than C4.
02:26 Press Enter.
02:28 The result we get is FALSE.
02:32 In the same manner, you can apply other conditional statements and study the results.
02:38 These statements are very useful when dealing with large amounts of data.
02:44 You can also use the If and Or condition on data
02:49 to print the results according to the condition that holds TRUE.
02:55 Let’s click on the cell referenced as “C10” and type:
02:59 is equal to IF and within braces, C3 greater than C4 comma, within double quotes “Positive” comma and again within double quotes “Negative”.
03:16 This means, if the value in cell C3 is greater than the value in cell C4, “Positive” will be displayed
03:25 or else “Negative” will be displayed.
03:28 Now, press Enter.
03:31 Notice that the result is “Positive” since rupees 6000 is greater than rupees 800.
03:39 Now, in the conditional statement, let’s change “greater than” to “less than” and press the Enter key.
03:47 Note that the result is now “Negative”, as the value in cell C3 is greater than the value in cell C4.
03:57 You can also see the change in result, if we change the data in the cells C3 and C4.
04:04 The result which is displayed now is Negative.
04:09 Now, let us increase the value in cell C4 to “7000” and press the Enter key.
04:17 The result automatically changes to “Positive”.
04:22 Again, let’s decrease the value in cell C4 to “800”
04:26 and press the Enter key.
04:29 The result again automatically changes to “Negative”.
04:34 Now, let us delete the changes made.
04:38 Next, let’s learn a few arithmetic and statistic functions.
04: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.
04:57 Now, let’s perform some operations to check how the Sum, Product and the Quotient functions work.
05:05 First, let’s select “Sheet 3”.
05:08 Enter the numbers “50”, ”100” and ”150” within the cells referenced “B1”, “B2” and “B3” respectively.
05:19 Click on the cell A4 and type SUM.
05:23 Click on the cell B4.
05:26 We shall compute the result in this cell.
05:30 Type: is equal to “SUM” and within the braces B1 comma B2 comma B3.
05:37 Press Enter.
05:39 Notice the result shows “300”.
05:43 You can also enter a range of cells like this.
05:47 Click on B4 again.
05:49 Now, within the braces, instead of B1 comma B2 comma B3, type B1 colon B3
05:58 Press Enter.
06:00 Once again, the result shows “300”.
06:03 Now, let’s click on the cell “A5” and type: “PRODUCT”.
06:08 Click on the cell “B5”.
06:10 Here, type: is equal to “PRODUCT” and within the braces, B1 colon B3.
06:18 Press Enter.
06:20 Notice, the result shows “7,50,000”.
06:26 Now, let’s see how Quotient works.
06:29 Click on the cell referenced “A6” and type: “QUOTIENT”.
06:34 Now click on the cell “B6”.
06:37 We shall use this cell to compute the result.
06:40 And type: is equal to QUOTIENT and within the braces, B2 comma B1.
06:47 Press Enter.
06:49 You will get the result as “2”. That is because “100” divided by “50” gives 2.
06:59 Similarly, we can perform various arithmetic operations in Calc.
07:05 Now, let’s learn how to implement Statistic Functions.
07: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.
07:27 First, let us click on sheet 1.
07:30 Let’s see how to find the minimum, the maximum and the median costs, using statistical functions.
07:37 Let’s click on the cell referenced as “C10” where we will be displaying the result.
07:44 Under the heading “Cost”, we have very few entries.
07:48 The minimum cost is rupees 300.
07:51 The maximum cost is rupees 6000.
07:55 These are the results which should be displayed when we use their functions.
08:00 In the cell “C10”, let’s type: is equal to MAX and within braces C3 colon C7.
08:10 Now, press the Enter key.
08:13 Notice that the result is “6000” which is the maximum value in the column.
08:20 Now, let’s replace the term “MAX” in the statement with “MIN”
08:25 and press the Enter key.
08:28 Note that the result is “300” which is the minimum amount in the Cost column.
08:34 To find the median value, replace the term “MIN” with “MEDIAN”
08:40 and press the Enter key.
08:43 The result shows “800” which is the median cost in the column.
08:50 Similarly, you can use other statistical functions on data and analyze them accordingly.
08:58 Let us delete the changes in this cell.
09:02 Now, let’s learn how to round off numbers.
09:05 Let us make few changes under the heading- “Cost”.
09:09 We shall change:“6000” to “6000.34”“600” to “600.4”, ”300” to “300.3”.
09:23 Now, click on the cell referenced as “B11” and type the heading “ROUNDING OFF”.
09:31 Click on the cell referenced as “C11” where we will find the total of the items under the heading “Cost”.
09:39 In the cell C11, let’s type: is equal to SUM and within braces C3 colon C7.
09:49 Now, press the Enter key.
09:53 Notice that the total is “9701.04”.
09: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 off 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 summarizes 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