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

From Script | Spoken-Tutorial
Jump to: navigation, search

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