Solutions

Electronic Spreadsheet
7 (Advanced)
SESSION 1 ANALYSE DATA USING
SCENARIOS AND GOAL SEEK

A. Multiple choice questions.

1. (c) 2. (c) 3. (c)
B. 1. For Goal Seek, there should be exactly one unknown input.
Goal Seek can find out this unknown value.
For Solver, there can be multiple unknown inputs and still
solver solves for these unknown inputs. There is also greater
variety to how Solver can take inputs. It can stipulate binary
values, minimization, maximization and exact value. It can
have inequalities as its inputs. Solver is a much versatile
version of Goal Seek.
2. Suppose that the Principal is Rs 20,000, the Rate of interest
is 8% p.a. and the amount to accumulate is Rs 1600. The
required time, can be calculated to be one year, based on
the formula:
SI = (P x R x T)/100
We may provide the Goal Seek dialog the values Rs. 20000,
8% and get the 1 year as the output from it. The formula cell
will contain the formula for Simple Interest as given above.

SESSION 2 LINK SPREADSHEET DATA

A. Multiple choice questions.

1. (c) 2. (a) 3. (b)
B. 1. Do any one of the following:
• Select Insert > Sheet from the Menu Bar.
• Right-click on the sheet tab and click on Insert Sheet.
11
 • Click in an empty space at the end of the line of sheet
tabs.
Each method opens the Insert Sheet dialog box. Here, you
can choose to put the new sheet before or after the selected
sheet, how many sheets to insert, and what to name a single
sheet.
2. Hyperlinks can be stored within your file as either relative
or absolute. An absolute link will stop working only if the
target is moved. A relative link will stop working only if the
start and target locations change relative to each other. For
instance, if you have two spreadsheets in the same folder
linked to each other and you move the entire folder to a
new location, a relative hyperlink will not break.

SESSION 3 SHARE AND REVIEW A DOCUMENT

A. Multiple choice questions.

1. (a) 2. (a) 3. (c)
B. 1. At any time, you can set up a spreadsheet for sharing with
others. With the spreadsheet document open, choose Tools
> Share spreadsheet to activate the collaboration features
for this document. A dialog box opens where you can enable
or disable sharing. To enable sharing, check the box at the
top of the dialog, and then click on OK. If the spreadsheet
has been saved previously, a message appears stating that
you must save it to activate shared model; click on Yes to
continue. If the spreadsheet has not been saved previously,
the Save dialog box appears. After saving, the word (shared)
is shown on the title bar after the document’s title.
2. When you save a shared spreadsheet, one of several
situations may occur:
• If the document was not modified and saved by another
user since you opened it, the document is saved.
• If the document was modified and saved by another user
since you opened it, one of the following events will occur:
- If the changes do not conflict, the document is saved, the
message below appears, and any cells modified by the other
user are shown with a red border.
- If the changes conflict, the Resolve Conflicts dialog box is
shown. You must decide for each conflict which version to
12
 keep, yours or the other person’s. When all conflicts are
resolved, the document is saved. While you are resolving
the conflicts, no other user can save the shared document.
- If another user is trying to save the shared document
and resolve conflicts, you see a message that the shared
spreadsheet file is locked due to a merge-in process. You
can choose to cancel the Save command for now, or retry
saving later.

SESSION 4 USE MACROS IN SPREADSHEET

A. Multiple choice questions.

1. (b) 2. (a) 3. (a)
B. 1. Arguments passed to a macro from Cale are always values.
It is not possible to know what cells, if any, are used. For
example, =PositiveSum(A3) passes the value of cell A3, and
PositiveSum has no way of knowing that cell A3 was used.
If you must know which cells are referenced rather than
the values in the cells, pass the range as a string, parse the
string, and obtain the values in the referenced cells.
2. Depending on your settings in Tools > Options > LibreOffice
> Security > Macro Security, Calc will display the warning.
You will need to click Enable Macros, or Calc will not allow
any macros to be run inside the document. If you do not
expect a document to contain a macro, it is safer to click on
Disable Macros in case the macro is a virus.
If you choose to disable macros, then when the document
loads, Cale can no longer find the function.
3. A macro is a saved sequence of commands or keystrokes
that are stored for later use. An example of a simple macro
is one that ‘’types” your address. The LibreOffice macro
language is very flexible, allowing automation of both simple
and complex tasks. Macros are especially useful to repeat a
task the same way over and over again.

A. Multiple choice questions.

