View source for LibreOffice-Suite-Calc/C3/Formulas-and-Functions/English-timed

'''Resources for recording'''
[[Media:Formulas and Functions.zip |Formulas and Functions]]


{| border=1
|| '''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.