Calc Guide 24.2

Chapter 10 Data Analysis

Using Scenarios, Goal Seek, Solver, Statistics, others

Copyright

This document is Copyright © 2024 by the LibreOffice Documentation Team. Contributors are listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License (https://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.

All trademarks within this guide belong to their legitimate owners.

Contributors

To this edition

Steve Fanning

Olivier Hallot

To previous editions

Barbara Duprey	Jean Hollis Weber	John A Smith
Kees Kriek	Zachary Parliman	Olivier Hallot
Steve Fanning	Leo Moons	Felipe Viggiano
Skip Masonsmith	B. Antonio Fernández

Feedback

Please direct any comments or suggestions about this document to the Documentation Team’s forum at https://community.documentfoundation.org/c/documentation/loguides/ (registration is required) or send an email to: loguides@community.documentfoundation.org.

Note

Everything you send to a forum, including your email address and any other personal information that is written in the message, is publicly archived and cannot be deleted. Emails sent to the forum are moderated.

Publication date and software version

Published March 2024. Based on LibreOffice 24.2 Community.Other versions of LibreOffice may differ in appearance and functionality.

Using LibreOffice on macOS

Some keystrokes and menu items are different on macOS from those used in Windows and Linux. The table below gives some common substitutions for the instructions in this document. For a detailed list, see the application Help and Appendix A (Keyboard Shortcuts) to this guide.

Windows or Linux	macOS equivalent	Effect
Tools > Options on Menu bar	LibreOffice > Preferences on Menu bar	Access to setup options
Right-click	Ctrl+click and/or right-click depending on computer setup	Opens a context menu
Ctrl or Control	⌘ and/or Cmd or Command, depending on keyboard	Used with other keys
Alt	⌥ and/or Alt or Option depending on keyboard	Used with other keys

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.

Consolidation settings

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

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 Figure 3, Figure 4, and Figure 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 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 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.

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.

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

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 (includes hidden values)	Function index (ignores hidden values)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
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.

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.

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

Tab	Group by	Calculate subtotals for	Use function
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.

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.

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).

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:

Display border

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

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 protection	Prevent changes	Property 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 setting	All scenario parameters except for Copy entire 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 protection	Scenario cell protection	Prevent changes	Copy back	Change allowed
On	Off	On	On	Scenario cell values cannot be changed.
On	Off	Off	On	Scenario cell values can be changed, and the scenario is updated.
On	Off	Any setting	Off	Scenario cell values can be changed, but the scenario is not updated due to the Copy back setting.
On	On	Any setting	Any setting	Scenario cell values cannot be changed.
Off	Any setting	Any setting	Any setting	Scenario cell values can 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.

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).

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).

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.

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.

7)  Click OK. Now the profits are listed in column E and the annual profit per item in column F.

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).

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).

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.

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

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.

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 https://en.wikipedia.org/wiki/Mathematical_optimization, 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.

Note

Calc saves solver settings to the ODS file and each tab can have its own model. To support interoperability, the mechanism to save/load solver configurations is compatible with Microsoft Excel.

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.

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.

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.

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

Solver options

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.

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.

Using Statistics tools

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

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).

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.

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).

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 38 shows the statistics report generated for this input data using the settings shown in Figure 36.

Tip

For more information on descriptive statistics, refer to the corresponding Wikipedia article at https://en.wikipedia.org/wiki/Descriptive_statistics.

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).

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.

Tip

For more information on analysis of variance, refer to the corresponding Wikipedia article at https://en.wikipedia.org/wiki/Analysis_of_variance.

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).

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.

Tip

For more information on statistical correlation, refer to the corresponding Wikipedia article at https://en.wikipedia.org/wiki/Correlation_and_dependence.

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).

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.

Tip

For more information on statistical covariance, refer to the corresponding Wikipedia article at https://en.wikipedia.org/wiki/Covariance.

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).

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 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 https://en.wikipedia.org/wiki/Exponential_smoothing.

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).

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 https://en.wikipedia.org/wiki/Moving_average.

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).

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 + a1.ln[x1] + a2.ln[x2] + a3.ln[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 + a1.ln[x1] + a2.ln[x2] + a3.ln[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 https://en.wikipedia.org/wiki/Regression_analysis.

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.

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 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 https://en.wikipedia.org/wiki/Student's_t-test.

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.

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 https://en.wikipedia.org/wiki/F-test.

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.

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.

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 https://en.wikipedia.org/wiki/Z-test.

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.

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.

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 https://en.wikipedia.org/wiki/Chi-squared_test.

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).

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 https://en.wikipedia.org/wiki/Fourier_analysis.

Contents

• Copyright

• Contributors

• Feedback

• Publication date and software version

• Using LibreOffice on macOS

• Introduction

• Consolidating data

• Consolidation settings

• Consolidation example

• Creating subtotals

• Using the SUBTOTAL function

• A SUBTOTAL example

• The Subtotals tool

• Using the Subtotals tool

• Subtotal outline

• Subtotals tool options

• Reset and Remove

• Using scenarios

• Creating scenarios

• Scenario settings

• Changing scenarios

• Changing scenario properties

• Changing scenario cell values

• Working with scenarios using the Navigator

• Using the Multiple Operations tool

• Multiple Operations with one formula and one variable

• An example with one formula and one variable

• Calculating with several formulas simultaneously

• An example with two formulas and one variable

• Multiple operations with two variables

• Calculating with two variables

• Using Goal Seek

• Goal Seek example

• Using the Solver

• Solver dialog

• Solver options

• Solver example

• Using Statistics tools

• Sampling tool

• Descriptive Statistics tool

• Analysis of Variance (ANOVA) tool

• Correlation tool

• Covariance tool

• Exponential Smoothing tool

• Moving Average tool

• Regression tool

• Paired t-test tool

• F-test tool

• Z-test tool

• Chi-Square Test tool (Test of Independence)

• Fourier Analysis tool