Class X · Part B – Unit 2: Electronic Spreadsheet (Advanced) — Calc · Hindi + English

Ch 4: Scenarios and Goal Seek

Data Analysis in Calc

Data analysis is the process of extracting useful information from data to make effective decisions. A Calc file has the extension .ods.

Consolidating Data

Consolidate combines information from multiple sheets into one place to summarise it, so data can be viewed and compared to find trends and relationships.

Data > Consolidate.
In Source data ranges, select the range and click Add (repeat for each range).
In Copy results to, choose the target cell/sheet.
Select the Function (default is Sum); tick Link to source data to auto-update; click OK.

Example: two branches ABC and XYZ have monthly sales in separate sheets — Consolidate (Sum) merges them into one total per product.

Creating Subtotals

Subtotal automatically creates groups and applies functions like Sum or Average on the grouped data, using Group & Outline — no filters needed.

Data > Subtotals > choose the column in Group by > tick the columns to total and pick the function > OK. Use the +/– outline symbols to expand/collapse groups.

What-if Scenarios

Scenario: a named set of values used in the calculations of a sheet. Several scenarios can exist on the same sheet to compare alternatives when conditions change.

Select the cells that will hold the changing values.
Tools > Scenarios.
Type a scenario name and settings (border colour, copy back) and click OK.
Switch between scenarios using the drop-down on the bordered cells.

Example: a car/home loan — vary Principal and Number of years to see the resulting EMI in each scenario. The Multiple Operations tool (Data > Multiple Operations) uses two arrays to test a range of input values.

Goal Seek

Goal Seek works in reverse — it finds the INPUT value needed to reach a specific OUTPUT (target) value.

Select the cell containing the formula (result cell).
Tools > Goal Seek.
Set Formula cell (result), Target value (desired output) and Variable cell (input to change). Click OK.

Quick difference: Scenario / What-if → change inputs, see the output. Goal Seek → fix the output, find the required input.

Solved Example 1: Goal Seek for exam marks

Riya scored 72, 81 and 68 in three unit tests. What must she score in Test 4 to get an average of 80?

Cell	A	B
1	Unit test	Marks
2–4	Test 1, 2, 3	72, 81, 68
5	Test 4	0 (variable cell)
6	Average	`=AVERAGE(B2:B5)` (formula cell)

Click B6 (the formula cell) → Tools > Goal Seek.
Formula cell: $B$6 · Target value: 80 · Variable cell: $B$5 → OK.
Calc reports “Goal Seek succeeded. Result: 99” → click Yes to put 99 in B5.

Answer: Riya needs 99 marks in Test 4. Check: (72 + 81 + 68 + 99) ÷ 4 = 320 ÷ 4 = 80 ✔ (result verified in LibreOffice Calc 24.2)

Solved Example 2: How much loan for a fixed EMI?

A family can pay an EMI of ₹10,000 per month for 5 years at 9% yearly interest. How much loan can they take?

Cell	Content
B1 Loan amount	500000 (variable cell)
B2 Annual interest rate	9%
B3 Years	5
B4 Monthly EMI	`=-PMT(B2/12;B312;B1)` → ₹10,379.18 (formula cell)*

Goal Seek with Formula cell $B$4, Target 10000, Variable cell $B$1 gives a loan amount of ₹4,81,733.74. The minus sign before PMT makes the EMI a positive number.

Solved Example 3: Comparing loans with Scenarios

Select B1:B3, then Tools > Scenarios and create three scenarios. Switching between them from the drop-down gives these results (9% interest):

Scenario	Loan (₹)	Years	Monthly EMI (₹)	Total paid (₹)	Total interest (₹)
Small 3 yrs	3,00,000	3	9,539.92	3,43,437.11	43,437.11
Medium 5 yrs	5,00,000	5	10,379.18	6,22,750.66	1,22,750.66
Big 7 yrs	8,00,000	7	12,871.26	10,81,186.06	2,81,186.06

Insight: a longer loan has a smaller EMI per lakh, but you pay much more interest in total. This is exactly the kind of decision scenarios help with.

Comparison Table: Which tool when?

