Electronic Spreadsheet question answers have been given here in this post. Practice these Class 10 IT unit 2 Electronic Spreadsheet Question Answers to score best in your final exam.
Electronic Spreadsheet Question Answers
Define and Explain the usage of the following terms with Reference to OpenOffice Calc.
(a) Consolidating data (b) Subtotals (c) Goal seek (d) Scenario (e) Solver
(a) Consolidating data
Data Consolidation allows you to gather together your data from separate worksheets into a master worksheet.
The Data Consolidation function takes data from a series of worksheets or workbooks and summarizes it into a single worksheet that you can update easily. Consolidate provides a graphical interface for copying data from one range of cells to another, then running one of a dozen functions on the data. During consolidation, the contents of cells from several sheets can be combined in one place.
SUBTOTAL: totals/add data arranged in an array—that is, a group of cells with labels for columns and/or rows. Using the Subtotals dialog, you can select arrays, and then choose a statistical function (sum, average, max, min) to apply to them. . For efficiency, one must choose up to three groups of arrays to which to apply a function.
( c) Goal seek
Using the Goal Seek option under the Tools menu, you can discover what values will produce the result that you want.
Goal seeking is a general term used to describe the process involved in figuring out your input value based on an already known output value. The process involves using a specific operator in a formula, which can be calculated using computer software.
Example: Set Cell: It specifies the cell whose value will be changed to the desired value after the result of the Goal Seek operation.
Example: Jack scored 25 out of 30 in English, 22 out of 30 in Maths. He wants to calculate the score in IT he needs to achieve 85 percent in aggregate. So here goal is set and according to it Jack will find one unknown variable IT marks.
Scenarios are a tool to test “what-if” questions. Each scenario is named, and can be edited and formatted separately. When you print the spreadsheet, only the content of the currently active scenario is printed. A scenario is essentially a saved set of cell values for your calculations. You can easily switch between these sets using the Navigator or a drop-down list which can be shown beside the changing cells.
For example, if you wanted to calculate the effect of different interest rates on an investment, you could add a scenario for each interest rate, and quickly view the results. Formulas that rely on the values changed by your scenario are updated when the scenario is opened. If all your sources of income used scenarios, you could efficiently build a complex model of your possible income.
The solver option under the Tools menu amounts to a more elaborate form of Goal Seek. The difference is that the Solver deals with equations with multiple unknown variables. It is specifically designed to minimize or maximize the result according to a set of rules that you define.
Each of these rules sets up whether an argument in the formula should be greater than, lesser than, or equal to the value you enter.
For example, you can set the constraint that one of the variables or cells must not be bigger than another variable, or no bigger than a given value. You can also define the constraint that one or more variables must be integers (values without decimals), or binary values (where only 0 and 1 are allowed). Once you have finished setting up the rules, you can adjust the argument and the results by clicking the Solve button.
Explain how to create a reference to (a) Other worksheets within the same workbook (b) worksheet of another workbook with keyboard and mouse.
What are the two ways of referencing cells in other worksheets?
a) Other worksheets within the same workbook
- Click on the = icon next to the formula bar.
2. Now, click on the sheet tab for the sheet containing the cell to be referenced. (eg. Sheet2)
3. Click on cell F3 (where the balance is) in sheet2. The phrase ‘Sheet2’.F3 should appear in the formula bar. With the help of a keyboard, you have to type down the above formula in the formula bar.
b) Worksheet of another workbook
- Click the = icon next to the formula bar.
2. Switch to the other spreadsheet (the process to do this will vary depending on which
operating system you are using).
3. Select the sheet (Savings account or any) and then the reference cell (F3).
Creating The Reference With The Keyboard
Typing the reference is simple once you know the format the reference takes. Thereference has three parts to it:
- Path and file name
- Sheet name
Looking at the figure above, you can see the general format for the reference is
=’file:///Path &File Name’#$SheetName.cell name.
Differentiate between relative and absolute hyperlinks.
An absolute hyperlink will stop working only if the target is moved. A relative hyperlink will stop working only if the source and target locations change relative to each other. Suppose, if you have two spreadsheets in the same folder linked to each other and you move the entire folder to a new location, a relative hyperlink will not break a link.
How can we rename a worksheet in Spreadsheet?
There are three ways you can rename a worksheet, and the only difference between them is the way in which you start the renaming process. You can do any of the following:
- Double-click on one of the existing worksheet names.
- Right-click on an existing worksheet name, then choose Rename from the resulting Context menu.
- Select the worksheet you want to rename (click on the worksheet tab) and then select the Sheet option from the Format menu. This displays a submenu from which you should select the Rename option.
What is the advantage of sharing worksheet data?
- Speed up data entry
- Make things easier for collaboration purposes.
Explain features and use of Record changes.
Calc has the feature to track what data was changed when the change was made, who made the change, and in which cell the change has occurred.
A colored border, with a dot in the upper left-hand corner, appears around a cell where changes were made. Other reviewers then quickly know which cells were edited. A deleted column or row is marked by a heavy-colored bar.
Example: If you are the sponsor of a youth baseball team. The coach has submitted a budget to you for the season and you need to edit the costs and return them to her. You are concerned that if you just make the changes, then the coach won’t see the changes you made. You decide to use Calc with the record changes feature turned on so that the coach can easily see the changes you have made.
What is the purpose of adding comments?
Reviewers and authors can add their comments to explain their changes.
How can we add comments to the changes made?
To add a comment to a change:
1. Make the change to the spreadsheet.
2. Select the cell with the change.
3. Choose Edit > Changes > Comments. The dialog shown below appears. The automatically-added comment provided by Calc appears in the title bar of this dialog and cannot be edited. 4. Type your own comment and click OK.
Explain the feature of accepting or reject changes.
When you receive a worksheet back with changes, the beauty of the recording changes system becomes evident. Now, as the original author, you can step through each change and decide how to proceed. To begin this process:
1. Open the edited worksheet.
2. Select Edit > Changes > Accept or Reject. The dialog shown below opens.
3. Calc steps through the changes one at a time. You can choose to accept or reject each change as you go through it. or you can also select Accept all and reject all if you want to do so.
What are Macros? How can we record a Macro?
Macros help in saving time in cases when the same set of tasks are to be done repeatedly like formatting or applying a similar formula in a similar range of data. It can be used to name and record a set of actions.
Procedure to record a macro:
- input data
- before you do any action click on tools->macro->record macro
- now perform the action you want to repeat
- click on stop recording. now save macro by giving name to macro