1. (c) 2. (c) 3. (b) 4. (c) 5. (b)
6. (b) 7. (a) 8. (a)
13
B. 1. When you save a shared spreadsheet, one of several
situations may occur:
• If the document was not modified and saved by another
user since you opened it, the document is saved.
• If the document was modified and saved by another user
since you opened it, one of the following events will occur:
- If the changes do not conflict, the document is saved, a
message appears, and any cells modified by the other user
are shown with a red border.
- If the changes conflict, the Resolve Confticts dialog box
is shown. You must decide for each conflict which version
to keep, yours or the other person’s. When all conflicts are
resolved, the document is saved. While you are resolving
the conflicts, no other user can save the shared document.
- If another user is trying to save the shared document
and resolve conflicts, you see a message that the shared
spreadsheet file is locked due to a merge-in process. You
can choose to cancel the Save command for now, or retry
saving later.
2. Editing and formatting of comments is easy. For that, just
click on the cell and right-click on the cell. The context menu
comes up. Click on Edit Comment from the context menu.
The cursor shows up inside the comment. Then, start editing
of the comment. Once the pointer is inside the comment,
by just highlighting the text and clicking on the formatting
buttons in the menu bar shows up formatting changes.
3. A macro is a saved sequence of commands or keystrokes
that are stored for later use. An example of a simple macro
is one that ‘’types” your address. The LibreOffice macro
language is very flexible, allowing automation of both simple
and complex tasks. Macros are especially useful to repeat a
task the same way over and over again.
4. Using the Macro Recorder
The following steps create a macro that performs paste
special with multiply.
a. Open a new spreadsheet.
b. Enter numbers into a sheet.
c. Select cell A3, which contains the number 3, and press Ctrl
+ C to copy the value to the clipboard.
d. Select the range Al:C3.
e. Use Tools > Macros > Record Macro to start the macro
14
 recorder. The Record Macro dialog box is displayed with a
stop recording button.
f. Use Edit > Paste Special to open the Paste Special dialog box
g. Set the operation to Multiply and click on OK. The cells are
now multiplied by 3.
h. Click on Stop Recording to stop the macro reader. The
LibreOffice Basic Macros dialog box opens.
i. Select the current document. For this example, it is Untitled
1. Existing documents show a library named Standard. This
library is not created until the document is saved or the
library is needed, so at this point your new document does
not contain a library. You can create a new library to contain
the macro, but this is not necessary.
j. Click on New Module. If no libraries exist, then the Standard
Library is automatically created and used. In the New Module
dialog box, type a name for the new module or leave the
module as Modulel1.
k. Click on OK to create a new module named Modulel. Select
the newly created Modulel, type PasteMultiply in the Macro
name box at the upper left, and click on Save.
5. The libraries, modules and macro names must follow some
strict rules. Following the main rules, the names must:
• Begin with a letter • Not contain spaces
• Not contains special characters, accents included, except for
_ (underscore).
C. 1. Reviewing Changes
Viewing changes: You have some control over what changes
you see when reviewing a document. Select Edit > Track
Changes > Record from the menu bar. Thereafter, every
change you record on the spreadsheet will have a red border
around it. If you want to tum off the track changes that are
taking place, you have to again click on Edit > Track Changes
> Record one more time. The tracking of changes will stop.
2. The libraries, modules and macro names must follow some
strict rules. Following the main rules, the names must: •
Begin with a letter • Not contain spaces • Not contain special
characters, accents included, except for _ (underscore).
3. Arguments passed to a macro from Calc are always values.
It is not possible to know what cells, if any, are used. For
example, =PositiveSum(A3) passes the value of cell A3, and
PositiveSum has no way of knowing that cell A3 was used.
15
 If you must know which cells are referenced rather than
the values in the cells, pass the range as a string, parse the
string, and obtain the values in the referenced cells.
4. Depending on your settings in Tools > Options > LibreOffice
> Security > Macro Security, Calc will display the warning.
You will need to click Enable Macros, or Calc will not allow
any macros to be run inside the document. If you do not
expect a document to contain a macro, it is safer to click on
Disable Macros in case the macro is a virus.
D. 1. You can access a variety of databases and other data sources
and link them into Calc documents. First you need to register
the data source with LibreOffice. (To register means to tell
LibreOffice what type of data source it is and where the file
is located.) The way to do this depends on whether or not
the data source is a database in *.odb format.
To register a data source that is in * .odb format:
a. Choose Tools > Options > LibreOffice Base > Databases.
b. Click on the New button (below the list of registered
databases) to open the Create Database Link dialog box.
c. Enter the location of the database file, or click on Browse
to open a file browser and select the database file.
d. Type a name to use as the registered name for the database.
e. Click on OK. The database is added to the list of registered
databases. The OK button is enabled only when both fields
are filled in.
2. Using the Macro Recorder
The following steps create a macro that performs paste
special with multiply.
a. Open a new spreadsheet.
b. Enter numbers into a sheet.
c. Select cell A3, which contains the number 3, and press Ctrl
+ C to copy the value to the clipboard.
d. Select the range Al:C3.
e. Use Tools > Macros > Record Macro to start the macro
recorder. The Record Macro dialog box is displayed with a
stop recording button.
f. Use Edit > Paste Special to open the Paste Special dialog box
g. Set the operation to Multiply and click on OK. The cells are
now multiplied by 3.
h. Click on Stop Recording to stop the macro reader. The
LibreOffice Basic Macros dialog box opens.
16
 i. Select the current document. For this example, it is Untitled