Tool	Menu	What you give	What you get
Consolidate	Data > Consolidate	Same-layout ranges from several sheets	One combined summary (Sum by default)
Subtotals	Data > Subtotals	A list and a “Group by” column	Group totals + grand total with outline
Scenarios	Tools > Scenarios	Named sets of input values	Switch inputs, compare outputs
Multiple Operations	Data > Multiple Operations	A formula and a range of inputs	A table of results for every input
Goal Seek	Tools > Goal Seek	Formula cell, target value, variable cell	The input needed for the target

Practice File (free download)

Download class-10-it-402-data-analysis-practice.ods (LibreOffice Calc, 15 KB). It has five tasks on separate sheets: Consolidate two branches, Subtotals of fees, two Goal Seek problems and Scenarios. Answers:

Show answers

Task 1 (Consolidate, Sum): Kaju Katli 80,000 / 85,600 · Ghewar 39,000 / 56,500 · Rasmalai 42,500 / 39,200 · Gift Box 27,500 / 35,800 (July / August).
Task 2 (Subtotals): 9-A = 5,100 · 9-B = 2,700 · 10-A = 5,000 · Grand total = 12,800.
Task 3: Test 4 = 99. Task 4: loan = ₹4,81,733.74.
Task 5: EMIs ₹9,539.92, ₹10,379.18 and ₹12,871.26.

Important Questions

MCQ: Which feature finds the input needed for a desired result? (a) Scenario (b) Subtotal (c) Goal Seek (d) Consolidate — Answer: (c)
MCQ: The default function in the Consolidate dialog is (a) Average (b) Sum (c) Count (d) Max — Answer: (b)
MCQ: Scenarios are created from the (a) Data menu (b) Tools menu (c) Format menu (d) Insert menu — Answer: (b) Tools
Short answer: Name the three boxes of the Goal Seek dialog. — Formula cell, Target value, Variable cell.
Short answer: Why must the data be grouped (sorted) before Subtotals? — Subtotals are calculated each time the value in the Group-by column changes, so rows of the same group must be together. Calc can pre-sort the area for you.
Application: A shopkeeper wants a profit of ₹15,000 and knows profit = units × (price − cost). Which tool helps him find the number of units to sell, and which cells will he use? — Goal Seek: formula cell = profit, target = 15000, variable cell = units.

Summary

Consolidate merges multiple sheets (default function = Sum); Subtotal groups data and applies functions.
Scenarios store named value sets to compare alternatives; Multiple Operations uses two arrays.
Goal Seek is reverse analysis — find the input for a target output.

Quick check: 3 MCQs (tap an option)

Q1. Which tool finds the input value needed to reach a target result?

Q2. The default function in Data > Consolidate is:

Q3. A named set of values used to compare alternatives is a:

Calc में Data Analysis

Data analysis data से उपयोगी जानकारी निकालकर सही निर्णय लेने की प्रक्रिया है। Calc file का extension .ods होता है।

Data Consolidate करना

Consolidate कई sheets की जानकारी को एक जगह summarise करने के लिए जोड़ता है, ताकि data देखकर और तुलना करके trends व relationships पता चलें।

Data > Consolidate.
Source data ranges में range चुनकर Add कीजिए (हर range के लिए दोहराइए)।
Copy results to में target cell/sheet चुनिए।
Function चुनिए (default Sum); Link to source data tick करके auto-update; OK।

उदाहरण: दो branches ABC और XYZ की monthly sales अलग sheets में — Consolidate (Sum) उन्हें प्रति product एक total में जोड़ देता है।

Subtotals बनाना

Subtotal अपने आप groups बनाकर grouped data पर Sum या Average जैसी functions लगाता है, Group & Outline से — filters की ज़रूरत नहीं।

Data > Subtotals > Group by में column चुनिए > total वाले columns tick करके function चुनिए > OK। groups खोलने/बंद करने को +/– outline symbols इस्तेमाल कीजिए।

What-if Scenarios

Scenario: sheet की गणनाओं में इस्तेमाल होने वाला values का एक named set। एक ही sheet पर कई scenarios हो सकते हैं ताकि conditions बदलने पर विकल्पों की तुलना हो।

बदलने वाली values रखने वाले cells चुनिए।
Tools > Scenarios.
Scenario नाम और settings (border colour, copy back) देकर OK कीजिए।
Bordered cells के drop-down से scenarios बदलिए।

उदाहरण: car/home loan — Principal और Number of years बदलकर हर scenario में EMI देखिए। Multiple Operations tool (Data > Multiple Operations) दो arrays से input values की range test करता है।

