4.

Analyse Data using Scenarios and Goal Seek

Unit 2: Electronic Spreadsheet (Advanced) Using Libreoffice Calc
Unsolved Exercise
Section A (Objective Type Questions)
A. Multiple choice questions:
1. iv 2. iv 3. iii 4. iii 5. ii
B. Fill in the blanks:
1. single / master spreadsheet 2. Goal Seek 3. Solver 4. Subtotal 5. Group and outline
C. State whether the following statements are true or false:
1. True 2. False 3. True 4. True 5. False
D. i. Both A and R are true and R is the correct explanation of A.
E. iii. Statement 1 is true , Statement 2 is false
Section B (Subjective Type Questions)
A. 1. Followings options are present the in what-if scenarios dialog box:
• Name of Scenario - Here, scenario name can be given.
• Comment - Here, details regarding creation date of scenario, user name, etc. can be given.
• Settings - Here, setting regarding border colour, copy option can be activated.
2. Following setting options are available:
• Display border: It is used to highlight the Scenarios with a border. The colour for the border is specified in the field
to the right of this option. The border has a title bar displaying the name of the last scenario.
• Copy Back: It copies the values of cells that you change into the active Scenarios. If you do not select this option,
the scenario doesn’t change even when you change cell values.
• Copy entire sheet: It copies the entire sheet into an additional scenarios sheet.
• Prevent changes: It is used to prevent changes to the active scenarios
3. To use the multiple operations tool, we need two arrays of cells:
• The first array contains the original or default values and the formulas applied to them. The formulas must be in a
range.
• The second array is the formula array. It is created by entering variables which are alternative values for one or two
original values.
For Example:-
Rohit produces toys which he sells for $10 each. Each toy costs $2 to make, in addition to which he has fixed costs of
$10,000 per year. We can calculate how much profit he will make in a year if he sells a particular number of toys.
Suppose Selling price is kept in cell B1, manufacturing cost in cell B2, fixed cost in cell B3.
Step1: To calculate the profit, enter the formula =B4*(B1-B2)-B3 in cell B5.
Step 2: In column D, enter expected annual sales, one below the other; for example, 500 to 5000, in steps of 500.
Step 3: Select the range D2:E11, and thus it will select the values in column D and the empty cells alongside in
column E.
Step 4: Click on Data menu and select Multiple operations option.
The Multiple Operations dialog box appears on the screen.
Following options are there in the ‘Multiple Operations’ dialog box:
• Formulas: It is used to enter the cell reference to the formula that is applied to the data range.
• Row input cell: It is used to enter the reference to the first cell of the range arranged in rows that is part of the
formula.
• Column input cell: is used to enter the reference to the first cell of the range arranged in columns that is part of the
formula.
Step 5: With the cursor in the Formulas field, click on cell B5.
Step 6: Set the cursor in the Column input cell field and click on cell B4. This means that B4, the quantity, is the
variable in the formula, which is replaced by the selected column values.
Step 7: Click on OK to close the dialog box.
4. Let us consider an example to perform Goal Seek operation:
Imagine you are creating a mixture of 4 products, whose quantities in kg are 8, 5, 4. The targeted quantity of the
mixture is 30. We will have to perform goal seek to determine how much quantity of 4th product should be taken to
reach the targeted value.
Step 1: Enter the Quantity of all the products in different rows/ columns.
Step 2: Calculate the total no. of quantity of all the 3 products and put the 4th product quantity as 0.
Step 3: Click on Tools Menu.
Step 4: Select the Goal Seek option.
The Goal seek dialog box appears.
Step 5: Enter the cell No. in Formula Cell which contains the formula of total quantity
Step 6: Enter the targeted value as 30.
Step 7: Click on the variable cell and put the cell [Link] 4th product over there.
Step 8: Click on OK button.
Step 9: Click on Yes button. The Quantity of the 4th product will be displayed to achieve the targeted value.
5. Let’s continue with our previous example of 4 products and their quantity. Now to get target value of the product,
quantities of multiple products are to be calculated.
Step 1: Select Formula cell. Here, it is B6 to calculate the total quantity of the product.
Step 2: Click on the Tools menu.
Step 3: Select the Solver Option.
The Solver dialog box appears on the screen.
Step 4: Put the Formula cell in the targeted cell.
Step 5: Select the value of the radio button and set its value to 30.
Step 6: Select the range of cells whose value can be modified by changing cells text field.
Step 7: Click on the Solver Button and give the limiting conditions.
Step 8: Click on the Keep Result button to keep the updated values in the cell.
B. She can use the Subtotal feature
 5. Using Macros in Spreadsheet