1. Existing documents show a library named Standard. This
library is not created until the document is saved or the
library is needed, so at this point your new document does
not contain a library. You can create a new library to contain
the macro, but this is not necessary.
j. Click on New Module. If no libraries exist, then the Standard
Library is automatically created and used. In the New Module
dialog box, type a name for the new module or leave the
module as Modulel.
k. Click on OK to create a new module named Modulel. Select
the newly created Modulel, type PasteMultiply in the Macro
name box at the upper left, and click on Save.
3. Listing
PositiveSum calculates the sum of the positive arguments.
Function PositiveSum(Optional x)
Dim TheSum As Double
Dim iRow As Integer
Dim iCol As Integer
TheSum = 0.0
If NOT IsMissing(x) Then
If NOT lsArray(x) Then
If x > 0 Then TheSum = x
Else For iRow = LBound(x, 1) To UBound(x, 1)
For iCol = LBound(x, 2) To UBound(x, 2)
If x(iRow, iCol) > 0 Then
TheSum = TheSum + x(iRow, iCol)
Next
Next
End If
End If
PositiveSum = TheSum
End Function
The macro in Listing 5 demonstrates some important
techniques:
a. The argument x is optional. When an argument is NOT
optional and the function is called without it, LibreOffice
prints a warning message every time the macro is called.
If Calc calls the function many times, then the error will be
shown many times.
17
 b. IsMissing checks that an argument was passed before the
argument is used.
c. IsArray checks to see if the argument is a single value, or
an array. For example, =PositiveSum(7) or =PositiveSum(A4).
In the first case, the number 7 is passed as an argument,
and in the second case, the value of cell A4 is passed to the
function.
d. If a range is passed to the function, it is passed as a two-
dimensional array of values; for example, =PositiveSum(A2:B5).
LBound and UBound are used to determine the array
bounds that are used. Although the lower bound is one, it
is considered safer to use LBound in case it changes in the
future.
E. 1. (c) 2. (c) 3. (a) 4. (b) 5. (c)
6. (a) 7. (b) 8. (b) 9. (a) 10. (b)
11. (c) 12. (a) 13. (c) 14. (a) 15. (a)
16. (c) 17. (b) 18. (b)
19. The libraries, modules and macro names must follow some
strict rules. Following the main rules, the names must:
• Begin with a letter • Not contain spaces
• Not contains special characters, accents included, except for
_ (underscore).
20. Copy Entire Sheet Adds to your document a sheet that
permanently displays the new scenario in full. This is in
addition to creating the scenario and making it selectable
on the original sheet as normal.
21. Any data that is not a number, date or formula is a string.
String contains text.
In making a query, following operators may be used:
* stands for any number of any character.
? stands for any one character
& stands for the string concatenation operator
22. (b)
23. If you are viewing a scenario which has Copy back enabled
and then create a new scenario by changing the values and
selecting Tools > Scenarios, you also inadvertently overwrite
the values in the first scenario.
This is easily avoided if you leave the current values alone,
create a new scenario with Copy back enabled, and then
change the values only when you are viewing the new
scenario.

18
 24. (d)
25. (b)
26. Inserting New Sheets There are several ways to insert a new
sheet. The fastest method is to click on the Insert Worksheet
button. This inserts one new sheet at the point, without
opening the Insert sheet dialog box. Other ways are :
• Select Insert 7 sheet from the menu bar.
• Right-click on the sheet fab and click on insert sheet.
27. (c)
28. (c)
29. (a)
30. (a)
31. Syntax
SUBTOTAL (Function; Range)
Function is a number that stands for one of the following
functions.
Function index Functions
1 Average
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
Range is the range whose cells
are included
32. Goal Seek tool.

19