Comprehensive notes Class 10 Analyse data using scenarios and goal seek with Libre Office Calc

In this article, I am going to discuss Analyse data using scenarios and goal seek with Libre Office Calc for Information Technology Class 10. This is first learning outcome of Unit 2 Electronic Spreadsheet Advanced for class 10. Lets begin!

Analyse Data using scenarios and goal seek with Libre Office Calc

Data analysis is one of the important aspects of routine work for any organization. The spreadsheet software is the best software for analyzing data.

Libre Office Calc is a spreadsheet software that is used to analyze data easily and effectively. Spreadsheet allows to represent the data in tabular form to do the following:

1. retrieve
2. correlate
3. explore and visualize data to identify patterns, trends, and relationships
4. manipulate data
5. interpret results

Analysing data is the process to extract useful information for making effective decisions.

So let us begin Analyse Data using scenarios and goal seek with Libre Office Calc with Consolidating data.

Consolidating Data

Data consolidation is a tool that allows to use of a function and combine data from multiple sheets together and summarize data in a single worksheet.

In other words, Data consolidation refers to extracting data from multiple sheets and making a summary in a different sheet using a function.

To consolidate data, the following things are necessary:

1. Prepare multiple sheets with common data entries
2. Data types provided in the different sheets must match with each other
3. All sheet labels should match
4. The first column must be a primary column for data consolidation

Steps to Consolidate Data

To consolidate data follow the given steps:

[1] Prepare worksheets as follows and rename them as PT 1, and PT 2 respectively:

[2] Now create a blank spreadsheet with matched labels to consolidate data as follows and rename as consolidated:

[3] Place the cursor in C2 cell of the consolidated sheet as displayed below:

[4] Click on Data –> Consolidate

[5] The consolidation dialog box appears.

Options:

1. Function: This option provides functions to summarize records in a consolidated sheet. The functions are as follows:
   • Sum
   • Count
   • Average
   • Max
   • Min
   • Product
   • Count(numbers only)
   • StDev(sample)
   • StDevP(population)
   • Var(sample)
   • VarP(population)
2. Consolidation Ranges: Here the range of cells will be added. Ranges can be created or selected randomly.
3. Source Data Ranges: This option contains name ranges defined through the Data –> Define Ranges option. By default -undefined- ranges are given.
4. Copy Results to: This option specifies the range of cells where the result needs to be displayed.
5. Add: To add range of cells for data consolidation.
6. Delete: To delete range of cells from data consolidation.
7. Options: The options button has the following options for consolidated data:

• Consolidated by – Allows to consolidate data according to row or columns
  • Row Labels
  • Column Labels
• Options – Link to source data allows to change the consolidated result when changes are made to the original data,

[6] Choose the appropriate options as per requirement such as function, add the ranges of cells, and choose an additional option then click on OK.

Add range to data consolidation:

[7] Click on the OK button, finally.

Result:

Steps to define range:

Spreadsheet allows to define a range of cells with a specific name. To define range in Libre Office Calc follow these steps:

[1] Select the range of cells which is going to be defined.

[2] Click on Data –> Define Range option.

[3] Define Database Range dialog box appears as displayed below:

[4] Type the Range Name in the Name box and click on the Add button.

[5] Click on OK button.

Creating Subtotals

Libre Office Calc provides an option to Group records according to a specific column and creates an outline from them.

Subtotals in spreadsheets create an automatic group based on a columns having repeated data and aggregates the result in a group.

It uses a summary function like sum, max, min, average, etc. to group data in a spreadsheet. The data can be grouped into multiple columns. It can group subtotals on specific columns and sort them in ascending order or descending order.

Subtotals Dialog box in Libre Office Calc

To open the subtotal dialog box in the libre office calc follow these steps:

Click on the Data –> Subtotals option.

The subtotals dialog box in Libre Office Calc has the following options:

1. Grouping and Options tabs: The subtotals dialog box contains tabs for groping records like 1st Group, 2nd Group, 3rd Group, and Options tabs. The grouping tabs (1st Group, 2nd Group, 3rd Group) provide options such as Group by to select a column for the subtotal group. It also allows selecting all columns for grouping.
   • Options tab: This tab has two options as follows:
     • Groups: The groups option has three sub options:
       • Page break between groups: Allows page break between groups
       • Case sensitive: Checks for case sensitivity for grouping column names
       • Pre-Sort area according to groups: Allows sorting and enables sort option
     • Sort: By selecting this option data can be sorted in Ascending or Descending order including formats and custom sort orders such as months and days.
