Chapter 4 Analyse data using Scenarios and Goal Seek NOTES Important Points
Chapter 4 Analyse data using Scenarios and Goal Seek NOTES Important Points
Introduction
Data Analysis a is the process to extract useful information for making effective decisions. The spreadsheet is one of the best software used for data analysis. The spreadsheet component in LibreOffice known as Calc includes several tools for data analysis.
Consolidating Data
Consolidate is a function used to combine information from multiple sheets of the spreadsheet into one place
to summarize the information.
We need to check the following before consolidating data
- Data types of the data which we want to consolidate should be same in all spreadsheet.
- We should match the labels from all the sheets which are used for consolidating.
- The first column should be same in all spreadsheet on the basis of which the data is to be consolidated.
Steps to consolidate the data are as follows:
- Open the spreadsheet which has the data to be consolidated.
- Open the consolidate dialog box by Clicking Data > Consolidate.
- Choose the required function from the drop-down list. like Sum, Average etc.
- Add the ranges from the sheets which is to be consolidated.
- Select the cell under ‘Copy results to’ where we want to display result.
- Select the appropriate checkboxes under options and click on OK button.
Note the following:
‘Consolidate by’ has two options Row labels and Column labels. Check row label or column label or both if you want to consolidate it by matching the label.
If Link to source data is checked, then it will keep on updating the data of the Consolidate sheet automatically if there is any change made in the selected ranges.
Groups and Outline
Group and Outline in Calc is used to create group of rows and columns together so that one can collapse (-) to hide it or expand (+) it using a single click on it.
Select the data to be grouped, click on Data>Group and Outline>Group. Choose Rows or Columns on the basis of which you want to group the data and click OK
NOTE: Shortcut to group data is F12 and to ungroup data is Ctrl + F12.
Subtotals
The Subtotal tool in Calc creates the group automatically and applies common functions like sum, average on the grouped data. We can use any type of function for each column as per the requirement of data analysis.
Steps for applying Subtotal are given below
- Open the spreadsheet and Click on Data menu and choose Subtotals.
- Choose the column in the Group by list which is to be used for grouping the data.
- Select the column by clicking the checkbox under Calculate subtotals for to create subtotals.
- Select the desired function. (By default function is Sum)
- Click on OK button.
NOTE: Columns should have label(column heading) on which we are applying Subtotal.
After performing subtotal, you can see outline to the left of the row numbers. This outline shows the hierarchical structure where we can expand or collapse the data by clicking on ‘+’ or ‘-‘ sign respectively.
If you want to remove the outline feature then click on Data > Group and Outline > Remove Outline
What-if Scenarios
What-if scenario is a set of values that can be used within the calculations in the spreadsheet.
It can be used in the beginning of any project to optimise the output. This tool is used to predict the output while changing the inputs and thus one can choose the best plan.
Steps to create scenario are given below
- Select the cells which contains values in the sheet that needs to be changed.
- Choose Tools>Scenarios.
- Enter a name for the new scenario.
- Click on OK button
What-if Analysis Tool
What-if tool uses Data > Multiple Operations and is a planning tool for what-if questions.
The Multiple Operations tool creates a formula array to display the list of results on a list of values used in the formula.
This tool uses two arrays of cells, one array contains the input values and the second array uses the formula and display the result. What-if analysis tool is very helpful to know how much profit we earn for a particular product for a series of selling units.
Following steps are used for what-if analysis tool:
- Enter the data in the cells and then enter a formula to calculate a result from the data entered.
- Create an array of input values on the basis of which the output is to be generated.
- Select the cell range of input array and output array.
- Click on Data>Multiple Operations
- Enter the cell address where we applied formula in the dialog box.
- Enter the address of variable cell.
- Click on OK
Goal Seek
In general we fill in the values in the cells and then create formula on these values to get the required result. Goal seek helps in finding out the input for the specific output. for example
A student has received marks in 4 subjects and has to appear for the 5th subject and plans an aggregate as 70. So, he can use goal seek tool to check how many marks he has to score in the 5th subject to get the required percentage.
Steps for Goal Seek are given below:
- Enter the values and write the formula in the cell.
- Choose Tools > Goal Seek.
- Enter the address of cell in the “formula cell” box.
- Enter the address of cell whose value is to be predicted or unknown in “Variable cell” box.
- Enter the target value and click on OK.
Important links of Class X (IT – 402)
Unit 1: Digital Documentation (Advanced) using LibreOffice Writer
Chapter 1. Introduction to Styles – NOTES
Chapter 1. Introduction to Styles – Question Answers
Chapter 2. Working with Images – NOTES
Chapter 2. Working with Images – Question Answers
Chapter 3. Advanced features of Writer – NOTES
Chapter 3. Advanced features of Writer – Question Answers
UNIT 1: DIGITAL DOCUMENTATION (ADVANCED) – MCQ
UNIT-2: ELECTRONIC SPREADSHEET (ADVANCED) – MCQ
UNIT-3 RELATIONAL DATABASE MANAGEMENT SYSTEMS (BASIC) – MCQ
Disclaimer : I tried to give you the simple notes of “Chapter 4 Analyse data using Scenarios and Goal Seek NOTES Important Points” , but if you feel that there is/are mistakes in the notes of “Chapter 4 Analyse data using Scenarios and Goal Seek NOTES Important Points” given above, you can directly contact me at csiplearninghub@gmail.com. NCERT Book and Study material available on CBSE Website are used to create above article “Chapter 4 Analyse data using Scenarios and Goal Seek NOTES Important Point”.