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 analyse 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 “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 condition 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 of 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 summarises 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