Goal Seek

Goal Seek उल्टा काम करता है — यह किसी ख़ास OUTPUT (target) तक पहुँचने के लिए ज़रूरी INPUT value ढूँढता है।

Formula वाली cell (result cell) चुनिए।
Tools > Goal Seek.
Formula cell (result), Target value (चाहा output) और Variable cell (बदलने वाला input) set कीजिए। OK।

त्वरित अंतर: Scenario / What-if → inputs बदलिए, output देखिए। Goal Seek → output तय कीजिए, ज़रूरी input ढूँढिए।

हल किया उदाहरण 1: exam marks के लिए Goal Seek

Riya ने तीन unit tests में 72, 81 और 68 अंक पाए। 80 का average पाने के लिए Test 4 में कितने अंक चाहिए?

Cell	A	B
1	Unit test	Marks
2–4	Test 1, 2, 3	72, 81, 68
5	Test 4	0 (variable cell)
6	Average	`=AVERAGE(B2:B5)` (formula cell)

B6 (formula cell) पर click → Tools > Goal Seek।
Formula cell: $B$6 · Target value: 80 · Variable cell: $B$5 → OK।
Calc बताता है “Goal Seek succeeded. Result: 99” → B5 में 99 डालने के लिए Yes।

उत्तर: Riya को Test 4 में 99 अंक चाहिए। जाँच: (72 + 81 + 68 + 99) ÷ 4 = 320 ÷ 4 = 80 ✔ (LibreOffice Calc 24.2 में verified)

हल किया उदाहरण 2: तय EMI पर कितना loan?

एक परिवार 9% सालाना ब्याज पर 5 साल तक ₹10,000 महीना EMI दे सकता है। कितना loan ले सकते हैं?

Cell	Content
B1 Loan amount	500000 (variable cell)
B2 Annual interest rate	9%
B3 Years	5
B4 Monthly EMI	`=-PMT(B2/12;B312;B1)` → ₹10,379.18 (formula cell)*

Formula cell $B$4, Target 10000, Variable cell $B$1 के साथ Goal Seek loan amount ₹4,81,733.74 देता है। PMT से पहले minus चिह्न EMI को positive संख्या बनाता है।

हल किया उदाहरण 3: Scenarios से loans की तुलना

B1:B3 चुनें, फिर Tools > Scenarios से तीन scenarios बनाएँ। Drop-down से बदलने पर ये नतीजे मिलते हैं (9% ब्याज):

Scenario	Loan (₹)	साल	Monthly EMI (₹)	कुल भुगतान (₹)	कुल ब्याज (₹)
Small 3 yrs	3,00,000	3	9,539.92	3,43,437.11	43,437.11
Medium 5 yrs	5,00,000	5	10,379.18	6,22,750.66	1,22,750.66
Big 7 yrs	8,00,000	7	12,871.26	10,81,186.06	2,81,186.06

सीख: लंबे loan में प्रति लाख EMI कम होती है, पर कुल ब्याज बहुत ज़्यादा देना पड़ता है। Scenarios ऐसे ही फ़ैसलों में मदद करते हैं।

तुलना तालिका: कौन सा tool कब?

Tool	Menu	आप क्या देते हैं	आपको क्या मिलता है
Consolidate	Data > Consolidate	कई sheets की एक जैसी ranges	एक जोड़ा हुआ summary (default Sum)
Subtotals	Data > Subtotals	List और “Group by” column	Group totals + grand total, outline के साथ
Scenarios	Tools > Scenarios	Input values के नाम वाले sets	Inputs बदलें, outputs की तुलना
Multiple Operations	Data > Multiple Operations	एक formula और inputs की range	हर input के नतीजों की table
Goal Seek	Tools > Goal Seek	Formula cell, target value, variable cell	Target के लिए ज़रूरी input

Practice File (मुफ़्त download)

class-10-it-402-data-analysis-practice.ods download करें (LibreOffice Calc, 15 KB)। इसमें अलग sheets पर पाँच tasks हैं: दो branches का Consolidate, fees के Subtotals, दो Goal Seek सवाल और Scenarios। उत्तर:

उत्तर देखें

