Introduction

Once you are familiar with functions and formulas, the next step is to learn how to use
Calc’s automated processes to quickly perform useful analysis of your data.

In addition to formulas and functions, Calc includes several tools for processing your
data. These tools include features for copying and reusing data, creating subtotals,
running what-if analysis, and performing statistical analysis. They can be found under
the Tools and Data menus on the Menu bar. Although they are not necessary when
using Calc, they can help you save time and effort with handling large data sets or
preserving your work for future review.

Note
A related tool, the Pivot Table, is not mentioned here, as it is complex
enough to require its own chapter. See Chapter 9, Using Pivot Tables, for
more information.

Consolidating data
The Consolidate tool allows you to combine and aggregate data spread across one or
more sheets. This tool is useful if you need to quickly summarize a large, scattered set of
data for review. For example, you could use it to consolidate multiple department
budgets from different sheets into a single company-wide budget contained in a master
sheet.

To consolidate data:

1) Open the document containing the cell ranges to be consolidated.

2) Select Data > Consolidate on the Menu bar to open the Consolidate dialog
(Figure 1).
3) Click on the Source data ranges field, then type a reference to a source data
range, a named range, or select it with the mouse. Use the associated Shrink /
Expand button if you need to minimize the dialog while you select the range.
Alternatively, select a named range from the drop-down list to the left of the field.
4) Click Add. The selected range is added to the Consolidation ranges list.
5) Repeat steps and to add additional source ranges.
6) To delete an entry in the Consolidation ranges list, select it and click Delete.
The deletion is carried out without further confirmation.
7) Click on the Copy results to field, then type a reference to the first cell of the
target range or select it with your mouse. You can also select a named range in
the drop-down list to the left of the field.
 8) Select a function to aggregate your data in the Function drop-down list. The
default is Sum. Other available functions are Count, Average, Max, Min, Product,
Count (numbers only), StdDev (sample), StDevP (population), Var (sample), and
VarP (population).
9) Click OK to consolidate the ranges. Calc runs the function from step on your
source data ranges and populates the target range with the results.

Tip
If you are consolidating the same cell ranges repeatedly, consider
converting them into reusable named ranges to make the process easier.
For more information about named ranges, see Chapter 14, Calc as a
Database.

Figure 1: Consolidate dialog

Consolidation settings

In the Consolidate dialog, expand the Options section to access the settings shown
in Figure 2.

Figure 2: Consolidate dialog – Options section

Consolidate by
 In this section, choose whether to consolidate your source data ranges by their
range position or by matching labels. Consolidation labels must be contained
within each range, and the text of corresponding row or column labels must match
for the rows or columns themselves to be combined.
Row labels – Consolidates rows by matching label. If this option is unchecked,
the tool will consolidate rows by position instead.
Column labels – Works the same as Row labels, but with columns instead.
Options
Select Link to source data to add formulas in the target range that link back to
values in the source ranges. When you select this option, any changes you make
in the source ranges will automatically update values in the target range.

Note
If you use the Link to source data option, each source link is inserted into
the target range, then ordered and hidden from view. Only the final results of
consolidation are displayed by default.

Consolidation example

Figures 3, 4, and 5 show a simple example of consolidation using a spreadsheet with the
sheets Year 1, Year 2, and Consolidated Sales. Figure 3 shows the contents of the Year
1 sheet, with sales figures by region for each of the four product colors.

Figure 3: Year 1 sales by region

Figure 4 shows the Year 2 sheet, sales figures by region for each of four product colors.
Note the different ordering of row and column labels between the two figures.

Figure 4: Year 2 sales by region

Figure 5 shows the consolidated sales data, created by using the Consolidate dialog
settings shown in Figure 2. Note that because the Link to source data option was
selected, clicking on the plus sign (+) indicators to the left of the data will reveal
formula links back to the source ranges.

The source ranges and target range are saved as part of the document. If you later open
a document with consolidated ranges, they will still be available in the Consolidation
ranges list of the Consolidate dialog.

Figure 5: Consolidated sales by region

Creating subtotals
Calc offers two methods of creating subtotals: the SUBTOTAL function and the Subtotals
tool.

Using the SUBTOTAL function

The SUBTOTAL function is listed under the Mathematical category of the Function
Wizard, and the Functions deck of the Sidebar, which are described in Chapter 8, Using
Formulas and Functions. SUBTOTAL is a relatively limited method for generating a
subtotal, and works best if used with only a few categories.

A SUBTOTAL example
To illustrate how to use the SUBTOTAL function, we use the sales data sheet shown
in Figure 6. The AutoFilter function is already applied to the sales data, as indicated by
the down arrow buttons at the head of each column. AutoFilters are described in
Chapter 2, Entering and Editing Data.

Figure 6: Sales data with AutoFilter applied (only the first few rows are shown)

To create a summation subtotal for the Sales Value field using the Function Wizard:

1) Select the cell to contain a subtotal. Typically, this cell is at the bottom of the
column being subtotaled, which, for our example, is the Sales Value column.
2) Use one of the following methods to open the Function Wizard dialog (Figure
7):
Select Insert > Function on the Menu bar
Click the Function Wizard icon on the Formula bar
Press Ctrl+F2
Figure 7: Function Wizard dialog
 3) Select SUBTOTAL in the Function list of the Function Wizard dialog and
click Next.
4) Enter the numeric code of a function into the Function field on the right side of
the dialog. This code must be a value in the range 1 to 11, or 101 to 111, with the
meaning of each value shown in Table 1.

Note
Values 1 to 11 include hidden values in the calculated subtotal, while values
101 to 111 do not. Hiding and showing data is described in Chapter 2,
Entering and Editing Data. Filtered-out cells are always excluded by the
SUBTOTAL function.

Table 1: SUBTOTAL function numbers

Function index Function index

Function
(includes hidden values) (ignores hidden values)
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
 Function index Function index
Function
(includes hidden values) (ignores hidden values)
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

5) Click on the Range field, then type a reference to the Sales Value range or
select the cells with your mouse (Figure 7). Use the Shrink / Expand button if
you need to temporarily minimize the dialog while selecting the cells.
6) Click OK to close the Function Wizard dialog. The cell you selected in
step now contains the total sales value.
7) Click on the AutoFilter down arrow button at the top of the Employee column
and remove all marks from the filter area except those next
to Brigitte and (empty). The cell that you selected in step should now reflect the
sum of all of Brigitte’s sales (Figure 8).

Note
If the cell range used to calculate a subtotal contains other subtotals, these
subtotals will not be counted in the final one. Similarly, if you use this
function with AutoFilters, only the data satisfying the current filter selections
will be displayed. Any filtered-out data is ignored.

Figure 8: SUBTOTAL result for Brigitte’s sales

The Subtotals tool

