/**
* paste_cell:
* @target_col: Column to put the cell into
* @target_row: Row to put the cell into.
* @src: A #GnmCelCopy with the content to paste
* @paste_flags: Bit mask that describes the paste options.
*
* Pastes a cell in the spreadsheet.
*/
static void
paste_cell (int target_col, int target_row,
GnmCellCopy const *src,
const struct paste_cell_data *dat)
{
Sheet *dst_sheet = dat->pt->sheet;
int paste_flags = dat->pt->paste_flags;
if (paste_flags & PASTE_OPER_MASK)
paste_cell_with_operation (dst_sheet, target_col, target_row,
&dat->rinfo, src, paste_flags);
else {
GnmCell *dst = sheet_cell_fetch (dst_sheet, target_col, target_row);
if (NULL != src->texpr && (paste_flags & PASTE_CONTENTS)) {
GnmExprTop const *relo = gnm_expr_top_relocate (
src->texpr, &dat->rinfo, FALSE);
if (paste_flags & PASTE_TRANSPOSE) {
GnmExprTop const *trelo =
gnm_expr_top_transpose (relo ? relo : src->texpr);
if (trelo) {
if (relo)
gnm_expr_top_unref (relo);
relo = trelo;
}
} else if (!relo && gnm_expr_top_is_array_corner (src->texpr)) {
/* We must not share array expressions. */
relo = gnm_expr_top_new (gnm_expr_copy (src->texpr->expr));
}
gnm_cell_set_expr_and_value (dst, relo ? relo : src->texpr,
value_dup (src->val), TRUE);
if (NULL != relo)
gnm_expr_top_unref (relo);
} else {
GnmValue *newval = NULL;
GnmValue const *oldval = src->val;
if (dat->translate_dates && oldval && VALUE_IS_FLOAT (oldval)) {
GOFormat const *fmt = VALUE_FMT (oldval)
? VALUE_FMT (oldval)
: gnm_style_get_format (gnm_cell_get_style (dst));
if (go_format_is_date (fmt) > 0) {
gnm_float fnew = go_date_conv_translate
(value_get_as_float (oldval),
dat->cr->date_conv,
workbook_date_conv (dst_sheet->workbook));
newval = value_new_float (fnew);
value_set_fmt (newval, VALUE_FMT (oldval));
}
}
if (!newval)
newval = value_dup (src->val);
gnm_cell_set_value (dst, newval);
}
}
}
static void
filter (data_analysis_output_t *dao, Sheet *sheet, GSList *rows,
gint input_col_b, gint input_col_e, gint input_row_b, gint input_row_e)
{
GnmCell *cell;
int i, r=0;
if (dao->type == InPlaceOutput) {
sheet->has_filtered_rows = TRUE;
colrow_set_visibility (sheet, FALSE,
FALSE, input_row_b+1, input_row_e);
for (i=input_row_b; i<=input_row_e; i++) {
ColRowInfo *ri = sheet_row_fetch (sheet, i);
ri->in_advanced_filter = TRUE;
}
while (rows != NULL) {
const gint *row = rows->data;
colrow_set_visibility (sheet, FALSE, TRUE, *row, *row);
rows = rows->next;
}
sheet_redraw_all (sheet, TRUE);
/* FIXME: what happens if we just have hidden the selection? */
} else {
for (i=input_col_b; i<=input_col_e; i++) {
cell = sheet_cell_get (sheet, i, input_row_b);
if (cell == NULL)
dao_set_cell (dao, i - input_col_b, r, NULL);
else {
GnmValue *value = value_dup (cell->value);
dao_set_cell_value (dao, i - input_col_b, r,
value);
}
}
++r;
while (rows != NULL) {
const gint *row = rows->data;
for (i=input_col_b; i<=input_col_e; i++) {
cell = sheet_cell_get (sheet, i, *row);
if (cell == NULL)
dao_set_cell (dao, i - input_col_b, r,
NULL);
else {
GnmValue *value =
value_dup (cell->value);
dao_set_cell_value (dao,
i - input_col_b, r,
value);
}
}
++r;
rows = rows->next;
}
}
}
static GnmValue *
summary_cb (int col, int row, GnmValue *v, summary_cb_t *p)
{
char *tmp = dao_find_name (p->sheet, col, row);
int *index;
/* Check if some of the previous scenarios already included that
* cell. If so, it's row will be put into *index. */
index = g_hash_table_lookup (p->names, tmp);
if (index != NULL) {
dao_set_cell_value (&p->dao, 2 + p->col, 3 + *index,
value_dup (v));
/* Set the colors. */
dao_set_colors (&p->dao, 2 + p->col, 3 + *index,
2 + p->col, 3 + *index,
gnm_color_new_go (GO_COLOR_BLACK),
gnm_color_new_go (LIGHT_GRAY));
} else {
/* New cell. */
GnmCell *cell;
int *r;
/* Changing cell name. */
dao_set_cell (&p->dao, 0, 3 + p->row, tmp);
/* GnmValue of the cell in this scenario. */
dao_set_cell_value (&p->dao, 2 + p->col, 3 + p->row,
value_dup (v));
/* Current value of the cell. */
cell = sheet_cell_fetch (p->sheet, col, row);
dao_set_cell_value (&p->dao, 1, 3 + p->row,
value_dup (cell->value));
/* Set the colors. */
dao_set_colors (&p->dao, 2 + p->col, 3 + p->row,
2 + p->col, 3 + p->row,
gnm_color_new_go (GO_COLOR_BLACK),
gnm_color_new_go (LIGHT_GRAY));
/* Insert row number into the hash table. */
r = g_new (int, 1);
*r = p->row;
g_hash_table_insert (p->names, tmp, r);
/* Increment the nbr of rows. */
p->row++;
}
return v;
}
static
GnmExpr const *analysis_tool_combine_area (GnmValue *val_1, GnmValue *val_2, Workbook *wb)
{
GnmFunc *fd_array;
GnmExpr const *expr;
if (val_1->type == VALUE_CELLRANGE && val_2->type == VALUE_CELLRANGE &&
val_1->v_range.cell.a.sheet == val_2->v_range.cell.a.sheet) {
GnmRange r_1, r_2;
gboolean combined = FALSE;
range_init_rangeref (&r_1, &val_1->v_range.cell);
range_init_rangeref (&r_2, &val_2->v_range.cell);
if (r_1.start.row == r_2.start.row &&
range_height (&r_1) == range_height (&r_2)) {
if (r_1.end.col == r_2.start.col - 1) {
combined = TRUE;
r_1.end.col = r_2.end.col;
} else if (r_2.end.col == r_1.start.col - 1) {
combined = TRUE;
r_1.start.col = r_2.start.col;
}
} else if (r_1.start.col == r_2.start.col &&
range_width (&r_1) == range_width (&r_2)) {
if (r_1.end.row == r_2.start.row - 1) {
combined = TRUE;
r_1.end.row = r_2.end.row;
} else if (r_2.end.row == r_1.start.row - 1) {
combined = TRUE;
r_1.start.row = r_2.start.row;
}
}
if (combined) {
GnmValue *val = value_new_cellrange_r (val_1->v_range.cell.a.sheet, &r_1);
return gnm_expr_new_constant (val);
}
}
fd_array = gnm_func_lookup_or_add_placeholder ("ARRAY");
gnm_func_ref (fd_array);
expr = gnm_expr_new_funcall2 (fd_array,
gnm_expr_new_constant (value_dup (val_1)),
gnm_expr_new_constant (value_dup (val_2)));
gnm_func_unref (fd_array);
return expr;
}
static void
paste_cell_with_operation (Sheet *dst_sheet,
int target_col, int target_row,
GnmExprRelocateInfo const *rinfo,
GnmCellCopy const *src,
int paste_flags)
{
GnmCell *dst;
GnmExprOp op;
if (src->texpr == NULL &&
!VALUE_IS_EMPTY (src->val) &&
!VALUE_IS_NUMBER (src->val))
return;
dst = sheet_cell_fetch (dst_sheet, target_col, target_row);
if (!cell_has_expr_or_number_or_blank (dst))
return;
op = paste_op_to_expr_op (paste_flags);
/* FIXME : This does not handle arrays, linked cells, ranges, etc. */
if ((paste_flags & PASTE_CONTENTS) &&
(NULL != src->texpr || gnm_cell_has_expr (dst))) {
GnmExpr const *old_expr = contents_as_expr (dst->base.texpr, dst->value);
GnmExpr const *copied_expr = contents_as_expr (src->texpr, src->val);
GnmExprTop const *res = gnm_expr_top_new (gnm_expr_new_binary (old_expr, op, copied_expr));
GnmExprTop const *relo = gnm_expr_top_relocate (res, rinfo, FALSE);
if (relo) {
gnm_cell_set_expr (dst, relo);
gnm_expr_top_unref (relo);
} else
gnm_cell_set_expr (dst, res);
gnm_expr_top_unref (res);
} else {
GnmValue *value;
GnmEvalPos pos;
GnmExpr const *expr = gnm_expr_new_binary (
gnm_expr_new_constant (value_dup (dst->value)),
op,
gnm_expr_new_constant (value_dup (src->val)));
GnmExprTop const *texpr = gnm_expr_top_new (expr);
eval_pos_init_cell (&pos, dst);
pos.dep = NULL; /* no dynamic deps */
value = gnm_expr_top_eval (texpr, &pos,
GNM_EXPR_EVAL_SCALAR_NON_EMPTY);
gnm_expr_top_unref (texpr);
gnm_cell_set_value (dst, value);
}
}
static void
constraint_select_click (GtkTreeSelection *Selection,
SolverState * state)
{
GtkTreeModel *store;
GtkTreeIter iter;
GnmSolverConstraint const *c;
GnmValue const *lhs, *rhs;
if (gtk_tree_selection_get_selected (Selection, &store, &iter))
gtk_tree_model_get (store, &iter, 1, &state->constr, -1);
else
state->constr = NULL;
dialog_set_sec_button_sensitivity (NULL, state);
if (state->constr == NULL)
return; /* Fail? */
c = state->constr;
lhs = gnm_solver_constraint_get_lhs (c);
if (lhs) {
GnmExprTop const *texpr =
gnm_expr_top_new_constant (value_dup (lhs));
GnmParsePos pp;
gnm_expr_entry_load_from_expr
(state->lhs.entry,
texpr,
parse_pos_init_sheet (&pp, state->sheet));
gnm_expr_top_unref (texpr);
} else
gnm_expr_entry_load_from_text (state->lhs.entry, "");
rhs = gnm_solver_constraint_get_rhs (c);
if (rhs && gnm_solver_constraint_has_rhs (c)) {
GnmExprTop const *texpr =
gnm_expr_top_new_constant (value_dup (rhs));
GnmParsePos pp;
gnm_expr_entry_load_from_expr
(state->rhs.entry,
texpr,
parse_pos_init_sheet (&pp, state->sheet));
gnm_expr_top_unref (texpr);
} else
gnm_expr_entry_load_from_text (state->rhs.entry, "");
gtk_combo_box_set_active (state->type_combo, c->type);
}
/**
* gnm_scenario_apply:
* @sc: #GnmScenario
*
* Returns: (transfer full): the newly allocated #GOUndo.
**/
GOUndo *
gnm_scenario_apply (GnmScenario *sc)
{
GOUndo *undo = NULL;
GSList *l;
g_return_val_if_fail (GNM_IS_SCENARIO (sc), NULL);
for (l = sc->items; l; l = l->next) {
GnmScenarioItem *sci = l->data;
GnmValue const *val = sci->value;
GnmSheetRange sr;
Sheet *sheet;
if (!gnm_scenario_item_valid (sci, &sr))
continue;
sheet = eval_sheet (sr.sheet, sc->sheet);
if (val) {
/* FIXME: think about arrays. */
GnmCell *cell = sheet_cell_fetch
(sheet,
sr.range.start.col,
sr.range.start.row);
sheet_cell_set_value (cell, value_dup (val));
} else {
GOUndo *u = clipboard_copy_range_undo (sheet,
&sr.range);
/* FIXME: Clear the range. */
undo = go_undo_combine (undo, u);
}
}
return undo;
}
static gboolean
tool_random_engine_run_discrete (data_analysis_output_t *dao,
tools_data_random_t *info,
G_GNUC_UNUSED discrete_random_tool_t *param,
discrete_random_tool_local_t **continuity)
{
gint i;
discrete_random_tool_local_t *data = *continuity;
for (i = 0; i < info->n_vars; i++) {
int k;
for (k = 0; k < info->count; k++) {
int j;
gnm_float x = random_01 ();
for (j = 0; data->cumul_p[j] < x; j++)
;
dao_set_cell_value (dao, i, k,
value_dup (data->values[j]));
}
}
tool_random_engine_run_discrete_clear_continuity (continuity);
return FALSE;
}
static GnmValue *
cb_clipboard_prepend_cell (GnmCellIter const *iter, GnmCellRegion *cr)
{
GnmRange a;
GnmCellCopy *copy = gnm_cell_copy_new (cr,
iter->pp.eval.col - cr->base.col,
iter->pp.eval.row - cr->base.row);
copy->val = value_dup (iter->cell->value);
if (gnm_cell_has_expr (iter->cell)) {
gnm_expr_top_ref (copy->texpr = iter->cell->base.texpr);
/* Check for array division */
if (!cr->not_as_contents &&
gnm_cell_array_bound (iter->cell, &a) &&
(a.start.col < cr->base.col ||
a.start.row < cr->base.row ||
a.end.col >= (cr->base.col + cr->cols) ||
a.end.row >= (cr->base.row + cr->rows)))
cr->not_as_contents = TRUE;
} else
copy->texpr = NULL;
return NULL;
}
/**
* parse_criteria:
* @crit_val: #GnmValue
* @date_conv: #GODateConventions
*
* Returns: (transfer full): GnmCriteria which caller must free.
*
* ">=value"
* "<=value"
* "<>value"
* "<value"
* ">value"
* "=value"
* "pattern"
**/
GnmCriteria *
parse_criteria (GnmValue const *crit_val, GODateConventions const *date_conv,
gboolean anchor_end)
{
int len;
char const *criteria;
GnmCriteria *res = g_new0 (GnmCriteria, 1);
GnmValue *empty;
res->iter_flags = CELL_ITER_IGNORE_BLANK;
res->date_conv = date_conv;
if (VALUE_IS_NUMBER (crit_val)) {
res->fun = criteria_test_equal;
res->x = value_dup (crit_val);
return res;
}
criteria = value_peek_string (crit_val);
if (strncmp (criteria, "<=", 2) == 0) {
res->fun = criteria_test_less_or_equal;
len = 2;
} else if (strncmp (criteria, ">=", 2) == 0) {
res->fun = criteria_test_greater_or_equal;
len = 2;
} else if (strncmp (criteria, "<>", 2) == 0) {
/* "<>" by itself is special: */
res->fun = (criteria[2] == 0) ? criteria_test_nonempty : criteria_test_unequal;
len = 2;
} else if (*criteria == '<') {
res->fun = criteria_test_less;
len = 1;
} else if (*criteria == '=') {
/* "=" by itself is special: */
res->fun = (criteria[1] == 0) ? criteria_test_empty : criteria_test_equal;
len = 1;
} else if (*criteria == '>') {
res->fun = criteria_test_greater;
len = 1;
} else {
res->fun = criteria_test_match;
res->has_rx = (gnm_regcomp_XL (&res->rx, criteria, GO_REG_ICASE, TRUE, anchor_end) == GO_REG_OK);
len = 0;
}
res->x = format_match_number (criteria + len, NULL, date_conv);
if (res->x == NULL)
res->x = value_new_string (criteria + len);
else if (len == 0 && VALUE_IS_NUMBER (res->x))
res->fun = criteria_test_equal;
empty = value_new_empty ();
if (res->fun (empty, res))
res->iter_flags &= ~CELL_ITER_IGNORE_BLANK;
value_release (empty);
res->ref_count = 1;
return res;
}
static GnmScenarioItem *
gnm_scenario_item_dup (GnmScenarioItem *src)
{
GnmScenarioItem *dst = gnm_scenario_item_new (src->dep.sheet);
dependent_managed_set_expr (&dst->dep, src->dep.texpr);
dst->value = value_dup (src->value);
return dst;
}
static GnmValue *
cb_collect_unique (GnmValueIter const *iter, UniqueCollection *uc)
{
GOFormat const *fmt = (NULL != iter->cell_iter)
? gnm_cell_get_format (iter->cell_iter->cell) : NULL;
g_hash_table_replace (uc->hash,
value_dup (iter->v),
format_value (fmt, iter->v, -1, uc->date_conv));
return NULL;
}
static GnmExpr const *
contents_as_expr (GnmExprTop const *texpr, GnmValue const *val)
{
if (texpr)
return gnm_expr_copy (texpr->expr);
if (VALUE_IS_EMPTY (val))
return gnm_expr_new_constant (value_new_float (0.0));
if (VALUE_IS_NUMBER (val))
return gnm_expr_new_constant (value_dup (val));
return NULL;
}
static GnmValue *
callback_function_simtable (GnmEvalPos const *ep, GnmValue const *value, void *closure)
{
simtable_t *p = closure;
if (p->index == ep->sheet->simulation_round)
p->value = value_dup (value);
++(p->index);
return NULL;
}
/*
* We need a horizontal strip of 5 cells containing:
*
* 0. Formula cell.
* 1: X value cell.
* 2: Y target value.
* 3: Min value.
* 4: Max value.
*/
static void
dialog_goal_seek_test (Sheet *sheet, const GnmRange *range)
{
GoalSeekState state;
GnmCell *cell;
int r, c;
GoalSeekStatus status;
g_return_if_fail (range->start.row == range->end.row);
g_return_if_fail (range->start.col + 4 == range->end.col);
memset (&state, 0, sizeof (state));
r = range->start.row;
c = range->start.col;
state.wb = sheet->workbook;
state.sheet = sheet;
state.set_cell = sheet_cell_fetch (sheet, c + 0, r);
state.change_cell = sheet_cell_fetch (sheet, c + 1, r);
state.old_value = value_dup (state.change_cell->value);
cell = sheet_cell_fetch (sheet, c + 2, r);
state.target_value = value_get_as_float (cell->value);
cell = sheet_cell_fetch (sheet, c + 3, r);
state.xmin = VALUE_IS_EMPTY (cell->value)
? -max_range_val
: value_get_as_float (cell->value);
cell = sheet_cell_fetch (sheet, c + 4, r);
state.xmax = VALUE_IS_EMPTY (cell->value)
? max_range_val
: value_get_as_float (cell->value);
status = gnumeric_goal_seek (&state);
if (status == GOAL_SEEK_OK) {
/* Nothing */
} else {
sheet_cell_set_value (state.change_cell,
value_new_error_VALUE (NULL));
}
value_release (state.old_value);
}
static GnmValue *
cb_collect_content (GnmCellIter const *iter, UniqueCollection *uc)
{
GnmCell const *cell = (iter->pp.sheet == uc->src_sheet) ? iter->cell
: sheet_cell_get (uc->src_sheet,
iter->pp.eval.col, iter->pp.eval.row);
if (gnm_cell_is_blank (cell))
uc->has_blank = TRUE;
else {
GOFormat const *fmt = gnm_cell_get_format (cell);
GnmValue const *v = cell->value;
g_hash_table_replace (uc->hash,
value_dup (v),
format_value (fmt, v, -1, uc->date_conv));
}
return NULL;
}
static GnmCell *
pg_fetch_cell (GnmPreviewGrid *pg, int col, int row)
{
GnmPreviewGridClass *klass = GNM_PREVIEW_GRID_GET_CLASS (pg);
GnmCell *cell;
GnmValue *v = NULL;
g_return_val_if_fail (klass != NULL, NULL);
g_return_val_if_fail (pg != NULL, NULL);
g_return_val_if_fail (col >= 0 && col < gnm_sheet_get_max_cols (pg->sheet), NULL);
g_return_val_if_fail (row >= 0 && row < gnm_sheet_get_max_rows (pg->sheet), NULL);
if (NULL != klass->get_cell_value)
v = (klass->get_cell_value) (pg, col, row);
if (NULL == v)
v = value_dup (pg->defaults.value);
cell = sheet_cell_fetch (pg->sheet, col, row);
gnm_cell_set_value (cell, v);
gnm_cell_render_value (cell, TRUE);
return cell;
}
static gboolean
analysis_tool_wilcoxon_mann_whitney_engine_run (data_analysis_output_t *dao,
analysis_tools_data_generic_b_t *info)
{
GnmFunc *fd_count;
GnmFunc *fd_sum;
GnmFunc *fd_rows;
GnmFunc *fd_rank_avg;
GnmFunc *fd_rank;
GnmFunc *fd_min;
GnmFunc *fd_normdist;
GnmFunc *fd_sqrt;
GnmFunc *fd_if;
GnmFunc *fd_isblank;
GnmExpr const *expr_total;
GnmExpr const *expr_pop_1;
GnmExpr const *expr_pop_2;
GnmExpr const *expr_u;
GnmExpr const *expr_count_total;
GnmValue *val_1 = value_dup (info->range_1);
GnmValue *val_2 = value_dup (info->range_2);
Workbook *wb = dao->sheet ? dao->sheet->workbook : NULL;
fd_count = gnm_func_lookup_or_add_placeholder ("COUNT");
gnm_func_ref (fd_count);
fd_sum = gnm_func_lookup_or_add_placeholder ("SUM");
gnm_func_ref (fd_sum);
fd_rows = gnm_func_lookup_or_add_placeholder ("ROWS");
gnm_func_ref (fd_rows);
fd_rank_avg = gnm_func_lookup_or_add_placeholder ("RANK.AVG");
gnm_func_ref (fd_rank_avg);
fd_rank = gnm_func_lookup_or_add_placeholder ("RANK");
gnm_func_ref (fd_rank);
fd_min = gnm_func_lookup_or_add_placeholder ("MIN");
gnm_func_ref (fd_min);
fd_normdist = gnm_func_lookup_or_add_placeholder ("NORMDIST");
gnm_func_ref (fd_normdist);
fd_sqrt = gnm_func_lookup_or_add_placeholder ("SQRT");
gnm_func_ref (fd_sqrt);
fd_if = gnm_func_lookup_or_add_placeholder ("IF");
gnm_func_ref (fd_if);
fd_isblank = gnm_func_lookup_or_add_placeholder ("ISBLANK");
gnm_func_ref (fd_isblank);
dao_set_italic (dao, 0, 0, 0, 8);
dao_set_italic (dao, 0, 1, 3, 1);
dao_set_merge (dao, 0, 0, 3, 0);
dao_set_cell (dao, 0, 0, _("Wilcoxon-Mann-Whitney Test"));
set_cell_text_col (dao, 0, 2, _("/Rank-Sum"
"/N"
"/U"
"/Ties"
"/Statistic"
"/U-Statistic"
"/p-Value"));
dao_set_cell (dao, 3, 1, _("Total"));
/* Label */
analysis_tools_write_label_ftest (val_1, dao, 1, 1, info->labels, 1);
analysis_tools_write_label_ftest (val_2, dao, 2, 1, info->labels, 2);
expr_total = analysis_tool_combine_area (val_1, val_2, wb);
expr_pop_1 = gnm_expr_new_constant (val_1);
expr_pop_2 = gnm_expr_new_constant (val_2);
/* =sum(if(isblank(region1),0,rank.avg(region1,combined_regions,1))) */
dao_set_cell_array_expr (dao, 1, 2,
gnm_expr_new_funcall1
(fd_sum,
gnm_expr_new_funcall3
(fd_if,
gnm_expr_new_funcall1
(fd_isblank,
gnm_expr_copy (expr_pop_1)),
gnm_expr_new_constant (value_new_int (0)),
gnm_expr_new_funcall3
(fd_rank_avg,
gnm_expr_copy (expr_pop_1),
gnm_expr_copy (expr_total),
gnm_expr_new_constant (value_new_int (1))))));
dao_set_cell_array_expr (dao, 2, 2,
gnm_expr_new_funcall1
(fd_sum,
gnm_expr_new_funcall3
(fd_if,
gnm_expr_new_funcall1
(fd_isblank,
gnm_expr_copy (expr_pop_2)),
gnm_expr_new_constant (value_new_int (0)),
gnm_expr_new_funcall3
(fd_rank_avg,
gnm_expr_copy (expr_pop_2),
gnm_expr_copy (expr_total),
gnm_expr_new_constant (value_new_int (1))))));
expr_count_total = gnm_expr_new_funcall1
(fd_count, gnm_expr_copy (expr_total));
dao_set_cell_expr (dao, 3, 2,
gnm_expr_new_binary
(gnm_expr_new_binary
(gnm_expr_copy (expr_count_total),
GNM_EXPR_OP_MULT,
gnm_expr_new_binary
(gnm_expr_copy (expr_count_total),
GNM_EXPR_OP_ADD,
gnm_expr_new_constant (value_new_int (1)))),
GNM_EXPR_OP_DIV,
gnm_expr_new_constant (value_new_int (2))));
dao_set_cell_expr (dao, 1, 3,
gnm_expr_new_funcall1
(fd_count,
expr_pop_1));
dao_set_cell_expr (dao, 2, 3,
gnm_expr_new_funcall1
(fd_count,
expr_pop_2));
dao_set_cell_expr (dao, 3, 3,
gnm_expr_new_funcall1
(fd_count,
gnm_expr_copy (expr_total)));
expr_u = gnm_expr_new_binary
(make_cellref (0,- 2), GNM_EXPR_OP_SUB,
gnm_expr_new_binary
(gnm_expr_new_binary
(make_cellref (0,- 1),
GNM_EXPR_OP_MULT,
gnm_expr_new_binary
(make_cellref (0,- 1),
GNM_EXPR_OP_ADD,
gnm_expr_new_constant (value_new_int (1)))),
GNM_EXPR_OP_DIV,
gnm_expr_new_constant (value_new_int (2))));
dao_set_cell_expr (dao, 1, 4, gnm_expr_copy (expr_u));
dao_set_cell_expr (dao, 2, 4, expr_u);
dao_set_cell_expr (dao, 3, 4,
gnm_expr_new_binary
(make_cellref (-2,-1),
GNM_EXPR_OP_MULT,
make_cellref (-1,-1)));
dao_set_cell_array_expr (dao, 1, 5,
gnm_expr_new_funcall1
(fd_sum,
gnm_expr_new_binary
(gnm_expr_new_funcall2
(fd_rank_avg,
gnm_expr_copy (expr_total),
gnm_expr_copy (expr_total)),
GNM_EXPR_OP_SUB,
gnm_expr_new_funcall2
(fd_rank,
gnm_expr_copy (expr_total),
gnm_expr_copy (expr_total)))));
if (dao_cell_is_visible (dao, 2, 4)) {
GnmExpr const *expr_prod;
GnmExpr const *expr_sqrt;
GnmExpr const *expr_normdist;
expr_prod = gnm_expr_new_binary
(make_cellref (0,-5),
GNM_EXPR_OP_MULT,
make_cellref (1,-5));
expr_sqrt = gnm_expr_new_funcall1
(fd_sqrt,
gnm_expr_new_binary
(gnm_expr_new_binary
(gnm_expr_copy(expr_prod),
GNM_EXPR_OP_MULT,
gnm_expr_new_binary
(gnm_expr_new_binary
(make_cellref (0,-5),
GNM_EXPR_OP_ADD,
make_cellref (1,-5)),
GNM_EXPR_OP_ADD,
gnm_expr_new_constant (value_new_int (1)))),
GNM_EXPR_OP_DIV,
gnm_expr_new_constant (value_new_int (12))));
expr_normdist = gnm_expr_new_funcall4
(fd_normdist,
make_cellref (0,-1),
gnm_expr_new_binary
(expr_prod,
GNM_EXPR_OP_DIV,
gnm_expr_new_constant (value_new_int (2))),
expr_sqrt,
gnm_expr_new_constant (value_new_bool (TRUE)));
dao_set_cell_expr (dao, 1, 6,
gnm_expr_new_funcall2
(fd_min,
make_cellref (0,-4),
make_cellref (1,-4)));
dao_set_cell_expr (dao, 1, 7,
gnm_expr_new_funcall2
(fd_min,
make_cellref (0,-3),
make_cellref (1,-3)));
dao_set_cell_expr (dao, 1, 8,
gnm_expr_new_binary
(gnm_expr_new_constant (value_new_int (2)),
GNM_EXPR_OP_MULT,
expr_normdist));
dao_set_cell_comment (dao, 1, 8,
_("This p-value is calculated using a\n"
"normal approximation, so it is\n"
"only valid for large samples of\n"
"at least 15 observations in each\n"
"population, and few if any ties."));
} else {
dao_set_cell_na (dao, 1, 6);
dao_set_cell_comment (dao, 1, 6,
_("Since there is insufficient space\n"
"for the third column of output,\n"
"this value is not calculated."));
dao_set_cell_na (dao, 1, 7);
dao_set_cell_comment (dao, 1, 7,
_("Since there is insufficient space\n"
"for the third column of output,\n"
"this value is not calculated."));
dao_set_cell_na (dao, 1, 8);
dao_set_cell_comment (dao, 1, 8,
_("Since there is insufficient space\n"
"for the third column of output,\n"
"this value is not calculated."));
}
gnm_expr_free (expr_count_total);
gnm_expr_free (expr_total);
gnm_func_unref (fd_count);
gnm_func_unref (fd_sum);
gnm_func_unref (fd_rows);
gnm_func_unref (fd_rank_avg);
gnm_func_unref (fd_rank);
gnm_func_unref (fd_min);
gnm_func_unref (fd_normdist);
gnm_func_unref (fd_sqrt);
gnm_func_unref (fd_if);
gnm_func_unref (fd_isblank);
dao_redraw_respan (dao);
return 0;
}
GnmValue *
gnm_ifs_func (GPtrArray *data, GPtrArray *crits, GnmValue const *vals,
float_range_function_t fun, GnmStdError err,
GnmEvalPos const *ep, CollectFlags flags)
{
int sx, sy, x, y;
unsigned ui, N = 0, nalloc = 0;
gnm_float *xs = NULL;
GnmValue *res = NULL;
gnm_float fres;
g_return_val_if_fail (data->len == crits->len, NULL);
if (flags & ~(COLLECT_IGNORE_STRINGS |
COLLECT_IGNORE_BOOLS |
COLLECT_IGNORE_BLANKS |
COLLECT_IGNORE_ERRORS)) {
g_warning ("unsupported flags in gnm_ifs_func %x", flags);
}
sx = value_area_get_width (vals, ep);
sy = value_area_get_height (vals, ep);
for (ui = 0; ui < data->len; ui++) {
GnmValue const *datai = g_ptr_array_index (data, ui);
if (value_area_get_width (datai, ep) != sx ||
value_area_get_height (datai, ep) != sy)
return value_new_error_VALUE (ep);
}
for (y = 0; y < sy; y++) {
for (x = 0; x < sx; x++) {
GnmValue const *v;
gboolean match = TRUE;
for (ui = 0; match && ui < crits->len; ui++) {
GnmCriteria *crit = g_ptr_array_index (crits, ui);
GnmValue const *datai = g_ptr_array_index (data, ui);
v = value_area_get_x_y (datai, x, y, ep);
match = crit->fun (v, crit);
}
if (!match)
continue;
// Match. Maybe collect the data point.
v = value_area_get_x_y (vals, x, y, ep);
if ((flags & COLLECT_IGNORE_STRINGS) && VALUE_IS_STRING (v))
continue;
if ((flags & COLLECT_IGNORE_BOOLS) && VALUE_IS_BOOLEAN (v))
continue;
if ((flags & COLLECT_IGNORE_BLANKS) && VALUE_IS_EMPTY (v))
continue;
if ((flags & COLLECT_IGNORE_ERRORS) && VALUE_IS_ERROR (v))
continue;
if (VALUE_IS_ERROR (v)) {
res = value_dup (v);
goto out;
}
if (N >= nalloc) {
nalloc = (2 * nalloc) + 100;
xs = g_renew (gnm_float, xs, nalloc);
}
xs[N++] = value_get_as_float (v);
}
}
if (fun (xs, N, &fres)) {
res = value_new_error_std (ep, err);
} else
res = value_new_float (fres);
out:
g_free (xs);
return res;
}
/*
* collect_floats:
*
* exprlist: List of expressions to evaluate.
* cr: Current location (for resolving relative cells).
* flags: COLLECT_IGNORE_STRINGS: silently ignore strings.
* COLLECT_COERCE_STRINGS: coerce string into numbers
* COLLECT_ZERO_STRINGS: count strings as 0.
* (Alternative: return #VALUE!.)
* COLLECT_IGNORE_BOOLS: silently ignore bools.
* COLLECT_ZEROONE_BOOLS: count FALSE as 0, TRUE as 1.
* (Alternative: return #VALUE!.)
* COLLECT_IGNORE_SUBTOTAL : ignore expressions that include
* the function SUBTOTAL directly and ignore any content
* in filtered rows.
* n: Output parameter for number of floats.
*
* Return value:
* NULL in case of strict and a blank.
* A copy of the error in the case of strict and an error.
* Non-NULL in case of success. Then n will be set.
*
* Evaluate a list of expressions and return the result as an array of
* gnm_float.
*/
static gnm_float *
collect_floats (int argc, GnmExprConstPtr const *argv,
GnmEvalPos const *ep, CollectFlags flags,
int *n, GnmValue **error, GSList **info,
gboolean *constp)
{
collect_floats_t cl;
CellIterFlags iter_flags = CELL_ITER_ALL;
GnmValue *key = NULL;
CollectFlags keyflags = flags & ~COLLECT_ORDER_IRRELEVANT;
gboolean strict;
if (constp)
*constp = FALSE;
if (info) {
*info = NULL;
g_return_val_if_fail (!(flags & COLLECT_SORT), NULL);
flags |= COLLECT_INFO;
} else {
if (flags & COLLECT_IGNORE_BLANKS)
iter_flags = CELL_ITER_IGNORE_BLANK;
flags &= ~COLLECT_INFO;
}
/* ---------------------------------------- */
/* Try cache. */
if (argc == 1 &&
(flags & (COLLECT_INFO | COLLECT_IGNORE_SUBTOTAL)) == 0) {
key = get_single_cache_key (argv[0], ep);
}
if (key) {
SingleFloatsCacheEntry *ce =
get_or_fake_cache_entry (key, keyflags, ep);
if (ce) {
value_release (key);
if (ce->error) {
*error = value_dup (ce->error);
return NULL;
}
*n = ce->n;
if (constp) {
*constp = TRUE;
return ce->data;
}
return g_memdup (ce->data, *n * sizeof (gnm_float));
}
}
/* ---------------------------------------- */
if (flags & COLLECT_IGNORE_SUBTOTAL)
iter_flags |= (CELL_ITER_IGNORE_SUBTOTAL |
CELL_ITER_IGNORE_FILTERED);
strict = (flags & (COLLECT_IGNORE_ERRORS | COLLECT_ZERO_ERRORS)) == 0;
cl.alloc_count = 0;
cl.data = NULL;
cl.count = 0;
cl.flags = flags;
cl.info = NULL;
cl.date_conv = workbook_date_conv (ep->sheet->workbook);
*error = function_iterate_argument_values
(ep, &callback_function_collect, &cl,
argc, argv,
strict, iter_flags);
if (*error) {
g_assert (VALUE_IS_ERROR (*error));
g_free (cl.data);
cl.data = NULL;
cl.count = 0;
g_slist_free (cl.info);
cl.info = NULL;
} else {
if (cl.data == NULL) {
cl.alloc_count = 1;
cl.data = g_new (gnm_float, cl.alloc_count);
}
if (flags & COLLECT_SORT) {
qsort (cl.data, cl.count, sizeof (cl.data[0]),
float_compare);
}
}
if (info)
*info = cl.info;
*n = cl.count;
if (key) {
SingleFloatsCacheEntry *ce = g_new (SingleFloatsCacheEntry, 1);
SingleFloatsCacheEntry *ce2;
ce->value = key;
ce->flags = keyflags;
ce->n = *n;
ce->error = value_dup (*error);
if (cl.data == NULL)
ce->data = NULL;
else if (constp) {
*constp = TRUE;
ce->data = cl.data;
} else
ce->data = g_memdup (cl.data, MAX (1, *n) * sizeof (gnm_float));
prune_caches ();
/*
* We looked for the entry earlier and it was not there.
* However, sub-calculation might have added it so be careful
* to adjust sizes and replace the not-so-old entry.
* See bug 627079.
*/
ce2 = g_hash_table_lookup (single_floats_cache, ce);
if (ce2)
total_cache_size -= 1 + ce2->n;
g_hash_table_replace (single_floats_cache, ce, ce);
total_cache_size += 1 + *n;
}
return cl.data;
}
/**
* collect_float_pairs:
* @v0: value describing first data range
* @v1: value describing second data range
* @ep: evaluation position
* @flags: flags describing how to handle value types
* @xs0: return location for first data vector
* @xs1: return location for second data vector
* @n: return location for number of data points
* @constp: optional return location for an indicator of the return vectors
* being owned by this function as opposed to the normal copy owned by the
* caller.
*
* If @n is not positive upon return, no data has been allocated.
* If @n is negative upon return, the two ranges had different
* sizes.
*/
GnmValue *
collect_float_pairs (GnmValue const *vx, GnmValue const *vy,
GnmEvalPos const *ep, CollectFlags flags,
gnm_float **xs0, gnm_float **xs1, int *n,
gboolean *constp)
{
GnmValue *key_x = NULL;
GnmValue *key_y = NULL;
PairsFloatsCacheEntry *ce = NULL;
gboolean use_cache, free_keys = TRUE;
if (vx->type == VALUE_CELLRANGE)
key_x = get_single_cache_key_from_value (vx, ep);
if (vy->type == VALUE_CELLRANGE)
key_y = get_single_cache_key_from_value (vy, ep);
if ((use_cache = (key_x && key_y)))
ce = get_or_fake_pairs_cache_entry (key_x, key_y, flags, ep);
if (!ce) {
ce = collect_float_pairs_ce (vx, vy, ep, flags);
if (use_cache) {
PairsFloatsCacheEntry *ce2;
ce->vx = key_x;
ce->vy = key_y;
free_keys = FALSE;
/*
* We looked for the entry earlier and it was not there.
* However, sub-calculation might have added it so be careful
* to adjust sizes and replace the not-so-old entry.
* See bug 627079.
*/
ce2 = g_hash_table_lookup (pairs_floats_cache, ce);
if (ce2)
total_cache_size -= 1 + ce2->n;
g_hash_table_replace (pairs_floats_cache, ce, ce);
total_cache_size += 1 + ce->n;
}
}
if (free_keys) {
value_release (key_x);
value_release (key_y);
}
if (ce == NULL)
return value_new_error_VALUE (ep);
else {
if (ce->error) {
if (use_cache)
return value_dup (ce->error);
else {
GnmValue *ret = ce->error;
ce->error = NULL;
pairs_floats_cache_entry_free (ce);
return ret;
}
}
*n = ce->n;
if (ce->n <= 0) {
if (!use_cache)
pairs_floats_cache_entry_free (ce);
*xs0 = NULL;
*xs1 = NULL;
if (constp)
*constp = FALSE;
return NULL;
}
if (use_cache) {
if (constp) {
*xs0 = ce->data_x;
*xs1 = ce->data_y;
*constp = TRUE;
} else {
*xs0 = g_memdup (ce->data_x, *n * sizeof (gnm_float));
*xs1 = g_memdup (ce->data_y, *n * sizeof (gnm_float));
}
} else {
if (constp)
*constp = FALSE;
*xs0 = ce->data_x;
*xs1 = ce->data_y;
ce->data_x = NULL;
ce->data_y = NULL;
pairs_floats_cache_entry_free (ce);
}
return NULL;
}
}
static gboolean
analysis_tool_frequency_engine_run (data_analysis_output_t *dao,
analysis_tools_data_frequency_t *info)
{
gint i_limit, col;
GSList *l;
GnmFunc *fd_sum;
GnmFunc *fd_if;
GnmFunc *fd_index;
GnmFunc *fd_isblank;
GnmFunc *fd_rows = NULL;
GnmFunc *fd_columns = NULL;
GnmFunc *fd_exact = NULL;
fd_sum = gnm_func_lookup_or_add_placeholder ("SUM");
gnm_func_ref (fd_sum);
fd_if = gnm_func_lookup_or_add_placeholder ("IF");
gnm_func_ref (fd_if);
fd_index = gnm_func_lookup_or_add_placeholder ("INDEX");
gnm_func_ref (fd_index);
fd_isblank = gnm_func_lookup_or_add_placeholder ("ISBLANK");
gnm_func_ref (fd_isblank);
if (info->exact) {
fd_exact = gnm_func_lookup_or_add_placeholder ("EXACT");
gnm_func_ref (fd_exact);
}
if (info->percentage) {
fd_rows = gnm_func_lookup_or_add_placeholder ("ROWS");
gnm_func_ref (fd_rows);
fd_columns = gnm_func_lookup_or_add_placeholder ("COLUMNS");
gnm_func_ref (fd_columns);
}
/* General Info */
dao_set_italic (dao, 0, 0, 0, 1);
set_cell_text_col (dao, 0, 0, _("/Frequency Table"
"/Category"));
/* Setting up the categories */
if (info->predetermined) {
int row = 2, i, j, i_h_limit, i_w_limit;
GnmExpr const *expr_bin;
GnmRange range;
range_init_value (&range, info->bin);
i_h_limit = range_height (&range);
i_w_limit = range_width (&range);
i_limit = i_h_limit * i_w_limit;
expr_bin = gnm_expr_new_constant (info->bin);
for (i = 1; i <= i_h_limit; i++)
for (j = 1; j <= i_w_limit; j++) {
GnmExpr const *expr_index;
expr_index = gnm_expr_new_funcall3
(fd_index,
gnm_expr_copy (expr_bin),
gnm_expr_new_constant (value_new_int (i)),
gnm_expr_new_constant (value_new_int (j)));
dao_set_cell_expr (dao, 0, row++,
gnm_expr_new_funcall3
(fd_if,
gnm_expr_new_funcall1
(fd_isblank,
gnm_expr_copy (expr_index)),
gnm_expr_new_constant (value_new_string ("")),
expr_index));
}
gnm_expr_free (expr_bin);
} else {
i_limit = info->n;
}
for (l = info->base.input, col = 1; l; col++, l = l->next) {
GnmValue *val = value_dup ((GnmValue *)l->data);
GnmValue *val_c = NULL;
GnmExpr const *expr_count;
GnmExpr const *expr_data;
GnmExpr const *expr_if;
int i, row = 2;
dao_set_italic (dao, col, 1, col, 1);
if (info->base.labels) {
val_c = value_dup (val);
switch (info->base.group_by) {
case GROUPED_BY_ROW:
val->v_range.cell.a.col++;
break;
default:
val->v_range.cell.a.row++;
break;
}
dao_set_cell_expr (dao, col, 1,
gnm_expr_new_funcall1 (fd_index,
gnm_expr_new_constant (val_c)));
} else {
char const *format;
switch (info->base.group_by) {
case GROUPED_BY_ROW:
format = _("Row %d");
break;
case GROUPED_BY_COL:
format = _("Column %d");
break;
default:
format = _("Area %d");
break;
}
dao_set_cell_printf (dao, col, 1, format, col);
}
expr_data = gnm_expr_new_constant (val);
if (info->exact)
expr_if = gnm_expr_new_funcall2
(fd_exact, gnm_expr_copy (expr_data),
make_cellref (- col, 0));
else
expr_if = gnm_expr_new_binary
(gnm_expr_copy (expr_data),
GNM_EXPR_OP_EQUAL, make_cellref (- col, 0));
expr_count = gnm_expr_new_funcall1 (fd_sum,
gnm_expr_new_funcall3
(fd_if, expr_if,
gnm_expr_new_constant (value_new_int (1)),
gnm_expr_new_constant (value_new_int (0))));
if (info->percentage) {
dao_set_format (dao, col, 2, col, i_limit + 2, "0.0%");
expr_count = gnm_expr_new_binary (expr_count,
GNM_EXPR_OP_DIV,
gnm_expr_new_binary
(gnm_expr_new_funcall1
(fd_rows, gnm_expr_copy (expr_data)),
GNM_EXPR_OP_MULT,
gnm_expr_new_funcall1
(fd_columns, expr_data)));
} else
gnm_expr_free (expr_data);
for (i = 0; i < i_limit; i++, row++)
dao_set_cell_array_expr (dao, col, row, gnm_expr_copy (expr_count));
gnm_expr_free (expr_count);
}
gnm_func_unref (fd_if);
gnm_func_unref (fd_sum);
gnm_func_unref (fd_index);
gnm_func_unref (fd_isblank);
if (fd_rows != NULL)
gnm_func_unref (fd_rows);
if (fd_columns != NULL)
gnm_func_unref (fd_columns);
if (fd_exact != NULL)
gnm_func_unref (fd_exact);
/* Create Chart if requested */
if (info->chart != NO_CHART) {
SheetObject *so;
GogGraph *graph;
GogChart *chart;
GogPlot *plot;
GogSeries *series;
GOData *cats;
GOData *values;
int ct;
graph = g_object_new (GOG_TYPE_GRAPH, NULL);
chart = GOG_CHART (gog_object_add_by_name (
GOG_OBJECT (graph), "Chart", NULL));
plot = gog_plot_new_by_name ("GogBarColPlot");
if (info->chart == BAR_CHART)
go_object_toggle (plot, "horizontal");
gog_object_add_by_name (GOG_OBJECT (chart),
"Plot", GOG_OBJECT (plot));
cats = dao_go_data_vector (dao, 0, 2,
0, 2 + i_limit);
for (ct = 1; ct < col; ct ++) {
g_object_ref (cats);
values = dao_go_data_vector (dao, ct, 2,
ct, 2 + i_limit);
series = gog_plot_new_series (plot);
gog_series_set_dim (series, 0, cats, NULL);
gog_series_set_dim (series, 1, values, NULL);
}
g_object_unref (cats);
so = sheet_object_graph_new (graph);
g_object_unref (graph);
dao_set_sheet_object (dao, 0, 1, so);
}
dao_redraw_respan (dao);
return FALSE;
}
void
gnm_scenario_item_set_value (GnmScenarioItem *sci, const GnmValue *v)
{
value_release (sci->value);
sci->value = value_dup (v);
}
static gboolean
tool_random_engine_run_discrete_last_check (G_GNUC_UNUSED data_analysis_output_t *dao,
tools_data_random_t *info,
discrete_random_tool_t *param,
discrete_random_tool_local_t **continuity)
{
discrete_random_tool_local_t *data;
GnmValue *range = param->range;
gnm_float cumprob = 0;
int j = 0;
int i;
data = *continuity = g_new0 (discrete_random_tool_local_t, 1);
data->n = range->v_range.cell.b.row - range->v_range.cell.a.row + 1;
data->cumul_p = g_new (gnm_float, data->n);
data->values = g_new0 (GnmValue *, data->n);
for (i = range->v_range.cell.a.row;
i <= range->v_range.cell.b.row;
i++, j++) {
GnmValue *v;
gnm_float thisprob;
GnmCell *cell = sheet_cell_get (range->v_range.cell.a.sheet,
range->v_range.cell.a.col + 1, i);
if (cell == NULL ||
(v = cell->value) == NULL ||
!VALUE_IS_NUMBER (v)) {
gnm_cmd_context_error_calc (GO_CMD_CONTEXT (info->wbc),
_("The probability input range "
"contains a non-numeric value.\n"
"All probabilities must be "
"non-negative numbers."));
goto random_tool_discrete_out;
}
if ((thisprob = value_get_as_float (v)) < 0) {
gnm_cmd_context_error_calc (GO_CMD_CONTEXT (info->wbc),
_("The probability input range "
"contains a negative number.\n"
"All probabilities must be "
"non-negative!"));
goto random_tool_discrete_out;
}
cumprob += thisprob;
data->cumul_p[j] = cumprob;
cell = sheet_cell_get (range->v_range.cell.a.sheet,
range->v_range.cell.a.col, i);
if (cell == NULL || cell->value == NULL) {
gnm_cmd_context_error_calc (GO_CMD_CONTEXT (info->wbc),
_("None of the values in the value "
"range may be empty!"));
goto random_tool_discrete_out;
}
data->values[j] = value_dup (cell->value);
}
if (cumprob != 0) {
/* Rescale... */
for (i = 0; i < data->n; i++) {
data->cumul_p[i] /= cumprob;
}
return FALSE;
}
gnm_cmd_context_error_calc (GO_CMD_CONTEXT (info->wbc),
_("The probabilities may not all be 0!"));
random_tool_discrete_out:
tool_random_engine_run_discrete_clear_continuity (continuity);
return TRUE;
}
static gboolean
analysis_tool_chi_squared_engine_run (data_analysis_output_t *dao,
analysis_tools_data_chi_squared_t *info)
{
GnmExpr const *expr_check;
GnmExpr const *expr_region;
GnmExpr const *expr_statistic;
GnmExpr const *expr_row_ones;
GnmExpr const *expr_col_ones;
GnmExpr const *expr_row;
GnmExpr const *expr_column;
GnmExpr const *expr_expect;
GnmFunc *fd_mmult = analysis_tool_get_function ("MMULT", dao);
GnmFunc *fd_row = analysis_tool_get_function ("ROW", dao);
GnmFunc *fd_column = analysis_tool_get_function ("COLUMN", dao);
GnmFunc *fd_transpose = analysis_tool_get_function ("TRANSPOSE", dao);
GnmFunc *fd_sum = analysis_tool_get_function ("SUM", dao);
GnmFunc *fd_min = analysis_tool_get_function ("MIN", dao);
GnmFunc *fd_offset = analysis_tool_get_function ("OFFSET", dao);
GnmFunc *fd_chiinv = analysis_tool_get_function ("CHIINV", dao);
GnmFunc *fd_chidist = analysis_tool_get_function ("CHIDIST", dao);
char const *label;
char *cc;
label = (info->independence)
/* translator info: The quotation marks in the next strings need to */
/* remain since these are Excel-style format strings */
? _("[>=5]\"Test of Independence\";[<5][Red]\"Invalid Test of Independence\"")
: _("[>=5]\"Test of Homogeneity\";[<5][Red]\"Invalid Test of Homogeneity\"");
dao_set_italic (dao, 0, 1, 0, 4);
set_cell_text_col (dao, 0, 1, _("/Test Statistic"
"/Degrees of Freedom"
"/p-Value"
"/Critical Value"));
cc = g_strdup_printf ("%s = %.2" GNM_FORMAT_f, "\xce\xb1", info->alpha);
dao_set_cell_comment (dao, 0, 4, cc);
g_free (cc);
if (info->labels)
expr_region = gnm_expr_new_funcall5
(fd_offset,
gnm_expr_new_constant (value_dup (info->input)),
gnm_expr_new_constant (value_new_int (1)),
gnm_expr_new_constant (value_new_int (1)),
gnm_expr_new_constant (value_new_int (info->n_r)),
gnm_expr_new_constant (value_new_int (info->n_c)));
else
expr_region = gnm_expr_new_constant (value_dup (info->input));
expr_row = gnm_expr_new_funcall1 (fd_row, gnm_expr_copy (expr_region));
expr_column = gnm_expr_new_funcall1 (fd_column, gnm_expr_copy (expr_region));
expr_col_ones = gnm_expr_new_funcall1 (fd_transpose,
gnm_expr_new_binary (gnm_expr_copy (expr_column),
GNM_EXPR_OP_DIV,
expr_column));
expr_row_ones = gnm_expr_new_funcall1 (fd_transpose,
gnm_expr_new_binary (gnm_expr_copy (expr_row),
GNM_EXPR_OP_DIV,
expr_row));
expr_expect = gnm_expr_new_binary (gnm_expr_new_funcall2
(fd_mmult,
gnm_expr_new_funcall2
(fd_mmult,
gnm_expr_copy (expr_region),
expr_col_ones),
gnm_expr_new_funcall2
(fd_mmult,
expr_row_ones,
gnm_expr_copy (expr_region))),
GNM_EXPR_OP_DIV,
gnm_expr_new_funcall1 (fd_sum, gnm_expr_copy (expr_region)));
expr_check = gnm_expr_new_funcall1 (fd_min, gnm_expr_copy (expr_expect));
dao_set_merge (dao, 0, 0, 1, 0);
dao_set_italic (dao, 0, 0, 0, 0);
dao_set_cell_expr (dao, 0, 0, expr_check);
dao_set_format (dao, 0, 0, 0, 0, label);
dao_set_align (dao, 0, 0, 0, 0, GNM_HALIGN_CENTER, GNM_VALIGN_BOTTOM);
expr_statistic = gnm_expr_new_funcall1 (fd_sum,
gnm_expr_new_binary
(gnm_expr_new_binary (gnm_expr_new_binary
(gnm_expr_copy (expr_region),
GNM_EXPR_OP_SUB,
gnm_expr_copy (expr_expect)),
GNM_EXPR_OP_EXP,
gnm_expr_new_constant (value_new_int (2))),
GNM_EXPR_OP_DIV,
gnm_expr_copy (expr_expect)));
dao_set_cell_array_expr (dao, 1, 1, expr_statistic);
dao_set_cell_int (dao, 1, 2, (info->n_r - 1)*(info->n_c - 1));
dao_set_cell_expr(dao, 1, 3, gnm_expr_new_funcall2
(fd_chidist, make_cellref (0,-2), make_cellref (0,-1)));
dao_set_cell_expr(dao, 1, 4, gnm_expr_new_funcall2
(fd_chiinv,
gnm_expr_new_constant (value_new_float (info->alpha)),
make_cellref (0,-2)));
gnm_func_unref (fd_mmult);
gnm_func_unref (fd_row);
gnm_func_unref (fd_column);
gnm_func_unref (fd_transpose);
gnm_func_unref (fd_sum);
gnm_func_unref (fd_min);
gnm_func_unref (fd_offset);
gnm_func_unref (fd_chiinv);
gnm_func_unref (fd_chidist);
gnm_expr_free (expr_expect);
gnm_expr_free (expr_region);
dao_redraw_respan (dao);
return FALSE;
}
static GnmValue *
gnumeric_ifna (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
{
return value_dup ((value_error_classify (argv[0]) == GNM_ERROR_NA) ? argv[1] : argv[0]);
}
static gboolean
analysis_tool_one_mean_test_engine_run (data_analysis_output_t *dao,
analysis_tools_data_one_mean_test_t *info)
{
guint col;
GSList *data = info->base.input;
gboolean first = TRUE;
GnmFunc *fd_mean;
GnmFunc *fd_var;
GnmFunc *fd_sqrt;
GnmFunc *fd_abs;
GnmFunc *fd_tdist;
GnmFunc *fd_iferror;
GnmFunc *fd_count;
fd_count = gnm_func_lookup_or_add_placeholder ("COUNT");
gnm_func_inc_usage (fd_count);
fd_mean = gnm_func_lookup_or_add_placeholder ("AVERAGE");
gnm_func_inc_usage (fd_mean);
fd_var = gnm_func_lookup_or_add_placeholder ("VAR");
gnm_func_inc_usage (fd_var);
fd_sqrt = gnm_func_lookup_or_add_placeholder ("SQRT");
gnm_func_inc_usage (fd_sqrt);
fd_abs = gnm_func_lookup_or_add_placeholder ("ABS");
gnm_func_inc_usage (fd_abs);
fd_tdist = gnm_func_lookup_or_add_placeholder ("TDIST");
gnm_func_inc_usage (fd_tdist);
fd_iferror = gnm_func_lookup_or_add_placeholder ("IFERROR");
gnm_func_inc_usage (fd_iferror);
dao_set_italic (dao, 0, 0, 0, 9);
set_cell_text_col (dao, 0, 0, _("/Student-t Test"
"/N"
"/Observed Mean"
"/Hypothesized Mean"
"/Observed Variance"
"/Test Statistic"
"/df"
"/\xce\xb1"
"/P(T\xe2\x89\xa4t) one-tailed"
"/P(T\xe2\x89\xa4t) two-tailed"));
for (col = 1; data != NULL; data = data->next, col++) {
GnmValue *val_org = value_dup (data->data);
GnmExpr const *expr;
GnmExpr const *expr_org;
GnmExpr const *expr_range_clean;
GnmExpr const *expr_stddev;
GnmExpr const *expr_abs;
/* Note that analysis_tools_write_label may modify val_org */
dao_set_italic (dao, col, 0, col, 0);
analysis_tools_write_label (val_org, dao, &info->base, col, 0, col);
expr_org = gnm_expr_new_constant (val_org);
expr_range_clean = gnm_expr_new_funcall2
(fd_iferror, gnm_expr_copy (expr_org), gnm_expr_new_constant (value_new_string("")));
if (first) {
dao_set_cell_float (dao, col, 3, info->mean);
dao_set_cell_float (dao, col, 7, info->alpha);
first = FALSE;
} else {
dao_set_cell_expr (dao, col, 3, make_cellref (-1,0));
dao_set_cell_expr (dao, col, 7, make_cellref (-1,0));
}
expr = gnm_expr_new_funcall1 (fd_count, expr_org);
dao_set_cell_expr (dao, col, 1, expr);
expr = gnm_expr_new_funcall1 (fd_mean, gnm_expr_copy (expr_range_clean));
dao_set_cell_array_expr (dao, col, 2, expr);
expr = gnm_expr_new_funcall1 (fd_var, expr_range_clean);
dao_set_cell_array_expr (dao, col, 4, expr);
dao_set_cell_expr (dao, col, 6, gnm_expr_new_binary
(make_cellref (0,-5), GNM_EXPR_OP_SUB, gnm_expr_new_constant (value_new_int (1))));
expr_stddev = gnm_expr_new_funcall1
(fd_sqrt, gnm_expr_new_binary (make_cellref (0,-1), GNM_EXPR_OP_DIV, make_cellref (0,-4)));
expr = gnm_expr_new_binary
(gnm_expr_new_binary (make_cellref (0,-3), GNM_EXPR_OP_SUB, make_cellref (0,-2)),
GNM_EXPR_OP_DIV,
expr_stddev);
dao_set_cell_array_expr (dao, col, 5, expr);
expr_abs = gnm_expr_new_funcall1 (fd_abs, make_cellref (0,-3));
expr = gnm_expr_new_funcall3 (fd_tdist, expr_abs, make_cellref (0,-2),
gnm_expr_new_constant (value_new_int (1)));
dao_set_cell_expr (dao, col, 8, expr);
expr_abs = gnm_expr_new_funcall1 (fd_abs, make_cellref (0,-4));
expr = gnm_expr_new_funcall3 (fd_tdist, expr_abs, make_cellref (0,-3),
gnm_expr_new_constant (value_new_int (2)));
dao_set_cell_expr (dao, col, 9, expr);
}
gnm_func_dec_usage (fd_count);
gnm_func_dec_usage (fd_mean);
gnm_func_dec_usage (fd_var);
gnm_func_dec_usage (fd_abs);
gnm_func_dec_usage (fd_sqrt);
gnm_func_dec_usage (fd_tdist);
gnm_func_dec_usage (fd_iferror);
dao_redraw_respan (dao);
return FALSE;
}
static gboolean
analysis_tool_principal_components_engine_run (data_analysis_output_t *dao,
analysis_tools_data_generic_t *info)
{
int l = g_slist_length (info->input), i;
GSList *inputdata;
GnmFunc *fd_mean;
GnmFunc *fd_var;
GnmFunc *fd_eigen;
GnmFunc *fd_mmult;
GnmFunc *fd_munit;
GnmFunc *fd_sqrt;
GnmFunc *fd_count;
GnmFunc *fd_sum;
GnmFunc *fd_and;
GnmFunc *fd_if;
GnmExpr const *expr;
GnmExpr const *expr_count;
GnmExpr const *expr_munit;
GnmExpr const *expr_and;
int data_points;
GnmExprList *and_args = NULL;
if (!dao_cell_is_visible (dao, l, 9 + 3 * l)) {
dao_set_bold (dao, 0, 0, 0, 0);
dao_set_italic (dao, 0, 0, 0, 0);
dao_set_cell (dao, 0, 0,
_("Principal components analysis has "
"insufficient space."));
return 0;
}
fd_mean = gnm_func_lookup_or_add_placeholder
("AVERAGE", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_mean);
fd_var = gnm_func_lookup_or_add_placeholder
("VAR", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_var);
fd_eigen = gnm_func_lookup_or_add_placeholder
("EIGEN", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_eigen);
fd_mmult = gnm_func_lookup_or_add_placeholder
("MMULT", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_mmult);
fd_munit = gnm_func_lookup_or_add_placeholder
("MUNIT", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_munit);
fd_sqrt = gnm_func_lookup_or_add_placeholder
("SQRT", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_sqrt);
fd_count = gnm_func_lookup_or_add_placeholder
("COUNT", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_count);
fd_sum = gnm_func_lookup_or_add_placeholder
("SUM", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_sum);
fd_and = gnm_func_lookup_or_add_placeholder
("AND", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_and);
fd_if = gnm_func_lookup_or_add_placeholder
("IF", dao->sheet ? dao->sheet->workbook : NULL, FALSE);
gnm_func_ref (fd_if);
dao_set_bold (dao, 0, 0, 0, 0);
dao_set_italic (dao, 0, 0, 0, 11 + 3 * l);
dao_set_format (dao, 0, 0, 0, 0,
_("\"Principal Components Analysis\";"
"[Red]\"Principal Components Analysis is invalid.\""));
dao_set_align (dao, 0, 0, 0, 0,
HALIGN_LEFT, VALIGN_BOTTOM);
dao->offset_row++;
analysis_tool_table (dao, info, _("Covariances:"), "COVAR", TRUE);
dao->offset_row--;
for (i = 1, inputdata = info->input; inputdata != NULL; i++, inputdata = inputdata->next)
analysis_tools_write_label (inputdata->data, dao, info, 0, 9 + 2 * l + i, i);
data_points = value_area_get_width (info->input->data, NULL) *
value_area_get_height (info->input->data, NULL);
for (i = 0; i < l; i++)
and_args = gnm_expr_list_prepend
(and_args,
gnm_expr_new_binary
(gnm_expr_new_constant (value_new_int (data_points)),
GNM_EXPR_OP_EQUAL,
make_cellref (1 + i, 3 + l)));
expr_and = gnm_expr_new_funcall (fd_and, and_args);
dao_set_cell_expr (dao, 0, 0,
gnm_expr_new_funcall3
(fd_if,
expr_and,
gnm_expr_new_constant (value_new_int (1)),
gnm_expr_new_constant (value_new_int (-1))));
dao_set_merge (dao,0,0,2,0);
set_cell_text_col (dao, 0, 3 + l,
_("/Count:"
"/Mean:"
"/Variance:"
"//Eigenvalues:"
"/Eigenvectors:"));
dao_set_cell (dao, 0, 11 + 3 * l, _("Percent of Trace:"));
dao_set_italic (dao, 0, 9 + 2 * l, 1 + l, 9 + 2 * l);
dao_set_percent (dao, 1, 11 + 3 * l, 1 + l, 11 + 3 * l);
for (i = 1, inputdata = info->input; inputdata != NULL; i++, inputdata = inputdata->next) {
expr = gnm_expr_new_constant (value_dup (inputdata->data));
dao_set_cell_expr (dao, i, 3 + l,
gnm_expr_new_funcall1 (fd_count, gnm_expr_copy (expr)));
dao_set_cell_expr (dao, i, 4 + l,
gnm_expr_new_funcall1 (fd_mean, gnm_expr_copy (expr)));
dao_set_cell_expr (dao, i, 5 + l,
gnm_expr_new_funcall1 (fd_var, expr));
}
expr_count = gnm_expr_new_binary (make_cellref (0,-4), GNM_EXPR_OP_DIV,
gnm_expr_new_binary (make_cellref (0,-4), GNM_EXPR_OP_SUB,
gnm_expr_new_constant (value_new_int (1))));
expr = gnm_expr_new_funcall1
(fd_eigen, gnm_expr_new_binary
(expr_count, GNM_EXPR_OP_MULT, make_rangeref (0, - (5 + l), l - 1, - 6)));
dao_set_array_expr (dao, 1, 7 + l, l, l + 1, expr);
for (i = 1; i <= l; i++) {
dao_set_align (dao, i, 9 + 2 * l, i, 9 + 2 * l,
HALIGN_CENTER, VALIGN_BOTTOM);
dao_set_cell_printf (dao, i, 9 + 2 * l, "\xce\xbe%i", i);
dao_set_cell_expr (dao, i, 11 + 3 * l,
gnm_expr_new_binary (make_cellref (0,- 4 - 2 * l),
GNM_EXPR_OP_DIV,
gnm_expr_new_funcall1
(fd_sum,
dao_get_rangeref (dao, 1, 7 + l, l, 7 + l))));
}
expr_munit = gnm_expr_new_funcall1 (fd_munit, gnm_expr_new_constant (value_new_int (l)));
expr = gnm_expr_new_funcall2 (fd_mmult,
gnm_expr_new_binary
(gnm_expr_new_funcall1
(fd_sqrt, gnm_expr_new_binary
(gnm_expr_new_constant (value_new_int (1)),
GNM_EXPR_OP_DIV,
make_rangeref (0, - 5 - l, l - 1, - 5 - l))),
GNM_EXPR_OP_MULT,
gnm_expr_copy (expr_munit)),
make_rangeref (0, - 2 - l, l - 1, - 3));
expr = gnm_expr_new_funcall2 (fd_mmult, expr,
gnm_expr_new_binary
(gnm_expr_new_funcall1
(fd_sqrt, make_rangeref (0, - 3 - l, l - 1, - 3 - l)),
GNM_EXPR_OP_MULT,
expr_munit));
dao_set_array_expr (dao, 1, 10 + 2 * l, l, l, expr);
gnm_func_unref (fd_mean);
gnm_func_unref (fd_var);
gnm_func_unref (fd_eigen);
gnm_func_unref (fd_mmult);
gnm_func_unref (fd_munit);
gnm_func_unref (fd_sqrt);
gnm_func_unref (fd_count);
gnm_func_unref (fd_sum);
gnm_func_unref (fd_and);
gnm_func_unref (fd_if);
dao_redraw_respan (dao);
return 0;
}
static gboolean
analysis_tool_normality_engine_run (data_analysis_output_t *dao,
analysis_tools_data_normality_t *info)
{
guint col;
GSList *data = info->base.input;
GnmFunc *fd;
GnmFunc *fd_if;
char const *fdname;
char const *testname;
char const *n_comment;
GogGraph *graph = NULL;
GogPlot *plot = NULL;
SheetObject *so;
switch (info->type) {
case normality_test_type_andersondarling:
fdname = "ADTEST";
testname = N_("Anderson-Darling Test");
n_comment = N_("For the Anderson-Darling Test\n"
"the sample size must be at\n"
"least 8.");
break;
case normality_test_type_cramervonmises:
fdname = "CVMTEST";
testname = N_("Cram\xc3\xa9r-von Mises Test");
n_comment = N_("For the Cram\xc3\xa9r-von Mises Test\n"
"the sample size must be at\n"
"least 8.");
break;
case normality_test_type_lilliefors:
fdname = "LKSTEST";
testname = N_("Lilliefors (Kolmogorov-Smirnov) Test");
n_comment = N_("For the Lilliefors (Kolmogorov-Smirnov) Test\n"
"the sample size must be at least 5.");
break;
case normality_test_type_shapirofrancia:
fdname = "SFTEST";
testname = N_("Shapiro-Francia Test");
n_comment = N_("For the Shapiro-Francia Test\n"
"the sample size must be at\n"
"least 5 and at most 5000.");
break;
default:
g_assert_not_reached();
}
fd = gnm_func_lookup_or_add_placeholder (fdname);
gnm_func_ref (fd);
fd_if = gnm_func_lookup_or_add_placeholder ("IF");
gnm_func_ref (fd_if);
dao_set_italic (dao, 0, 0, 0, 5);
dao_set_cell (dao, 0, 0, _(testname));
if (info->graph) {
GogChart *chart;
graph = g_object_new (GOG_TYPE_GRAPH, NULL);
chart = GOG_CHART (gog_object_add_by_name (
GOG_OBJECT (graph), "Chart", NULL));
plot = gog_plot_new_by_name ("GogProbabilityPlot");
go_object_set_property (G_OBJECT (plot), "distribution",
"Distribution", "GODistNormal",
NULL, NULL);
gog_object_add_by_name (GOG_OBJECT (chart),
"Plot", GOG_OBJECT (plot));
}
/* xgettext:
* Note to translators: in the following string and others like it,
* the "/" is a separator character that can be changed to anything
* if the translation needs the slash; just use, say, "|" instead.
*
* The items are bundled like this to increase translation context.
*/
set_cell_text_col (dao, 0, 1, _("/Alpha"
"/p-Value"
"/Statistic"
"/N"
"/Conclusion"));
dao_set_cell_comment (dao, 0, 4, _(n_comment));
for (col = 1; data != NULL; data = data->next, col++) {
GnmValue *val_org = value_dup (data->data);
/* Note that analysis_tools_write_label may modify val_org */
dao_set_italic (dao, col, 0, col, 0);
analysis_tools_write_label (val_org, dao, &info->base,
col, 0, col);
if (info->graph) {
GogSeries *series;
series = gog_plot_new_series (plot);
gog_series_set_dim (series, 0,
gnm_go_data_vector_new_expr
(val_org->v_range.cell.a.sheet,
gnm_expr_top_new (gnm_expr_new_constant (value_dup (val_org)))),
NULL);
}
if (col == 1)
dao_set_cell_float (dao, col, 1, info->alpha);
else
dao_set_cell_expr (dao, col, 1,
make_cellref (1 - col, 0));
dao_set_array_expr (dao, col, 2, 1, 3,
gnm_expr_new_funcall1 (fd, gnm_expr_new_constant (val_org)));
dao_set_cell_expr (dao, col, 5,
gnm_expr_new_funcall3
(fd_if, gnm_expr_new_binary
(make_cellref (0, -4),
GNM_EXPR_OP_GTE,
make_cellref (0, -3)),
gnm_expr_new_constant (value_new_string (_("Not normal"))),
gnm_expr_new_constant (value_new_string (_("Possibly normal")))));
}
if (info->graph) {
so = sheet_object_graph_new (graph);
g_object_unref (graph);
dao_set_sheet_object (dao, 0, 1, so);
}
gnm_func_unref (fd);
gnm_func_unref (fd_if);
dao_redraw_respan (dao);
return 0;
}
static GnmValue *
gnumeric_iferror (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
{
return value_dup (VALUE_IS_ERROR (argv[0]) ? argv[1] : argv[0]);
}