LibreOffice-Suite-Calc/C3/Formulas-and-Functions/English-timed
From Script | Spoken-Tutorial
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. |