Calc offers the Subtotals tool as a more comprehensive alternative to the SUBTOTAL
function. In contrast to SUBTOTAL, which only works on a single array, the Subtotals tool
can create subtotals for up to three arrays arranged in labeled columns. It also groups
subtotals by category and sorts them automatically, thereby eliminating the need to
apply AutoFilters and filter categories by hand.

Using the Subtotals tool

To insert subtotal values into a sheet:
1) Select the cell range for the subtotals that you want to calculate, and
remember to include the column heading labels. Alternatively, click on a single
cell within your data to allow Calc to automatically identify the range.
2) Select Data > Subtotals on the Menu bar to open the Subtotals dialog (Figure
9).
3) In the Group by drop-down list on the 1st Group tab, select a column by its
label. Entries in the cell range from step will be grouped and sorted by matching
values in this column.
4) In the Calculate subtotals for box on the 1st Group tab, select a column
containing values to be subtotaled. If you later change values in this column, Calc
will automatically recalculate the subtotals.
5) In the Use function box on the 1st Group tab, select a function to calculate the
subtotals for the column selected in step .
6) Repeat steps and to create subtotals for other columns on the 1st Group tab.
7) You can create two more subtotal categories by using the 2nd Group and 3rd
Group tabs and repeating steps to . If you do not want to add more groups, then
leave the Group by list for each page set to “- none -”.
8) Click OK. Calc will add subtotal and grand total rows to your cell range.
Figure 9: Subtotals dialog

For our sales data example, a partial view of the results is shown in Figure 10. The group
settings are identified in Table 2.
 Table 2: Group settings used on Subtotals dialog for example sales data

Calculate
Tab Group by Use function
subtotals for
1st Group Employee Sales Value Sum
2nd Group Category Sales Value Sum
3rd Group - none - - -

Subtotal outline

When you use the Subtotals tool, Calc inserts an outline to the left of the row number
column. This outline represents the hierarchical structure of your subtotals, and can be
used to hide or show data at different levels in the hierarchy using the numbered
column indicators at the top of the outline or the group indicators, denoted by plus (+)
and minus (-) signs.

This feature is useful if you have many subtotals, as you can simply hide low-level
details, such as individual entries, to produce a high-level summary of your data. For
more information on how to use outlines, see Chapter 2, Entering and Editing Data.

To turn off outlines, select Data > Group and Outline > Remove Outline on the Menu
bar. To reinstate them, select Data > Group and Outline > AutoOutline.

Figure 10 shows the outline for our sales data example.

Figure 10: Partial outlined view of sales data example with subtotals
Column 1 represents the highest group level, the grand total over all employees.
Outline columns 2 to 5 show descending group levels as follows:
Column 2 represents the grand total over all categories.
Column 3 represents the total for each employee.
Column 4 represents the total for each category for an individual employee.
Column 5 shows individual entries.

Subtotals tool options

Click on the Options tab of the Subtotals dialog to access the following settings:

Groups
Defines how subtotal data is organized.
 Page break between groups – inserts page breaks between each subtotal
group so that each group displays on a separate page when you print the data.
Case sensitive – prevents the tool from grouping entries by data labels that
differ by case. In our sales data example, entries with “Brigitte” and “brigitte”
under the Employee column will not match if this option is selected.
Pre-sort area according to groups – sorts entries by group before calculating
subtotals. Disabling this option prevents the tool from grouping matching entries
together. As a result, distinct subtotals will be created for matching entries if they
do not appear on consecutive rows. For example, two entries under the “Golf”
category will not count towards the same group subtotal if there is an entry for
“Tennis” in between them.
Figure 11: Options tab of the Subtotals dialog

Sort
Defines how subtotal data is sorted. This section is disabled if Pre-sort area
according to groups is unchecked.
Ascending or Descending – sorts entries by value from lowest to highest and
highest to lowest, respectively. You can modify these sort rules by using Data >
Sort on the Menu bar. For more detail, see Chapter 2, Entering and Editing Data.
Include formats – carries over formatting, such as the currency format, from the
data to the corresponding subtotals.
Custom sort order – sorts your data according to one of the predefined custom
sorts defined in Tools > Options > LibreOffice Calc > Sort Lists on the Menu
bar. For more details about custom sort lists, see Chapter 2, Entering and Editing
Data.

Reset and Remove

In the Subtotals dialog, use the Reset button to undo any changes made on the current
tab. Use the Remove button to remove any subtotals that have already been created
using the Subtotals tool. Use these features with care, as no confirmation dialogs will be
displayed.

Using scenarios
Scenarios are saved, named cell ranges that you can use to answer “what-if” questions
about your data. You can create multiple scenarios for the same calculation set, then
quickly swap between them to view the outcomes of each. This feature is useful if you
need to test the effects of different conditions on your calculations, but do not want to
deal with repetitive manual data entry. For example, if you wanted to test different
interest rates for an investment, you could create scenarios for each rate, then switch
between them to find out which rates work the best for you.

Creating scenarios

To create a new scenario:

1) Select the cells that contain the values that will change between scenarios. To
select multiple ranges, hold down the Ctrl key as you click. You must select at
least two cells.
2) Choose Tools > Scenarios on the Menu bar to open the Create Scenario
dialog (Figure 12).
Figure 12: Create Scenario dialog

3) Enter a name for the new scenario in the Name of Scenario field.

Tip
For each scenario you create, use a unique name that clearly identifies and
distinguishes it. This practice will save you time and headache if you have to
work with a large set of scenarios. We do not recommend using the default
name suggested by Calc.

4) Optionally, add information to the Comment field. The example in Figure

12 shows the default comment.
 5) Click OK to close the dialog. The new scenario is automatically activated upon
creation.
6) Repeat steps to to create additional scenarios. Select the same cell range that
you used for the first scenario to have multiple scenarios for the same
calculations.

Tip
To keep track of what calculations are dependent on your scenarios,
use Tools > Detective > Trace Dependents on the Menu bar after
highlighting your scenario cells. Arrows will point from your scenario cells to
the dependent formula cells. For more information about the Detective tool,
see Chapter 8, Using Formulas and Functions.

Scenario settings

The Settings section of the Create Scenario dialog contains the following options:

Figure 13: Create Scenario dialog – Settings section

Display border
Places a colored border around the cell range that contains your scenario (Figure
14).
Figure 14: Scenario cell range with border

To choose the color of the border, use the drop-down field to the right of this
option. The border has a title bar displaying the name of the active scenario and a
down arrow button that opens a drop-down list of all the scenarios defined for the
current cell range. You can swap to another scenario by selecting it in this list.
Copy back
 Copies any changes made to a scenario’s cell values back into the active
scenario. If you uncheck this option, then the scenario’s initial values cannot be
overwritten. The actual behavior of the Copy back setting depends on the current
cell and sheet protections and the Prevent changes setting (see Table 4 on
page 1).