Unit 2: Electronic Spreadsheet (Advanced) Using Libreoffice Calc
Unsolved Exercise
Section A (Objective Type Questions)
A. 1. iii
2. i
3. iv
4. ii
5. i
B. 1. underscore
2. Sub
3. BASIC
4. values
5. macro
C. ii. Both A and R are true but R is not the correct explanation of A.
D. iv. Statement 1 is false , Statement 2 is true
Section B (Subjective Type Questions)
A. 1. Steps to create a macro that work as a function and displays ‘hello world’ are as follows:
Step 1: Go to Tools -> Macros -> Organize Macros -> Basic -> Macros -> Edit macros
Step 2: The Macro code window opens.
Step 3: Write the code between the section Function and End function as shown:
REM ******* BASIC******
Option Explicit
Sub Main
End Sub
Function hello()
hello()=” Hello World”
End Function
Step 4: Press ‘Ctrl+S’ to save the changes in the code window.
2. While organising a macro, we have to focus on the following key points:
a. Break It Down: Divide a Macro into smaller, manageable sections.
b. Use clear names: Name variables and parts of the Macro clearly for easy understanding.
b. Add comment: Include notes in your court to explain what each part does.
c. Handle errors: make sure the Macro can deal with problems without crashing.
d. Test regularly: check the Macro to ensure it works as expected.
3. Follow the steps to record a macro
Step 1: Create a new spreadsheet.
Step 2: Enter number into a spreadsheet.
Step 3: Select the cell B2.
Step 4: Press Ctrl+C key combination to copy the value to the clipboard.
Step 5: Select the range A1:B3.
Step 6: Click on the Tools menu from the Menu bar.
Step 7: Select the Macros option.
Step 8: Select the Record Macro option.
 The recording will start immediately. As soon as the record macro is active a dialog box of Stop recording will be
activated.
Step 9: Select the Edit menu from the Menu bar.
Step 10: Select the Paste special → Paste Special option from the submenu.
The Paste Special dialog box opens.
Step 11: Select the Multiply radio button under Operations section.
Step 12: Click on the OK button.
The operations specified in macro is applied to the selected cell range(A1:B3).
Step 13: Click on the Stop Recording option.
The BASIC Macros dialog box opens, to stop the Macro Recorder and to save the macro.
Step 14: Select the current Spreadsheet. Click on the ‘+’ next to the spreadsheet to view the contained libraries.
Step 15: Click on the New Module to create a module in the Standard library. If no libraries exist, then the Standard
library is automatically created and used.
Step 16: Click on the OK button to create a module named Multiply.
Step 17: Select the newly created module Multiply.
Step 18: Type the macro name Multiply5 in the Macro Name text box.
Step 19: Click on the Save [Link] 1
4. Following are the steps to view macro:
• Go to Tools > Macros > Organize Macros > Basic to open the Basic Macros dialog.
• Select the desired macro that you want to view and click Edit.
The Basic IDE will open and the macro.
5.
Predefined functions Macros
Predefined functions are built-in operations readily Macros are user-defined sequences of actions that can
available to users for common calculations like SUM or perform more complex, customized tasks, essentially
AVERAGE recording and replaying a series of steps to automate
repetitive actions.
Predefined functions are ready-to-use tools Predefined functions are ready-to-use tools

B. She can create a macro function ADD( ) .

She can go to Tools menu → Macros → Edit Macros
Write the code as given below:
REM ***** BASIC *****
Sub Main
End Sub
Function add(a,b)
add=a+b
End Function
This function can be used in any cell and cell address can be given as arguments.
 6. Linking Spreadsheet Data
