Spreadsheet Tools: OO Calc – I
Que 1: What is a cell and how is it referred in OOo Calc?
Ans: A cell is the intersection of a row and a column. It is a basic unit of worksheet where numbers, text, formulas, etc. can be entered.
The cell is referred by Cell address.
Every cell in OpenOffice Calc has a unique address, called a Cell Address. It is the combination of a column letter and a row number. For example, A1, Z25, C19, etc.
Que 2: What do you mean by a range of cells?
Ans: A range of cells is a group of cells that forms a rectangular area in shape. A range is specified by giving the addresses of the first cell in the range and the last cell of the range.
start cell address : last cell address
: (colon) is the indicator of range in OOo Calc.
For example; A2 : D9, B12 : H25, etc.
Que 3: What is the difference between a worksheet and a workbook?
Ans: A worksheet is a grid of cells made up of horizontal rows and vertical columns. Number of rows and columns vary from package to package. OpenOffice Calc can have a maximum of 1,024 columns in each sheet. Each sheet can also have a maximum of 1,048,576 rows.
A workbook or spreadsheet is a combination of multiple worksheets. A workbook/spreadsheet can have up to 256 sheets/worksheets.
Que 4: What are the different types of data that can be entered in OOo Calc?
Ans: Different types of data that can be entered in OOo Calc are
- Numbers
- Text
- Date and Time
- Formula
Que 5: The keyboard shortcut for Copy is _____ and for Paste is _____.
Ans: Ctrl + C, Ctrl + V
Que 6: What will happen to the contents of the destination cell if you copy the contents of the source cell into the destination cell?
Ans: The destination cell value gets changed according to the source cell. Now both, source and destination cells contain the same value.
Que 7: What is the name of the package which permits people to quickly create, manipulate and analyse data arranged in rows ad columns?
Ans: Spreadsheet Package
Que 8: What does an electronic spreadsheet consist of?
Ans: Rows, Columns, and Cells (All the above)
Que 9: State whether the following statements are true or false:
(i) When you increase the font size the row height is automatically adjusted.
(ii) By default, the numbers are left-aligned and text values are right-aligned.
Ans: (i) True (ii) False
Que 10: Write the cell reference of the following:
- (i) Cell formed by intersection of row 18, and column Z.
- (ii) The rightmost cell in row 32 in a worksheet.
- (iii) If you select on the entire worksheet, which range of cells gets selected?
- (iv) Reference (Fixed column and Relative row) formed by row 134 and column BD.
- (v) Mixed Reference (Relative column and Fixed row) formed by row 120 and column IA.
- (vi) Absolute Reference formed by 45 and column Z.
- (vii) Relative reference formed by row 19 and column AB.
Ans: (i) Z18
(ii) AMJ32
(iii) A1:AMJ1048576
(iv) $BD134
(v) IA$120
(vi) $Z$45
(vii) AB19
Que 11: Suggest the Calc functions that can be used for carrying out the following operations:
- (i) To calculate total marks of a student if his marks in five subjects are given in five different cells.
- (ii) To calculate average sales made by a salesman of a company, if sales made by each of the salesman is available.
- (iii) To find out the marks of top scorer in a class, if marks of all the students are available.
- (iv) To find out the minimum quoted rate from various quotations available.
Ans: (i) SUM
(ii) AVERAGE
(iii) MAX
(iv) MIN
Que 12: What are the rules to be followed while entering the following OOo Calc?
(i) numbers (ii) text (iii) formulas
Ans: Rules to be followed while entering the following in OOo Calc are
(i) Numbers:
- These are numeric entries. These entries can be used in calculations.
- A number can contains 0,1,2,3,4,5,6,7,8,9,+,-,/,(),%,e,E
- Numbers are right aligned.
(ii) Text
- A combination of alphabets, digits, and symbols is known as Text.
- The text is left-aligned in cell.
- Text entries are not used in calculations.
(iii) Formula
- The formula contains values, cell addresses, operators, or function names.
- It produces a new result or value.
- In OOo Calc, the formula start with ‘=’ sign.
Que 13: Explain what are the different methods of cell referencing in OOo Calc?
Ans: Every cell in the worksheet has a unique address formed by the combination of its intersecting column and row. This is called cell reference or cell address.
In OOo Calc, a cell can be referenced in three ways:
- Relative Referencing: When cell reference is used like ColumnNameRowNumber i.e. E6 in the formula, it is called Relative Referencing.
During the copying of the formula the relative cell addresses automatically get adjusted with respect to the current cell where the formula is being copied. - Absolute Referencing: When a $ symbol is used before the part of the formula (column and row), then cell reference becomes absolute. For example $E$6
Absolute cell reference does not change when you copy the formula from one cell to another cell, only the relative cell reference will update. - Mixed Referencing: A cell address contains two parts – a column letter and a row number. If you put the $ before any one part only, then it is called Mixed Referencing.
In this case, if you copy the formula, then only that part of the cell address will update which does not contain $ symbol.
Que 14: Do it by Yourself
Ans: Practical Based Question
Que 15: Differentiate between mixed referencing and absolute referencing by giving suitable example.
Ans: Mixed reference is the combination of absolute and relative i.e $ symbol is used before either column letter or row number.
While absolute reference contains the $ symbol before the column letter and the row number of a cell address.
For example:
Mixed Reference: $A5, G$10
Absolute Reference: $A$5, $G$10
Que 16: What is the difference between copying and moving a range?
Ans:
| Copying | Moving |
| * Copying is used to make the duplicate copy of the text/number/formula i.e. content. * In copying, the content remains at their original position and the duplicate copy is created at another position. | * Moving is used to move the text/number/formula i.e. content from one cell or range to another cell or range. * In moving, the contents deleted form original position and moved to another position. |
Que 17: What is the difference between the following commands:
(i) Edit -> Delete Contents -> Delete all and
Edit -> Delete Contents -> Text
(ii) Edit -> Delete Contents -> Text and
Edit -> Delete Contents -> Formats
(iii) Edit -> Delete Contents -> Text and
Edit -> Delete Contents -> Notes
Ans: (i)
- Edit -> Delete Contents -> Delete all will delete all contents including formats, comments, etc.
- Edit -> Delete Contents -> Text will delete only text, not numbers, formats, comments, formulas etc.
Ans: (ii)
- Edit -> Delete Contents -> Text will delete only text, not a format, numbers, formula, comments, etc.
- Edit -> Delete Contents -> Formats will delete formats only.
Ans: (iii)
- Edit -> Delete Contents -> Text will delete only text, not a format, numbers, formula, comments,
- Edit -> Delete Contents -> Notes will delete only comments.
Que 18: Create the following worksheet and save the workbook as WAGES.ods
(i) Find out the number of days each worker has worked, by subtracting date on which worker has hired from today’s date.
Ans: =$C$3 – B7
(ii) Calculate Gross wages for each worker. The gross wages can be calculated by using the following formula:
Gross Wages = No. of days worked * Pay rate
Ans: =C7*$C$4
Que 19: Do it by Yourself
Ans: Practical Based Question
Que 20: Do it by Yourself
Ans: Practical Based Question
Que 21: Do it by Yourself
Ans: Practical Based Question
Que 22: Do it by Yourself
Ans: Practical Based Question
Que 23: Do it by Yourself
Ans: Practical Based Question
Que 24: Do it by Yourself
Ans: Practical Based Question
Que 25: Do it by Yourself
Ans: Practical Based Question
Que 26: Write commands for the operations (i) – (ii) based upon the spreadsheet shown below:
(i) To calculate the total salary as sum of Basic Salary, HRA, and DA for each employee for the year 1997.
Ans: =B2+B2*C2/100+D2
(ii) To calculate the total salary of each employee for the year 1998 as sum of salary for the year 1997 and bonus. Also, calculate the percentage increase in the total salary from 1997 to 1998 for each employee.
Ans: =SUM(E2;F2) or = E2 + F2
Formula to calculate Percentage Increase
= F2 * 100 / E2
Final sheet