Caution
When creating a new scenario from the cells of a scenario with Copy
back enabled, be careful not to overwrite the old scenario. To avoid this
situation, create the new scenario with Copy back enabled first, then
change its values only once it is active.

Copy entire sheet

Creates a copy of the sheet with the new active scenario. The sheet is named
after this scenario. Note that changing scenario values in the copy sheet will not
affect the active scenario, even if it has Copy back enabled.
Prevent changes
Prevents changes to a scenario with Copy back enabled when the sheet is
protected but the cells are not. Also prevents changes to the scenario’s settings
while the sheet is protected. This option and its effects are more fully explained in
the next section.

Changing scenarios

Scenarios have two aspects that can be altered independently:

Scenario properties (that is, its settings)

Scenario cell values

The extent to which these aspects can be changed depends on the active scenario's
properties and the current sheet and cell protections. For more detail about sheet and
cell protections, see Chapter 2, Entering and Editing Data.

Changing scenario properties

Table 3 summarizes how sheet protection and the Prevent changes option affect your
ability to change scenario properties.

Table 3: Changing scenario properties

Sheet Prevent Property changes

protection changes
On On No scenario properties can be changed.
On Off Display border and Copy back can be
changed. Prevent changes and Copy entire
sheet cannot be changed.
 Off Any All scenario parameters except for Copy entire
setting sheet can be changed. In this case, the Prevent
changes option has no effect.

Changing scenario cell values

Table 4 summarizes the interaction of various settings in making changes to scenario

cell values.

Table 4: Changing scenario cell values

Sheet Scenario Prevent Copy back Change allowed

protection cell changes
protection
On Scenario cell values
Off On On
cannot be changed.
On Off On Scenario cell values can
Off be changed, and the
scenario is updated.
On Scenario cell values can
be changed, but the
Any
Off Off scenario is not updated
setting
due to the Copy
back setting.
On On Any Any Scenario cell values
setting setting cannot be changed.
Off Any Any Any Scenario cell values can
setting setting setting be changed and the
scenario is updated or
not, depending on
the Copy back setting.

Working with scenarios using the Navigator

After you add scenarios to a spreadsheet, you can view a particular scenario by using
the Navigator. Open it by selecting View > Navigator on the Menu bar, then click on
the Scenarios icon in the Navigator and select a scenario in the list (Figure 15). Any
defined scenarios are listed along with the comments that were entered when each
scenario was created. You can also use the equivalent facilities in the Navigator deck of
the Sidebar. For more information about the Navigator, see Chapter 1, Introduction.

Figure 15: Scenarios in the Navigator

To apply a scenario to the current sheet, double-click the scenario name in the
Navigator.

To delete a scenario, right-click the name in the Navigator and choose Delete, or
press Delete after selecting it. A confirmation dialog will be displayed.

To edit a scenario, right-click the name in the Navigator and choose Properties. Calc
displays the Edit Scenario dialog, which is similar to the Create Scenario dialog (Figure
12).

Using the Multiple Operations tool

Like scenarios, the Multiple Operations tool performs what-if analysis on your
calculations. Unlike scenarios, which represent individual sets of values for multiple
formula variables, this tool uses a whole range of values for just one or two variables. It
then uses one or more formulas to create a matching range of solutions. Since each
solution corresponds to one or two variable values, both the variable and solution
ranges can be easily arrayed in tabular format. As a result, the Multiple Operations tool
is well-suited to generating data that is easy to read and share or visualize using graphs.

Tip
Exercising good organization can make using this tool relatively painless.
For example, we recommend keeping your data together on one sheet and
using labels to identify your formulas, variables, and table ranges.

Multiple Operations with one formula and one variable

The easiest way to learn how to use the Multiple Operations tool is with one formula
and one variable. To learn how to use the tool with multiple formulas or with two
variables, see “Calculating with several formulas simultaneously” below and “Multiple
operations with two variables” below, respectively.

To use the Multiple Operations tool with one formula and one variable:

1) In the cells of a worksheet, enter a formula and at least one variable that it
uses.
2) In the same worksheet, enter values into a cell range that occupies a single
column or row. These values will be used for one of the variables of the formula
that you defined in step .
3) With the mouse, select the range containing both the variable range that you
defined in step and the adjacent empty cells that follow it. Depending on how your
variable range is arrayed, these empty cells will either be in the column to the
right (if the range is in a column) or in the row immediately below (if it is in a row).
4) Select Data > Multiple Operations on the Menu bar to open the Multiple
Operations dialog (Figure 16).
Figure 16: Multiple Operations dialog

5) Click on the Formulas field and type a cell reference to the formula you
defined in step or select the cell with the mouse. Use the associated Shrink /
Expand button if you need to minimize the dialog while selecting the cell.
6) If the range from step is arrayed in a column, then click on the Column input
cell field and type a cell reference to the variable that you want to use or select
the cell with the mouse. If the range is in a row, then use the Row input cell field
instead.
7) Click OK to run the tool. The Multiple Operations tool will generate its results
in the empty cells that you selected in step . Each result value corresponds to the
variable value adjacent to it, and together they form the entries of a results table.

An example with one formula and one variable

Using the Multiple Operations tool is best explained by example. Suppose that you
produce toys that you sell for $10 each (cell B1 of a worksheet). Each toy costs $2 to
make (B2), and you have a fixed annual cost of $10,000 (B3). What is the minimum
number of toys that you must sell to break even? Suppose that our initial estimate of
quantity sold is 2,000 (B4).

Figure 17: Inputs to Multiple Operations tool for one formula, one variable

To answer this question:

1) Enter the following formula into B5: =B4*(B1-B2)-B3. This formula

represents the equation Profit = Quantity * (Selling price – Direct costs) – Fixed
costs. With this equation, our initial quantity produces a $6,000 profit, which is
higher than the break-even point.
2) In D2:D11, enter a range of alternate quantities from 500 to 5000 in steps of
500.
3) Select the range D2:E11 to define the results table. This range includes the
alternate quantity values (column D) and the empty results cells (column E).
4) Select Data > Multiple Operations on the Menu bar to open the Multiple
Operations dialog.
5) Using the Formulas field, select the cell B5.
6) Using the Column input cell field, select the cell B4 to set the quantity as the
variable for our calculations. Figure 17 shows the worksheet and Multiple
Operations dialog at this point.
7) Click OK. The profits for the different quantities are now shown in column E
(Figure 18). We can see that the break-even point is between 1000 and 1500 toys
 sold – namely, 1250. Figure 18 shows an XY (Scatter) chart showing the profit as
a function of quantity.
Figure 18: Results of Multiple Operations tool for one formula and one variable

Figure 19: XY (Scatter) plot of profit over quantity of toys sold (example of visualization)

Calculating with several formulas simultaneously

Using the Multiple Operations tool with multiple formulas follows nearly the same
process as with one formula, but with two important differences:

1) For each formula that you add, you must also add a corresponding column or
row to the results table to contain the output of that formula.
2) How you initially arrange your formulas determines how their results will be
displayed in the results table. For example, if you arrange the formulas A, B,
and C in a single row in that order, then Calc will generate the results of A in the
first results table column, the results of B in the second column, and the results
of C in the third.

Note
The Multiple Operations tool only accepts formulas arranged in a single row
or column, depending on how your results table is oriented. If the table is
column-oriented – that is, the way it is in our sales data example – then your
 formulas must be arranged in a row. If the table is row-oriented, then your
formulas must be in a column.

Caution
Be careful not to add empty cells between formulas, as they will create gaps
in the results table and may cause some results not to appear if you don't
select enough rows or columns for the table.

An example with two formulas and one variable

Using our sales data example, suppose that we want to calculate the annual profit per
item sold in addition to the annual overall profit. To calculate the results:

1) In the sheet from the previous example, delete the results in column E.
2) Enter the following formula in C5: =B5/B4. You are now calculating the annual
profit per item sold.
3) Select the range D2:F11 for the results table. Column F will contain the results
of the annual profit per item formula in C5.
4) Select Data > Multiple Operations on the Menu bar to open the Multiple
Operations dialog.
5) Using the Formulas field, select the range B5:C5.
6) Using the Column input cell field, select the cell B4. Figure 20 shows the
worksheet and the dialog at this point.
Figure 20: Inputs to Multiple Operations tool for one variable and two formulas
 7) Click OK. Now the profits are listed in column E and the annual profit per item
in column F.
Figure 21: Results of Multiple Operations tool for one variable and two formulas

Multiple operations with two variables

When you use the Multiple Operations tool with two variables, it creates a two-
dimensional results table. Each variable defines one of the table’s dimensions such that
the alternate values for both variables serve as the table’s row and column headings,
respectively. Each cell in the table corresponds to a distinct pair of row and column
heading values. In turn, the results in each cell are created from these values for both
variables.

Since you are using two variables, you must use both the Column input cell and Row
input cell dialog fields to define them. The order is important; the Column input cell field
corresponds to the row heading values, while the Row input cell field corresponds to
the column heading values.

Tip
A good guideline to remember is that since column headings are in a row at
the top of the table, they correspond to the Row input cell field. Likewise,
row headings are in a column, so they correspond to the Column input
cell field.

Note
If you use two variables, the Multiple Operations tool will not work with
multiple formulas. It will allow you to enter the extra formulas, but will not
generate the expected results for any formula beyond the first.

Calculating with two variables

Using our sales example, suppose that in addition to varying the quantity of toys sold,
you also want to vary the unit sale price as well. To calculate the results:
1) Expand the sales data table by entering $8, $10, $15 and $20 in the range
E1:H1.
2) Select the range D1:H11 for the results table.
3) Select Data > Multiple Operations on the Menu bar to open the Multiple
Operations dialog.
4) Using the Formulas field, select cell B5.
5) Using the Row input cell field, select cell B1. The column headings –
$8, $10, $15 and $20 – are now linked to the unit sale price variable defined in cell
B1.
6) Using the Column input cell field, select cell B4. The row headings – 500, 1000,
... , 5000 – are now linked to the quantity sold variable defined in cell B4. Figure
22 shows the worksheet and dialog at this point.
7) Click OK. The profits for the different sale prices and quantities are now shown
in the range E2:H11 (Figure 22).
Figure 22: Inputs to Multiple Operations tool for two variables

Figure 23: Results of Multiple Operations tool for two variables

Using Goal Seek
In addition to scenarios and the Multiple Operations tool, Calc has a third “what-if”
analysis tool: Goal Seek. Usually, you use a formula to calculate a result from existing
values. In contrast, with Goal Seek, you work backwards from a result to discover what
values produce it. This feature is useful if you already know the outcome you want, but
need to answer questions such as how to reach it or how it could be changed if you
altered conditions.

Note
Only one argument can be altered at a time in a single goal seek. If you
need to test multiple arguments, then you must run a separate goal seek on
each one.

Goal Seek example

To illustrate how to use Goal Seek, suppose that we want to calculate the annual interest
return for an account. To calculate annual interest (I), we must create a table with values
for the capital (C), the interest period length in years (n), and the interest rate (i). The
formula is I = C*n*i.

Suppose that the interest rate i = 7.5% (cell B3 of a worksheet) and the period length n
= 1 (B2) remain constant. We want to know how much investment capital C is needed to
achieve a return of I = $15,000. Assume that our initial capital estimate is C =
$100,000 (B1).

To calculate the return:

1) Enter the return formula (=B1*B2*B3) into B4 and select the cell with the
mouse.
2) Select Tools > Goal Seek on the Menu bar to open the Goal Seek dialog
(Figure 24).
Figure 24: Goal Seek dialog
3) B4 should already be entered in the Formula cell field. However, if you want to
select a different cell, use the associated Shrink / Expand button to minimize the
dialog while you select the required cell.
4) Click on the Variable cell field, then type a reference to cell B1 or select it with
the mouse to make the capital the variable in the current Goal Seek.
5) Enter the desired formula result in the Target value field. In this example, the
value is 15000. Figure 25 shows the cells and dialog fields at this point.
Figure 25: Example setup for goal seek

6) Click OK. A dialog appears informing you that the goal seek was successful
(Figure 26).
Figure 26: Goal seek result dialog

7) Click Yes to enter the goal value into the variable cell. The result is shown
in Figure 27, indicating that a capital requirement of $200,000 is needed to
achieve a $15,000 return.
Figure 27: Result of goal seek in worksheet
Note
Not every goal seek problem succeeds in returning a good result. It depends
on the formula used, goal value, and initial value. The goal seek algorithm
iterates internally several times converging to the goal.

If the goal seek is unsuccessful, Calc displays an information dialog

reporting the failure. This dialog offers the choice of inserting the closest
value into the variable cell. Press Yes or No as required.

Using the Solver

The Solver amounts to a more elaborate form of goal seek, which allows you to solve
mathematical programming or optimization problems. A mathematical programming
problem is concerned with minimizing or maximizing a function subject to a set of
constraints. Such problems arise in many scientific, engineering, business, and other
disciplines. A full discussion of mathematical programming is beyond the scope of this
guide and the interested reader is referred to the relevant Wikipedia page
at [Link] which provides high level
information and references to more detailed materials.

Currently Calc offers the following selection of solver engines:

DEPS (Differential Evolution & Particle Swarm Optimization) Evolutionary Algorithm.

SCO (Social Cognitive Optimization) Evolutionary Algorithm.
LibreOffice CoinMP Linear Solver.
LibreOffice Linear Solver.
LibreOffice Swarm Non-Linear Solver (experimental).

