From abe8187060201a5e62844b53d3786c6b99070ec2 Mon Sep 17 00:00:00 2001 From: Olivier Hallot Date: Fri, 22 May 2020 14:25:26 -0300 Subject: tdf81599 Better Help pages for Calc Solvers Add information on algorithms paramenters Change-Id: I36f330593687efa616594e397cb7d6054c2198ed Reviewed-on: https://gerrit.libreoffice.org/c/help/+/94703 Tested-by: Jenkins Reviewed-by: Olivier Hallot --- AllLangHelp_scalc.mk | 1 + source/auxiliary/scalc.tree | 1 + source/text/scalc/01/solver.xhp | 90 +++++-- source/text/scalc/01/solver_options.xhp | 51 +++- source/text/scalc/01/solver_options_algo.xhp | 386 +++++++++++++++++++++++++++ 5 files changed, 493 insertions(+), 36 deletions(-) create mode 100644 source/text/scalc/01/solver_options_algo.xhp diff --git a/AllLangHelp_scalc.mk b/AllLangHelp_scalc.mk index d8660002b5..d0599281e5 100644 --- a/AllLangHelp_scalc.mk +++ b/AllLangHelp_scalc.mk @@ -266,6 +266,7 @@ $(eval $(call gb_AllLangHelp_add_helpfiles,scalc,\ helpcontent2/source/text/scalc/01/live_data_stream \ helpcontent2/source/text/scalc/01/solver \ helpcontent2/source/text/scalc/01/solver_options \ + helpcontent2/source/text/scalc/01/solver_options_algo \ helpcontent2/source/text/scalc/01/statistics \ helpcontent2/source/text/scalc/01/statistics_anova \ helpcontent2/source/text/scalc/01/statistics_correlation \ diff --git a/source/auxiliary/scalc.tree b/source/auxiliary/scalc.tree index a82517becb..8aeeaac285 100644 --- a/source/auxiliary/scalc.tree +++ b/source/auxiliary/scalc.tree @@ -190,6 +190,7 @@ Deactivating Automatic Changes Consolidating Data Applying Goal Seek +Using Linear and Non-Linear Solvers Applying Multiple Operations Applying Multiple Sheets Validity of Cell Contents diff --git a/source/text/scalc/01/solver.xhp b/source/text/scalc/01/solver.xhp index 956f21211a..188b05f8c9 100644 --- a/source/text/scalc/01/solver.xhp +++ b/source/text/scalc/01/solver.xhp @@ -27,54 +27,70 @@ -
goal seeking;solver what if operations;solver back-solving solver + mathematical programming;solver for Calc + mathematical optimization;solver for Calc + operations research;solver for Calc -Solver -Opens the Solver dialog. A solver allows you to solve equations with multiple unknown variables by goal-seeking methods. +

Solver

+Opens the Solver dialog. A solver allows you to solve mathematical problems with multiple unknown variables and a set of constraints on the variables by goal-seeking methods.
-
target cell + +target cell +

Solver settings

+ +The dialog settings are retained until you close the current document. + -Enter or click the cell reference of the target cell. This field takes the address of the cell whose value is to be optimized.maximum +

Target Cell

+Enter or click the cell reference of the target cell. This field takes the address of the cell whose value is to be optimized. + +

Optimize results to

-Try to solve the equation for a maximum value of the target cell.where is the equation?minimum -Try to solve the equation for a minimum value of the target cell.value of - -Try to solve the equation to approach a given value of the target cell.value of text field -Enter the value or a cell reference.by changing cells + + + + Maximum: Try to solve the equation for a maximum value of the target cell. + + + Minimum: Try to solve the equation for a minimum value of the target cell. + + + Value of: Try to solve the equation to approach a given value of the target cell. + Enter the value or a cell reference in the text field. + + + +

By Changing Cells

-Enter the cell range that can be changed.cell reference +Enter the cell range that can be changed. These are the variables of the equations. -Enter a cell reference.operators -Select an operator from the list.what do these operators do? what is the meaning of Integer and Binary here? -I hate such spec docs that leave so many questionsvalue -Enter a value or a cell reference.shrinker @@ -86,22 +102,52 @@ I hate such spec docs that leave so many questions -Click to shrink or restore the dialog. You can click or select cells in the sheet. You can enter a cell reference manually in the input box.delete row -Click to remove the row from the list. Any rows from below this row move up.options button + +

Limiting Conditions

+Add the set of constraints for the mathematical problem. Each constraint is represented by a cell reference (a variable), an operator, and a value. + + + Cell reference: Enter a cell reference of the variable. + Click the Shrink button to shrink or restore the dialog. You can click or select cells in the sheet. You can enter a cell reference manually in the input box. + + + Operator: Select an operator from the list. Use Binary operator to restrict your variable to 0 or 1. Use the Integer operator to restrict your variable to take only integer values (no decimal part). + + + Value: Enter a value or a cell reference. This field is ignored when the operator is Binary or Integer. + + + Remove button: Click to remove the row from the list. Any rows from below this row move up. + + +You can set multiple conditions for a variable. For example, a variable in cell A1 that must be an integer less than 10. In that case, set two limiting conditions for A1. + +