2. Calculate subtotal for: This option allows to select a column to compute the subtotal on it. All columns present in the worksheet are available here for selection. Any number of columns can be selected at a time for subtotal calculation.
3. Use Function: It provides various functions to compute subtotal. These functions are : Sum, Count, Max, Min, Product, Count (Numbers Only), StDev(Sample), StDevP(Population), Var(Sample), VarP(Population).

Practical Activity – Creating Subtotals

Observe the data given below and create subtotals for the same.

Here I am going to display sum of products in ascending order with subtotals. So lets begin!

[1] Select the data and click on Data –> Subtotals option.

[2] The subtotals dialog box appears. Select the column on which grouping option is required. Select the column against the option Group by as below here I have selected Product column, Select column to Calculate subtotals for and Sum function:

[3] Click on OK button and it will display the result as follows:

Here subtotals created for given data. On left of the results the outline is created automatically. By clicking on + and – sign it can be expanded and collapsed. This outline can be removed using Data –> Group and Outline –> Remove Outline option.

What-if Scenarios

A what-if scenario is one of the spreadsheet tools that allows you to save a set of values under various names and present them under a specific name assigned. Users can create several scenarios on one spreadsheet.

What-if scenarios are used for the following purposes:

1. To explore and compare values based on changing conditions
2. It can provide optimization of output for any project
3. It generates various outputs with different kind of input that allows users to choose the best result for them

Example Creating a Scenario

Let’s create a scenario for a team’s net run rate in a cricket tournament. Net run rate is calculated by the below-given inputs match by match:

1. Runs scored in each match
2. Overs faced in each match
3. Runs given in each match
4. Overs bowled in each match

The formula to calculate the net run rate is:

RR=(runs_scored/overs_faced)-(runs_given/overs_bowled)

For each match, I have created a different scenario and named them as Match 1, Match 2 , Match 3 etc.

The screenshots are as follows:

Steps to create a scenario

Follow the below given steps to create a scenario:

[1] Select the cells that contain values.

[2] Click on the Tools –> Scenarios option. The Create Scenario dialog box will appear as displayed below.

[3] Type the scenario name in the Name of Scenario box, Edit the comment in the comment box, and Change the desired color from settings.

[4] Click on the OK button.

[5] Repeat steps 3 and 4 for each scenario, and change the date, name, and desired options as per the requirements.

Goal Seek

Goal Seek allows you to set a target value by changing variables in a spreadsheet. It requires a cell to have a formula and input variables. The input variables can be changed to get the desired value set as a target. It is one of the what-if tools.

For example, Priya is a class 10 student. Her PT 1 result is as follows:

She aimed to score 90% marks. But she feels she got less marks in Social Science. So here goal seek help her to see what exact mark she needs to score to score in Social Science to achieve 90%.

The Goal Seek dialog box in Libre Office Calc

To open Goal Seek dialog box in Libre Office Calc click on Tools –> Goal Seek option. The Goal Seek dialog box looks like as follows:

It provides three options:

1. Formula cell: It is a cell which contains a formula.
2. Target value: It is the values to be achieved using goal seek
3. Variable cell: It is the cell in which the value needs to be changed

Steps for Goal Seek

To use Goal Seek option follow the given steps:

[1] Place the cursor in the cell in which Formula has been entered.

[2] Now click on Tools –> Goal Seek… option.

Goal Seek dialog box will appears.

[3] By default it shows the absolute cell reference on first box which contains a formula. Type the target value in the target value box and choose the cell reference to be changed to achieve the desired result.

[4] Click on OK button. This will open a message box to display the message such as “Goal Seek succeeded. Result 22.5. Insert the result into the variable cell?”

[5] Click on Yes button to keep the value otherwise No. The value is to be displayed in the cell as below:

Solver in Libre Office Calc

The solver option is present in the Tools menu. It is a more elaborated form of Goal Seek. The solver is operated on multiple variables. It is designed to minimize or maximize optimization according to rules and specified conditions.

The solver dialog box

The solver dialog box contains the following options:

You can see these options in the dialog box:

Steps to apply solver

To apply solver in Libre Office Calc, follow the given steps:

[1] Select data and click on Tools –> Solver option.

[2] The solver dialog box appears as discussed above.

[3] Apply different options to solve. Here I have applied the following:

1. Target Cell: $B$7
2. Value of: 95
3. By changing cells: $B$2,$B$4,$B$5,$B$6
4. Liming Conditions: Cells $B$2,$B$4,$B$5,$B$6 <=25

[4] Click on Solve button. It will display a new message box with a message –

“Solving successfully finished.

Result:95

Do you want to keep the result or do you want to restore previous values?”

As displayed in the above dialog box click on the relevant button to keep the result in the sheet.