**Narration**
1
00:00:00 --> 00:00:06
Welcome to the **Spoken Tutorial** on **Formulas and Functions** in **LibreOffice Calc**.
2
00:00:07 --> 00:00:18
In this tutorial, we will learn about: **Conditional Operator** **If..Or statement** **Basic statistic functions** **Rounding off numbers**.
3
00:00:19 --> 00:00:29
Here, we are using **Ubuntu Linux** version **10.04** as our **operating system** and **LibreOffice Suite** version **3.3.4**.
4
00:00:30 --> 00:00:38
We have already learnt to apply the basic arithmetic operators like addition, subtraction and average on data.
5
00:00:39 --> 00:00:42
Now, let’s learn about a few other useful operators.
6
00:00:43 --> 00:00:50
One of the most commonly used operator is the **Conditional Operator**.
7
00:00:51 --> 00:00:55
Conditional Operators: check for the condition applied on the data by the user
8
00:00:56 --> 00:01:00
and then show results in Boolean - **TRUE** or **FALSE**.
9
00:01:01 --> 00:01:04
Let’s open “**Personal-Finance-Tracker.ods**”.
10
00:01:05 --> 00:01:10
Here, under the heading “**Cost**”, we have listed the prices of several items.
11
00:01:11 --> 00:01:16
Let’s apply conditional operators on them and analyze the results.
12
00:01:17 --> 00:01:23
Let’s click on the **cell** referenced as “B10” and type “**Condition Result**” inside it.
13
00:01:24 --> 00:01:27
Now, click on the **cell** referenced as “**C10**”.
14
00:01:28 --> 00:01:32
The condition’s result will be applied and displayed in this cell.
15
00:01:33 --> 00:01:37
Note that the cost of the “House Rent” is rupees 6,000.
16
00:01:38 --> 00:01:42
The cost for the “**Electricity Bill**” is rupees 800.
17
00:01:43 --> 00:01:47
The cost of “**House Rent**” is more than that of “**Electricity Bill**”.
18
00:01:48 --> 00:01:53
We can apply different conditions on them and check the results.
19
00:01:54 --> 00:01:56
Click on the cell referenced as “C10”.
20
00:01:57 --> 00:02:08
In this cell, type the first condition as: **is equal to C3 greater than C4 ** and press the **Enter** key.
21
00:02:09 --> 00:02:17
Since the value in cell **C3** is greater than the value in cell 'C4', the result we get is **“TRUE”**.
22
00:02:18 --> 00:02:25
Now, let us change this conditional statement to: **is equal to C3 less than C4**.
23
00:02:26 --> 00:02:27
Press **Enter**.
24
00:02:28 --> 00:02:31
The result we get is **FALSE**.
25
00:02:32 --> 00:02:37
In the same manner, you can apply other conditional statements and study the results.
26
00:02:38 --> 00:02:43
These statements are very useful when dealing with large amounts of data.
27
00:02:44 --> 00:02:48
You can also use the **If** and **Or** condition on data
28
00:02:49 --> 00:02:54
to print the results according to the condition that holds **TRUE**.
29
00:02:55 --> 00:02:58
Let’s click on the cell referenced as **“C10”** and type:
30
00:02:59 --> 00:03:15
**is equal to IF** and within braces, **C3 greater than C4** comma, within double quotes **“Positive”** comma and again within double quotes **“Negative”**.
31
00:03:16 --> 00:03:24
This means, if the value in cell **C3** is greater than the value in cell **C4**, “Positive” will be displayed
32
00:03:25 --> 00:03:27
or else “**Negative**” will be displayed.
33
00:03:28 --> 00:03:30
Now, press **Enter**.
34
00:03:31 --> 00:03:38
Notice that the result is “Positive” since rupees 6000 is greater than rupees 800.
35
00:03:39 --> 00:03:46
Now, in the conditional statement, let’s change “greater than” to “less than” and press the **Enter** key.
36
00:03:47 --> 00:03:56
Note that the result is now “Negative”, as the value in cell **C3** is greater than the value in cell **C4**.
37
00:03:57 --> 00:04:03
You can also see the change in result, if we change the data in the cells **C3** and **C4**.
38
00:04:04 --> 00:04:08
The result which is displayed now is **Negative**.
39
00:04:09 --> 00:04:16
Now, let us increase the value in cell **C4** to “7000” and press the **Enter** key.
40
00:04:17 --> 00:04:21
The result automatically changes to “Positive”.
41
00:04:22 --> 00:04:25
Again, let’s decrease the value in cell **C4** to “800”
42
00:04:26 --> 00:04:28
and press the **Enter** key.
43
00:04:29 --> 00:04:33
The result again automatically changes to “Negative”.
44
00:04:34 --> 00:04:37
Now, let us delete the changes made.
45
00:04:38 --> 00:04:42
Next, let’s learn a few arithmetic and statistic functions.
46
00:04:43 --> 00:04:56
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.
47
00:04:57 --> 00:05:04
Now, let’s perform some operations to check how the **Sum, Product** and the **Quotient** functions work.
48
00:05:05 --> 00:05:07
First, let’s select “Sheet 3”.
49
00:05:08 --> 00:05:18
Enter the numbers “50”, ”100” and ”150” within the cells referenced “B1”, “B2” and “B3” respectively.
50
00:05:19 --> 00:05:22
Click on the cell **A4** and type **SUM**.
51
00:05:23 --> 00:05:25
Click on the cell **B4**.
52
00:05:26 --> 00:05:29
We shall compute the result in this cell.
53
00:05:30 --> 00:05:36
Type: **is equal to “SUM”** and within the braces **B1 comma B2 comma B3**.
54
00:05:37 --> 00:05:38
Press **Enter**.
55
00:05:39 --> 00:05:42
Notice the result shows “300”.
56
00:05:43 --> 00:05:46
You can also enter a range of cells like this.
57
00:05:47 --> 00:05:48
Click on **B4** again.
58
00:05:49 --> 00:05:57
Now, within the braces, instead of **B1 comma B2 comma B3**, type **B1 colon B3**
59
00:05:58 --> 00:05:59
Press **Enter**.
60
00:06:00 --> 00:06:02
Once again, the result shows “300”.
61
00:06:03 --> 00:06:07
Now, let’s click on the cell “A5” and type: “PRODUCT”.
62
00:06:08 --> 00:06:09
Click on the cell “B5”.
63
00:06:10 --> 00:06:17
Here, type: **is equal to “PRODUCT”** and within the braces, **B1 colon B3**.
64
00:06:18 --> 00:06:19
Press **Enter**.
65
00:06:20 --> 00:06:25
Notice, the result shows “7,50,000”.
66
00:06:26 --> 00:06:28
Now, let’s see how **Quotient** works.
67
00:06:29 --> 00:06:33
Click on the cell referenced “A6” and type: “QUOTIENT”.
68
00:06:34 --> 00:06:36
Now click on the cell “B6”.
69
00:06:37 --> 00:06:39
We shall use this **cell** to compute the result.
70
00:06:40 --> 00:06:46
And type: **is equal to QUOTIENT** and within the braces, **B2 comma B1**.
71
00:06:47 --> 00:06:48
Press **Enter**.
72
00:06:49 --> 00:06:58
You will get the result as “2”. That is because “100” divided by “50” gives 2.
73
00:06:59 --> 00:07:04
Similarly, we can perform various arithmetic operations in **Calc**.
74
00:07:05 --> 00:07:08
Now, let’s learn how to implement Statistic Functions.
75
00:07:09 --> 00:07:26
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.
76
00:07:27 --> 00:07:29
First, let us click on **sheet 1**.
77
00:07:30 --> 00:07:36
Let’s see how to find the minimum, the maximum and the median costs, using statistical functions.
78
00:07:37 --> 00:07:43
Let’s click on the cell referenced as “C10” where we will be displaying the result.
79
00:07:44 --> 00:07:47
Under the heading “Cost”, we have very few entries.
80
00:07:48 --> 00:07:50
The minimum cost is rupees 300.
81
00:07:51 --> 00:07:54
The maximum cost is rupees 6000.
82
00:07:55 --> 00:07:59
These are the results which should be displayed when we use their functions.
83
00:08:00 --> 00:08:09
In the cell “C10”, let’s type: **is equal to MAX** and within braces **C3 colon C7**.
84
00:08:10 --> 00:08:12
Now, press the **Enter** key.
85
00:08:13 --> 00:08:19
Notice that the result is “6000” which is the maximum value in the column.
86
00:08:20 --> 00:08:24
Now, let’s replace the term “MAX” in the statement with “MIN”
87
00:08:25 --> 00:08:27
and press the **Enter** key.
88
00:08:28 --> 00:08:33
Note that the result is “300” which is the minimum amount in the **Cost** column.
89
00:08:34 --> 00:08:39
To find the median value, replace the term “MIN” with “MEDIAN”
90
00:08:40 --> 00:08:42
and press the **Enter** key.
91
00:08:43 --> 00:08:49
The result shows “800” which is the **median cost** in the column.
92
00:08:50 --> 00:08:57
Similarly, you can use other statistical functions on data and analyze them accordingly.
93
00:08:58 --> 00:09:01
Let us delete the changes in this cell.
94
00:09:02 --> 00:09:04
Now, let’s learn how to **round off** numbers.
95
00:09:05 --> 00:09:08
Let us make few changes under the heading- “Cost”.
96
00:09:09 --> 00:09:22
We shall change:“6000” to “6000.34”“600” to “600.4”, ”300” to “300.3”.
97
00:09:23 --> 00:09:30
Now, click on the cell referenced as “B11” and type the heading “ROUNDING OFF”.
98
00:09:31 --> 00:09:38
Click on the cell referenced as “C11” where we will find the total of the items under the heading “Cost”.
99
00:09:39 --> 00:09:48
In the cell **C11**, let’s type: **is equal to SUM** and within braces **C3 colon C7**.
100
00:09:49 --> 00:09:52
Now, press the **Enter** key.
101
00:09:53 --> 00:09:58
Notice that the total is “9701.04”.
102
00:09:59 --> 00:10:03
Now, suppose, we don’t want any decimal places in our result.
103
00:10:04 --> 00:10:08
The best solution is to round off the result to the nearest whole number.
104
00:10:09 --> 00:10:14
Let us click on the cell with the total “9701.04”.
105
00:10:15 --> 00:10:24
Type: **is equal to ROUND**, open brace **SUM** and again within braces **C3 colon C7**.
106
00:10:25 --> 00:10:28
Close the brace. Press the **Enter** key.
107
00:10:29 --> 00:10:43
You see that the result is now “9701” which is “9701.04”, rounded off to the nearest whole number.
108
00:10:44 --> 00:10:51
Rounding off can also be done to either the lower whole number or the higher number.
109
00:10:52 --> 00:10:58
Let’s click on the cell with the result and **edit** the term “ROUND” to “ROUNDUP”.
110
00:10:59 --> 00:11:01
Now, press the **Enter** key.
111
00:11:02 --> 00:11:09
You see that the result is now “9702” which is the higher whole number.
112
00:11:10 --> 00:11:16
In order to round off to the lower whole number, change the term “ROUNDUP” to “ROUNDDOWN”
113
00:11:17 --> 00:11:18
and press the **Enter** key.
114
00:11:19 --> 00:11:27
The result is now “9701” which is the lower whole number.
115
00:11:28 --> 00:11:36
Let us **undo** these changes in order to get our “Personal-Finance-Tracker.ods” to its original form.
116
00:11:37 --> 00:11:42
This brings us to the end of this **Spoken Tutorial** on **LibreOffice Calc**.
117
00:11:43 --> 00:11:54
To summarize, we learned about: **Conditional Operator** **If..Or statement** Basic **statistic functions** **Rounding off** numbers.
118
00:11:55 --> 00:11:57
Watch the video available at the following link.
119
00:11:58 --> 00:12:00
It summarizes the Spoken Tutorial project.
120
00:12:01 --> 00:12:05
If you do not have good bandwidth, you can download and watch it.
121
00:12:06 --> 00:12:07
The Spoken Tutorial project team:
122
00:12:08 --> 00:12:10
Conducts workshops using spoken tutorials.
123
00:12:11 --> 00:12:14
Gives certificates for those who pass an online test.
124
00:12:15 --> 00:12:20
For more details, please write to:**contact at spoken hyphen tutorial dot org**.
125
00:12:21 --> 00:12:25
**Spoken Tutorial** project is a part of the **Talk to a Teacher** project.
126
00:12:26 --> 00:12:33
It is supported by the National Mission on Education through ICT, MHRD, Government of India.
127
00:12:34 --> 00:12:36
More information on this mission is available at:
128
00:12:37 --> 00:12:44
**spoken hyphen tutorial dot org slash NMEICT hyphen Intro**.
129
00:12:45 --> 00:12:50
This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd. Thanks for joining.