1 /* vim: set sw=8: -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
6 * Jukka-Pekka Iivonen <iivonen@iki.fi>
7 * Morten Welinder (terra@gnome.org)
9 * This program is free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
19 * You should have received a copy of the GNU General Public License
20 * along with this program; if not, see <https://www.gnu.org/licenses/>.
23 #include <gnumeric-config.h>
24 #include <glib/gi18n-lib.h>
34 #include <dependent.h>
35 #include <gnm-format.h>
39 #include <number-match.h>
40 #include <parse-util.h>
42 #include <workbook-control.h>
44 #include <workbook-view.h>
45 #include <goal-seek.h>
47 #include <widgets/gnumeric-expr-entry.h>
48 #include <selection.h>
49 #include <application.h>
55 static const gnm_float max_range_val
= GNM_const(1e24
);
57 #define MAX_CELL_NAME_LEN 20
58 #define GOALSEEK_KEY "goal-seek-dialog"
63 GnmExprEntry
*set_cell_entry
;
64 GnmExprEntry
*change_cell_entry
;
65 GtkWidget
*to_value_entry
;
66 GtkWidget
*at_least_entry
;
67 GtkWidget
*at_most_entry
;
68 GtkWidget
*close_button
;
69 GtkWidget
*cancel_button
;
70 GtkWidget
*apply_button
;
71 GtkWidget
*target_value_label
;
72 GtkWidget
*current_value_label
;
73 GtkWidget
*solution_label
;
74 GtkWidget
*result_label
;
75 GtkWidget
*result_grid
;
79 gnm_float target_value
;
86 GtkWidget
*warning_dialog
;
93 GnmCell
*xcell
, *ycell
;
99 goal_seek_eval (gnm_float x
, gnm_float
*y
, void *vevaldata
)
101 GoalEvalData
const *evaldata
= vevaldata
;
102 GnmValue
*v
= value_new_float (x
);
104 if (evaldata
->update_ui
) {
105 sheet_cell_set_value (evaldata
->xcell
, v
);
107 gnm_cell_set_value (evaldata
->xcell
, v
);
108 cell_queue_recalc (evaldata
->xcell
);
110 gnm_cell_eval (evaldata
->ycell
);
112 if (evaldata
->ycell
->value
) {
113 *y
= value_get_as_float (evaldata
->ycell
->value
) - evaldata
->ytarget
;
118 return GOAL_SEEK_ERROR
;
122 static GoalSeekStatus
123 gnumeric_goal_seek (GoalSeekState
*state
)
125 GoalSeekData seekdata
;
126 GoalEvalData evaldata
;
127 GoalSeekStatus status
;
131 goal_seek_initialize (&seekdata
);
132 seekdata
.xmin
= state
->xmin
;
133 seekdata
.xmax
= state
->xmax
;
135 evaldata
.xcell
= state
->change_cell
;
136 evaldata
.ycell
= state
->set_cell
;
137 evaldata
.ytarget
= state
->target_value
;
138 evaldata
.update_ui
= FALSE
;
139 evaldata
.state
= state
;
141 hadold
= !VALUE_IS_EMPTY_OR_ERROR (state
->change_cell
->value
);
142 oldx
= hadold
? value_get_as_float (state
->change_cell
->value
) : 0;
144 /* PLAN A: Newton's iterative method from initial or midpoint. */
148 if (hadold
&& oldx
>= seekdata
.xmin
&& oldx
<= seekdata
.xmax
)
151 x0
= (seekdata
.xmin
+ seekdata
.xmax
) / 2;
153 status
= goal_seek_newton (goal_seek_eval
, NULL
,
154 &seekdata
, &evaldata
,
156 if (status
== GOAL_SEEK_OK
)
160 /* PLAN B: Trawl uniformly. */
161 if (!seekdata
.havexpos
|| !seekdata
.havexneg
) {
162 status
= goal_seek_trawl_uniformly (goal_seek_eval
,
163 &seekdata
, &evaldata
,
164 seekdata
.xmin
, seekdata
.xmax
,
166 if (status
== GOAL_SEEK_OK
)
170 /* PLAN C: Trawl normally from middle. */
171 if (!seekdata
.havexpos
|| !seekdata
.havexneg
) {
175 sigma
= MIN (seekdata
.xmax
- seekdata
.xmin
, 1e6
);
176 mu
= (seekdata
.xmax
+ seekdata
.xmin
) / 2;
178 for (i
= 0; i
< 5; i
++) {
180 status
= goal_seek_trawl_normally (goal_seek_eval
,
181 &seekdata
, &evaldata
,
183 if (status
== GOAL_SEEK_OK
)
188 /* PLAN D: Trawl normally from left. */
189 if (!seekdata
.havexpos
|| !seekdata
.havexneg
) {
193 sigma
= MIN (seekdata
.xmax
- seekdata
.xmin
, 1e6
);
196 for (i
= 0; i
< 5; i
++) {
198 status
= goal_seek_trawl_normally (goal_seek_eval
,
199 &seekdata
, &evaldata
,
201 if (status
== GOAL_SEEK_OK
)
206 /* PLAN E: Trawl normally from right. */
207 if (!seekdata
.havexpos
|| !seekdata
.havexneg
) {
211 sigma
= MIN (seekdata
.xmax
- seekdata
.xmin
, 1e6
);
214 for (i
= 0; i
< 5; i
++) {
216 status
= goal_seek_trawl_normally (goal_seek_eval
,
217 &seekdata
, &evaldata
,
219 if (status
== GOAL_SEEK_OK
)
224 /* PLAN F: Newton iteration with uniform net of starting points. */
225 if (!seekdata
.havexpos
|| !seekdata
.havexneg
) {
229 for (i
= 1; i
<= N
; i
++) {
230 gnm_float x0
= seekdata
.xmin
+
231 (seekdata
.xmax
- seekdata
.xmin
) / (N
+ 1) * i
;
233 status
= goal_seek_newton (goal_seek_eval
, NULL
,
234 &seekdata
, &evaldata
,
236 if (status
== GOAL_SEEK_OK
)
241 /* PLAN Z: Bisection. */
243 status
= goal_seek_bisection (goal_seek_eval
,
244 &seekdata
, &evaldata
);
245 if (status
== GOAL_SEEK_OK
)
250 evaldata
.update_ui
= TRUE
;
251 if (status
== GOAL_SEEK_OK
) {
253 (void) goal_seek_eval (seekdata
.root
, &yroot
, &evaldata
);
256 (void) goal_seek_eval (oldx
, &ydummy
, &evaldata
);
263 cb_dialog_destroy (GoalSeekState
*state
)
265 if (!state
->cancelled
266 && state
->old_value
!= NULL
267 && state
->old_cell
!= NULL
) {
268 cmd_goal_seek (GNM_WBC(state
->wbcg
),
269 state
->old_cell
, state
->old_value
, NULL
);
270 state
->old_value
= NULL
;
273 value_release (state
->old_value
);
274 if (state
->gui
!= NULL
)
275 g_object_unref (state
->gui
);
277 wbcg_edit_finish (state
->wbcg
, WBC_EDIT_REJECT
, NULL
);
282 cb_dialog_cancel_clicked (G_GNUC_UNUSED GtkWidget
*button
,
283 GoalSeekState
*state
)
285 state
->cancelled
= TRUE
;
287 if ((state
->old_cell
!= NULL
) && (state
->old_value
!= NULL
)) {
288 sheet_cell_set_value (state
->old_cell
, state
->old_value
);
289 state
->old_value
= NULL
;
293 gtk_widget_destroy (state
->dialog
);
297 cb_dialog_close_clicked (G_GNUC_UNUSED GtkWidget
*button
,
298 GoalSeekState
*state
)
300 gtk_widget_destroy (state
->dialog
);
304 * cb_dialog_apply_clicked:
308 * Close (destroy) the dialog
311 cb_dialog_apply_clicked (G_GNUC_UNUSED GtkWidget
*button
,
312 GoalSeekState
*state
)
315 GoalSeekStatus status
;
317 GnmRangeRef
const *r
;
318 GOFormat
const *format
;
320 if (state
->warning_dialog
!= NULL
)
321 gtk_widget_destroy (state
->warning_dialog
);
324 target
= gnm_expr_entry_parse_as_value (state
->set_cell_entry
,
326 if (target
== NULL
) {
327 go_gtk_notice_nonmodal_dialog (GTK_WINDOW(state
->dialog
),
328 &(state
->warning_dialog
),
330 _("You should introduce a valid cell "
331 "name in 'Set Cell:'!"));
332 gnm_expr_entry_grab_focus (state
->set_cell_entry
, TRUE
);
335 r
= &target
->v_range
.cell
;
336 state
->set_cell
= sheet_cell_get (r
->a
.sheet
, r
->a
.col
, r
->a
.row
);
337 value_release (target
);
338 if (state
->set_cell
== NULL
|| !gnm_cell_has_expr (state
->set_cell
)) {
339 go_gtk_notice_nonmodal_dialog (GTK_WINDOW(state
->dialog
),
340 &(state
->warning_dialog
),
342 _("The cell named in 'Set Cell:' "
343 "must contain a formula!"));
344 gnm_expr_entry_grab_focus (state
->set_cell_entry
, TRUE
);
349 target
= gnm_expr_entry_parse_as_value (state
->change_cell_entry
,
351 if (target
== NULL
) {
352 go_gtk_notice_nonmodal_dialog (GTK_WINDOW(state
->dialog
),
353 &(state
->warning_dialog
),
355 _("You should introduce a valid cell "
356 "name in 'By Changing Cell:'!"));
357 gnm_expr_entry_grab_focus (state
->change_cell_entry
, TRUE
);
361 r
= &target
->v_range
.cell
;
362 state
->change_cell
= sheet_cell_fetch (r
->a
.sheet
, r
->a
.col
, r
->a
.row
);
363 value_release (target
);
364 if (gnm_cell_has_expr (state
->change_cell
)) {
365 go_gtk_notice_nonmodal_dialog (GTK_WINDOW(state
->dialog
),
366 &(state
->warning_dialog
),
368 _("The cell named in 'By changing cell' "
369 "must not contain a formula."));
370 gnm_expr_entry_grab_focus (state
->change_cell_entry
, TRUE
);
375 format
= gnm_style_get_format (gnm_cell_get_style (state
->set_cell
));
376 if (entry_to_float_with_format (GTK_ENTRY(state
->to_value_entry
),
377 &state
->target_value
, TRUE
, format
)){
378 go_gtk_notice_nonmodal_dialog (GTK_WINDOW(state
->dialog
),
379 &(state
->warning_dialog
),
381 _("The value given in 'To Value:' "
383 focus_on_entry (GTK_ENTRY(state
->to_value_entry
));
387 format
= gnm_style_get_format (gnm_cell_get_style (state
->change_cell
));
388 if (entry_to_float_with_format (GTK_ENTRY(state
->at_least_entry
),
389 &state
->xmin
, TRUE
, format
)) {
390 state
->xmin
= -max_range_val
;
391 gtk_entry_set_text (GTK_ENTRY (state
->at_least_entry
), "");
394 if (entry_to_float_with_format (GTK_ENTRY(state
->at_most_entry
), &state
->xmax
,
396 state
->xmax
= +max_range_val
;
397 gtk_entry_set_text (GTK_ENTRY (state
->at_most_entry
), "");
400 if ((state
->old_cell
!= NULL
) && (state
->old_value
!= NULL
)) {
401 sheet_cell_set_value (state
->old_cell
, state
->old_value
);
402 state
->old_value
= NULL
;
405 state
->old_cell
= state
->change_cell
;
406 state
->old_value
= value_dup (state
->change_cell
->value
);
408 status
= gnumeric_goal_seek (state
);
414 const char *actual_str
;
415 const char *solution_str
;
416 GOFormat
*format
= go_format_general ();
417 GnmValue
*error_value
= value_new_float (state
->target_value
-
418 value_get_as_float (state
->set_cell
->value
));
419 char *target_str
= format_value (format
, error_value
, -1,
420 workbook_date_conv (state
->wb
));
421 gtk_label_set_text (GTK_LABEL (state
->target_value_label
), target_str
);
423 value_release (error_value
);
426 g_strdup_printf (_("Goal seeking with cell %s found a solution."),
427 cell_name (state
->set_cell
));
428 gtk_label_set_text (GTK_LABEL (state
->result_label
), status_str
);
431 /* FIXME? Do a format? */
432 actual_str
= state
->set_cell
->value
433 ? value_peek_string (state
->set_cell
->value
)
435 gtk_label_set_text (GTK_LABEL (state
->current_value_label
), actual_str
);
437 solution_str
= state
->change_cell
->value
438 ? value_peek_string (state
->change_cell
->value
)
440 gtk_label_set_text (GTK_LABEL (state
->solution_label
), solution_str
);
447 g_strdup_printf (_("Goal seeking with cell %s did not find a solution."),
448 cell_name (state
->set_cell
));
449 gtk_label_set_text (GTK_LABEL (state
->result_label
), status_str
);
451 gtk_label_set_text (GTK_LABEL (state
->current_value_label
), "");
452 gtk_label_set_text (GTK_LABEL (state
->solution_label
), "");
453 gtk_label_set_text (GTK_LABEL (state
->target_value_label
), "");
456 state
->cancelled
= FALSE
;
458 gtk_widget_show (state
->result_grid
);
471 dialog_realized (G_GNUC_UNUSED GtkWidget
*dialog
,
472 GoalSeekState
*state
)
474 gtk_widget_hide (state
->result_grid
);
478 * dialog_preload_selection:
485 dialog_preload_selection (GoalSeekState
*state
, GnmExprEntry
*entry
)
489 sel
= selection_first_range
490 (wb_control_cur_sheet_view
491 (GNM_WBC (state
->wbcg
)), NULL
, NULL
);
493 gnm_expr_entry_load_from_range (entry
,
501 * Create the dialog (guru).
505 dialog_init (GoalSeekState
*state
)
509 state
->dialog
= go_gtk_builder_get_widget (state
->gui
, "GoalSeek");
510 if (state
->dialog
== NULL
)
513 gnm_dialog_setup_destroy_handlers (GTK_DIALOG (state
->dialog
),
515 GNM_DIALOG_DESTROY_CURRENT_SHEET_REMOVED
);
517 state
->close_button
= go_gtk_builder_get_widget (state
->gui
, "closebutton");
518 g_signal_connect (G_OBJECT (state
->close_button
),
520 G_CALLBACK (cb_dialog_close_clicked
), state
);
522 state
->cancel_button
= go_gtk_builder_get_widget (state
->gui
, "cancelbutton");
523 g_signal_connect (G_OBJECT (state
->cancel_button
),
525 G_CALLBACK (cb_dialog_cancel_clicked
), state
);
526 state
->apply_button
= go_gtk_builder_get_widget (state
->gui
, "applybutton");
527 g_signal_connect (G_OBJECT (state
->apply_button
),
529 G_CALLBACK (cb_dialog_apply_clicked
), state
);
531 gnm_init_help_button (
532 go_gtk_builder_get_widget (state
->gui
, "helpbutton"),
533 GNUMERIC_HELP_LINK_GOAL_SEEK
);
535 state
->to_value_entry
= go_gtk_builder_get_widget (state
->gui
, "to_value_entry");
536 state
->at_least_entry
= go_gtk_builder_get_widget (state
->gui
, "at_least-entry");
537 state
->at_most_entry
= go_gtk_builder_get_widget (state
->gui
, "at_most-entry");
538 state
->target_value_label
= go_gtk_builder_get_widget (state
->gui
, "target-value");
539 gtk_label_set_justify (GTK_LABEL (state
->target_value_label
), GTK_JUSTIFY_RIGHT
);
540 state
->current_value_label
= go_gtk_builder_get_widget (state
->gui
, "current-value");
541 gtk_label_set_justify (GTK_LABEL (state
->current_value_label
), GTK_JUSTIFY_RIGHT
);
542 state
->solution_label
= go_gtk_builder_get_widget (state
->gui
, "solution");
543 gtk_label_set_justify (GTK_LABEL (state
->solution_label
), GTK_JUSTIFY_RIGHT
);
545 state
->result_label
= go_gtk_builder_get_widget (state
->gui
, "result-label");
546 state
->result_grid
= go_gtk_builder_get_widget (state
->gui
, "result-grid");
548 grid
= GTK_GRID (go_gtk_builder_get_widget (state
->gui
, "goal-grid"));
549 state
->set_cell_entry
= gnm_expr_entry_new (state
->wbcg
, TRUE
);
550 gnm_expr_entry_set_flags (state
->set_cell_entry
,
551 GNM_EE_SINGLE_RANGE
|
552 GNM_EE_FORCE_ABS_REF
,
554 gtk_grid_attach (grid
, GTK_WIDGET (state
->set_cell_entry
),
556 gtk_widget_set_hexpand (GTK_WIDGET (state
->set_cell_entry
), TRUE
);
557 gnm_editable_enters (GTK_WINDOW (state
->dialog
),
558 GTK_WIDGET (state
->set_cell_entry
));
559 dialog_preload_selection (state
, state
->set_cell_entry
);
560 gtk_widget_show (GTK_WIDGET (state
->set_cell_entry
));
562 state
->change_cell_entry
= gnm_expr_entry_new (state
->wbcg
, TRUE
);
563 gnm_expr_entry_set_flags (state
->change_cell_entry
,
564 GNM_EE_SINGLE_RANGE
|
565 GNM_EE_FORCE_ABS_REF
,
567 gtk_grid_attach (grid
, GTK_WIDGET (state
->change_cell_entry
),
569 gtk_widget_set_hexpand (GTK_WIDGET (state
->change_cell_entry
), TRUE
);
570 gnm_editable_enters (GTK_WINDOW (state
->dialog
),
571 GTK_WIDGET (state
->change_cell_entry
));
572 dialog_preload_selection (state
, state
->change_cell_entry
);
573 gtk_widget_show (GTK_WIDGET (state
->change_cell_entry
));
576 g_signal_connect (G_OBJECT (state
->dialog
),
578 G_CALLBACK (dialog_realized
), state
);
580 state
->old_value
= NULL
;
581 state
->old_cell
= NULL
;
583 wbc_gtk_attach_guru (state
->wbcg
, state
->dialog
);
584 g_object_set_data_full (G_OBJECT (state
->dialog
),
585 "state", state
, (GDestroyNotify
) cb_dialog_destroy
);
587 gnm_expr_entry_grab_focus (state
->set_cell_entry
, FALSE
);
593 * We need a horizontal strip of 5 cells containing:
602 dialog_goal_seek_test (Sheet
*sheet
, const GnmRange
*range
)
607 GoalSeekStatus status
;
609 g_return_if_fail (range
->start
.row
== range
->end
.row
);
610 g_return_if_fail (range
->start
.col
+ 4 == range
->end
.col
);
612 memset (&state
, 0, sizeof (state
));
613 r
= range
->start
.row
;
614 c
= range
->start
.col
;
616 state
.wb
= sheet
->workbook
;
619 state
.set_cell
= sheet_cell_fetch (sheet
, c
+ 0, r
);
620 state
.change_cell
= sheet_cell_fetch (sheet
, c
+ 1, r
);
621 state
.old_value
= value_dup (state
.change_cell
->value
);
623 cell
= sheet_cell_fetch (sheet
, c
+ 2, r
);
624 state
.target_value
= value_get_as_float (cell
->value
);
626 cell
= sheet_cell_fetch (sheet
, c
+ 3, r
);
627 state
.xmin
= VALUE_IS_EMPTY (cell
->value
)
629 : value_get_as_float (cell
->value
);
631 cell
= sheet_cell_fetch (sheet
, c
+ 4, r
);
632 state
.xmax
= VALUE_IS_EMPTY (cell
->value
)
634 : value_get_as_float (cell
->value
);
636 status
= gnumeric_goal_seek (&state
);
637 if (status
== GOAL_SEEK_OK
) {
640 sheet_cell_set_value (state
.change_cell
,
641 value_new_error_VALUE (NULL
));
644 value_release (state
.old_value
);
652 * Create the dialog (guru).
656 dialog_goal_seek (WBCGtk
*wbcg
, Sheet
*sheet
)
658 GoalSeekState
*state
;
661 g_return_if_fail (IS_SHEET (sheet
));
666 g_object_get_data (G_OBJECT (sheet
), "ssconvert-goal-seek");
668 Sheet
*start_sheet
, *end_sheet
;
672 gnm_rangeref_normalize (range
,
673 eval_pos_init_sheet (&ep
, sheet
),
674 &start_sheet
, &end_sheet
,
676 g_return_if_fail (start_sheet
== sheet
);
678 dialog_goal_seek_test (sheet
, &r
);
683 g_return_if_fail (wbcg
!= NULL
);
685 /* Only pop up one copy per workbook */
686 if (gnm_dialog_raise_if_exists (wbcg
, GOALSEEK_KEY
))
688 gui
= gnm_gtk_builder_load ("res:ui/goalseek.ui", NULL
, GO_CMD_CONTEXT (wbcg
));
692 state
= g_new (GoalSeekState
, 1);
694 state
->wb
= wb_control_get_workbook (GNM_WBC (wbcg
));
695 state
->sheet
= sheet
;
697 state
->warning_dialog
= NULL
;
698 state
->cancelled
= TRUE
;
700 if (dialog_init (state
)) {
701 go_gtk_notice_dialog (wbcg_toplevel (wbcg
), GTK_MESSAGE_ERROR
,
702 _("Could not create the Goal-Seek dialog."));
707 gnm_keyed_dialog (state
->wbcg
, GTK_WINDOW (state
->dialog
),
710 gtk_widget_show (state
->dialog
);