Analyse Data using Scenario and Goal seek

Chapter 4

CONSOLIDATION

ANALYSE DATA USING SCENARIO & GOAL SEEK SPREADSHEETDownload

What is Consolidating Data?

Data Consolidation is a feature in Calc used to combine and summarize information from multiple sheets into a single target sheet. It allows you to view, compare, and analyze diverse data in one location to identify trends and relationships.

Criteria for Consolidating Data

Before performing data consolidation, ensure the following conditions are met:

  1. Matching Data Types: The data types in the sheets you want to consolidate must match.
  2. Matching Labels: The row and column labels must match across all sheets.
  3. Primary Column: The first column must serve as the primary column on the basis of which the data is consolidated.

Steps to Consolidate Data

  1. Prepare sheets: Open your spreadsheet and create a new sheet where the consolidated result will be saved.
  2. Open Consolidate tool: Go to Data > Consolidate from the top menu.
  3. Choose function: Select the desired function from the Function drop-down list (the default is Sum).
  4. Add source ranges: Click inside the Source data ranges box, select the cell range from your first source sheet, and click Add. Repeat this step for all other sheets/ranges you want to combine.
  5. Set target location: Click in the Copy results to box and select the starting cell in your new target sheet.
  6. Configure options (optional): Click on Options to check Row labels or Column labels to consolidate by matching text headers, or check Link to source data so the consolidated sheet updates automatically whenever changes are made to the source data.
  7. Generate result: Click OK to complete the consolidation.

GROUPS AND SUBTOTALS

Groups and Subtotals

  • Group and Outline: Creates an outline of selected data, allowing you to collapse (-) to hide or expand (+) to view rows and columns with a single click. To apply manually, select the data and go to Data > Group and Outline > Rows (or Columns).
  • Subtotal Tool: Automatically creates groups and applies summary functions (like Sum or Average) to the grouped data by category, sorting them without needing filters.

Steps to Apply Subtotals

  1. Ensure labels exist: Make sure the worksheet columns have headers/labels.
  2. Open Subtotals: Click Data > Subtotals.
  3. Select group column: Choose the primary column to group by in the Group by list.
  4. Select target columns: Under Calculate subtotals for, check the boxes for the columns requiring subtotals.
  5. Choose function: Select the desired summary function under Use function.
  6. Set additional levels (optional): Use the 2nd Group and 3rd Group tabs to add further levels of grouping.
  7. Generate: Click OK.

(Note: You can remove the generated outline at any time by going to Data > Group and Outline > Remove Outline).

WHAT IF SCENARIO

What is a What-if Scenario?

A What-if Scenario is a named set of input values that can be used within calculations in a spreadsheet. It is used to explore, compare, and predict outcomes based on changing conditions to help choose the best plan of action.

Steps to Create a Scenario

  1. Select the cells containing the values that need to change (hold Ctrl and click to select multiple non-adjacent cells).
  2. Choose Tools > Scenarios from the menu.
  3. Enter a name for your new scenario and leave the other fields unchanged.
  4. Click the OK button.
  5. This creates the new scenario, which is activated automatically.

WHAT IF ANALYSIS

What is What-if Analysis?

What-if analysis in Calc is a planning tool for what-if questions accessed via Data > Multiple Operations. It uses two arrays of cells—one containing alternative input values and the other displaying results—to create a formula array that lists all alternative outcomes at once.

Steps to Use What-if Analysis

  1. Enter data and formula: Enter your base data and the formula that calculates the result in a cell.
  2. Create input array: List your alternative input values in a row or column.
  3. Select the range: Select both the input array and the adjacent empty cells where the outputs will go.
  4. Open the tool: Click Data > Multiple Operations.
  5. Set the formula: Enter the cell address containing your formula in the Formulas box.
  6. Set the variable: Enter the cell address of the original variable used in your formula in the Column input cell box.
  7. Generate results: Click OK to fill the output array with the calculated results.

GOAL SEEK

What is Goal Seek?

Goal Seek is an analysis tool in Calc used to perform backward calculation to find the specific input value needed to achieve a desired output.

Steps to Use Goal Seek

  1. Enter values: Input your known data into the worksheet.
  2. Write formula: Insert the formula in the cell where the final calculation is performed.
  3. Open Goal Seek: Select the formula cell and choose Tools > Goal Seek from the menu bar.
  4. Select variable cell: Place the cursor in the Variable cell box and click on the cell containing the input you want to change.
  5. Set target value: Enter the desired numeric outcome in the Target value box.
  6. Execute: Click OK.
  7. Confirm changes: Click Yes if prompted to add a zero value for an empty variable cell, and click Yes to automatically insert the calculated result into your sheet once the calculation succeeds.

Scroll to Top