LibreOffice-Suite-Calc-6.3/C3/Formulae-and-Functions-in-Calc/English-timed

TIME	NARRATION
00:01	Welcome to the spoken tutorial on Formulae and Functions in Calc.
00:07	In this tutorial we will learn about:
00:11	Conditional Operators,
00:13	If..Or statement
00:16	Basic statistical functions and Rounding off numbers
00:23	This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
00:37	Earlier in the series, we learnt to use basic arithmetic operators and average function
00:45	Now we shall learn about conditional operators.
00:49	Conditional operators, Check for the condition applied on the data by the user
00:56	Show results in boolean: TRUE or FALSE.
01:01	Let’s open Personal-Finance-Tracker.ods.
01:06	This file has been provided to you in the Code files link on this tutorial page.
01:13	Please download and extract the file. Make its copy and use it for practising.
01:22	Here, under the column Cost, we have listed the prices of several items.
01:28	Let’s apply conditional operators on them and analyse the results.
01:34	Note that the amount for House Rent is rupees 6',000.
01:40	The cost for Electricity Bill is rupees 800.
01:45	The amount for House Rent is more than the Electricity Bill.
01:50	We can apply different conditions on them and check the results.
01:56	Click on cell B10.
01:59	The result of the condition will be applied and displayed in this cell.
02:05	In this cell, type the first condition as is equal to C3 greater than C4. And then press the Enter key.
02:18	Since the value in cell C3 is greater than the value in cell C4, the result we get is TRUE.
02:27	Now let us change this conditional statement to is equal to C3 lesser than C4. And press Enter key.
02:39	The result we get is FALSE this time.
02:43	Likewise, we can type some other conditions and study the results.
02:49	These statements are very useful when dealing with large amounts of data.
02:55	We can also use the If and Or condition on our data
03:01	To print the results according to the condition that holds TRUE. Let’s try this.
03:09	Click on cell B10.
03:12	Type is equal to IF and within brackets, C3 greater than C4 comma, within double quotes Positive comma and again within double quotes Negative.
03:28	The if condition checks whether the value in cell C3 is greater than the value in cell C4.
03:36	Accordingly, Positive or Negative will be displayed.
03:41	Now press the Enter key.
03:44	Notice that the result is Positive since rupees 6000 is greater than rupees 800.
03:52	Now, in the condition statement let’s change greater than to less than. And press the Enter key.
04:01	We got the output as expected.
04:05	You can also see the change in result, if we change the data in the cells C3 and C4.
04:14	Now, let us increase the value in cell C4 to 8000 and press the Enter key.
04:23	The result automatically changes to Positive.
04:28	Again, let’s decrease the value in cell C4 to 800. and press the Enter key.
04:37	The result again automatically changes to Negative.
04:42	Now, let us delete the changes made.
04:47	Next, let’s learn a few basic and statistical functions.
04:53	The syntax to write a formula with a function is:
04:57	Equals to = Function name (SUM for addition) Arguments (cell references)
05:10	Common basic functions include-
05:13	SUM for addition,
05:16	PRODUCT for multiplication,
05:19	QUOTIENT for division
05:22	Now let me demonstrate how the Sum, Product and the Quotient functions work.
05:29	First let’s select Sheet 3.
05:33	Type the numbers 50, 100 and 150 within cells B1, B2 and B3 respectively.
05:44	Click on cell A5 and type the text SUM.
05:49	Click on cell B5.
05:52	We shall compute the result in this cell.
05:56	If we know the function and the syntax, we can directly type it in the respective cell as shown.
06:04	Type is equal to SUM, and within the braces, B1 colon B3. And press Enter on the keyboard.
06:15	Notice the result shows 300. Undo the change.
06:21	If we don't know the function name, we can make use of the Functions section.
06:27	Click on the fx icon in the sidebar.
06:31	The Functions Wizard opens. It displays a library of all Calc functions.
06:39	From the functions drop-down, select Mathematical.
06:44	The list of all mathematical functions available in Calc will be displayed below.
06:51	Scroll down and double-click on the function SUM.
06:56	At the bottom, we can see information about the selected function.
07:02	Notice the cell B5.
07:05	The syntax for the selected function is inserted in the cell.
07:10	Select the cells B1 to B3 as shown, with the cursor.
07:17	Look at the Input Line. The formula is auto-completed.
07:22	Now press the Enter key on the keyboard.
07:26	Once again, the result shows 300.
07:30	Next, let’s click on cell A6 and type PRODUCT.
07:36	Click on cell B6.
07:39	From the Functions Wizard, scroll and double-click on the function PRODUCT.
07:46	Select the cells B1 to B3 as shown, with the cursor. And press Enter on the keyboard.
07:56	Notice the result shows 7,50,000.
06:01	If you wish to select only specific cells, then select those cells by holding the Ctrl key.
06:09	Double-click on the function PRODUCT.
06:12	Select the cells B1 and B3 as shown, with the cursor while pressing the Ctrl key. And press Enter on the keyboard.
06:24	The new result 7,500 is seen.
06:29	Next, let’s see how Quotient works.
06:33	Click on cell A7 and type QUOTIENT.
06:38	Now click on cell B7.
06:42	We shall use this cell to compute the result.
06:46	Type is equal to QUOTIENT, and within the braces, B2 comma B1. Press Enter.
06:55	We will get the result as 2 because 100 divided by 50 is 2.
09:02	Let’s change the number in cell B2 to 151 now.
09:08	Notice the change in cell B7. The quotient is 3 but the remainder 1 is lost.
09:17	What if we want to see the remainder?
09:20	Click on cell B8 and type is equal to MOD, and within the braces, B2 comma B1. Press Enter.
09:31	Explore the other functions available in the Functions Wizard later on your own. Close the Functions Wizard.
09;40	Next, let’s learn how to implement statistical functions.
09:46	Statistical functions are useful for analysis of data in spreadsheets.
09:53	For example, statistical functions like COUNT, MIN, MAX, MEDIAN, MODE
10:02	Let us go to Sheet 1.
10:05	Let’s see how to find the minimum, maximum and the median costs.
10:11	This we will do using Calc’s statistical functions.
10:16	Under the column Cost, we have very few entries.
10:20	The minimum cost is rupees 300.
10:24	The maximum cost is rupees 6000.
10:28	These are the results which should be displayed when we use the functions.
10:34	Let’s click on the cell C10 where we will be displaying the result.
10:40	Click on the Format as Number icon in the Formatting toolbar
10:45	This applies the numbering format to the selected cell.
10:49	Now go to the Formula bar and select the Autosum icon.
10:54	From the list select the option MIN.
10:58	Select the cells C3 to C7 as shown, with the cursor. And press Enter on the keyboard.
11:07	Now the result is 300, which is the minimum value.
11:12	Likewise do for the Maximum function.
11:19	Now the result is 6000 which is the maximum value.
11:24	To find the median value, in the Input Line replace the term MAX with MEDIAN. And press the Enter key.
11:34	The result shows 800, which is the median cost in this column.
11:41	Now, let’s find the count of all entries in this column.
11:47	In the Input Line replace the term MEDIAN with COUNT. And press the Enter key.
11:55	The result is shown as 5 which is the total number of entries in this column.
12:02	Now, let’s find the count of all entries in this column which are greater than 1000.
12:09	In the Input Line replace the term COUNT with COUNTIF.
12:15	After the range type comma space within double quotes greater than 1000. And press the Enter key.
12:25	The result is shown as 2 which is the total number of entries greater than 1000.
12:32	Explore other statistical functions and analyze them on your own later.
12:38	Let us now delete the changes in cell C10.
12:43	Next, let’s learn how to round off numbers.
12:47	Let us make a few changes under the column Cost.
12:51	We shall change: 6000 to 6000.34, 600 to 600.4, 300 to 300.3.
13:06	Notice that the sum total is 9701.04.
13:13	Now suppose we don’t want any decimal places in our result.
13:18	The best solution is to round off the number to the nearest whole number.
13:24	Let us click on the cell C10.
13:27	Type is equal to ROUND, open bracket SUM and again within brackets C3 colon C7 and close the bracket. Press the Enter key.
13:41	You see, that the result is now 9701.
13:46	The value 9701.04 is rounded off to the nearest whole number.
13:54	Rounding off, can be done to either the lower whole number or the higher number.
14:01	Let’s click on the cell with the result and edit the term ROUND to ROUNDUP. Now, press the Enter key.
14:11	You see that the result is now 9702 which is the higher whole number.
14:19	To round off to the lower whole number, change the term ROUNDUP to ROUNDDOWN. And press the Enter key.
14:29	This time the result is 9701 which is the lower whole number.
11:36	There are many other functions available in the Functions Wizard library of Calc. Explore and analyze them on your own later.
14:46	Let us close the file without saving the changes.
14:51	This brings us to the end of this tutorial, let us summarize.
14:56	In this tutorial, we learnt about: Conditional Operators, If..Or statement, Basic statistical functions and Rounding off numbers
15:10	As an assignment Open Spreadsheet-Practice.ods file
15:16	With the salary data try out MIN, MAX and MEDIAN functions
15:23	Try round off function with the same salary data Undo the changes at the end.
15:30	The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
15:37	The Spoken Tutorial Project Team conducts workshops and gives certificates. For more details, please write to us.
15:474	Please post your timed queries in this forum.
15:51	Spoken Tutorial project is funded by MHRD, Govt. of India.
15:57	This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011
16:05	This is Arvind along with Spoken Tutorial team from IIT Bombay signing off. Thank you for watching.

Contributors and Content Editors

PoojaMoolya