Caution
As the LibreOffice Swarm Non-Linear Solver is an experimental tool, it may
not be supported in future versions of Calc, and we recommend that you do
not use it unless you are familiar with non-linear programming concepts.

The DEPS and SCO Evolutionary Algorithms are intended for solving non-linear
problems - they are only available if you have a Java runtime environment installed on
your computer and have enabled the Tools > Options > LibreOffice > Advanced >
Use a Java runtime environment configuration option. The DEPS Evolutionary
Algorithm is the initial default if it is available, while LibreOffice CoinMP Linear Solver is
the initial default otherwise.

The available options provide flexibility to choose the most suitable algorithm for a
given problem, which may be linear or non-linear, and a given performance
requirement. The Help system contains much more information about the available
algorithms and their configuration options.

In order to use the Solver to solve a mathematical programming problem, you must
formulate the problem as follows:

Decision variables – a set of n non-negative variables x1, … , xn,. Decision variables

may be real numbers, but generally tend to be integers in many real world problems.
Constraints – a set of linear equalities or inequalities involving the decision variables.
Objective function – a linear expression involving the decision variables.

The goal is usually to find values of the decision variables that satisfy the constraints
and maximize or minimize the result of the objective function.

Solver dialog

After setting up the data for the problem in your Calc spreadsheet, select Tools >
Solver on the Menu bar to open the Solver dialog (Figure 28).

Note
Depending on the configuration of your computer, a message may be
displayed the first time that you select Tools > Solver after starting Calc.
The nature of this message will change dependent on the existence of a
Java runtime environment (JRE) on your system. If no JRE is detected, the
message will simply be a warning to that effect. In the case where a JRE is
detected but the Tools > Options > LibreOffice > Advanced > Use a Java
runtime environment option is disabled, then the message will include a
button to enable that option.

Target cell
Type a cell reference to the objective function or select it with the mouse.
Optimize result to
Select Maximum to find the maximum result for the objective
function, Minimum to find the minimum result, or Value of to set it to a specific
value. If you select Value of, enter the required value or a reference to the cell
containing that value.
By changing cells
Enter the locations of any cells that define your decision variables.
Limiting Conditions
Enter your constraints in the fields in this area:
 Cell reference – enter a cell reference to a decision variable.
Operator – defines a parameter for a constraint. Available options
include <= (less than or equal to), = (equal to), => (greater than or equal
to), Integer (values without decimals), and Binary (only 0 or 1).
Value – enter a value or a cell reference to a constraint formula.
Remove button – deletes the currently-defined constraint.
Figure 28: Solver dialog

Tip
Remember that for some of these options, you can minimize the Solver
dialog using the associated Shrink / Expand buttons if you need to select
cells with the mouse.

Once you have finished setting up the Solver, click the Solve button to begin the
process of adjusting values and calculating results. Depending on the complexity of the
task, this may take some time. If you want to start again, click the Reset All button and
the data inserted in the Solver dialog (Figure 28) will be cleared.

If you are using either the DEPS Evolutionary Algorithm or the SCO Evolutionary
Algorithm, Calc may periodically interrupt execution of the solver engine to display the
Solver Status dialog (Figure 29). This dialog provides diagnostic information about the
current status of the engine’s calculations, which may be of interest to an expert user of
the Solver. Click OK to dismiss this dialog and finish the calculations, or
click Continue to allow the engine to carry on processing a step further, with the
diagnostic data on the dialog refreshed at the next break point. The display of the
Solver Status dialog is enabled by default but may be disabled by deselecting the Show
enhanced solver status setting on the Solver Options dialog.

Figure 29: Solver Status dialog

On successful completion, Calc presents a Solving Result dialog (Figure 30). This dialog
includes buttons to save (Keep Result) or discard (Restore Previous) your results.

Figure 30: Solving Result dialog

The Solver dialog also has an Options button, which opens the Options dialog shown
in Figure 31.

Solver options
Figure 31: Solver Options dialog
 Solver engine
With the standard LibreOffice download, and an enabled Java runtime
environment, the Solver engine drop-down list provides five options:
DEPS Evolutionary Algorithm
SCO Evolutionary Algorithm
LibreOffice CoinMP Linear Solver
LibreOffice Linear Solver
LibreOffice Swarm Non-Linear Solver (experimental)
Settings
This area enables the user to adjust the detailed processing parameters of the
selected solver engine, and the options available vary between engines.

Solver example

Suppose that you have $10,000 that you want to invest in two mutual funds for one
year. Fund X is a low risk fund with an 8% interest rate and Fund Y is a higher risk fund
with a 12% interest rate. How much money should be invested in each fund to earn a
total interest of $1,000?

To find the answer using the Solver:

1) Enter the following labels and data into a worksheet:

Row labels: Fund X, Fund Y, and Total in cells A2, A3, and A4.
Column labels: Interest earned, Amount invested, Interest rate, and Time
period in cells B1 thru E1.
Interest rates: 8% and 12% in cells D2 and D3.
Time period: 1 in cells E2 and E3.
Total amount invested: $10000 in cell C4.
 Enter an arbitrary value ($0 or leave blank) in cell C2 as the amount invested in
Fund X.
2) Enter the following formulas:
In cell C3, enter the formula =C4–C2 (total amount – amount invested in Fund X)
as the amount invested in Fund Y.
In cells B2 and B3, enter the formulas =C2*D2*E2 (B2) and =C3*D3*E3 (B3).
In cell B4, enter the formula =B2+B3 as the total interest earned. Figure 32 shows
the worksheet at this point.
Figure 32: Solver example setup

3) Select Tools > Solver on the Menu bar to open the Solver dialog (Figure 28).
4) Using the Target cell field, select the cell that contains the target value. In this
example, it is B4, which contains the total interest value.
5) Select Value of and enter 1000 in the field next to it. In this example, the
target cell value is 1000 because your target is a total interest earned of $1,000.
6) Using the By changing cells field, select cell C2 in the sheet. In this example,
you need to find the amount invested in Fund X (cell C2).
7) Enter the following limiting conditions for the variables by using the Cell
reference, Operator, and Value fields:
C2 <= C4 – the amount invested in Fund X cannot exceed the total amount
available.
C2 => 0 – the amount invested in Fund X cannot be negative.
C2 is an Integer – specified for convenience.
8) Click Solve. The result is shown in Figure 33.
Figure 33: Solver example result

Using Statistics tools

Under Data > Statistics on the Menu bar, Calc features several tools for quick and easy
statistical analysis of your data. These tools include:

Sampling
 Descriptive Statistics

Analysis of Variance (ANOVA)

Correlation

Covariance

Exponential Smoothing

Moving Average

Regression

Paired t-test

F-test

Z-test

Chi Square Test

Fourier Analysis

Sampling tool