Options

+ -Opens the Options dialog.solve button +Opens the Solver Options dialog. +The Solver Options dialog let you select the different solver algorithms for either linear and non-linear problems and set their solving parameters. +

Solve

+solve button -Click to solve the equation with the current settings. The dialog settings are retained until you close the current document. +Click to solve the problem with the current settings. The dialog settings are retained until you close the current document. -To solve equations with the solver +

To solve equations with the solver

The goal of the solver process is to find those variable values of an equation that result in an optimized value in the target cell, also named the "objective". You can choose whether the value in the target cell should be a maximum, a minimum, or approaching a given value. The initial variable values are inserted in a rectangular cell range that you enter in the By changing cells box. You can define a series of limiting conditions that set constraints for some cells. For example, you can set the constraint that one of the variables or cells must not be bigger than another variable, or not bigger than a given value. You can also define the constraint that one or more variables must be integers (values without decimals), or binary values (where only 0 and 1 are allowed). -The default solver engine supports only linear equations.Add an example. A good one is at http://www.solver.com/stepbystep.htm but that is not OpenSource +

Using Non-Linear solvers

+Regardless whether you use DEPS or SCO, you start by going to Tools - Solver and set the Cell to be optimized, the direction to go (minimization, maximization) and the cells to be modified to reach the goal. Then you go to the Options and specify the solver to be used and if necessary adjust the according parameters. +There is also a list of constraints you can use to restrict the possible range of solutions or to penalize certain conditions. However, in case of the evolutionary solvers DEPS and SCO, these constraints are also used to specify bounds on the variables of the problem. Due to the random nature of the algorithms, it is highly recommended to do so and give upper (and in case "Assume Non-Negative Variables" is turned off also lower) bounds for all variables. They don't have to be near the actual solution (which is probably unknown) but should give a rough indication of the expected size (0 ≤ var ≤ 1 or maybe -1000000 ≤ var ≤ 1000000). +Bounds are specified by selecting one or more variables (as range) on the left side and entering a numerical value (not a cell or a formula) on the right side. That way you can also choose one or more variables to be Integer or Binary only. +
+ + + Wiki page on solvers and theirs algorithms +
- diff --git a/source/text/scalc/01/solver_options.xhp b/source/text/scalc/01/solver_options.xhp index d77b3850ab..e4e2291d4d 100644 --- a/source/text/scalc/01/solver_options.xhp +++ b/source/text/scalc/01/solver_options.xhp @@ -20,7 +20,7 @@ - Options + Solver Options /text/scalc/01/solver_options.xhp @@ -29,29 +29,52 @@ + + solver for Calc;options + +

Solver Options

-Options -The Options dialog for the Solver is used to set some options. +Use the Options dialog to configure the solver engine.
-
solver engine + + +Click OK to accept the changes and to go back to the Solver dialog. +

Solver engine

-Select a solver engine. The listbox is disabled if only one solver engine is installed. Solver engines can be installed as extensions.settings +Select a solver engine. The listbox is disabled if only one solver engine is installed. +You can install more solver engines as extensions, if available. Open Tools - Extension Manager and browse to the Extensions web site to search for extensions. -Configure the current solver.edit +

Settings

+Configure the current solver. In the Settings box, check all settings that you want to use for the current goal seeking operation. If the current option offers different values, the Edit button is enabled. Click Edit to open a dialog where you can change the value. + + + + + + + + + + + + + + +

Edit

-If the current entry in the Settings listbox allows you to edit a value, you can click the Edit button. A dialog opens where you can change the value.edit settings spin box +If the current entry in the Settings listbox allows you to edit a value, you can click the Edit button. A dialog opens where you can change the value. +

Edit settings spin box

-Enter or change the value. -Use the Options dialog to configure the current solver engine. -You can install more solver engines as extensions, if available. Open Tools - Extension Manager and browse to the Extensions web site to search for extensions. -Select the solver engine to use and to configure from the listbox. The listbox is disabled if only one solver engine is installed. -In the Settings box, check all settings that you want to use for the current goal seeking operation. If the current option offers different values, the Edit button is enabled. Click Edit to open a dialog where you can change the value. -Click OK to accept the changes and to go back to the Solver dialog. +Enter or change the value of the selected setting. +
+ + + Wiki page on non-linear solvers and theirs algorithms +
- diff --git a/source/text/scalc/01/solver_options_algo.xhp b/source/text/scalc/01/solver_options_algo.xhp new file mode 100644 index 0000000000..486212284f --- /dev/null +++ b/source/text/scalc/01/solver_options_algo.xhp @@ -0,0 +1,386 @@ + + + + + + + + Options + /text/scalc/01/solver_options_algo.xhp + + + + + Solver for Nonlinear Problems;Options + solver for Calc; DEPS evolutionary algorithm + solver for Calc; SCO evolutionary algorithm + solver for Calc; linear solver + solver for Calc; CoinMP linear solver + solver for Calc; swarm non-linear solver + + +

