Communication Skills Class XII PDF

Class XII Communication Skills 1. What is Communication? Communication is the act of conveying meanings from one entity or group to another through the use of mutually understood signs, symbols, and semiotic rules. *The following figure represents types of communication...

Class XII Communication Skills 1. What is Communication? Communication is the act of conveying meanings from one entity or group to another through the use of mutually understood signs, symbols, and semiotic rules. *The following figure represents types of communication (Fig 1) Learning objectives of Effective communication 1. Development of Interpersonal Skills 2. To express effectively & with maximum efficiency Communication skills are beneficial in and out of the workplace. Having the ability to clearly communicate instructions, ideas and concepts can help you find success in any career. With practice, anyone can develop their communication skills. 2. Active Listening One of the most critical skills in effective communication is Active listening. Developing this soft skill will help build and maintain relationships, solve problems, improve processes and retain information such as instructions, procedures and expectations. 1  2.1 Why is active listening important in the workplace? Whether we are seeking a new job opportunity, striving to earn a promotion or working to improve in our current role, improving our active listening skills will help us succeed. Much like critical thinking and conflict resolution, this soft skill will help increase our value. Here are several benefits of being an active listener: It helps us build connections. Active listening helps others feel comfortable sharing information with us. When we demonstrate our ability to sincerely listen to what others have to say, people will be more interested in communicating with us on a regular basis. This can help open up opportunities to collaborate with others, get work done quickly or start new projects. All of these things can help lead us to success in our career. It helps you build trust. When people know they can speak freely with us without interruptions, judgment or unwelcome interjections, they’ll be more likely to confide in us. This is especially helpful when meeting a new customer or business contact with whom we want to develop a long-term working relationship. It helps you identify and solve problems. Actively listening to others will help you detect challenges and difficulties others are facing, or problems within projects. The more quickly you’re able to spot these issues, you sooner you can find a solution or create a plan to address it. 2.2 Four Steps to Active Listening Active listening requires four discrete steps. CONTACT—connect with the participant who is contributing; eye contact, open posture, and nonverbal responses. ABSORB—take in all aspects of the spoken message, implicit and explicit and nonverbal clues. Do not judge or evaluate. REFLECTIVE FEEDBACK—mirror, reflect, or feedback what you have heard and why the contributor claims to be valid. CONFIRM—receive confirmation from the speaker that you heard the participant’s message accurately. If not, start the method over again at the beginning by having the speaker restate their view. 2  2.3 Demonstrating Active Listening skills The Story teller In this game, the teacher starts a story with a beginning phrase, and then each child in the classroom adds one word to the story in turn. Students must be active participants and follow the story closely so that when their time comes to add a sentence, the story will make sense. Another way to practice this is by playing a traditional game of Telephone where a message is passed around the room to see if it stays the same. Active Listening Skills(game) Directions: Read and listen to the statements below. After reading and listening to the teacher read the statements, write a response for each statement demonstrating reflective listening on the part of you the listener. Your answer should demonstrate the response. Remember a dialogue is a conversation between two people. One person sends a message (the statement. The person who receives the message then responds (your written response). Make sure to include all of the points each speaker makes. Do not assume (Note: offering reasons, solutions or excuses for behaviour is not part of the reflective listening response and must be avoided.). In each of your responses, underline all words that show you will restate the points made by the original speaker. Look at the example below. Note each part of the sender’s statement is also mentioned in the reflective listening statement. (Statement) I get furious with him when he says things that suggest that I don’t take good care of the kids. (Active Listening Response) What I think I hear you saying is that you feel furious when he implies that you’re not a good care giver for your kids. From the statements listed below, select a different phrase to use for each one of the dialogues you need to complete. “What I think I hear you saying is…” “In other words, you think that…” “Correct me if I’m wrong, but aren’t you saying…” “Let me review what I’ve heard you say. Please correct me if I leave anything out.” “I hear you saying…Is that right?” 1. Father to Son/Daughter: “I am sick and tired of you asking to borrow the car/bike when you haven’t completed your homework or your chores, you leave your room in a total wreck and you have been disrespectful to your family. You need to grow up and learn to show some respect. Response: 2.Teacher to Student: “I am very concerned about your lack of progress in this course. You haven’t been keeping up with your work and the work that you have been submitting is of poor quality. You are a senior and this course is a graduation requirement. If you don’t start turning in your work you are going to fail this course and possibly miss graduation.” Response: 3. Friend to Friend: I just don’t know what to do about my parents. It seems like they just don’t understand me. Everything I like seems to go against their values, and they just won’t accept my feelings as being right for me. It’s not that they don’t love me, they do,But they just don’t accept me. Response: 3 2.4 Designing poster on steps of Active Listening 3. Interview Skills Interviewing is a skill in and of itself, one in which our ability to interact with the interviewer and to articulate our thoughts are factors that are just as important in getting the job as are the qualifications listed on our resume. 4 3.1 Interview Do’s and Don’ts 3.1.1 DO’s a) What to do before an interview Get a good night's sleep the night before. Do research. Eat a good breakfast. Prepare questions beforehand. Know who will be interviewing and learn a bit about their background. Know your strengths and put together a list of them. Turn off your cell phone Prepare a solid list of references b) What to do during beginning of an interview Do try to sparkle! Use gestures in your conversation. Make sure they are smooth and emphatic. Do smile. Do make sure you get the interview’s name right and use it a few times in the interview Do go to the rest room before you visit the employment lobby. It is embarrassing to interrupt an interview, and you want to be as comfortable as possible c) What to do during an interview Do look the interviewer in the eye. Recruiters place a lot of emphasis on eye contact. Do take notes Let your achievements speak for you. Take a second before answering a question. Sit up straight. Ask questions. d) What to do after an interview Do let the interviewer decide when the interview is over. Do ask the interviewer when you will hear from him or her again if he or she does not offer the information. Drop off a thank you note. Always thank interviewer after you have left. Follow up appropriately. Create a list of items that you did well and you like to improve on. 3.1.2 Don’ts Don’t be late. In fact, plan to be early for any scheduled interview. If you are late or arrive just in the nick of time, the interviewer will start to wonder Don’t sit down until you are asked. Don’t lean on or put your elbows on the interviewer’s desk. Sit back in your chair, so the interviewer can see more of you. Sit erect. Don’t show your nervousness by drumming your fingers, swing your leg, or cracking your knuckles. Don’t talk too quickly Don’t digress from your points. Answer questions directly. 5  Don’t use words you do not know meaning of Do not use slang. 3.1.3 To teach interview skills in high school, we should follow these four steps: Introduce interview skills Talk about why good interview skills matter Explore what good job interview skills look like Group project A practical, and interactive activity for participants to learn how to prepare for a job interview. This activity will help them avoid some common mistakes before or during a job interview. Warm- up Activity: Words “ Interview” will be written on the Whiteboard/blackboard which be followed by discussion..The participants will be put be questions like: a. When was the last time you had an interview? b. Did you have good or bad experiences? c. Do you have any interview- related stories? d. d. Brainstorm some common mistakes before or during an interview and write them down. https://www.youtube.com/watch?v=S3l7COBI77U A small video clip will be played will played for a few times and then the Students will prepare five questions of their own on what they should andshould not do during an interview. For example, “What should you prepare for your interview?” “What should you do at the interview?” “What shouldn’t you do at the interview?” (3) On the whiteboard, write the following questions and ask the students to repeat with you a few times, and then pair the students to do the questions and answers. (Optional: The teacher can write down the participants answers on the white board.) A. What should you do before the interview? B. What should you do at the beginning of the interview? C. What should you do during the interview? D. What shouldn’t you do at the interview? … E. What should you do after the interview? 6  Actvity ( Group Project) All about me One of the first steps in an application process is telling the school /college/company a little bit about yourself.The students write a paragraph about themselves giving whatever information they think a potential school or employer would want to know including their education. Then, they will write a second version of their paragraph that leaves out some of the information in the first and also includes other information not in the first version. Collect students’ papers and choose some of the best pairs to share with the class.)Have students work in pairs to look at the two versions of the about me paragraph with each person in possession of one version. Without reading each other’s copies, the students will talk until they can point out which information each version has that is different from their partner’s and what information each version is lacking. Summary A classroom environment relies heavily towards your classroom climate. Students with effective communication skills will be more likely to contribute to class discussions, will be more productive members in group projects, and will ultimately gain more from their experience in the class. Learning and practicing writing skills help students to handle professional and social tensions. References : Google : You tube : British council Library 7 8 Class XII ICT Skills Unit 1. Performing Tabulation Using Spreadsheet Application 1.1 INTRODUCTION TO SPREADSHEET APPLICATION OpenOffice Calc is a spreadsheet program, a part of the free OpenOffice suite. The program is easy to use and contains most of the commonly used features found in commercial spreadsheet programs. OpenOfficeCalcis a software that helps in performing calculations using formulae and in analysing the data. Do you remember your Mathematics notebook of your primary class? It has small boxes to practice mathematics. These boxes are intersection of horizontal rows and columns. A spreadsheet or electronic Spreadsheet is also a long sheet of rows and columns on the computer screen. This helps to manage and organize data in rows and columns. Spreadsheets can be used to do calculations on data, create data reports, manage accounting documents, do data analysis, etc. You can also create graphical representation of data. Another term that is used in a spreadsheet software is Workbook. A Workbook is another name for OpenOffice Calc file. A Workbook is a collection of one or more worksheets in a single file. Each sheet can have many cells arranged in rows and columns. In this chapter we will be discussing about Apache OpenOffice Calc 4.1.5. You regularly get updates of these softwares. 1.2 SPREADSHEET APPLICATIONS Spreadsheet programs have become very popular because of the following features: Built-in functions make calculations easier, faster, and more accurate. Large volumes of data can be easily handled and manipulated. Data can be exported to or imported from other software. Data can be easily represented in pictorial form like graphs or charts. Formulae are automatically recalculated whenever underlying data values are changed. 1.3 CREATING A NEW WORKSHEET In this section, we will discuss how to start OpenOfficeCalc, components of Calc screen. Also we will learn how to create a new workbook and save it. 1.3.1 Starting OpenOffice Calc To start OpenOffice Calc: 1. Click Start All Programs OpenOffice 4.1.5 OpenOffice Calc. 14  2. A spreadsheet workbook named Untitled1 opens up in an OpenOffice Calc application window (Fig. 1). Columns Menu bar Standard Formatting Title bar bar bar Name box Active Formula cell bar Rows Side bar Sheet tabs Status bar Fig. 1 OpenOffice Calc worksheet 1.3.2 Components of a Calc Screen Title bar The Title bar is located at the top of the Calc window. It displays the name of the workbook on which you are currently working. When you create a new worksheet, is named as Untitled 1, Untitled 2, and so on. A workbook is a collection of one or more worksheets. The right side of the title bar contains the Minimize, Restore Down or Maximize, and Close buttons. Menu bar The menu bar is located below the Title bar. It has commands like File , Edit, etc. Clicking on each menu option displays a list of commands. Standard bar This bar contains icons (buttons) to provide quick access to commands such as New, Open, Print, Copy, and Paste etc. Formatting bar It has buttons and drop-down menus that allow you to select a formatting option like, font, font color, alignment, number format, border, and background color. Formula bar It contains the Name Box and a long white box, known as the Input line. Name Box It is present to the left of the Formula bar and displays the address of the selected cell. The rest of the window contains the spreadsheet. It is divided into rows that have a number at the left of each row, and columns with a letter at the top of each column. 15  Worksheet tabs A workbook, by default, opens three worksheets named as Sheet1, Sheet2, and Sheet3. You can click any sheet tab to open that worksheet. To insert a new worksheet, the steps are: 1. Clicking the empty area after the sheet tab. 2. The Insert Sheet dialog box appears. (Fig 2) 3. Select the required option and click OK Fig 2 Inserting a new sheet Click blank area to add more sheet You can also rename the sheet. Simply, double-click the Sheet name and type the new name. Rows and columns A worksheet in OpenOffice 4.1.5 Calc has 1,048,576 rows and 1,024 columns. The rows are numbered from top to bottom along the left edge of the worksheet as 1, 2, and so on. Columns are labelled from left to right with letters A…Z, AA…AZ, A…BZ…AAA…AAZ, ABA…ABZ…AMA…AMJ. Cell and Cell Address A cell is formed by the intersection of a row and a column. Each cell has a unique address which is formed by the intersection of row number and column letter. For example, a cell formed by intersection of column F and row 5 will have address F5. Active Cell: Data is entered in a cell. To enter data in a cell, we have to first select it. The selected cell is called the active cell and is highlighted with a thick border. Also, the address of the active cell is displayed in the Name box. Range of cells: A block of adjacent cells which are selected is called range of cells. For example, if the cells from A1 to B5 are selected, then the range of selected cells is referred as A1:B5. The cells in this range are – A1, A2, A3, A4, A5, B1, B2, B3, B4, and B5. 16 1.3.3 Creating a New Workbook The steps to create a new Calc workbook are: 1. Select File New Spreadsheet. Or Click the New Document drop-down menu arrow on the Standard bar and selectSpreadsheet. (Fig 3) Or Press CTRL + N. 1.3.4 Saving a workbook Fig 3 New button on Standard bar The steps to save a workbook are: 1. Select Save option from the File menu. Or Click the Save icon on the Standard bar Or Press Ctrl + S If you are saving a workbook for the first time, a Save As dialog box will appear. 2. Type the file name and choose a location to save the file. Notice that the file extension is.ods. 1.4 OPENING WORKBOOK AND ENTERING TEXT In this section, we will learn how to open an already saved workbook. Also, how to enter data in a cell. 1.4.1 Opening a Workbook The steps to open an already saved workbook are: 1. Select Open option from the File menu. Or Click Open icon on the Standard bar. Or Press Ctrl + O 2. The Open dialog box appears. 3. Select the drive and the folder from where you want to open the file. 4. Select the file and click Open button. 1.4.2 Entering text To enter data in a cell, 1. Select the cell. 2. Type the content. 3. Press ENTER key. You can enter numbers, text, and formulas in a cell. By default, the text is left-aligned in a cell and numbers are right-aligned. 17  Left-aligned right-aligned To cancel the data you have entered before pressing the ENTER key, press the ESC key. 1.5 RESIZE FONTS AND STYLES We can always change the font, style and size of the text or data entered in a worksheet. This can be done before typing or after typing the content. The options for formatting data/text are available on the Formatting bar (Fig 4). The use of each of the options is shown in the figure. Fig. 4 Formatting bar 1.6 COPYING AND MOVING In this section, we will learn how to select cells, rows, and columns. We will also discuss how to insert and delete cells, rows, and columns. How to change the row height and column width is also discussed here. Before performing any operation, like, making text bold, changing text color, etc, on a range of cells, you need to select the range. A range is a rectangular block of contiguous cells, i.e., cells that touch each other, especially along a line. 1.6.1 Selecting Cells You can select range of cells in any one of the following ways: Using the mouse Using the keyboard 18 Using the mouse To select a range of cells using the mouse, the steps are: 1. Click the cell you wish to start your selection from. 2.Click and hold the left mouse button down, drag the mouse pointer to the diagonally opposite corner cell. For example, if the range A1 to D3 is to be selected, place the mouse pointer at cell A1. Hold the left mouse button down and drag it to the cell D3. The range A1 to D3 is represented as A1:D3. When you select the range, all the cells appear highlighted (Fig. 5). Fig 5 Selecting range using mouse Using the Keyboard To select a range of cells using the keyboard, the steps are: 1. Place the cell pointer at one of the corner cells of the range to be selected. 2. Press the SHIFT key and move to the diagonally opposite corner cell using the arrow keys. 3. Release the SHIFT key when the required range has been selected. Selecting Multiple Ranges Simultaneously To select multiple ranges, do the following: 1. Select the first range of cells. 2. Hold the CTRL key and select another range of cells. 3. Repeat step 2 to select more ranges if required (Fig. 6). Fig 6 Selecting multiple range of cells Selecting the entire row To select the entire row in which the cell pointer is positioned, click the row heading. Selecting the entire column To select the entire column in which the cell pointer is positioned, click the column heading. Selecting the entire worksheet To select the entire worksheet: Press CTRL + A Fig 7 Select All button Or Click the blank button (called the Select Allbutton) at the junction of the row and column headers.(Fig 7) 19  Let’s Try Start OpenOfficeCalc and create a new worksheet. Now, try the following: 1. Select cell B3. 2. Select the range of cells from A1 to D3. 3. Select the range of cells from C5 to F5. 4. Select multiple range of cells B4:D7 and C4:F8. 5. Select row 5. 6. Select row 3 and 8. 7. Select column D. 8. Select column B and G. Worksheet 1. By default, how many worksheets are there in a Calc workbook? ____________ 2. How are rows numbered in Calc? ________________ 3. Name the column after column Z. ________________ 4. What is the address of the cell formed by the intersection of 3rd column 5th row? ____ 5. Name the cells in the range A2:B5. ________________________________ 6. How can you select entire worksheet using keyboard? _________________ 7. How can you select entire column number B? _______________________________ 8. Which key will you press if you want to select multiple ranges of cells? __________ 9. Name the column after BZ? ___________ 10. What is the extension of a file saved in Calc? _____________ 1.6.2 Copying Cell Contents You can copy content of cell(s) to another cell(s). To copy cell contents in Calc, the steps are: 1. Select the cell(s) that contain(s) the data you want to copy. 2. Select Copy option from the Edit menu. Or Click the Copy button on the Standard bar. (Fig 8) Or Press CTRL + C to copy the data. 3. Click on the cell(s) where you want to paste the data. 4. Select Paste option from the Edit menu. Or Fig 8 Standard bar Click the Paste button on the Standard bar. Or Press CTRL + V. 1.6.3 Moving Cell Contents To move cell contents from one cell to another in Calc, the steps are: 1. Select the cell that contains the data you want to cut. 2. Select Cut option from the Edit menu. Or Click the Cut button on the Standard bar. Or Press CTRL + X to cut the data. 20 3. Click on the cell where you want to paste the data. 4. Select Paste option from the Edit menu. Or Click the Paste button on the Standard bar. Or Press CTRL + V. 1.6.4 Inserting and Deleting Cells To insert cells, the steps are: 1. Select the range of cells where you want to insert a block of cells. 2. Select Cells option from the Insert menu. 3. The Insert Cells dialog box appears. 4. Select the appropriate option and click OK. To delete cells, the steps are: 1. Select the range of cells where you want to delete a block of cells. 2. Select Delete Cells option from the Edit menu. 3. The Delete Cells dialog box appears. 4. Select the appropriate option and click OK. 1.6.5 Inserting Rows and Columns The steps to insert rows are: 1. Select the row where you want to insert a new row. 2. Select Rows option from the Insert menu. Or Right-click the row header and select Insert Rows in the shortcut menu. A new row is inserted above the selected/highlighted row. Cells in the new row are formatted similar to the corresponding cells in the row before which the new row is inserted. Multiple rows can be inserted at once by selecting multiple rows using theCTRL key or by dragging the mouse while holding down the left mouse button. To insert columns, the steps are: 1. Select the column where you want to insert a new column. 2. Select Columns option from the Insert menu. Or Right-click the column header and select Insert Columns in the shortcutmenu. When you insert a new column, it is inserted to the left of the selected/highlighted column. Cells in the new column are formatted similar to the corresponding cells in the column to the left of which the new column or row is inserted. Multiple columns can be inserted at once by selecting multiple columns using theCTRL key or by dragging the mouse while holding down the left mouse button. 1.6.6 Deleting Rows and Columns To delete rows, the steps are: 21 1. Select the row to be deleted. 2. Right-click on the selected row header. 3. Select Delete Rows option in the shortcut menu. To delete multiple rows, select them using the CTRL key, or by dragging the mouse while holding the left mouse button. To delete columns, the steps are: 1. Select the column to be deleted. 2. Right-click on the selected column header. 3. Select Delete Columns in the shortcut menu. To delete multiple columns, select them using the CTRL key, or by dragging the mouse while holding the left mouse button. Deleting Content Instead of deleting a row or column, you may want to delete the contents of the cells but keep the empty row or column. This can be done in the following manner: 1. Select the cell(s), the contents of which you want to delete. 2. Press the DELETE key. Or Select Delete Contents option of Edit menu. 3. The Delete Contents dialog box appears. 3. Check the boxes of the kind of data you want to delete (e.g., checking Formats will remove the formatting changes such as bold, italics, font colors, and borders). 4. Click OK. 1.6.7 Changing Row Height and Column Width You can change the row height in a Calc worksheet in any of the following ways: Drag the divider below the row (Fig 9). To fit the row height to the cell contents, double-click the divider. Select Format Row Height. The Row Height dialog box appears (Fig. 10). Enter the value for row height in the Height spinbox. Click Ok button. Fig. 10 Row Height dialog box Fig 9 Changing row height 22 Changing Column Width You can change the column width in a Calc worksheet in any of the following ways: Drag the divider to the right of the column header (Fig 11). 2 To fit the column width to the cell contents, double-click the divider. To change the column width, select Format Column Width. The Column Width dialog box appears. Enter the value for column width in the Width spinbox. Click Ok button. Fig 11 Changing column width Let’s Try 1. Create the following worksheet. 2. Insert a column after column D. 3. Move the marks of Maths in the newly inserted column. 4. Insert a new row after row 5. 5. Insert the following details in the new row 1105 Tiya 69 64 59 54 6. Change the row height of all the rows. 7. Change the column width of columns A to F. 1.7 FILTER AND SORTING 1.7.1 Filtering Data Filtering is a quick and easy way to find and work with selected data based on the criteria you specify. The filter feature selectively blocks out the data you do not want to see and displays only the rows or columns that meet the conditions or criteria you specify. Sorting rearranges the range of cells but filtering only hides temporarily the rows/columns you do not want. Different ways in which filtering can be done in Calc are: AutoFilter Standard Filter 23 AutoFilter To apply AutoFilter in a worksheet, the steps are: 1. Select cell, say, A1. 2. Select Data Filter AutoFilter. 3. A drop-down menu arrows appear in each column heading (Fig 12). 4. Click the drop-down menu arrow for Total Sales and select an item (Fig 13). 5. Only those rows whose contents meet the filter criteria are displayed. a. To display all the records again, select the All option in the filter drop-down menu. b. Select Top 10 to display the highest 10 values. Fig. 12 Drop-down menu arrows Fig. 13 Drop-down menu for Total in column Headings Standard Filter To apply standard filter in your worksheet, the steps are: 1. Select Data Filter Standard Filter.. 2. The Standard Filter dialog box appears (Fig. 14). a. You can use the dialog box to connect multiple conditions with either a logical AND or a logical OR operator. 3. Select the options in the Standard Filter dialog box. The records with Total Sales greater than 4000 will be displayed (Fig. 15). Fig. 14 Standard Filter dialog box Fig. 15 Rows after applying Standard Filter To remove the filter, 1. Select Data Filter Remove Filter. 24  To hide the filter, 1. Select Data Filter Hide AutoFilter. To remove the filter from column headings, 1. Select Data Filter AutoFilter. 1.7.2 Sorting Data Once you have entered data and applied relevant formulas in a worksheet, you can arrange the data in ascending or descending order. This is called sorting of data. Sorting on numerical and textual values is a one of the main features of any spreadsheet software. In Calc, sorting can be done in the following manner. Sorting on One Column 1. Enter data in a worksheet (Fig. 16). 2. Select any cell, say C1. 3. Click the Sort option from the Data menu (Fig. 17) Fig. 16 Data in worksheet Fig. 17 Sort option in the Data menu 3. The Sort dialog box appears (Fig. 18). Notice that column Marks appears under Sort by section. 4. Select the Descending option under Sort by and click OK. 5. The data in column Marks is sorted in descending order.(Fig 19) Fig 19 Data arranges in descending order of Marks box You can also sort the data on one column is by using the sorting icons on the Standard bar. On the Standard bar, click (Fig 20) 25  The Sort Ascending button to sort the data in ascending order. The Sort Descending button to sort the data in descending order. Sort Sort Descending Ascending Fig 20 Sort buttons on Standard bar Sorting on Multiple Columns You can sort the data on multiple columns. 1. Consider the following worksheet. (Fig 21) 2. Select cell, say C1. 3. Select Sort option from Data menu. 4. The Sort dialog box appears. 5. The Sort Criteria tab on the Sort dialog box has options to sort the data on multiple columns. 6. Select the options as shown in Figure 22. 7. The data is sorted in descending order of column Total Sale, and where total sale is same, sorting is done in ascending order of column Salesman Name. (Fig 23) Fig 21 Worksheet Fig 23 Data sorted on multiple Fig. 22 Multiple columns selected in the Sort columns dialog box 26  Lets Try! 1. Create the following worksheet and perform the following operations: a. Sort the table in ascending order of height. b. Filter the data to view only those rows where height is more than 8500 m. 1.8 FORMULAS AND FUNCTIONS The most important feature of Spreadsheet software is that you can perform arithmetic operations on the data in a worksheet. 1.8.1 Formulas Formulae are used to calculate results through arithmetic operations. A formula in Calc always starts with an equal to (=) sign. If you forget to put = sign before the formula, it will be treated as text and no calculation will be performed. Also, you should not write anything before the = sign. Again it will be treated as text and no calculation will be performed. The data in a formula consists of one or a combination of the following: Value Numeric (e.g., 45) or string (e.g., “Smiling”) Cell Address B4, A2:C6 Function SUM, AVERAGE, MIN, MAX, etc. Operator +, -, *, /, >, =, etc. Parenthesis To control the left to right order of precedence in a formula (e.g., = (B2*B3)*2) Numeric Formulae In numeric formulae, you have to make use of operators. The results are calculated based on the order of precedence of the operators. Mathematical Operators Used in Formulae The mathematical operators used in Calc and their order of evaluation in formulae is given below: Operation Operator Order of evaluation Formula Result Exponent ^ 1 =2^3 8 Multiplication * 2 =3*5 15 Division / 2 = 9/ 3 3 Addition + 3 = 12 + 15 27 Subtraction - 3 = 18 - 15 3 Order of evaluation 1. Any operation contained within brackets will be carried out first 27  2. Then any exponent. 3. Then follow division and multiplication operations. Multiplication and division are given equal importance. They are carried out in the order they occur in the formula, from left to right. Whichever appears first in the formula is carried out first. 4. After that, addition and subtraction operations are given equal importance. They are also carried out in the order they occur in the formula, from left to right. Following are some examples of how Calc evaluates formulae. Formula Output = (3 + 7) * 2 20 =4*3+5 17 =3+3^2 12 =5+3*4–2 15 = 6 + 14/2 * 3 - 4 23 Error Results Sometimes a formula displays an error result rather than a proper value. This happens when the formula or data has a problem and Calc cannot evaluate it. Some common errors are shown below: Error Reason ##### The column is not wide enough to display the value. #DIV/0! The formula contains an invalid operation, i.e., division by zero. #VALUE! The formula has invalid argument, e.g., text in a cell where numeric value is required. Text Formulae A text string or a text value is a sequence of characters. You can join two strings together. This is called concatenation. We use the ampersand (&) character to concatenate strings. For example, if you type = “Keep” &“ “& “Smiling” in a cell and press ENTER, you will see the result as Keep Smiling. You cannot do operations such as subtraction, multiplication, and division on strings. Cell and Range References A cell reference identifies a cell or a range of cells. Each cell in the worksheet has a unique address formed by the combination of its intersecting row and column. When a cell address is referred to in a formula, it is called cell referencing. Consider the following examples: Cell or a Range of Cells Reference The cell in column B and row 4 B4 All cells in row 5 5:5 All cells in rows 5 through 9 5:9 All cells in column B B:B The range of cells in column F and rows 1 through 7 F1:F7 The range of cells in row 5 and columns C through E C5:E5 All cells in columns C through G C:G The range of cells in columns B through G and rows 4 through 8 B4:G8 28 Entering A Formula All formulae in OpenOfficeCalc begin with an equal to (=) sign. A formula can contain number, text, arithmetic operators (+, -, *, /), or functions. The order of precedence is already discussed. To enter a formula: 1. Select the cell and enter the formula directly in the cell or in the Formula Bar. 2. Press the Enter key. 3. The cell will show the result of the formula and the formula itself. You can see the formula in the Formula bar when the cell is selected. Consider the following worksheet.(Fig 24) Fig 24 Worksheet To find the total marks: 1. Select cell D3. 2. Type the formula =B3+C3. 3. To copy this formula in other cells: a. Select cell D3. b. Click the AutoFill handle and drag till cell D6. (Fig 25) c. The formula copied in cell D4 will be =B4+C4. d. The formula copied in cell D5 will be =B5+C5. e. The formula copied in cell D6 will be =B6+C6. Fig 25 Copying formula 29  Let’s Try 1. Create the following worksheet. 2. Now, type the formula =45+67+77 in cell D2. Press Enter key. 3. Now change the value 88 in cell C2. 4. Did you notice any change in the cell D2? 5. Now, type the formula =A2+B2+C2 in cell D2 and press Enter key. 6. Notice the result in cell D2. 7. Now, change the value in cell A2 to 89. 8. Notice the change in cell D2. 9. Notice the benefit of giving cell address in a formulae. Let’s Try Create the following worksheet in Calc. 1. Write the formula to calculate area of a rectangle in cell C3 (=A3*B3). Then press Enter key. 2. To copy the formula to cells C4 and C5: a. Select cell C3. b. Click and drag the AutoFill handle to cells C4 and C5. 3. Write the formula to calculate perimeter of rectangle in cells D3 (=2*(A3+B3)). Then press the Enter key. 4. Now, copy the formula in cell D3 to D4 and D5. 5. Save the worksheet as ‘rectangle’. Let’s Try 1. Open the file ‘rectangle’. 2. Change the values of length and breadth in cells A3, A4,B3 and B4. 3. Observe the change in the results in cells C3, C4, D3 and D4. 30  Let’s Try Create the following worksheet and perform the following operations a. Filter the records according to the following condition: b. Add another column at the end with heading ‘Total marks’ c. Calculate total marks for each student. d. Now, arrange the data in descending order of Total marks. 1.8.2 Using Functions to do calculations Functions are predefined formulae that perform calculations using specific values called arguments. The format of writing any function in Calc is: =function_name(Argument1;Argument2; Argument3;…..) Arguments These are the values passed to a function so that the function carries out the intended calculation or manipulation to give results. Arguments can be constants, formulae, or function. SUM You have already learnt how to find total using the formula. You can also obtain the sum of the values in a range of cells by: Clicking the Sum button on the Formula bar (Fig 26) SUM button Using the SUM() function SUM function is used to find total of numbers in a range of cells. For example: Fig 26 Formula bar Formula Result =SUM(5;6;12) 23 =SUM(A1;B1;C1) where A1, B1 49 and C1 contain the values 12, 23 and 14 respectively 31 Let us consider an example. 1. Consider the worksheet shown in Figure 27. Fig 27 Creating a worksheet Fig 28 2. Select the cell D2. 3. Click the Sum button on the Formula bar. Notice it will select the range A2:C2. (Fig 28). 4. Select the correct range B2:D2 or type the correct range (Fig 29). Fig 29 Fig 30 5. Press ENTER (Fig. 30) to perform the operation. The sum of the range B2:C2 will appear in cell D2. 6. Select cell D2 and using the AutoFill handle, drag the formula through D7. The respective sums will appear in the respective cells.(Fig 31) AVERAGE Function Average function is used to find the average of numbers in a range of cell. Fig 31 For example: Formula Result =AVERAGE(3;6;9) 6 =AVERAGE(A1;B1;C1) 5 where A1, B1 and C1 contain the values 4, 5 and 6 respectively 32 COUNT Function The COUNT function is used to count the number of numeric values in a range of cells. For example: Formula Result =COUNT(5;8;14;19) 4 =COUNT(A1:A10) MAX Function The MAX function is used to find the maximum of numbers in a given range of cells. For example, Formula Result =MAX(74;102;134) 134 =MAX(A1;B1;C1) Or =MAX (A1:C1) 6 where A1, B1 and C1 contain the values 4, 5 and 6 respectively MIN Function The MIN function is used to find the minimum of values in the given range of cells. For example; Formula Result =MIN(74;102;134) 74 =MIN(A1;B1;C1) Or =MIN (A1:C1) 4 where A1, B1 and C1 contain the values 4, 5 and 6 respectively Worksheet 1. Every formula in Calc starts with which symbol? _____________ 2. The formula in cell B6 is ‘=MIN(B1:B5)’. When copied to C6, the formula changes to ________________. 2. If A1:A4 contains the numbers 11, 13, 15, 17, the formula =AVERAGE(A1:A4) in cell A5 will display ______________. 3. Cell B1 contains 12 and C1 contains 8. What will be the content of cell D1, if the formula =B1*C1/2 is entered in cell C1? ___________________ 4. The contents of cell B2, C2, D2, and E2 are 13, 15, 17, 19 respectively. What will be the value in cell E2, if the formula =MAX(B2:E2) is entered in cell E2? ___________ 5. What is the difference between MAX and MIN functions in Calc? ____________________________________________ ____________________________________________ 33  1.9 PASSWORD PROTECTION In OpenOffice Calc, you can protect your spreadsheet with a password. To protect your data, you can either assign a password to a sheet or Calc document. This can be done using Tools menu and aslo while saving the document. 1.9.1 Using Tools menu The steps to protect worksheet or Calc document using option of Tools menu are: 1. Select Tools menu Protect document Choose whether to protect Sheet or Document. (Fig 32 ). 2. If you select Sheet, the Protect Sheet dialog box appears. (Fig 33). Fig 33 Protect Sheet dialog box Fig 32 Tools menu If you select Document, the protect Document dialog box appears. 3. Type the password in Password text box. Again type the password in Confirm text box. Note that the password is case sensitive. 4. Click OK button. Tip Undoing Password Protection Rules to select a password: To remove a password, open the document, then save without Length of 8 or more characters password Mix of lowercase and uppercase letters, numbers and special characters 1.9.2 Protecting Calc doument while Saving You can also protect your spreadsheet with a password while saving the document. The steps are: 1. Select Save As option from File menu. 2. The Save As dialog box appears. Select the drive and the folder where you want to save the file. 34  3. Select Save with password check box. (Fig 34) Fig 34 Set Password dialog box box 4. Click Save button. 5. The Set Password dialog box appears. (Fig 35) 6. Enter the password to open. Again type the Fig 35 Set Password dialog box password in Confirm password text box. 7. Next, click on More options. The dialog box expands as shown below in figure 36. 8. Here, you can give file sharing password. You can select the check box of Open file read-only option, if you want the recipient to only read the file and make no changes. You can also enter password to allow editing. 9. Click OK button. Fig 36 1.10 PRINTING A SPREADSHEET Select File Page Preview to view a worksheet so as to get an idea of how it will look when printed. or Click the Page Preview button on the Standard bar (Fig 37). Print button Page Preview Fig 37 35 To print a worksheet, the steps are: 1. Click File Print. 2. The Print dialog box will appear (Fig. 38). 3. Select the printer, the range to be printed, and the number of copies. 4. Click the Print button. To quickly print without getting the Print dialog box, click the Print button on the Standard bar. (Fig 37) Fig. 38 Print dialog box Lets Try it 4. Create the following worksheet and perform calculations using functions in Calc. 36  Lets Try it 1. Create the following worksheet and calculate total and percentage for each student. 2. Create the following worksheet. Calculate area and perimeter of square. 3. Create the following worksheet. Calculate area and perimeter of rectangle. 37  Worksheet 1. How will you refer to a cell in column B and row 3? 2. How will you refer to range of cells in row 5? 3. How will you refer to range of cells in column D through F and rows 3 through 8? 4. Write mathematical operator for the following operation: a. Multiplication b. Division 5. If formula in cell C3 is =A3+B3, what formula will be copied in cell D3? 6. How will you write a function to find average of numbers 5, 12, and 17? 7. Name the function used to find total of numbers in the range of cells A1 to A10. 8. Name the menu used to print the document. 9. What error will you get if the column is not wide enough to display the value? 10. What happens if you write the formula =45/0 in cell A1? 1.11 SAVING A SPREADSHEET IN VARIOUS FORMATS We have already learnt how to save a document in OpenOffice Calc. The default extension of a Calc file is.ods. 1.11.1 Saving in Microsoft Excel Format If you want to save the file in the Microsoft Excel file format, then do the following while saving: 1. Select Save As option from the File emnu. 2. The Save As dialog box appears. (Fig 39) 3. Change the Save as type to Microsoft Excel 97/2000/XP(.xls). 4. Delect the drive and the folder where you want to save the file. 5. Type the filename and clcik Save button. Fig 39 Save AS dialog box 38 1.11.2 Saving in PDF format Sometimes, when saving a document, you do not want the recipient to modify it. The safest way is to save the document in PDF (Portable Document Format) format and then share it. The simplest way to do this is: 1. Click on the Export Directly as PDF icon on the Standard bar. This will export the entire document using the default PDF settings. 2. The Export dialog box appears. 3. Select the drive and the folder where you want to save the file. 4. Type the file name and clik OK button. 39 40 41 70