Unit 2 – Electronic Spreadsheet (Advanced) – Info Tech Code 402
Session 1: Analyse Data using Scenarios and Goal Seek
Important Question Answer
1. What is OpenOffice Calc?
Answer: OpenOffice Calc is the most popular open-source spreadsheet program. It helps you to do calculations automatically. It is mainly used to process numerical data like Results, Earnings, Expenses, Profit and Loss, etc.
2. What do you mean by Analysing Data? Which tools of OpenOffice Calc are used to perform this?
Answer: Analysing data means processing data and getting the result or summary quickly which helps in making decisions.
OpenOffice Calc helps in doing this. It helps in creating subtotals automatically and to help in finding the answers you need.
Tools available in OpenOffice Calc are:- Consolidate, Subtotal, Scenarios, Goal Seek & Solver.
3. What is Data Consolidation?
Answer: The Data Consolidation function takes data from a series of worksheets or workbooks and summaries them into a single worksheet that you can update easily.
Data Consolidation is a process of collecting and consolidating data from multiple sources into a single worksheet, while applying a specific function such as sum, average, count, max, min, etc.
4. When do you want to use Data consolidation?
Answer: Data Consolidation can be used, when you want to gather together your data from separate worksheets into a master worksheet
You can use the Consolidate feature to combine the data from two or more ranges of cells into a new range while running the functions Sum, Average, etc., on the data.
5. Consolidate option is available in which menu?
Answer: Data Menu.
6. How do you consolidate data in calc? or Write the steps of data consolidation?
Answer: To do Data consolidation, follow these steps: –
- (i) Open the worksheet that contains the cell ranges to be consolidated.
- (ii) Choose the Consolidate option under the Data menu. It will open the Consolidate dialog.
- (iii) Select the source data range.
- (iv) Click Add to insert the selected range in the Consolidation ranges field.
- (v) Select additional ranges and click Add after each selection.
- (vi) Specify where you want to display the result by selecting a target range from the Copy results to box.
- (vii) Select a function from the Function list. The Sum function is the default setting.
- (viii) Click OK to consolidate the ranges.
7. How do you define a Range in OpenOffice Calc?
Answer: Define Range option is available under the
Data Menu. To define a range, follow these steps: –
- (i) Select the range, which name you want to define.
- (ii) Click the command Data->Define Range. It will open the Define Database Range dialog box
- (iii) Under the Name box, type the name of the range.
- (iv) Click Add to add the range.
- (v) Click OK to close the dialog box.
8. What is the command used to consolidate data in Calc?
Answer: Data -> Consolidate
9. What check boxes are shown when you click on the More button under the Consolidate dialog box
Answer: Under Consolidate By, two check boxes are available. These are
[ ] Row labels and [ ] Column labels, and
Under Options, only one checkbox is available named – [ ] Link to Source data.
10. What is the advantage of selecting Link to source data under the consolidate dialog box?
Answer: If you tick the [ ] link to source data, then the summary sheet will update automatically when you change the value of the consolidated range.
11. What is Subtotal?
Answer: SUBTOTAL is a function listed under the Mathematical category, which totals/add data arranged in an array i.e group of cells with labels for columns or rows or both.
12. Write the command to do Subtotals. In which menu Subtotals is available?
Answer: Data -> Subtotals.
SUBTOTALS is available in the Data menu.
13. How SUBTOTALS works?
Answer: SUBTOTAL, totals/adds 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 to apply to them.
- Choose the group of arrays (up to three groups) to which to apply a function.
- When you click OK, Calc adds subtotals and grand totals to the selected arrays.
14. Write the steps to do Subtotal in OpenOffice Calc.
Answer: Before starting SUBTOTLAS, you Ensure that the columns have labels.
Steps to insert subtotal values into a sheet:
- (i) Select the range of cells that you want to calculate subtotals for, and then choose Data-> Subtotals.
- (ii) In the Subtotals dialog, in the Group by box, select the column to that you want to add the subtotals. If the contents of the selected column change, the subtotals are automatically recalculated.
- (iii) In the Calculate subtotals for box, select the columns containing the values that you want to subtotal.
- (iv) In the Use function box, select the function that you want to use to calculate the subtotals.
- (v) Click OK
15. How many groups are available in the Subtotal in OpenOffice Calc.
Answer: Three
16. What is What-If Analysis?
Answer: What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet.
17. Write the name of What-If Analysis tools available in OpenOffice Calc?
Answer: OpenOffice Calc provides three tools for What-If Analysis. These are: –
(i) Scenarios (ii) Goal Seek (iii) Solver
18. How does What-If Analysis is helpful?
Answer: What-If Analysis is useful in creating scene or scenarios in one place for different values or conditions.
It helps in finding how much amount you need to invest for your future savings with the help of Goal Seek.
It helps in finding the value required for your Gaol with multiple options with the help of Solver.
19. Explain the following with respect to spreadsheets:
(a) Scenario (b) Goal Seek
Answer:
(a) Scenario: 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.
(b) Goal seek: Goal seek is one of the tools used in “what-if analysis” on computer software programs. A what-if analysis is a process of changing values in cells to see how these changes will affect formula outcomes on the worksheet.
