CLASS - VII | CHAPTER -2 ( Functions and Manipulation in Excel)

                                                                 SUBJECT: COMPUTER

CHAPTER-2

Functions and Manipulation in Excel

Using Function

Functions are the special pre-written formulae and instruction that ‘accept’ the values as arguments and ‘return’ the result values in the cell in which they have been typed.

General Syntax of Function

=Function name (Arguments).

For example, =AVERAGE(A1:A7)  OR  =SUM(9,6,12)

Arguments-Arguments can be of the following types:

Constants-These values can be used directly in the Function.

For example, =SUM(12,4513) will return the value 70.

Cells or Range- The cell reference or the range of cells can also be specified as the Argument.

For example, = Average(A1:A5) are the correct Functions.

Entering the Functions

1. All Functions begin with an “=” sign.

2. Parentheses are used to open and close the function like Sum().

3. All Arguments are given inside Parentheses.

Function Categories

Functions can be broadly classified in the following categories:

1. Text Functions

2. Mathematical Functions

3. Statistical Functions

4. Date & Time Functions

5. Logical Functions

Text Functions - Excel’s Text Functions help you to manage the text data in your spreadsheets.

Syntax	Purpose	Example
=concatenate(text1, text2)	Joins multiple text strings into one.	=concatenate (“My”,”country”)= My Country
=len(text)	Returns number of characters including spaces.	=len(“india”)=5
=lower(text)	Return text in lower-case	=lower(“INDIA”)= india
=upper(text)	Return text in upper-case	=upper(“india”)= INDIA
=right(text, n)	Returns n characters starting from the right side	=right(“India”,2)=In
=mid(text,n)	Returns the mentioned number of characters, beginning from the starting position.	=mid(“ My Country”,4,5)=Count

Mathematical Functions - Excel’s Mathematical Functions can be used to perform common mathematical operations such as addition (Sum function), multiplication (Product function), division and finding the square root.

Syntax	Purpose	Example
=sum(num1,num2)	Returns the Sum value of the Arguments	=sum(7,6)=13
=Product(num1, num2)	Returns the Product of the values given as the Arguments	=product(9,10,120=1080
=Mod(num1,num2)	Returns the Remainder, after diving the num1 by the num2	=mod(12,10) = 2
=Sqrt(num), num>0	Return the Square root of the number.	=sqrt(4) = 2

Statistical Functions - Excel’s Statistical Functions can be used to analyse the data in a spreadsheet. 

Syntax	Purpose	Example
=average(num1,num2…..)	Returns the average of the Arguments	=average(12,10,14)=12
=max(num1, num2…..)	Returns the maximum value out of the Arguments.	=max(15,20,10)    =20
=min(num1, num2…..)	Returns the minimum value out of the Arguments.	=min(15,20,10)     =10

Logical Functions - Excel’s Logical Functions can be used to introduce decision making into your spreadsheet. In logical functions you pass the condition as the arguments and it will return the result as True or False according to the satisfaction of the criteria of the condition.

IF Functions -  IF(logical_test, true_action, false_action)

Return one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Example - IF(Marks > =33, “Pass”,”Fail”)

Data Manipulation

There are many options in MS Excel which will help in manipulation of data as required. For example, you can use the Goal seek option to find a value to manipulate the result or you can filter out the records to be displayed, which match some specific criteria, or simply sort the data to arrange it into some order.

Using the Goal Seek -  The Goal Seek option is useful when you ant to manipulate the result which you have obtained by applying some Formula.Follow the steps given to use the Goal seek option.

 

Activity - 1 To use the Goal Seek

1. Click on the Data tab

2. Click on the What-if Analysis arrowhead button. From the drop-down list, select the Goal Seek option.

3. The Goal Seek dialog box opens.

1. In the set cell box, specify the cell address where you have entered the formula to calculate the result or place the cursor in the Set cell text box and click on the cell whose value you want to fix where you have entered the formula.

2. In the To value text box, enter the final value, which you want to obtain.

3. In the By changing cell text box, enter the cell address by changing the value for which you want to obtain the required result from or place cell whose value you want to change. The cell address will be automatically filled in the By changing cell text box. Click on the Ok button.

Using the Filter -  You can use the Filter option from the Data tab to display only those records which meet some specific criteria.

Activity - 2 To use the Filter.

1. Click on any Cell within the Data.

2. Click on the Data tab.

3. Click on the Filter button

4. The Arrowhead buttons will appear next to the field names in the list of entries.

Conditional Formatting -  If you want to apply formatting to cells, which contain data that meets certain conditions, you can use the conditional formatting. To do this, follow the steps given below

Activity - 2 To use the  Conditional formatting

1. Select the range of cells to format.

2. Click on the Home tab.

3. Click on the Conditional formatting button and point to Highlight Cell Rules button. Click on the condition type which you want to apply.

4. The Condition box opens. Enter the condition for which you want the cells to get formatted.

5. In the with drop-down list, specify the formatting.

6. Click on the OK button when finished.

LAB TIME

1 You have obtained 191 marks as the result of the total obtained as the sum of marks in 3 subjects. But you needed 240 marks in total to get a distinction. You can check how many marks you require, in any particular subject to achieve the total of 240.

2 To find out the list of students who have scored 75 or more marks in the subject English.

3 Enter marks in the cell range B2:D7 and apply Conditional Formatting so that if the marks are between 60 and 80, it should become light red filled with dark red text.

Worksheets

Worksheet 1  Multiple Choice Question (MCQs).

1. Which Functions will you use to perform common mathematical functions?

1.  Text Functions b.  Logical Functions

3. Mathematical Functions d.  Statistical Functions

2. Which Function returns square root of the number?

1.  Sqroot b.  Sqrt

2. Sqt d. SQR

3. Which sign is used to begin a Function?

1.  = b.  &

C. %               d.  $

4. Which function returns n characters number of characters including spaces?

1. Len(text) b.  Concatenate (text1, text2)

C.  Right(text) d. Right(text,n)

5. which Function returns number of characters including space?

a. Sum b.  Mod

C. Lower(text) d. Len(text)

Worksheet 2  Write (T) for True and (F) for False against the statements.

1.  The logical functions returns the result as True or False.

2. The = average(12,14,16) will given the result 16.

3. The = max(num1,num2,….) function returns the maximum value out of the Arguments.

4. The sqrt(num1) function returns the Square of the number Num1.

5. In the function = SUM(A1:A12), the argument is SUM.

Worksheet 3  Select the suitable words and fill in the blanks.

Mod Filter arguments Len Left Goal seek

1. You can  use the ______________________ option to display only those records which meet some specific criteria.

2. The ______________________ option is useful when you want to manipulate the result which you have obtained by applying some Formula.

3. The ______________________ function returns the Remainder, after dividing the num1 by the num2.

4. The ______________________ function returns n characters starting from the left side.

5. Functions are the special pre - written formula and instructions that accept the values as ______________________ and ‘return’ the result values.

Worksheet 4  Answer the following:

1. What are function. Write the syntax of a function.

2. Write one example for the syntax for joining multiple text strings into one.

3. What is the use of the Filter option?

4. Write the syntax used for returning the sum value of the Arguments.

5. What is the use of  Conditional Formatting?