Chandrika: Created page with ''''Resources for recording''' Formulas and Functions {| border=1 || '''VISUAL CUE''' || '''NARRATION''' |- || '''Show Slide''' Welcome…'

2012-11-29T05:24:21Z

Created page with ''''Resources for recording''' Formulas and Functions {| border=1 || '''VISUAL CUE''' || '''NARRATION''' |- || '''Show Slide''' Welcome…'

New page

'''Resources for recording'''
[[Media:Formulas and Functions.zip |Formulas and Functions]]

{| border=1
|| '''VISUAL CUE'''
|| '''NARRATION'''

|-
|| '''Show Slide'''
Welcome to the '''Spoken Tutorial -''' '''LibreOffice Calc Formulas and Functions'''
||
Welcome to the Spoken Tutorial on '''Formulas and Functions''' in LibreOffice Calc.

|-
|| '''Show Slide '''
'''Learning Objectives'''

* Conditional Operator
* If..Or statement
* Basic statistic functions
* Rounding off numbers
||

In this tutorial we will learn about:
* Conditional Operator
* If..Or statement
* Basic statistic functions
* Rounding off numbers

|-
|| '''Show Slide'''
'''System Requirements'''

'''Here we are using:'''
* Ubuntu Linux version 10.04
* LibreOffice Suite version 3.3.4
|| Here we are using Ubuntu Linux version 10.04 as our operating system and LibreOffice Suite version 3.3.4.

|-
|| '''Show Slide'''
'''Conditional Operator'''
* We have already learnt about the basic operators -
** addition
** subtraction
** average
* Now we shall learn about '''Conditional Operators'''.
||
We have already learnt to apply the basic arithmetic operators like addition,subtraction and average on data.
Now, let’s learn about a few other useful operators.

|-
|| '''Show Slide'''
'''Conditional Operators''',
* check for the condition applied on the data by the user
* and then show results in boolean -
TRUE or FALSE.
|| One of the most commonly used operator is the '''Conditional Operator'''.
<<pause>>
'''Conditional Operators''',
* check for the condition applied on the data by the user and
* then show results in boolean - TRUE or FALSE.

|-
|| Open “'''Personal-Finance-Tracker.ods'''”
|| Let’s open “'''Personal-Finance-Tracker.ods'''”.

|-
|| Point to the heading “'''Cost'''” and point to the prices that are listed in the spread sheet.
|| Here, under the heading “'''Cost'''”, we have listed the prices of several items.
Let’s apply conditional operators on them and analyse the results.

|-
|| Click on cell “'''B10'''”.
Type “'''Condition Result'''” inside it.
|| Let’s click on the cell referenced as “'''B10'''” and type “'''Condition Result'''” inside it.

|-
|| Click on cell “'''C10'''”.
|| Now, click on the cell referenced as “'''C10'''”.
The condition’s result will be applied and displayed in this cell.

|-
|| Point to the heading “'''House Rent'''” and “6000”.
Then point to the heading “'''Electricity Bill'''” and “800”.
|| Note, that the cost of “'''House Rent'''” is rupees 6,000.
The cost for the “'''Electricity Bill'''” is rupees 800.

|-
|| Point to “6000”
Then Point to the “800”.
|| The cost of “'''House Rent'''” is more than that of “'''Electricity Bill'''”.
We can apply different conditions on them and check the results.

|-
|| Click on cell “'''C10'''”.
|| Click on the cell referenced as “'''C10'''”.

|-
|| Type =C3>C4
Press “'''Enter'''”

Point to C3 and
then point to C4
Point to “TRUE”
|| In this cell, type the first condition as “is equal to C3 greater than C4 ” and 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 less than C4”
Press “'''Enter'''”.

The result we get is “'''FALSE'''”.

|-
||
||In the same manner, you can apply other conditional statements and study the results.
These statements are very useful when dealing with large amounts of data.

|-
|| '''Show Slide'''
'''If and Or Condition'''
Use the If and Or condition
* to print the results
* according to the condition
* that holds '''TRUE'''.
|| You can also use the “'''If and O'''r” condition on data
* to print the results
* according to the condition that holds '''TRUE'''.

|-
|| Click on cell “C10”
Type =IF(C3>C4, “Positive”, “Negative”)
|| Let’s click on the cell referenced as “'''C10'''” and type,
“''' is equal to IF'''” and within braces, “'''C3 greater than C4'''” comma,
within double quotes “'''Positive'''” comma and again within double quotes “'''Negative'''”.

|-
|| Point to C3 then point to C4
|| This means if the value in cell C3 is greater than the value in cell C4, “'''Positive'''” will be displayed
or else “'''Negative'''” will be displayed..

|-
|| Press “'''Enter'''”.
|| Now press “'''Enter'''”.

|-
|| 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'''”.
Point to C3 and then to C4
|| Note, that the result is now “'''Negative'''”, as the value in cell C3 is '''greater than '''the value in cell C4.

|-
||
|| You can also see the change in result, if we change the data in the cells '''C3 '''and '''C4'''.

|-
|| Click on the cell “C10”.
|| The result which is displayed now is “'''Negative'''”.

|-
|| Increase the value in C4 to “7000”.
Press “'''Enter'''”.
|| Now, let us increase the value in cell C4 to '''“7000'''” and press the “'''Enter'''” key.

|-
|| Point to “'''Positive'''”
|| 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 changes made
|| Now, let us delete the changes made.

|-
||
|| Next, let’s learn a few arithmetic and statistic functions.

|-
|| '''Show Slide'''
'''Arithmetic Functions'''
Basic arithmetic functions include
*'''SUM '''for addition
*'''PRODUCT '''for multiplication
*'''QUOTIENT '''for division
* and many more
||
Basic arithmetic functions include
*''' SUM '''for addition,
*'''PRODUCT '''for multiplication,
*'''QUOTIENT '''for division and
* many more which we have already learnt in the earlier tutorials.

|-
||
|| Now let’s perform some operations to check how the Sum, Product and the Quotient functions work.

|-
|| Select “Sheet 3”.
|| First let’s select “'''Sheet 3'''”.

|-
|| Enter the numbers “50”,”100” and ”150” within the cells referenced “B1”, “B2” and “B3” respectively.
|| Enter the numbers “50”,”100” and ”150” within the cells referenced “'''B1'''”, “'''B2'''” and “'''B3'''” respectively.

|-
|| Click on the cell “A4” and type “SUM”.
|| Click on the cell “'''A4'''” and type “'''SUM'''”.

|-
|| Click on the cell “B4”
|| Click on the cell “'''B4'''”.
We shall compute the result in this cell.

|-
|| Type in the cell =SUM(B1,B2,B3)

Press '''Enter'''.
|| Type “is equal to “'''SUM'''”, and within the braces, '''B1 '''comma '''B2 '''comma '''B3'''.

Press '''Enter'''.

|-
|| Point to the result “300”.
|| Notice the result shows “300”.
<<pause>>

|-
||
|| You can also enter a range of cells like this

|-
|| Click on B4

Type in the cell =SUM(B1:B3)

Press '''Enter'''.
|| Click on “'''B4'''” again.
Now, within the braces, instead of '''B1 '''comma '''B2 '''comma '''B3''', type '''B1 colon B3'''

Press '''Enter.'''

|-
|| Point to the result “300”.
|| Once again, the result shows “300”

|-
|| Click on on the cell “A5” and type “PRODUCT”.
|| Now let’s click on the cell “'''A5'''” and type “'''PRODUCT'''”.

|-
|| Click on the cell “B5”
|| Click on the cell “'''B5'''”.

|-
|| Type in the cell =PRODUCT(B1:B3)

Press '''Enter'''.
|| Here type “is equal to “'''PRODUCT'''”, and within the braces, '''B1 colon B3'''.

Press '''Enter'''.

|-
|| Point to the result “750000”.
|| Notice the result shows “7,50,000”.

|-
||
|| Now let’s see how '''Quotient '''works.

|-
|| Click on the cell referenced “A6” and type in that, “QUOTIENT”.
|| Click on the cell referenced “'''A6'''” and type “'''QUOTIENT'''”.

|-
|| Click on the cell “B6”.
|| Now click on the cell “'''B6'''”. We shall use this cell to compute the result.

|-
|| Type =QUOTIENT(B2,B1)

Press '''Enter'''.
|| And 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”.
|| You will get the result as “2”. That is because “100” divided by “50” gives 2.
<<pause>>

|-
||
|| Similarly, we can perform various arithmetic operations in Calc.

|-
||
|| Now, let’s learn how to implement '''Statistic Functions'''.

|-
|| '''Show Slide'''
'''Statistic Functions'''
Statistical functions are useful
* for analysis of data in spreadsheets
* For example,
* Functions like COUNT, MIN, MAX, MEDIAN, MODE
* and many more are statistical in nature.
||

* Statistical functions are useful for analysis of data in spreadsheets.
* For example, functions like '''COUNT, MIN, MAX, MEDIAN, MODE '''
* and many more are statistical in nature.

|-
|| Click on sheet 1.
|| First, let us click on sheet 1.

|-
||
|| Let’s see how to find the minimum, the maximum and the median costs, using statistical functions.

|-
|| Click on cell C10.
|| Let’s click on the cell referenced as “C10” where we will be displaying the result.

|-
|| Point to the heading “'''Cost'''” and point to the prices that are listed.
|| Under the heading “'''Cost'''”, we have very few entries.

|-
|| Point to 300.
|| The minimum cost is rupees 300.

|-
|| Point to 6000.
|| The maximum cost is rupees 6000.

|-
||
|| These are the results which should be displayed when we use their functions.

|-
|| Click on cell C10.
Type “=MAX(C3:C7)”
|| In the cell “'''C10'''” let’s type “is equal to '''MAX'''” and within braces “'''C3” colon “C7'''”.

|-
|| Press “'''Enter'''”.
|| Now press the “'''Enter'''” key.

|-
|| Point to “6000” in the result.
|| Notice, that the result is “6000”, which is the '''maximum '''value in the column.

|-
|| Replace “'''MAX'''” with “'''MIN'''”

Press “'''Enter'''”
|| Now, let’s replace the term “'''MAX'''” in the statement with “'''MIN'''”.
And press the “'''Enter'''” key.

|-
|| Point to “300”
|| Note, that the result is “300” which is the '''minimum '''amount in the '''Cost '''column.

|-
|| Replace “'''MIN'''” with “'''MEDIAN'''”.

Press “'''Enter'''”.
|| To find the median value, replace the term “'''MIN'''” with “'''MEDIAN'''”.

And press the “'''Enter'''” key.

|-
|| Point to “800”

|| The result shows “800”, which is the '''median '''cost in the column.

|-
||
|| Similarly, you can use other statistical functions on data and analyze them accordingly.

|-
|| Delete the changes in this cell
|| Let us delete the changes in this cell.

|-
||
|| Now, let’s learn how to round off numbers.
Let us make few changes under the heading, “'''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'''”.

|-
|| Click on B11.
Type “'''ROUNDING OFF'''”
|| Now, click on the cell referenced as “'''B11'''” and type the heading “'''ROUNDING OFF'''”.

|-
|| Click on '''C11'''
|| Click on the cell referenced as “'''C11'''” where we will find the total of the items under the heading “'''Cost'''”.

|-
|| Type “=SUM(C3:C7)”

Press “'''Enter'''”
|| In the cell '''C11 '''let’s type “is equal to '''SUM'''” and within braces “'''C3” colon “C7”'''.
Now, press the '''Enter '''key.

|-
|| Point to “9701.4”
|| Notice, that the total is “9701.04”.

Now suppose we don’t want any decimal places in our result.
The best solution is to round off the result to the nearest whole number.

|-
|| Click on cell C11.
|| Let us click on the cell with the total “9701.04”.

|-
|| Type “=ROUND(SUM(C3:C7))”

Press “'''Enter'''”
|| Type “is equal to '''ROUND'''”,open brace “'''SUM'''” and again within braces “'''C3” colon “C7'''”.
Close the brace.
Press the '''Enter '''key.

|-
|| Point to “9701”
|| You see, that the result is now “9701”, which is “9701.04” rounded of to the nearest whole number.
<<Pause>>

|-
||
|| Rounding off, can also be done to either the lower whole number or the higher number.

|-
|| Click on cell C11.

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 C11

Edit “ROUNDUP” to “ROUNDDOWN”.
Press “'''Enter'''”
|| In order to round off to the lower whole number, change the term “'''ROUNDUP'''” to “'''ROUNDDOWN'''”

And press the “'''Enter'''” key.

|-
|| Point to “9701”
|| The result is now “9701” which is the lower whole number.
<<Pause>>

|-
|| Undo the changes
|| Let us undo these changes in order to get our “'''Personal-Finance-Tracker.ods'''” to its original form.

|-
|| Show Slide Number 3
'''SUMMARY'''

* Conditional Operator
* If..Or statement
* Basic statistic functions
* Rounding off numbers
|| This brings us to the end of this Spoken Tutorial on LibreOffice Calc.
To summarize, we learned about:
* Conditional Operator
* If..Or statement
* Basic statistic functions
* Rounding off numbers

|-
|| Show About Slide

About the Spoken Tutorial Project

* Watch the video available at http://spoken-tutorial.org/What_is_a_Spoken_Tutorial

* It summarises the Spoken Tutorial project

* If you do not have good bandwidth, you can download and watch it
||

* Watch the video available at the following link

* It summarises the Spoken Tutorial project

*If you do not have good bandwidth, you can download and watch it
|-
|| Show About Slide

Spoken Tutorial Workshops

The Spoken Tutorial Project Team

* Conducts workshops using spoken tutorials

*Gives certificates for those who pass an online test

* For more details, please write to contact@spoken-tutorial.org
||

The Spoken Tutorial Project Team

* Conducts workshops using spoken tutorials.

* Gives certificates for those who pass an online test

* For more details, please write to contact at spoken hyphen tutorial dot org
|-
|| Show Acknowledgement Slide

Acknowledgements

* Spoken Tutorial Project is a part of the Talk to a Teacher project

* It is supported by the National Mission on Education through ICT, MHRD, Government of India.

* More information on this Mission is available at

* http://spoken-tutorial.org/NMEICT-Intro
||

*Spoken Tutorial Project is a part of the Talk to a Teacher project

* It is supported by the National Mission on Education through ICT, MHRD, Government of India

*More information on this Mission is available at

* spoken hyphen tutorial dot org slash NMEICT hyphen Intro
|-
|| Show About the contributor Slide

About the Contributor

* This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd

* www.desicrew.in

* Thanks for joining
||

*This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd

* Thanks for joining
|-
|}

LibreOffice-Suite-Calc/C3/Formulas-and-Functions/English - Revision history

Chandrika: Created page with ''''Resources for recording''' Formulas and Functions {| border=1 || '''VISUAL CUE''' || '''NARRATION''' |- || '''Show Slide''' Welcome…'