Difference between revisions of "LibreOffice-Suite-Calc/C3/Formulas-and-Functions/English-timed"

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

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

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

||We have already learnt to apply the basic arithmetic operators like addition, subtraction and average on data.  

||One of the most commonly used operator is the '''Conditional Operator'''.

||Conditional Operators: check for the condition applied on the data by the user

||and then show results in Boolean - '''TRUE''' or '''FALSE'''.

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

||Here, under the heading “'''Cost'''”, we have listed the prices of several items.

||Let’s apply conditional operators on them and analyze the results.  

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

||Now, click on the '''cell''' referenced as “'''C10'''”.

||Note that the cost of the “House Rent” is rupees 6,000.

||The cost for the “'''Electricity Bill'''” is rupees 800.  

||The cost of “'''House Rent'''” is more than that of “'''Electricity Bill'''”.

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

||Now, let us change this conditional statement to: '''is equal to C3 less than C4'''.

||Press '''Enter'''.

||The result we get is '''FALSE'''.

||You can also use the '''If''' and '''Or''' condition on data

|| to print the results according to the condition that holds '''TRUE'''.  

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

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

||Now, press '''Enter'''.  

||Notice that the result is “Positive” since rupees 6000 is greater than rupees 800.  

||Now, in the conditional statement, let’s change “greater than” to “less than” and press the '''Enter''' key.  

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

||The result which is displayed now is '''Negative'''.  

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

||Again, let’s decrease the value in cell '''C4''' to “800”

||and press the '''Enter''' key.  

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

||First, let’s select “Sheet 3”.  

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

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

||Press '''Enter'''.

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

||Click on '''B4''' again.

||Now, within the braces, instead of '''B1 comma B2 comma B3''', type '''B1 colon B3'''

||Press '''Enter'''.

||Once again, the result shows “300”.

||Now, let’s click on the cell “A5” and type: “PRODUCT”.

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

||Press '''Enter'''.  

||Notice, the result shows “7,50,000”.  

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

||Click on the cell referenced “A6” and type: “QUOTIENT”.  

||We shall use this '''cell''' to compute the result.

||And type: '''is equal to QUOTIENT''' and within the braces, '''B2 comma B1'''.

||Press '''Enter'''.  

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

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

||First, let us click on '''sheet 1'''.

||In the cell “C10”, let’s type: '''is equal to MAX''' and within braces '''C3 colon C7'''.  

||Now, press the '''Enter''' key.  

||Notice that the result is “6000” which is the maximum value in the column.  

||Now, let’s replace the term “MAX” in the statement with “MIN”

||and press the '''Enter''' key.  

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

||To find the median value, replace the term “MIN” with “MEDIAN”

||and press the '''Enter''' key.  

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

||Now, let’s learn how to '''round off''' numbers.

||Let us make few changes under the heading- “Cost”.  

||We shall change:“6000” to “6000.34”“600” to “600.4”, ”300” to “300.3”.  

||In the cell '''C11''', let’s type: '''is equal to SUM''' and within braces '''C3 colon C7'''.

||Now, press the '''Enter''' key.  

||Notice that the total is “9701.04”.

||Now, suppose, we don’t want any decimal places in our result.

||Type: '''is equal to ROUND''', open brace '''SUM''' and again within braces '''C3 colon C7'''.

||Close the brace. Press the '''Enter''' key.  

||You see that the result is now “9701” which is “9701.04”, rounded off to the nearest whole number.

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

||Let’s click on the cell with the result and '''edit''' the term “ROUND” to “ROUNDUP”.

||Now, press the '''Enter''' key.  

||and press the '''Enter''' key.  

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

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

||Watch the video available at the following link.

||It summarizes the Spoken Tutorial project.

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

||The Spoken Tutorial project team:

||Gives certificates for those who pass an online test.

||For more details, please write to:'''contact at spoken hyphen tutorial dot org'''.

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

||This tutorial has been contributed by DesiCrew Solutions Pvt. Ltd. Thanks for joining.