Pravin1389 at 08:55, 12 September 2020

2020-09-12T08:55:47Z

Arvindpillai90: Created page with "Title:Formulae and Functions Author: Arvind Pillai Novice reviewer: Nikita Misal and Pooja Moolya Domain reviewer: Praveen S Keywords: LibreOffice,Calc,Ubuntu,Conditional..."

2020-09-10T08:22:23Z

Created page with "Title:Formulae and Functions Author: Arvind Pillai Novice reviewer: Nikita Misal and Pooja Moolya Domain reviewer: Praveen S Keywords: LibreOffice,Calc,Ubuntu,Conditional..."

New page

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

{| border="1"
|-
|| '''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 '''<nowiki>=C3>C4</nowiki>'''

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 '''<nowiki>=C3<C4 </nowiki>'''

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:
* <nowiki>= Equal to</nowiki>
* <> 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 '''<nowiki>=IF(C3<C4,”Positive”,”Negative”)</nowiki>'''

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 '''<nowiki>=SUM(B1:B3)</nowiki>'''

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

<nowiki>=COUNTIF(C3:C7, ">=1000")</nowiki>

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 '''<nowiki>=ROUND(SUM(C3:C7))</nowiki>'''

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 along with Spoken Tutorial team from''' IIT Bombay''' signing off.

Thank you for watching.
|-
|}

@@ Line 598: / Line 598: @@
 || This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011
-This is  along with Spoken Tutorial team from''' IIT Bombay''' signing off.
+This is Arvind along with Spoken Tutorial team from''' IIT Bombay''' signing off.
 Thank you for watching.
 |-
 |}

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

Pravin1389 at 08:55, 12 September 2020

Arvindpillai90: Created page with "Title:Formulae and Functions Author: Arvind Pillai Novice reviewer: Nikita Misal and Pooja Moolya Domain reviewer: Praveen S Keywords: LibreOffice,Calc,Ubuntu,Conditional..."