The Sampling tool creates a target table with data sampled from a source table. The
sampling tool can pick samples randomly or on a periodic basis. Sampling is done row-
wise, with whole rows of the source table copied into rows of the target table. To use
this tool, select Data > Statistics > Sampling on the Menu bar to access the Sampling
dialog (Figure 34).

Figure 34: Sampling dialog

Input range
Specifies the cell range containing the source table.
Results to
Specifies the top left cell of the target table. When you run the tool, it will fill out
the rest of the target table starting from this cell. Be careful, Calc will replace any
data that is in the range of the table to be created.
Random
Enables random sampling mode when selected.
Sample size
Defines the number of lines to be sampled from the source table during random
sampling. Only available if the Random option is selected. If With replacement is
deselected, the maximum value that can be entered in this field is limited to the
number of rows in the source table.
With replacement
If this option is selected, a sample drawn from the source table is effectively
returned and so is eligible for re-sampling later. In this case, the sample size can
be larger than the number of rows in the source table. If the option is deselected,
a sample drawn from the source table is not eligible for re-sampling later. In this
case, the sample size is limited to the number of rows in the source table. This
option is automatically deselected for periodic sampling. It is not possible to have
both With replacement and Keep order options selected simultaneously.
Keep order
If this option is selected, samples are drawn from the source table in order of the
data in the source table. If deselected, samples are drawn in random order from
the source table. This option is automatically selected for periodic sampling. It is
not possible to have both With replacement and Keep order options selected
simultaneously.
Periodic
Enables periodic sampling mode when selected.
 Period
Defines the number of lines to skip between samples during periodic sampling.
For example, a value of 2 will cause alternate lines to be sampled from the source
table, starting with the second line. Only available if the Periodic option is
selected. The maximum value that can be entered in this field is limited to the
number of rows in the source table.

Tip
Use the Shrink / Expand buttons next to the Input range and Results
to fields if you need to shrink the dialog while selecting cells with the mouse.

Figure 35 shows the source table (below the Source Data heading) and the
corresponding target table (under the Target Data heading), sampled using the settings
shown in Figure 34.

Figure 35: Example data for the Sampling tool

Descriptive Statistics tool

Given a set of data, the Descriptive Statistics tool creates a tabular report of the data
set’s primary statistical properties, such as information about its central tendency and
variability. Select Data > Statistics > Descriptive Statistics on the Menu bar to access
the Descriptive Statistics dialog (Figure 36).

Figure 36: Descriptive Statistics dialog

 Input range
Specifies the cell range containing the source data.
Results to
Specifies the top left cell of the results area. When you run the tool, it will fill out
the rest of the report table starting at this cell. Be careful, Calc will replace any
data that is in the range of the table to be created.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.

Tip
Use the Shrink / Expand buttons next to the Input range and Results
to fields if you need to shrink the dialog while selecting cells with the mouse.

Figure 37 shows a small data set comprising student examination results in three
subjects.

Figure 37: Input data for descriptive statistics analysis

Figure 38 shows the statistics report generated for this input data using the settings
shown in Figure 36.

Figure 38: Results from Descriptive Statistics tool

Tip
For more information on descriptive statistics, refer to the corresponding
Wikipedia article at [Link]

Analysis of Variance (ANOVA) tool

The Analysis of Variance (ANOVA) tool compares the means of two or more groups in a
sample. Select Data > Statistics > Analysis of Variance (ANOVA) on the Menu bar to
access the Analysis of Variance (ANOVA) dialog (Figure 39).

Figure 39: Analysis of Variance (ANOVA) dialog

Input range
Specifies the cell range containing the source data.
Results to
 Specifies the top left cell of the results area. When you run the tool, it will fill out
the analysis of variance report table starting at this cell. Be careful, Calc will
replace any data that is in the range of the table to be created.
Single factor / Two factor
Determines whether the analysis is for single factor or two factor ANOVA.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows. Only
available if Single factor is selected.
Alpha
In this field, enter a significance level in the range 0.01 to 0.99. The default is
0.05.
Rows per sample
Defines how many rows a sample has. This option is always set to 1 in this
version of Calc.

Tip
Use the Shrink / Expand buttons next to the Input range and Results
to fields if you need to shrink the dialog while selecting cells with the mouse.

To illustrate how to use this tool, we use the input data set from Figure 37. Figure
40 shows the analysis of variance results generated for this data using the settings
shown in Figure 39.

Figure 40: Results from Analysis of Variance (ANOVA) tool

Tip
For more information on analysis of variance, refer to the corresponding
Wikipedia article at [Link]

Correlation tool

The Correlation tool calculates the correlation of two sets of numeric data and
generates the resulting correlation coefficient. This coefficient is a value between -1 and
+1 that indicates how strongly two variables are related to each other. A correlation
coefficient of +1 indicates a perfect positive correlation (the data sets match) and a
coefficient of -1 indicates a perfect negative correlation (the data sets are inverse to
each other). Select Data > Statistics > Correlation on the Menu bar to access the
Correlation dialog (Figure 41).

Figure 41: Correlation dialog

Input range
Specifies the cell range containing the source data.
Results to
Specifies the top left cell of the results area. When you run the tool, it will fill out
the correlation coefficient table starting at this cell. Be careful, Calc will replace
any data that is in the range of the table to be created.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.

Tip
Use the Shrink / Expand buttons next to the Input range and Results
to fields if you need to shrink the dialog while selecting cells with the mouse.

To illustrate how to use this tool, we again use the data set from Figure 37. Figure
42 shows the correlation coefficients generated for this input data using the settings
shown in Figure 41.

Figure 42: Correlation results

Tip
For more information on statistical correlation, refer to the corresponding
Wikipedia article
at [Link]

Covariance tool
The Covariance tool measures how much two sets of numeric data vary together.
Select Data > Statistics > Covariance on the Menu bar to access the Covariance
dialog (Figure 43).

Figure 43: Covariance dialog

Input range
Specifies the cell range containing the source data.
Results to
Specifies the top left cell of the results area. When you run the tool, it will fill out
the covariance table starting at this cell. Be careful, Calc will replace any data that
is in the range of the table to be created.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.

Tip
Use the Shrink / Expand buttons next to the Input range and Results
to fields if you need to shrink the dialog while selecting cells with the mouse.

To illustrate how to use this tool, we again use the data set from Figure 37. Figure
44 shows the six covariance values generated for this input data using the settings
shown in Figure 43.

Figure 44: Covariance results

Tip
For more information on statistical covariance, refer to the corresponding
Wikipedia article at [Link]

Exponential Smoothing tool

The Exponential Smoothing tool filters a data set to produce smoothed results. It is
used in domains such as stock market analysis and in sampled measurements.
Select Data > Statistics > Exponential Smoothing on the Menu bar to access the
Exponential Smoothing dialog (Figure 45).

Figure 45: Exponential Smoothing dialog