Unit 2: Electronic Spreadsheet (Advanced) Using Libreoffice Calc
Unsolved Exercise
Section A (Objective Type Questions)
A. 1. ii
2. i
3. i
4. i
B. 1. True
2. False
3. True
4. False
C. ii. Both A and R are true and but R is not the correct explanation of A.
Section B (Subjective Type Questions)
A. 1.
Relative Hyperlink Absolute Hyperlink
A Relative Hyperlink contains a partial address. An Absolute Hyperlink contains a full address, the protocol and
domain name for URL, and the entire path and file name for
documents.
For Example: [Link] [Link] For Example: [Link]/ [Link]

2. Following are the steps to create a hyperlink to a document:

Step 1: Select the cell in which hyperlink is to be created. Click on the Insert menu and select Hyperlink option.
Hyperlink dialog box appears.
Step 2: Select the Document option present on the Hyperlink dialog box.
Step 3: Click on the open file button to specify the path.
Step 4: Browse the location of the target document and select the file.
Step 5: Click on the Open button. [Or]
If you want to create hyperlink between different sheets in a single spreadsheet, follow the steps
Step 1: Click on the Target in Document button. This is used to link a particular sheet of the target document.
The Target in Document dialog box appears on the screen.
Step 2: Select the sheet to be linked and click on the Apply button.
Step 3: Click on the Close button.
Step 4: In the Text field in Further settings area, type the text that needs to appear in the cell and click on Apply
followed by click on Close button to finish the process of linking.
3. Following are the steps to create reference to other sheet
Step 1: Open a new spreadsheet Untitled1 and select any cell.
Step 2: Open any existing spreadsheet.
Step 3: Go back to Untitled 1 spreadsheet and type = in any cell say A1
Step 4: Select the desired cell A2 on the existing sheet.
Step 5: A reference of the cell has been created in Untitled 1.
Step 6: Press Enter
4. Following are the steps to edit a hyperlink:
Step 1: Hover the cursor anywhere on the link and right-click on it.
A pop up menu will be displayed.
Step 2: Click on the option Edit Hyperlink.
The Hyperlink dialog box will appear. Make desired changes in the hyperlink.
 7. Share and Review a Spreadsheet
Unit 2: Electronic Spreadsheet (Advanced) Using Libreoffice Calc
Unsolved Exercise
Section A (Objective Type Questions)
A. 1. i
2. ii
3. ii
4. ii
B. 1. False
2. False
3. True
4. False
5. True
C. i. Both A and R are true and R is the correct explanation of A.
Section B (Subjective Type Questions)
A. 1. The purpose of sharing a spreadsheet is to allow multiple users to access, edit, and update the data on the same
sheet simultaneously.
2. To edit and format the text of a comment we have to follow the following steps:
• Right-click on the cell containing the comment marker and then select Show Comment option from the popup
menu.
• Double click on the comment. The cursor changes to the blinking text entry and the formatting toolbar changes to
show text attributes.
• After editing, click outside the comment to deselect it.
3. To merge different versions of a spreadsheet, follow the given steps:
• Open the original document into which you want to merge all copies.
• Click on the Edit menu.
• Click on Track changes option and then select Merge Document.
• Select the copy of the document from the dialog box. If there has been no subsequent change in the original
document, the copy is merged into the original.
• If changes have been made in the original document, a dialog box appears asking to accept or reject changes.
After merging the documents, you can see the recorded changes from the copied document into the original
document.
4. When a shared spreadsheet is saved, one of the several situations may occur:
• If the document was not modified and saved by another user, the document will be saved.
• If the document was modified and saved by another user , one of the following events will occur:
ⴰ If the changes do not conflict, that document is saved, a dialog box appears, and any cells modified by the other
user are shown.
ⴰ If the changes conflict, the Resolve conflicts window appears on the screen, decide
for each conflict that is which version to keep, Keep Mine or Keep Other. When all conflicts are resolved, the
document is saved.
ⴰ If another user is trying to save the shared document while another user is resolved in conflicts, a message that the
shared document is locked due to a merge in progress.
Choose the Cancel saving or Retry saving later.
B. Reviewing Changes