OFFICE AUTOMATION – I EXCEL - BASICS 28. FUNCTIONS AND FORMULAS IN MICROSOFT EXCEL In this lesson you will learn to apply formulas and functions in a worksheet. This will help you in performing calculations using MS Excel worksheet and working with formulae and functions.
28.0 Objectives On completion of the lesson you will be able to: • •
Work with general formulas Also use built in functions
28.1 Introduction In the previous lessons you have learnt formatting of worksheets, making of tables/lists and working with columns and rows etc. This lesson will deal with how to apply functions and formulas in a worksheet.
28.2 Introduction to Functions 28.2.1 Function: SUM () The Sum () function is used for adding the numbers in a data set. Type as: =SUM (A1:A5) The answer is 140. 28.2.2 Function: COUNT () The Count () function is used for counting the numbers in a data set. Type as: =COUNT (A1:A5)
The answer is 5. 28.2.3 Function: Average () The Average () function is used for calculating average value of the data set. Type as: =Average (A1:A5) The answer is 28. 28.2.4 Function: MAX () The Max () function is used for finding the largest number of the data set. Type as: =MAX (A1:A5) The answer is 40. 28.2.5 Function: MIN () The Min () function is used for finding the smallest number of the data set. Type as: =MIN (A1:A5) The answer is 20.
28.3 Date and Time Functions 28.3.1 Function: Today () This function is used for entering today’s date. When you Type=Today () today’s date will be displayed. 28.3.2 Function: Now () This function is used for entering today’s date and time. Type as: =Now () today’s date and time will be displayed as shown.
28.3.3 Day () This function returns the day of a given date. Type the function as shown below this will give you the day of the date which has been typed.
28.3.4 Month () This function returns the Month of a given date.
Type the function as shown below this will give you the month of the date which has been typed. 28.3.5 Year () This function returns the Year of a given date. Type the function as shown below this will give you the year of the date which has been typed.
28.4 Copy and Paste Functions When a formula or function from one cell is copied and pasted to another cell, the cell addresses available in the formula also change relatively. The working of formula is shown as below. In this case the formula from cell C2 is copied and pasted on the cells C3 and C4, relatively the cell address in the formula has been changed from A2 to A3 and A4. This will give us result as “2006” in cell C2, “2005” in cell C3 and “2001” in cell C4 as shown below.
28.5 Auto Sum “Auto Sum” is used to calculate the sum of the numbers in a data set automatically. To do this, select the cell below the last number in the list where you want the sum of numbers to appear, and press “Alt” + “=“keys or select “AutoSum” icon from Standard Tool Bar. An example for Auto Sum is as shown.
28.6 Text and Additional Functions 28.6.1 Function: LEFT () LEFT function returns the characters from left hand side in a text value, based on the number of characters you specify.
This is typed as LEFT (text, num of chars) Type as: “=LEFT (B1, 1)” as shown in the figure this will return the character “E” 28.6.2 Function: RIGHT () RIGHT function returns the characters from right hand side in a text value, based on the number of characters you specify. This is typed as RIGHT (text, num of chars) Type as:” =RIGHT (B1, 1)” as shown in the figure this will return the character “L” 28.6.3 Function: MID () MID function returns a specific number of characters from a text, starting at the position you specify and based on the number of characters you specify. This is typed as MID (text, starting position, num of chars). Starting position is the position of the first character you want to extract in a text. Num of chars specifies the number of characters you want MID () to return from the text. Type as: “=MID (B1, 2, 1)” as shown in the figure this will return the character “X” 28.6.4 Function: LEN () The Len () function returns the length of the text. Type as: “=LEN (B1)” as shown in the figure this will return the Length of the text as 5. 28.6.5 Function: TRIM () TRIM Function removes all spaces from text except for single spaces between words. You use TRIM function on the text when you import text from another application and find that the text has irregular spacing. In the figure shown data has been copied from another application and pasted in the cells A1:A3.
The cell A2 has two blank spaces before the word Month. Please note: You can not remove the spaces from the data in the same cell, but you can remove the spaces in other cells using TRIM() function and after removing the spaces you can copy and paste the data back on the original cells. To remove the spaces: Type as: “=TRIM (A2)” in cell B2 and you will see Month with spacing removed in cell B2. 28.6.6 Function: AND () AND Function Returns TRUE if all its conditions are TRUE and returns FALSE if one or more conditions are FALSE. Type as: “=AND (B1=“EXCEL”, B2=“WORD”)” as shown in the figure this returns the answer as “TRUE”. Note: Maximum 30 conditions can be entered at a time. 28.6.7 Function: OR () OR Function Returns TRUE if any condition is TRUE and returns FALSE if all conditions are FALSE. Type as: “=OR (B1=“EXCEL”, B2=“WORD”)” as shown in the figure this returns the answer as “TRUE”. Note: Maximum 30 conditions can be entered at a time.
28.6.8 Function: SUMIF () SUMIF Function calculates the sum of the numbers in a data set when they satisfy a given condition. This is typed as SUMIF (range, condition, range of numbers to add) Range: is the range of cells which satisfies the condition. Condition: is the criterion in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be like “32”, "<32", ">32", "apples". Range of numbers to add: are the cells for which the total is to calculate. Type as: “=SUMIF (A1:A5,”WORD”, B1:B5)” as shown in the figure the sum returns as 60. 28.6.9 Function: COUNTIF () COUNTIF Function Counts the numbers when they satisfy a given condition. COUNTIF (range, condition) Range: is the range of cells which satisfies the condition. Condition: is the criterion in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be like “32”, "<32", ">32", "apples". Type as: “=COUNTIF (A1:A5,”WORD”)” as shown in the figure the count returns as 2.
28.7 If Condition {Single and Nested} IF Condition Returns one value if one condition is TRUE and another value if this condition is FALSE. Type as: “=IF (B1=“EXCEL”,”TRUE”,”FALSE”)” as shown in the figure this returns the answer as “TRUE”.
The nested “if condition” consists of two or more if conditions in a single cell and it checks multiple conditions in that single cell. If any of the condition is satisfying the criterion then this condition stops working. For example in the below mentioned statement/syntax =IF(B1=”EXCEL”,"A",IF(B2=“WORD”,"B",IF(B3=“POWERPOINT”,"C",IF(B4=“ ACCESS”,"D","F")))) If cell B1 has “Excel” then the condition gives an answer as “A” and stops execution of the condition, else the condition works until all criteria are checked and provides the answer as per the last TRUE or FALSE condition. Only maximum seven if conditions can be included in a single nested if statement/syntax.
28.8 Auto Calculate Auto Calculate means if we change the values in a cell, the result will automatically change in the destination cell. For example if you have a series of numbers and you have applied a sum formula as shown in figure. Now if you change 30 to 50 the formula result will change automatically.
Self-Check Questions Fill in the Blanks 1. To add numbers from a range, based on a condition _________________ function is used. 2. To get a number of month from a date ____________________ function is used. 3. To add numbers from a range _____________________ function is used. Answer True or False 4. MS Excel automatically recalculates the result of a formula if you change the value in a cell 5. On applying “If Condition” the result of formula changes if you change the condition 6. The result of the function “=day(01/11/2006)” will be 1 7. To get maximum number from the data available in the selected range you will use “=Average(A1:A10)” formula Match the following 8. =Sum () 9. =Average () 10. =Max () 11. =Now ()
a. Used for Maximum number of the selected range b. Used to give current Date and Time c. Used to add the numbers d. Used to calculate the average of selected range
28.9 Sorting Data sorting is a kind of arrangement to manage the data either in ascending or descending order. Create a list of some persons with some addresses in MS Excel and save the file as “Address.xls” with the headings in first row as shown in the figure below.
In the file “Address.xls” you can sort the data by “City”, this first level of data sorting. In MS Excel you can have three levels of data sorting. For example “City”, “Name” and “Street Address 1”. With every condition, you can select the order of sorting either “Ascending” or “Descending”. When data is sorted alphabetically (A-Z) or numerically (0-9), it is known as Ascending Order Sorting. When data is sorted alphabetically (Z-A) or numerically (9-0) is known as Descending Order Sorting. When data is sorted on a single field, it is known to be as normal Sorting. But when more than one field is used then it is known as multiple field sorting. In case of multiple sorting the data is first sorted on the first field and then in the result window, data is again sorted on second field, and so on. Before sorting any list, select the list including “Header Row”. Click on “Sort…” option from the “Data" menu.
“Sort” dialog box appears. In the “Sort by” drop down menu select “City” and click on “Ascending”. In the “Then by” drop down menu select “Name” and click on “Ascending”. In the “Then by” drop down menu select “Street Address 1” and click on “Ascending”. Click on “Header row” option from the “My list has” section if you have selected the list with the header row.
Click on “Options…” button for advanced sort. “Sort options” dialog box appears, the default value in the “First key sort order” drop down menu is “Normal”. In the “Orientation" section click on the option required for sorting, as in this case it is “Sort top to bottom”. Click “OK”.
Click on “OK” again to sort the data. Finally you will see your data as shown below sorted on all the three fields.
Press “Ctrl + Z” to undo sorting.
Self-Check Questions Answer True or False 12. Data sorting is only possible for alphabets 13. Data can be sorted only on one field 14. Header Row is not must for sorting.
28.10 Summing Up In this lesson you have familiarized yourself with the following: • • • • • • •
Introduction to Functions Date and Time Functions Copy and Paste Functions Auto Sum Text and Additional Functions If Condition (Single and Nested) Auto Calculate
28.11 Answers to Self-Check Questions 1. Sumif() 2. Month() 3. Sum() 4. T 5. T 6. T 7. F 8. c 9. d 10. a 11. b 12. False 13. False 14. True
28.12 Terminal Questions 1. What is the use of “If condition”? 2. What does “Autosum” function do and how do you use it? 3. Explain the use of And /OR functions? What is the maximum number of conditions that can be used in And function?
4. What is the purpose of “CountIF” function?
28.13 Glossary • •
Formulas: BODMAS calculations Functions: Ready made formulas that lead to the result of a formula by providing the parameters.