Solver Algorithms Options

+ +

DEPS Evolutionary Algorithm

+ DEPS consists of two independent algorithms: Differential Evolution and Particle Swarm Optimization. Both are especially suited for numerical problems, such as nonlinear optimization, and are complementary to each other in that they even out their others shortcomings. + + + + + + + + + + + + Agent Switch Rate + + + Specifies the probability for an individual to choose the Differential Evolution strategy. + + + + + + + + + + + + + DE: Crossover Probability + + + Defines the probability of the individual being combined with the globally best point. If crossover is not used, the point is assembled from the own memory of the individual. + + + + + DE: Scaling Factor + + + During crossover, the scaling factor decides about the “speed” of movement. + + + + + Learning Cycles + + + Defines the number of iterations, the algorithm should take. In each iteration, all individuals make a guess on the best solution and share their knowledge. + + + + + PS: Cognitive Constant + + + Sets the importance of the own memory (in particular the best reached point so far). + + + + + PS: Constriction Coefficient + + + Defines the speed at which the particles/individuals move towards each other. + + + + + PS: Mutation Probability + + + Defines the probability, that instead of moving a component of the particle towards the best point, it randomly chooses a new value from the valid range for that variable. + + + + + PS: Social Constant + + + Sets the importance of the global best point between all particles/individuals. + + + + + Show Enhanced Solver Status + + + If enabled, an additional dialog is shown during the solving process which gives information about the current progress, the level of stagnation, the currently best known solution as well as the possibility, to stop or resume the solver. + + + + + Size of Swarm + + + Defines the number of individuals to participate in the learning process. Each individual finds its own solutions and contributes to the overall knowledge. + + + + + Stagnation Limit + + + If this number of individuals found solutions within a close range, the iteration is stopped and the best of these values is chosen as optimal. + + + + + Stagnation Tolerance + + + Defines in what range solutions are considered “similar”. + + + + + Use ACR Comparator + + +
+ If disabled (default), the BCH Comparator is used. It compares two individuals by first looking at their constraint violations and only if those are equal, it measures their current solution. + If enabled, the ACR Comparator is used. It compares two individuals dependent on the current iteration and measures their goodness with knowledge about the libraries worst known solutions (in regard to their constraint violations). +
+ + + + + Use Random Starting Point + + + If enabled, the library is simply filled up with randomly chosen points. + If disabled, the currently present values (as given by the user) are inserted in the library as reference point. + + + + + Variable Bounds Guessing + + + If enabled (default), the algorithm tries to find variable bounds by looking at the starting values. + + + + + Variable Bounds Threshold + + + When guessing variable bounds, this threshold specifies, how the initial values are shifted to build the bounds. For an example how these values are calculated, please refer to the Manual in the Wiki. + + +
+ +

SCO Evolutionary Algorithm

+ Social Cognitive Optimization takes into account the human behavior of learning and sharing information. Each individual has access to a common library with knowledge shared between all individuals. + + + + + + + + + + + + + + + + + + + + Learning Cycles + + + Defines the number of iterations, the algorithm should take. In each iteration, all individuals make a guess on the best solution and share their knowledge. + + + + + + + + + + + + + Size of Library + + + Defines the amount of information to store in the public library. Each individual stores knowledge there and asks for information. + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+

%PRODUCTNAME Linear Solver and CoinMP Linear solver

+ + + + Setting + + + Description + + + + + Assume variables as integers + + + Mark to force variables to be integers only. + + + + + Assume variables as non negative + + + Mark to force variables to be positive only. + + + + + Epsilon level + + + Epsilon level. Valid values are in range 0 (very tight) to 3 (very loose). Epsilon is the tolerance for rounding values to zero. + + + + + Limit branch-and-bound depth + + + Specifies the maximum branch-and-bound depth. A positive value means that the depth is absolute. A negative value means a relative branch-and-bound depth limit. + + + + + Solver time limit + + + Sets the maximum time for the algorithm to converge to a solution. + + +
+

%PRODUCTNAME Swarm Non-Linear Solver (Experimental)

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Swarm algorithm + + + Set the swarm algorithm. 0 for differential evolution and 1 for particle swarm optimization. Default is 0. + + +
+
+ + + Wiki page on non-linear solvers and theirs algorithms + lp_solve reference guide in sourceforge.net +
+ + -- cgit