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:

Analyse data using scenarios and goal seek with Libre Office Calc

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

Consolidated sheet empty - Analyse data using scenarios and goal seek with Libre Office Calc

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

place the cursor where consolidation starts

[4] Click on Data –> Consolidate

Data consolidate option

[5] The consolidation dialog box appears.

Data Consolidate dialog box in libre office calc

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:
Data consolidate options button in libre office calc
  • 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:

add cell ranges to data consolidations
Add range 2

[7] Click on the OK button, finally.

Result:

data consolidation ranges

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.

selection of range of cells to be defined

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

Data Define range option in Libre office calc

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

Define Database Range

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

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

[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.

creating subtotals in libre office calc

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.

Data for creating subtotals

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.

data subtotals options in libre office calc

[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:

subtotals in libre office calc

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

Subtotals results in libre office calc

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.

Remove outline in libre office writer

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:

Scenario in libre office calc match 1
Scenario in libre office Match 2
Scenario in Libre Office Match 3

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.

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

[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.

Applying settings for scenarios

[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:

Goal Seek data for libre office calc

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:

The goal seek dialog box in Libre Office calc

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.

Goal Seek libre office calc step 1

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

Goal Seek in Libre office calc

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.

Goal Seek dialog box

[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?”

Message box display after goal seek

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

Goal Seek result display

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:

Solver dialog box in Libre office calc

You can see these options in the dialog box:

Solver options in libre office calc

Steps to apply solver

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

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

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

[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
solver dialog box in libre office writer with input

[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.

Leave a Reply