Chapter 4 Notes
Unit 2 Electronic Spreadsheet · Chapter 4
Analyse Data Using Scenarios & Goal Seek
Master decision modeling in LibreOffice Calc: Consolidating Data across Sheets, Subtotals with Multi-Level Grouping, What-If Scenarios, Multiple Operations Tables, Goal Seek Reverse Engineering, and Solver Optimization.
Chapter 4 Theory
1. Consolidating Data (Data > Consolidate)
Consolidation combines information from two or more cell ranges into a single unified summary sheet:
Supported Functions:
Sum, Count, Average, Max, Min, Product, Count (numbers only), StDev (sample), StDevP (population), Var, VarP.
Link to Source Data Checkbox:
When checked, any subsequent modifications in source sheets automatically recalculate the master target table.
2. Subtotals (Data > Subtotals)
Calculates subtotal values for columns grouped by categories, providing collapsible level outlines (1, 2, 3) on the left margin. Provides up to 3 Group tabs for nested categorization.
3. What-If Analysis Comparison Matrix
| Tool | Menu Path | Key Difference |
|---|---|---|
| Scenarios | Tools > Scenarios | Stores multiple sets of values for same cells; toggled via drop-down border header. |
| Multiple Operations | Data > Multiple Operations | Generates side-by-side data table showing results for 1 or 2 variable inputs simultaneously. |
| Goal Seek | Tools > Goal Seek | Reverse solves for strictly ONE unknown variable cell to reach a target formula value. |
| Solver | Tools > Solver | Solves for MULTIPLE variable cells subject to limiting constraints (<=, >=, =). |
Chapter 4 Practice
Solved MCQs: Scenarios & Goal Seek
1
What happens when you enable the 'Link to source data' option in the Consolidate dialog in LibreOffice Calc?
2
Up to how many group tabs can be configured when generating multi-level Subtotals in LibreOffice Calc?
3
Which What-If analysis tool in Calc is used to find an unknown single input value required to produce a specific desired result in a formula?
4
How does the 'Solver' tool differ from 'Goal Seek' in LibreOffice Calc?
5
Which What-If analysis feature builds a side-by-side comparative table showing multiple output results simultaneously without toggling separate views?
Frequently Asked Board Questions