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

From Script | Spoken-Tutorial
Jump to: navigation, search
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 dot 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.
08:01 If you wish to select only specific cells, then select those cells by holding the Ctrl key.
08:09 Double-click on the function PRODUCT.
08:12 Select the cells B1 and B3 as shown, with the cursor while pressing the Ctrl key.

And press Enter on the keyboard.

08:24 The new result 7,500 is seen.
08:29 Next, let’s see how Quotient works.
08:33 Click on cell A7 and type QUOTIENT.
08:38 Now click on cell B7.
08:42 We shall use this cell to compute the result.
08:46 Type is equal to QUOTIENT, and within the braces, B2 comma B1.

Press Enter.

08: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 hyphen Practice dot 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