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

Title:Formulae and Functions

Author: Arvind Pillai

Novice reviewer: Nikita Misal and Pooja Moolya

Domain reviewer: Praveen S

Keywords: LibreOffice,Calc,Ubuntu,Conditional Operators in Calc,Using IF-OR statements in Calc,Basic Statistical functions in Calc,Rounding off numbers in Calc, Function Wizards in Calc

VISUAL CUE	NARRATION
Slide: Tittle	Welcome to the spoken tutorial on Formulae and Functions in Calc.
Slide: Learning objectives	In this tutorial we will learn about: Conditional Operators If..Or statement Basic statistical functions and Rounding off numbers
Slide: System Requirements	This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
Slide:Conditional Operators.	Earlier in the series, we learnt to use basic arithmetic operators and average function Now we shall learn about conditional operators.
Slide:Conditional Operators.	Conditional operators, Check for the condition applied on the data by the user and Show results in boolean: TRUE or FALSE.
Open Personal-Finance-Tracker.ods	Let’s open Personal-Finance-Tracker.ods.
Slide: Code files	This file has been provided to you in the Code files link on this tutorial page. Please download and extract the file. Make its copy and use it for practising.
Point to the heading Cost	Here, under the column Cost, we have listed the prices of several items. Let’s apply conditional operators on them and analyse the results.
Point to House Rent and 6000. Point to Electricity Bill and 800.	Note that the amount for House Rent is rupees 6,000. The cost for Electricity Bill is rupees 800.
Point to 6000 Then Point to the 800.	The amount for House Rent is more than the Electricity Bill. We can apply different conditions on them and check the results.
Click on cell B10. Point to cell B10.	Click on cell B10. The result of the condition will be applied and displayed in this cell.
Type =C3>C4 Press Enter Point to C3 and then point to C4 Point to TRUE in cell B10	In this cell, type the first condition as is equal to C3 greater than C4. And then press the Enter key. Since the value in cell C3 is greater than the value in cell C4, the result we get is TRUE.
Edit statement to =C3<C4 Press Enter Point to FALSE	Now let us change this conditional statement to is equal to C3 lesser than C4. And press Enter key. The result we get is FALSE this time.
Slide: Other Conditions Other conditions to test: = Equal to <> Not equal to	Likewise, we can type some other conditions and study the results. These statements are very useful when dealing with large amounts of data.
Slide: If and Or Condition	We can also use the If and Or condition on our data To print the results according to the condition that holds TRUE. Let’s try this.
Click on cell B10 Type =IF(C3>C4, “Positive”,”Negative”)	Click on cell B10. Type is equal to IF and within brackets, C3 greater than C4 comma, within double quotes Positive comma and again within double quotes Negative.
Point to C3 then point to C4	The if condition checks whether the value in cell C3 is greater than the value in cell C4. Accordingly, Positive or Negative will be displayed.
Press Enter.	Now press the Enter key.
Point to Positive Point to C3 then point to C4	Notice that the result is Positive since rupees 6000 is greater than rupees 800.
Change statement to =IF(C3<C4,”Positive”,”Negative”) Press Enter.	Now, in the condition statement let’s change greater than to less than. And press the Enter key.
Point to Negative.	We got the output as expected.
Narration Only	You can also see the change in result, if we change the data in the cells C3 and C4.
Increase the value in C4 to 8000. Press Enter. Point to Positive	Now, let us increase the value in cell C4 to 8000 and press the Enter key. The result automatically changes to Positive.
Decrease the value in C4 to 800. Press Enter	Again, let’s decrease the value in cell C4 to 800. and press the Enter key.
Point to Negative	The result again automatically changes to Negative.
Delete the Row no. 10	Now, let us delete the changes made.
Narration Only	Next, let’s learn a few basic and statistical functions.
Slide:Syntax	The syntax to write a formula with a function is: Equals to sign = Function name (SUM for addition) Arguments (cell references)
Slide: Arithmetic Functions	Common basic functions include- SUM for addition, PRODUCT for multiplication, QUOTIENT for division
Narration Only	Now let me demonstrate how the Sum, Product and the Quotient functions work.
Select Sheet 3.	First let’s select Sheet 3.
Type 50,100 and 150 in cells B1, B2 and B3 respectively.	Type the numbers 50, 100 and 150 within cells B1, B2 and B3 respectively.
Click on the cell A5 and type SUM.	Click on cell A5 and type the text SUM.
Click on the cell B5	Click on cell B5. We shall compute the result in this cell.
Type in the cell =SUM(B1:B3) Press Enter	If we know the function and the syntax, we can directly type it in the respective cell as shown. Type is equal to SUM, and within the braces, B1 colon B3. And press Enter on the keyboard.
Point to the result 300. Press Ctrl + Z keys	Notice the result shows 300. Undo the change.
Only Narration Click on fx icon Point to Functions section	If we don't know the function name, we can make use of the Functions section. Click on the fx icon in the sidebar. The Functions Wizard opens. It displays a library of all Calc functions.
Click on Functions dropdown >> Select Mathematical Scroll down	From the functions drop-down, select Mathematical. The list of all mathematical functions available in Calc will be displayed below.
Double click on >> SUM	Scroll down and double-click on the function SUM.
Point to the Preview	At the bottom, we can see information about the selected function.
Point to B5	Notice the cell B5. The syntax for the selected function is inserted in the cell.
Click on Cell B1 and drag till cell B3	Select the cells B1 to B3 as shown, with the cursor.
Point to Input Line Press Enter	Look at the Input Line. The formula is auto-completed. Now press the Enter key on the keyboard.
Point to the result 300.	Once again, the result shows 300.
Click on the cell A6 and type PRODUCT.	Next, let’s click on cell A6 and type PRODUCT.
Click on the cell B6	Click on cell B6.
In functions section, scroll and select Product	From the Functions Wizard, scroll and double-click on the function PRODUCT.
Click on cell B1 and drag till cell B3 Press Enter	Select the cells B1 to B3 as shown, with the cursor. And press Enter on the keyboard.
Point to the result 750000.	Notice the result shows 7,50,000.
Click on B6 Double click on Product function Holding the Ctrl key select B1 and B3 [Enter]	If you wish to select only specific cells, then select those cells by holding the Ctrl key. Double-click on the function PRODUCT. Select the cells B1 and B3 as shown, with the cursor while pressing the Ctrl key. And press Enter on the keyboard.
Point to B6	The new result 7,500 is seen.
Narration Only	Next, let’s see how Quotient works.
Click on the cell A7 and type in that, QUOTIENT.	Click on cell A7 and type QUOTIENT.
Click on cell B7.	Now click on cell B7. We shall use this cell to compute the result.
Type =QUOTIENT(B2,B1) Press Enter.	Type is equal to QUOTIENT, and within the braces, B2 comma B1. Press Enter.
Point to the result 2. Point to the 100 and then to 50.	We will get the result as 2 because 100 divided by 50 is 2.
Change 100 to 151 in cell B2	Let’s change the number in cell B2 to 151 now.
Point to cell B7	Notice the change in cell B7. The quotient is 3 but the remainder 1 is lost.
Type =MOD(B2,B1) Press Enter.	What if we want to see the remainder? Click on cell B8 and type is equal to MOD, and within the braces, B2 comma B1. Press Enter.
Point to the Functions section Click on the X icon besides Functions.	Explore the other functions available in the Functions Wizard later on your own. Close the Functions Wizard.
Only narration	Next, let’s learn how to implement statistical functions.
Slide: Statistic Functions	Statistical functions are useful for analysis of data in spreadsheets. For example, statistical functions like COUNT, MIN, MAX, MEDIAN, MODE
Click on sheet 1.	Let us go to Sheet 1.
Narration Only	Let’s see how to find the minimum, maximum and the median costs. This we will do using Calc’s statistical functions.
Point to the heading Cost Point to 300. Point to 6000.	Under the column Cost, we have very few entries. The minimum cost is rupees 300. The maximum cost is rupees 6000.
Only Narration.	These are the results which should be displayed when we use the functions.
Click on cell C10.	Let’s click on the cell C10 where we will be displaying the result.
Click on Format as Number icon Point to cell C10	Click on the Format as Number icon in the Formatting toolbar This applies the numbering format to the selected cell.
Click on the Autosum Icon in the Formula bar Select MIN	Now go to the Formula bar and select the Autosum icon. From the list select the option MIN.
Click on Cell C3 >> Hold and drag till Cell C7 Press Enter	Select the cells C3 to C7 as shown, with the cursor. And press Enter on the keyboard.
Point to 300 in the result.	Now the result is 300, which is the minimum value.
Click on the Autosum Icon in the Formula bar Select MAX Click on Cell C3 >> Hold and drag till Cell C7 [Enter]	Likewise do for the Maximum function.
Point to 6000	Now the result is 6000 which is the maximum value.
Replace MIN with MEDIAN. Press Enter.	To find the median value, in the Input Line replace the term MAX with MEDIAN. And press the Enter key.
Point to 800	The result shows 800, which is the median cost in this column.
Replace MEDIAN with COUNT. Press Enter.	Now, let’s find the count of all entries in this column. In the Input Line replace the term MEDIAN with COUNT. And press the Enter key.
Point to 5	The result is shown as 5 which is the total number of entries in this column.
=COUNTIF(C3:C7, ">=1000") Press Enter.	Now, let’s find the count of all entries in this column which are greater than 1000. In the Input Line replace the term COUNT with COUNTIF. After the range type comma space within double quotes greater than 1000. And press the Enter key.
Point to Cell C10	The result is shown as 2 which is the total number of entries greater than 1000.
Narration Only	Explore other statistical functions and analyze them on your own later.
Delete the changes in this cell	Let us now delete the changes in cell C10.
Narration Only	Next, let’s learn how to round off numbers. Let us make a few changes under the column Cost.
Change 6000 to 6000.34 Change 600 to 600.4 change 300 to 300.3	We shall change: 6000 to 6000.34, 600 to 600.4 300 to 300.3.
Point to cell C8	Notice that the sum total is 9701.04. Now suppose we don’t want any decimal places in our result. The best solution is to round off the number to the nearest whole number.
Click on cell C10. Type =ROUND(SUM(C3:C7)) Press Enter	Let us click on the cell C10. Type is equal to ROUND, open bracket SUM and again within brackets C3 colon C7 and close the bracket. Press the Enter key.
Point to 9701	You see, that the result is now 9701. The value 9701.04 is rounded off to the nearest whole number.
Narration Only	Rounding off, can be done to either the lower whole number or the higher number.
Click on cell C10. Edit ROUND to ROUNDUP. Press Enter.	Let’s click on the cell with the result and edit the term ROUND to ROUNDUP. Now, press the Enter key.
Point to 9702	You see that the result is now 9702 which is the higher whole number.
Click on cell C 10 Edit ROUNDUP to ROUNDDOWN. Press Enter	To round off to the lower whole number, change the term ROUNDUP to ROUNDDOWN. And press the Enter key.
Point to 9701	This time the result is 9701 which is the lower whole number.
Narration Only	There are many other functions available in the Functions Wizard library of Calc. Explore and analyze them on your own later.
Click on the X icon and Click on Don't Save	Let us close the file without saving the changes.
Narration:	This brings us to the end of this tutorial, let us summarize.
Slide: Summary	In this tutorial, we learnt about: Conditional Operators If..Or statement Basic statistical functions and Rounding off numbers
Slide: Assignment	As an assignment Open Spreadsheet-Practice.ods file With the salary data try out MIN, MAX and MEDIAN functions Try round off function with the same salary data Undo the changes at the end.
Slide: About Spoken Tutorial Project	The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
Slide: Spoken Tutorial Workshops	The Spoken Tutorial Project Team conducts workshops and gives certificates. For more details, please write to us.
Slide: Forums	Please post your timed queries in this forum.
Slide: Acknowledgement	Spoken Tutorial project is funded by MHRD, Govt. of India.
Slide: Thank you Acknowledgement to DesiCrew	This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011 This is Arvind along with Spoken Tutorial team from IIT Bombay signing off. Thank you for watching.

Contributors and Content Editors

Arvindpillai90, Pravin1389