Input range
Specifies the cell range containing the source data.
Results to
Specifies the top left cell of the results area. When you run the tool, it will generate
smoothed results starting at this cell. Be careful, Calc will replace any data that is
in the range of the table to be created.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.
Smoothing factor
Enter a value here between 0 and 1 (default is 0.2) that represents the damping
factor alpha in the smoothing equation.

Tip
Use the Shrink / Expand buttons next to the Input range and Results
to fields if you need to shrink the dialog while selecting cells with the mouse.

To illustrate how to use this tool, we use the data set shown in Figure 46. The table has
two time series representing impulse functions at times t=0 and t=2.

Figure 46: Input data set for exponential smoothing example

Figure 47 shows the smoothed results for this input data using the settings shown
in Figure 45. In the result table, it is possible to change the outcome by varying the
Alpha parameter.

Tip
For more information on exponential smoothing, refer to the corresponding
Wikipedia article at [Link]

Figure 47: Results from Exponential Smoothing tool

Moving Average tool

The Moving Average tool calculates the moving average of a time series data set.
Select Data > Statistics > Moving Average on the Menu bar to access the Moving
Average dialog (Figure 48).

Figure 48: Moving Average dialog

 Input range
Specifies the cell range containing the source data.
Results to
Specifies the top left cell of the results area. When you run the tool, it will generate
the moving average results starting at this cell.
Trim input range to actual data content
Specifies whether to allow trimming of the input range to the actual data content
before computing the moving average. Enabled by default to provide better
performance.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.
Interval
Specifies the number of samples used in the moving average calculation (the
default value is 2).

Tip
Use the Shrink / Expand buttons next to the Input range and Results
to fields if you need to shrink the dialog while selecting cells with the mouse.

To illustrate how to use this tool, we again use the data set from Figure 46. Figure
49 shows the moving averages calculated for this input data using the settings shown
in Figure 48.

Tip
For more information on the moving average, refer to the corresponding
Wikipedia article at [Link]

Figure 49: Calculated moving averages

Regression tool

The Regression tool performs linear, logarithmic, or power regression analysis of a data
set comprising one dependent variable and multiple independent variables. Select Data
> Statistics > Regression on the Menu bar to access the Regression dialog (Figure 50).

Figure 50: Regression dialog

Independent variable(s) (X) range

Specifies the cell range containing the independent variables in the source data.
Dependent variable (Y) range
Specifies the cell range containing the dependent variable in the source data.
Both X and Y ranges have labels
Specifies whether the ranges above include data labels.
Results to
Specifies the top left cell of the results area. When you run the tool, it will generate
the regression analysis table starting at this cell. Be careful, Calc will replace any
data that is in the range of the table to be created.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.
Linear Regression
Select this option to use linear regression. Linear regression finds a linear function
in the form of y = b + a1.[x1] + a2.[x2] + a3.[x3] ..., where ai is the i-th slope, [xi] is
the i-th independent variable, and b is the intercept that best fits the data.
Logarithmic Regression
Select this option to use logarithmic regression. Logarithmic regression finds a
logarithmic curve in the form of y = b + [Link][x1] + [Link][x2] + [Link][x3] ..., where ai
is the i-th coefficient, b is the intercept and ln[xi] is the natural logarithm of the i-th
independent variable, that best fits the data.
Power Regression
Select this option to use power regression. Power regression finds a power curve
in the form of y = exp(b + [Link][x1] + [Link][x2] + [Link][x3] ...), where ai is the i-th
power, [xi] is the i-th independent variable, and b is intercept that best fits the data.
Confidence level
Specifies the confidence level, which is a value between 0 and 1. The default
value is 0.95. Calc uses this percentage to compute the corresponding confidence
intervals for each of the estimates (namely the slopes and intercept)
Calculate residuals
Select whether to opt in or out of computing the residuals, which may be beneficial
in cases where you are interested only in the slopes and intercept estimates and
their statistics. The residuals give information on how far the actual data points
deviate from the predicted data points, based on the regression model.
Force intercept to be zero
Specifies whether to force the regression intercept to be 0.

Tip
Use the Shrink / Expand buttons next to the Independent variable(s) (X)
range, Dependent variable (Y) range, and Results to fields if you need to
shrink the dialog while selecting cells with the mouse.

Tip
Calc utilizes the small, otherwise blank area above the Help, OK,
and Cancel buttons to provide feedback on erroneous selections on the
dialog. For example, the text “Independent variable(s) range is not valid.”
appears if you have not entered a valid cell range in the Independent
variable(s) (X) range field, and in this circumstance the OK button is grayed.
To illustrate how to use this tool, we use the data set shown in Figure 51. This table
contains measurements taken at 1 second intervals. Figure 52 shows the regression
outputs calculated for this input data using the settings shown in Figure 50.

Tip
For more information on regression analysis, refer to the corresponding
Wikipedia article at [Link]

Figure 51: Input data set for regression analysis

Figure 52: Linear regression outputs

Paired t-test tool

The Paired t-test tool compares the population means of two related sample sets and
determines the difference between them. Select Data > Statistics > Paired t-test on
the Menu bar to access the Paired t-test dialog (Figure 53).

Variable 1 range
Specifies the cell range containing the first set of input data.
Variable 2 range
Specifies the cell range containing the second set of input data.
Results to
Specifies the top left cell of the results area. When you run the tool, it will generate
the paired t-test table starting at this cell. Be careful, Calc will replace any data
that is in the range of the table to be created.
 Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.

Tip
Use the Shrink / Expand buttons next to the Variable 1 range, Variable 2
range, and Results to fields if you need to shrink the dialog while selecting
cells with the mouse.

Figure 53: Paired t-test dialog

To provide an example of using this tool, we make use of the input data set shown
in Figure 54. The data sets in columns A and B represent two sets of paired values
referred to as Variable 1 and Variable 2.

Figure 54: Input data for paired t-test example

Figure 55 shows the paired t-test results calculated for this input data using the settings
shown in Figure 53.

In the resulting table, it is possible to insert different values for Alpha and Hypothesized
Mean Difference. The t values (Stat, Critical one-tail, and Critical two-tail) will be
updated automatically.
Tip
For more information on paired t-tests, refer to the corresponding Wikipedia
article at [Link]

Figure 55: Results from Paired t-test tool

F-test tool

The F-test tool calculates the F-test of two data samples. The tool is used to test the
hypothesis that the variance of two populations are equal. Click Data > Statistics > F-
test on the Menu bar to access the F-test dialog shown in Figure 56 and define the
required inputs to the tool.

Figure 56: F-test dialog

Variable 1 range
Specifies the cell range containing the first set of input data.
Variable 2 range
Specifies the cell range containing the second set of input data.
Results to
 Specifies the top left cell of the results area. When you run the tool, it will generate
the F-test table starting at this cell. Be careful, Calc will replace any data that is in
the range of the table to be created.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.

