LibreOfficeSuiteCalc6.3/C3/FormulaeandFunctionsinCalc/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 IFOR 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:

Slide: System Requirements  This tutorial is recorded using

Slide:Conditional Operators. 

Slide:Conditional Operators.  Conditional operators,

Open PersonalFinanceTracker.ods  Let’s open PersonalFinanceTracker.ods. 
Slide: Code files 

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.

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:

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 
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

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 dropdown, select Mathematical.
The list of all mathematical functions available in Calc will be displayed below. 
Double click on >> SUM  Scroll down and doubleclick 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 autocompleted. 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 doubleclick 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.
Doubleclick 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 

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:

Slide: Assignment  As an assignment

Slide: About Spoken Tutorial Project 

Slide: Spoken Tutorial Workshops 

Slide: Forums 

Slide: Acknowledgement 

Slide: Thank you
Acknowledgement to DesiCrew 
This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011
This is along with Spoken Tutorial team from IIT Bombay signing off. Thank you for watching. 