Task 1 (Consolidate, Sum): Kaju Katli 80,000 / 85,600 · Ghewar 39,000 / 56,500 · Rasmalai 42,500 / 39,200 · Gift Box 27,500 / 35,800 (July / August)।
Task 2 (Subtotals): 9-A = 5,100 · 9-B = 2,700 · 10-A = 5,000 · Grand total = 12,800।
Task 3: Test 4 = 99। Task 4: loan = ₹4,81,733.74।
Task 5: EMIs ₹9,539.92, ₹10,379.18 और ₹12,871.26।

महत्वपूर्ण प्रश्न

MCQ: मनचाहे नतीजे के लिए ज़रूरी input कौन सा feature ढूँढता है? (a) Scenario (b) Subtotal (c) Goal Seek (d) Consolidate — उत्तर: (c)
MCQ: Consolidate dialog में default function है (a) Average (b) Sum (c) Count (d) Max — उत्तर: (b)
MCQ: Scenarios किस menu से बनते हैं? (a) Data (b) Tools (c) Format (d) Insert — उत्तर: (b) Tools
लघु उत्तर: Goal Seek dialog के तीन boxes के नाम लिखिए। — Formula cell, Target value, Variable cell।
लघु उत्तर: Subtotals से पहले data को group (sort) करना क्यों ज़रूरी है? — Group-by column की value बदलते ही subtotal बनता है, इसलिए एक group की rows साथ होनी चाहिए। Calc यह sorting ख़ुद कर सकता है।
Application: एक दुकानदार ₹15,000 मुनाफ़ा चाहता है और जानता है मुनाफ़ा = units × (price − cost)। कितनी units बेचनी हैं, यह कौन सा tool बताएगा और कौन सी cells लगेंगी? — Goal Seek: formula cell = मुनाफ़ा, target = 15000, variable cell = units।

सारांश

Consolidate कई sheets जोड़ता है (default function = Sum); Subtotal data group करके functions लगाता है।
Scenarios named value sets रखते हैं विकल्पों की तुलना को; Multiple Operations दो arrays इस्तेमाल करता है।
Goal Seek उल्टा विश्लेषण है — target output के लिए input ढूँढना।

झटपट जाँच: 3 MCQs (option पर tap करें)

Q1. Target result पाने के लिए ज़रूरी input value कौन सा tool ढूँढता है?

Q2. Data > Consolidate में default function है:

Q3. विकल्पों की तुलना के लिए नाम वाला values का समूह है:

Frequently Asked Questions

What is the difference between Goal Seek and a What-if Scenario?

In a What-if Scenario you change the input values to see the resulting output, while Goal Seek works in reverse: you fix the target output and it finds the input value needed to reach it.

What is the default function used by Consolidate?

The default function used by the Consolidate tool is Sum, though you can also choose Average, Count, Max, Min and others.

What is a scenario in LibreOffice Calc?

A scenario is a named set of values used in a sheet's calculations. Several scenarios can be created on the same sheet to compare different alternatives when conditions change.

What does the Subtotal feature do?

Subtotal automatically groups the data and applies functions like Sum or Average to each group using Group and Outline, without needing filters.

Ch 4: Scenarios and Goal Seek

Data Analysis in Calc

Consolidating Data

Creating Subtotals

What-if Scenarios

Goal Seek

Solved Example 1: Goal Seek for exam marks

Solved Example 2: How much loan for a fixed EMI?

Solved Example 3: Comparing loans with Scenarios

Comparison Table: Which tool when?

Practice File (free download)

Important Questions

Summary

Quick check: 3 MCQs (tap an option)

Calc में Data Analysis

Data Consolidate करना

Subtotals बनाना

What-if Scenarios

Goal Seek

हल किया उदाहरण 1: exam marks के लिए Goal Seek

हल किया उदाहरण 2: तय EMI पर कितना loan?

हल किया उदाहरण 3: Scenarios से loans की तुलना

तुलना तालिका: कौन सा tool कब?

Practice File (मुफ़्त download)

महत्वपूर्ण प्रश्न

सारांश

झटपट जाँच: 3 MCQs (option पर tap करें)

Frequently Asked Questions

What is the difference between Goal Seek and a What-if Scenario?

What is the default function used by Consolidate?

What is a scenario in LibreOffice Calc?

What does the Subtotal feature do?

How was this lesson?

Reviews from readers

Learn with video lessons in easy Hindi