Tip
Use the Shrink / Expand buttons next to the Variable 1 range, Variable 2
range, and Results to fields if you need to shrink the dialog while selecting
cells with the mouse.

To illustrate how to use this tool, we again use the data set from Figure 54. In this case,
the data in columns A and B represent two independent sample sets, referred to
as Variable 1 and Variable 2. Figure 57 shows the F-test results calculated for this input
data using the settings shown in Figure 56.

In the resulting table, it is possible to insert different values for Alpha. The F Critical
values (right-tail, left-tail, and two-tail) will be updated automatically.

Tip
For more information on F-tests, refer to the corresponding Wikipedia article
at [Link]

Figure 57: Results from F-test tool

Z-test tool

The Z-test tool calculates the Z-test of two data samples. The tool performs a two
sample Z-test to test the null hypothesis that there is no difference between the means
of the two data sets. The Z-test works better for large samples (n > 30); if you are using
a small sample, the Paired t-test tool may be more appropriate. Click Data > Statistics
> Z-test on the Menu bar to access the z-test dialog shown in Figure 58 and define the
required inputs to the tool.
 Variable 1 range
Specifies the cell range containing the first set of input data.
Variable 2 range
Specifies the cell range containing the second set of input data.
Results to
Specifies the top left cell of the results area. When you run the tool, it will generate
the Z-test table starting at this cell. Be careful, Calc will replace any data that is in
the range of the table to be created.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.

Tip
Use the Shrink / Expand buttons next to the Variable 1 range, Variable 2
range, and Results to fields if you need to shrink the dialog while selecting
cells with the mouse.

Figure 58: z-test dialog

To provide an example of using this tool, we again make use of the input data set
shown in Figure 54. In this case the data in columns A and B represent two data sets,
referred to as Variable 1 and Variable 2. Figure 59 shows the Z-test results calculated
for this input data using the settings shown in Figure 58.

Figure 59: Results from Z-test tool

For the Z-test tool to work properly, a known variance for each sample must be inserted
in the related cell. In the example shown in Figure 59, the variances (125.076923 and
94.435897) were inserted using the formula =VAR(A1:A13) into cell E5 and the
formula =VAR(B1:B13) into cell F5. The subsequent z and P values will be updated
automatically.

It is also possible to insert different values for Alpha (cell E2 in the example) and
Hypothesized Mean Difference (cell E3 in the example) inputs. As with the known
variances changes described above, after changing the Alpha and the Hypothesized
Mean Difference, the subsequent z and P values will be updated automatically.

Tip
When analyzing the Z-test results, compare the selected Alpha level with the
appropriate calculated P value (depending whether a one-tailed or two-tailed
test is required). If the calculated P value is smaller than the Alpha level, the
hypothesis (which, in the example given, is that the means of the two data
sets are the same) should be rejected.

Tip
For more information on z-tests, refer to the corresponding Wikipedia article
at [Link]

Chi-Square Test tool (Test of Independence)

The Chi-Square Test tool calculates the chi-square test of a data sample, which
determines how well a set of measured values fit a corresponding set of expected
values. Select Data > Statistics > Chi-square Test on the Menu bar to access the Test
of Independence (Chi-Square) dialog (Figure Error: Reference source not found).

Input range
Specifies the cell range containing the source data.
Results to
 Specifies the top left cell of the results area. When you run the tool, it will generate
the Chi-square table starting at this cell. Be careful, Calc will replace any data that
is in the range of the table to be created.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.
Figure 60: Test of Independence (Chi-Square) dialog

Tip
Use the Shrink / Expand buttons next to the Input range and Results
to fields if you need to shrink the dialog while selecting cells with the mouse.

To provide an example of using this tool, we again make use of the input data set
shown in Figure 54. In this case the data in column A is the observed data while the
data in column B are the corresponding expected values. Figure 61 shows the chi-
square results calculated for this input data using the settings shown in Figure Error:
Reference source not found.

Figure 61: Results of chi-square test

In the resulting table, it is possible to insert different values for Alpha. The Critical Value
will be updated automatically.

Tip
For more information on chi-square tests, refer to the corresponding
Wikipedia article at [Link]

Fourier Analysis tool

The Fourier Analysis tool performs the Fourier analysis of a data set by computing the
Discrete Fourier Transform (DFT) of an input array of complex numbers, using Fast
Fourier Transform (FFT) algorithms. Select Data > Statistics > Fourier Analysis on the
Menu bar to access the Fourier Analysis dialog (Figure 62).

Figure 62: Fourier Analysis dialog

Input range
Specifies the cell range containing the source data. Usually a 2 x N or N x 2 range
representing an array of complex numbers to be transformed, where N is the
length of the array. The array contains the real and imaginary parts of the data.
Results to
Specifies the top left cell of the results area. When you run the tool, it will generate
the Fourier transform table starting at this cell. Be careful, Calc will replace any
data that is in the range of the table to be created.
Input range has label
Specifies whether the first row or column of the input array is a label and not part
of the data to be analyzed.
Columns / Rows
Specifies whether the data to be analyzed is organized in columns or rows.
Inverse
If ticked, the tool calculates an inverse Discrete Fourier Transform.
Output in polar form
If ticked, the tool outputs the results in polar coordinates (that is, magnitude and
phase).
Minimum magnitude for polar form output
This option is only relevant when you select to output results in polar form. All
frequency components with magnitude less than the specified value in decibels
will be suppressed with a zero magnitude-phase entry. This is useful when looking
at the magnitude-phase spectrum of a signal because there is always some tiny
rounding error when performing FFT algorithms, which results in incorrect non-
 zero phase for non-existent frequencies. By providing a suitable value to this
parameter, these non-existent frequency components can be suppressed.

Tip
Use the Shrink / Expand buttons next to the Input range and Results
to fields if you need to shrink the dialog while selecting cells with the mouse.

Tip
Calc utilizes the small, otherwise blank area above the Help, OK,
and Cancel buttons to provide feedback on erroneous selections on the
dialog. For example, the text “Output address is not valid.” appears if you
have not entered a valid cell range in the Results to field, and in this
circumstance the OK button is grayed.

To provide an example of using this tool, we make use of the input data set shown in
columns B (real values) and C (imaginary values) of the spreadsheet shown in Figure 63.
The data shown in columns E (real values) and F (imaginary values) of the spreadsheet
are the Fourier transform results calculated by the tool for this input data, using the
settings shown in Figure 62.

Note
For those with a technical interest in the algorithms used by the Fourier
Analysis tool, a radix-2 decimation-in-time FFT is used when the length of
the input sequence is an even power of 2, while Bluestein’s FFT algorithm is
used when the length of the input sequence is not an even power of 2.

Tip
For more information on Fourier analysis, refer to the corresponding
Wikipedia article at [Link]

Figure 63: Fourier analysis tool - example input data and results