Difference between revisions of "LibreOffice-Suite-Calc/C3/Formulas-and-Functions/English-timed"
From Script | Spoken-Tutorial
PoojaMoolya (Talk | contribs) |
|||
| Line 362: | Line 362: | ||
||08: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” | ||
| + | |||
|- | |- | ||
||08:40 | ||08:40 | ||
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. |
