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 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 GnmExpr const * parse_subexpr(const psiconv_formula psi_formula) { int nrargs=0; /* -1 for variable */ int kind=-1; /* 0 for dat, 1 for operator, 2 for formula, 3 for special, -1 for unknown */ psiconv_formula psi_form1,psi_form2; GnmExpr const *expr1=NULL,*expr2=NULL; switch(psi_formula->type) { /* Translates values */ case psiconv_formula_dat_float: case psiconv_formula_dat_int: case psiconv_formula_dat_string: case psiconv_formula_dat_cellblock: case psiconv_formula_dat_vcellblock: nrargs = 0; kind = 0; break; /* Translates to binary operators */ case psiconv_formula_op_lt: case psiconv_formula_op_le: case psiconv_formula_op_gt: case psiconv_formula_op_ge: case psiconv_formula_op_ne: case psiconv_formula_op_eq: case psiconv_formula_op_add: case psiconv_formula_op_sub: case psiconv_formula_op_mul: case psiconv_formula_op_div: /* case psiconv_formula_op_pow: */ /* case psiconv_formula_op_and: */ /* case psiconv_formula_op_or: */ /* case psiconv_formula_op_con: */ nrargs = 2; kind = 1; break; /* Translates to unary operators */ case psiconv_formula_op_pos: case psiconv_formula_op_neg: case psiconv_formula_op_not: nrargs = 1; kind = 1; break; /* Specially handled */ case psiconv_formula_dat_cellref: case psiconv_formula_op_bra: nrargs = 1; kind = 3; break; /* Should never happen; caught by the default */ /* case psiconv_formula_mark_eof: */ /* case psiconv_formula_mark_opsep: */ /* case psiconv_formula_mark_opend: */ default: kind = -1; break; } if (kind == -1) { /* Unknown value */ return NULL; } else if (kind == 0) { /* Handling data */ GnmValue *v = NULL; switch(psi_formula->type) { case psiconv_formula_dat_float: v = value_new_float(psi_formula->data.dat_float); break; case psiconv_formula_dat_int: v = value_new_int(psi_formula->data.dat_int); break; case psiconv_formula_dat_string: v = psi_new_string(psi_formula->data.dat_string); break; case psiconv_formula_dat_cellblock: { GnmCellRef cr1, cr2; p_cellref_init (&cr1, psi_formula->data.dat_cellblock.first.row.offset, psi_formula->data.dat_cellblock.first.row.absolute, psi_formula->data.dat_cellblock.first.column.offset, psi_formula->data.dat_cellblock.first.column.absolute); p_cellref_init (&cr2, psi_formula->data.dat_cellblock.last.row.offset, psi_formula->data.dat_cellblock.last.row.absolute, psi_formula->data.dat_cellblock.last.column.offset, psi_formula->data.dat_cellblock.last.column.absolute); v = value_new_cellrange (&cr1, &cr2, 1, 1); break; } default: break; } if (!v) return NULL; return gnm_expr_new_constant(v); } else if (kind == 1) { /* Handling the operators */ if (nrargs >= 1) { if (!(psi_form1 = psiconv_list_get (psi_formula->data.fun_operands,0))) return NULL; if (!(expr1 = parse_subexpr(psi_form1))) return NULL; } if (nrargs >= 2) { if (!(psi_form2 = psiconv_list_get (psi_formula->data.fun_operands,1))) { gnm_expr_free(expr1); return NULL; } if (!(expr2 = parse_subexpr(psi_form2))) { gnm_expr_free(expr1); return NULL; } } switch(psi_formula->type) { case psiconv_formula_op_lt: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_LT,expr2); case psiconv_formula_op_le: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_LTE,expr2); case psiconv_formula_op_gt: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_GT,expr2); case psiconv_formula_op_ge: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_GTE,expr2); case psiconv_formula_op_ne: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_NOT_EQUAL,expr2); case psiconv_formula_op_eq: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_EQUAL,expr2); case psiconv_formula_op_add: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_ADD,expr2); case psiconv_formula_op_sub: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_SUB,expr2); case psiconv_formula_op_mul: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_MULT,expr2); case psiconv_formula_op_div: return gnm_expr_new_binary (expr1,GNM_EXPR_OP_DIV,expr2); case psiconv_formula_op_pos: return gnm_expr_new_unary (GNM_EXPR_OP_UNARY_PLUS,expr1); case psiconv_formula_op_neg: return gnm_expr_new_unary (GNM_EXPR_OP_UNARY_NEG,expr1); default: gnm_expr_free(expr1); gnm_expr_free(expr2); return NULL; } } else if (kind == 3) { switch(psi_formula->type) { case psiconv_formula_dat_cellref: { GnmCellRef cr; return gnm_expr_new_cellref (p_cellref_init (&cr, psi_formula->data.dat_cellref.row.offset, psi_formula->data.dat_cellref.row.absolute, psi_formula->data.dat_cellref.column.offset, psi_formula->data.dat_cellref.column.absolute)); } case psiconv_formula_op_bra: if (!(psi_form1 = psiconv_list_get (psi_formula->data.fun_operands,0))) return NULL; return parse_subexpr(psi_form1); default: break; } } return NULL; }
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_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 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; }
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; }
/** * gnm_validation_eval: * @wbc: * @mstyle: * @sheet: * * validation set in the GnmStyle if applicable. **/ ValidationStatus gnm_validation_eval (WorkbookControl *wbc, GnmStyle const *mstyle, Sheet *sheet, GnmCellPos const *pos, gboolean *showed_dialog) { GnmValidation const *v; GnmCell *cell; GnmValue *val; gnm_float x; int nok, i; GnmEvalPos ep; if (showed_dialog) *showed_dialog = FALSE; v = gnm_style_get_validation (mstyle); if (v == NULL) return GNM_VALIDATION_STATUS_VALID; if (v->type == GNM_VALIDATION_TYPE_ANY) return GNM_VALIDATION_STATUS_VALID; cell = sheet_cell_get (sheet, pos->col, pos->row); if (cell != NULL) gnm_cell_eval (cell); if (gnm_cell_is_empty (cell)) { if (v->allow_blank) return GNM_VALIDATION_STATUS_VALID; BARF (g_strdup_printf (_("Cell %s is not permitted to be blank"), cell_name (cell))); } val = cell->value; switch (val->type) { case VALUE_ERROR: if (typeinfo[v->type].errors_not_allowed) BARF (g_strdup_printf (_("Cell %s is not permitted to contain error values"), cell_name (cell))); break; case VALUE_BOOLEAN: if (typeinfo[v->type].bool_always_ok) return GNM_VALIDATION_STATUS_VALID; break; case VALUE_STRING: if (typeinfo[v->type].strings_not_allowed) BARF (g_strdup_printf (_("Cell %s is not permitted to contain strings"), cell_name (cell))); break; default: break; } eval_pos_init_cell (&ep, cell); switch (v->type) { case GNM_VALIDATION_TYPE_AS_INT: x = value_get_as_float (val); if (gnm_fake_floor (x) == gnm_fake_ceil (x)) break; else BARF (g_strdup_printf (_("'%s' is not an integer"), value_peek_string (val))); case GNM_VALIDATION_TYPE_AS_NUMBER: x = value_get_as_float (val); break; case GNM_VALIDATION_TYPE_AS_DATE: /* What the hell does this do? */ x = value_get_as_float (val); if (x < 0) BARF (g_strdup_printf (_("'%s' is not a valid date"), value_peek_string (val))); break; case GNM_VALIDATION_TYPE_AS_TIME: /* What the hell does this do? */ x = value_get_as_float (val); break; case GNM_VALIDATION_TYPE_IN_LIST: { GnmExprTop const *texpr = v->deps[0].texpr; if (texpr) { GnmValue *list = gnm_expr_top_eval (texpr, &ep, GNM_EXPR_EVAL_PERMIT_NON_SCALAR | GNM_EXPR_EVAL_PERMIT_EMPTY); GnmValue *res = value_area_foreach (list, &ep, CELL_ITER_IGNORE_BLANK, (GnmValueIterFunc) cb_validate_custom, val); value_release (list); if (res == NULL) { GnmParsePos pp; char *expr_str = gnm_expr_top_as_string (texpr, parse_pos_init_evalpos (&pp, &ep), ep.sheet->convs); char *msg = g_strdup_printf (_("%s does not contain the new value."), expr_str); g_free (expr_str); BARF (msg); } } return GNM_VALIDATION_STATUS_VALID; } case GNM_VALIDATION_TYPE_TEXT_LENGTH: /* XL appears to use a very basic value->string mapping that * ignores formatting. * eg len (12/13/01) == len (37238) = 5 * This seems wrong for */ x = g_utf8_strlen (value_peek_string (val), -1); break; case GNM_VALIDATION_TYPE_CUSTOM: { gboolean valid; GnmExprTop const *texpr = v->deps[0].texpr; if (!texpr) return GNM_VALIDATION_STATUS_VALID; val = gnm_expr_top_eval (texpr, &ep, GNM_EXPR_EVAL_SCALAR_NON_EMPTY); valid = value_get_as_bool (val, NULL); value_release (val); if (valid) return GNM_VALIDATION_STATUS_VALID; else { GnmParsePos pp; char *expr_str = gnm_expr_top_as_string (texpr, parse_pos_init_evalpos (&pp, &ep), ep.sheet->convs); char *msg = g_strdup_printf (_("%s is not true."), expr_str); g_free (expr_str); BARF (msg); } } default: g_assert_not_reached (); return GNM_VALIDATION_STATUS_VALID; } if (v->op == GNM_VALIDATION_OP_NONE) return GNM_VALIDATION_STATUS_VALID; nok = 0; for (i = 0; i < opinfo[v->op].nops; i++) { GnmExprTop const *texpr_i = v->deps[i].texpr; GnmExprTop const *texpr; GnmValue *cres; if (!texpr_i) { nok++; continue; } texpr = gnm_expr_top_new (gnm_expr_new_binary (gnm_expr_new_constant (value_new_float (x)), opinfo[v->op].ops[i], gnm_expr_copy (texpr_i->expr))); cres = gnm_expr_top_eval (texpr, &ep, GNM_EXPR_EVAL_SCALAR_NON_EMPTY); if (value_get_as_bool (cres, NULL)) nok++; value_release (cres); gnm_expr_top_unref (texpr); } if (nok < opinfo[v->op].ntrue) BARF (g_strdup_printf (_("%s is out of permitted range"), value_peek_string (val))); return GNM_VALIDATION_STATUS_VALID; }
static gboolean analysis_tool_signed_rank_test_engine_run (data_analysis_output_t *dao, analysis_tools_data_sign_test_t *info) { guint col; GSList *data = info->base.input; gboolean first = TRUE; GnmExpr const *expr; GnmExpr const *expr_isnumber; GnmExpr const *expr_diff; GnmExpr const *expr_expect; GnmExpr const *expr_var; GnmExpr const *expr_abs; GnmExpr const *expr_big; GnmFunc *fd_median = analysis_tool_get_function ("MEDIAN", dao); GnmFunc *fd_if = analysis_tool_get_function ("IF", dao); GnmFunc *fd_sum = analysis_tool_get_function ("SUM", dao); GnmFunc *fd_min = analysis_tool_get_function ("MIN", dao); GnmFunc *fd_normdist = analysis_tool_get_function ("NORMDIST", dao); GnmFunc *fd_isnumber = analysis_tool_get_function ("ISNUMBER", dao); GnmFunc *fd_iferror = analysis_tool_get_function ("IFERROR", dao); GnmFunc *fd_rank = analysis_tool_get_function ("RANK.AVG", dao); GnmFunc *fd_abs = analysis_tool_get_function ("ABS", dao); GnmFunc *fd_sqrt = analysis_tool_get_function ("SQRT", dao); GnmFunc *fd_max = analysis_tool_get_function ("MAX", dao); dao_set_italic (dao, 0, 0, 0, 9); set_cell_text_col (dao, 0, 0, _("/Wilcoxon Signed Rank Test" "/Median" "/Predicted Median" "/N" "/S\xe2\x88\x92" "/S+" "/Test Statistic" "/\xce\xb1" "/P(T\xe2\x89\xa4t) one-tailed" "/P(T\xe2\x89\xa4t) two-tailed")); for (col = 0; data != NULL; data = data->next, col++) { GnmValue *val_org = value_dup (data->data); GnmExpr const *expr_org; /* Note that analysis_tools_write_label may modify val_org */ dao_set_italic (dao, col + 1, 0, col+1, 0); analysis_tools_write_label (val_org, dao, &info->base, col + 1, 0, col + 1); expr_org = gnm_expr_new_constant (val_org); if (first) { dao_set_cell_float (dao, col + 1, 2, info->median); dao_set_cell_float (dao, col + 1, 7, info->alpha); first = FALSE; } else { dao_set_cell_expr (dao, col + 1, 2, make_cellref (-1,0)); dao_set_cell_expr (dao, col + 1, 7, make_cellref (-1,0)); } expr_isnumber = gnm_expr_new_funcall3 (fd_if, gnm_expr_new_funcall1 (fd_isnumber, gnm_expr_copy (expr_org)), make_int (1), make_int (0)); expr = gnm_expr_new_funcall1 (fd_median, gnm_expr_copy (expr_org)); dao_set_cell_expr (dao, col + 1, 1, expr); expr_diff = gnm_expr_new_binary (gnm_expr_copy (expr_org), GNM_EXPR_OP_SUB, make_cellref (0,-2)); expr_abs = gnm_expr_new_funcall1 (fd_abs, gnm_expr_copy (expr_diff)); expr_big = gnm_expr_new_binary (gnm_expr_new_funcall1 (fd_max, gnm_expr_copy (expr_abs)), GNM_EXPR_OP_ADD, make_int (1)); expr = gnm_expr_new_funcall3 (fd_if, gnm_expr_new_funcall1 (fd_isnumber, gnm_expr_copy (expr_org)), gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (gnm_expr_copy (expr_org), GNM_EXPR_OP_EQUAL, make_cellref (0,-2)), gnm_expr_copy (expr_big), expr_abs), expr_big); expr = gnm_expr_new_funcall3 (fd_rank, gnm_expr_new_unary (GNM_EXPR_OP_UNARY_NEG, expr_diff), expr, make_int (1)); dao_set_cell_array_expr (dao, col + 1, 4, gnm_expr_new_funcall1 (fd_sum, gnm_expr_new_binary (gnm_expr_copy (expr_isnumber), GNM_EXPR_OP_MULT, gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (gnm_expr_copy (expr_org), GNM_EXPR_OP_LT, make_cellref (0,-2)), expr, make_int (0))))); expr = gnm_expr_new_funcall1 (fd_sum, gnm_expr_new_binary (expr_isnumber, GNM_EXPR_OP_MULT, gnm_expr_new_funcall2 (fd_iferror, gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (expr_org, GNM_EXPR_OP_NOT_EQUAL, make_cellref (0,-1)), make_int (1), make_int (0)), make_int (0)))); dao_set_cell_array_expr (dao, col + 1, 3, expr); dao_set_cell_expr (dao, col + 1, 5, gnm_expr_new_binary (gnm_expr_new_binary (gnm_expr_new_binary (make_cellref (0,-2), GNM_EXPR_OP_MULT, gnm_expr_new_binary (make_cellref (0,-2), GNM_EXPR_OP_ADD, make_int (1))), GNM_EXPR_OP_DIV, make_int (2)), GNM_EXPR_OP_SUB, make_cellref (0,-1))); dao_set_cell_expr (dao, col + 1, 6, gnm_expr_new_funcall2 (fd_min, make_cellref (0,-1), make_cellref (0,-2))); expr_expect = gnm_expr_new_binary (gnm_expr_new_binary (make_cellref (0,-5), GNM_EXPR_OP_MULT, gnm_expr_new_binary (make_cellref (0,-5), GNM_EXPR_OP_ADD, make_int (1))), GNM_EXPR_OP_DIV, make_int (4)); expr_var = gnm_expr_new_binary (gnm_expr_new_binary (gnm_expr_copy (expr_expect), GNM_EXPR_OP_MULT, gnm_expr_new_binary (gnm_expr_new_binary (make_int (2), GNM_EXPR_OP_MULT, make_cellref (0,-5)), GNM_EXPR_OP_ADD, make_int (1))), GNM_EXPR_OP_DIV, make_int (6)); expr = gnm_expr_new_funcall4 (fd_normdist, gnm_expr_new_binary (make_cellref (0,-2), GNM_EXPR_OP_ADD, make_float (0.5)), expr_expect, gnm_expr_new_funcall1 (fd_sqrt, expr_var), gnm_expr_new_constant (value_new_bool (TRUE))); dao_set_cell_expr (dao, col + 1, 8, gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (make_cellref (0,-5), GNM_EXPR_OP_LT, make_int (12)), gnm_expr_new_constant (value_new_error_NA (NULL)), expr)); dao_set_cell_comment (dao, col + 1, 8, _("This p-value is calculated by a normal approximation.\n" "It is only valid if the sample size is at least 12.")); expr = gnm_expr_new_binary (make_int (2), GNM_EXPR_OP_MULT, make_cellref (0,-1)); dao_set_cell_expr (dao, col + 1, 9, expr); } gnm_func_unref (fd_median); gnm_func_unref (fd_if); gnm_func_unref (fd_min); gnm_func_unref (fd_sum); gnm_func_unref (fd_normdist); gnm_func_unref (fd_isnumber); gnm_func_unref (fd_iferror); gnm_func_unref (fd_rank); gnm_func_unref (fd_abs); gnm_func_unref (fd_sqrt); gnm_func_unref (fd_max); dao_redraw_respan (dao); return FALSE; }
static gboolean analysis_tool_signed_rank_test_two_engine_run (data_analysis_output_t *dao, analysis_tools_data_sign_test_two_t *info) { GnmValue *val_1; GnmValue *val_2; GnmExpr const *expr_1; GnmExpr const *expr_2; GnmExpr const *expr; GnmExpr const *expr_diff; GnmExpr const *expr_diff_pred; GnmExpr const *expr_isnumber_1; GnmExpr const *expr_isnumber_2; GnmExpr const *expr_isnumber; GnmExpr const *expr_expect; GnmExpr const *expr_var; GnmExpr const *expr_abs; GnmExpr const *expr_big; GnmFunc *fd_median = analysis_tool_get_function ("MEDIAN", dao); GnmFunc *fd_if = analysis_tool_get_function ("IF", dao); GnmFunc *fd_sum = analysis_tool_get_function ("SUM", dao); GnmFunc *fd_min = analysis_tool_get_function ("MIN", dao); GnmFunc *fd_normdist = analysis_tool_get_function ("NORMDIST", dao); GnmFunc *fd_isnumber = analysis_tool_get_function ("ISNUMBER", dao); GnmFunc *fd_iferror = analysis_tool_get_function ("IFERROR", dao); GnmFunc *fd_rank = analysis_tool_get_function ("RANK.AVG", dao); GnmFunc *fd_abs = analysis_tool_get_function ("ABS", dao); GnmFunc *fd_sqrt = analysis_tool_get_function ("SQRT", dao); GnmFunc *fd_max = analysis_tool_get_function ("MAX", dao); dao_set_italic (dao, 0, 0, 0, 10); set_cell_text_col (dao, 0, 0, _("/Wilcoxon Signed Rank Test" "/Median" "/Observed Median Difference" "/Predicted Median Difference" "/N" "/S\xe2\x88\x92" "/S+" "/Test Statistic" "/\xce\xb1" "/P(T\xe2\x89\xa4t) one-tailed" "/P(T\xe2\x89\xa4t) two-tailed")); val_1 = value_dup (info->base.range_1); val_2 = value_dup (info->base.range_2); /* Labels */ dao_set_italic (dao, 1, 0, 2, 0); analysis_tools_write_label_ftest (val_1, dao, 1, 0, info->base.labels, 1); analysis_tools_write_label_ftest (val_2, dao, 2, 0, info->base.labels, 2); expr_1 = gnm_expr_new_constant (value_dup (val_1)); expr_2 = gnm_expr_new_constant (value_dup (val_2)); dao_set_cell_float (dao, 1, 3, info->median); dao_set_cell_float (dao, 1, 8, info->base.alpha); expr_isnumber_1 = gnm_expr_new_funcall3 (fd_if, gnm_expr_new_funcall1 (fd_isnumber, gnm_expr_copy (expr_1)), make_int (1), make_int (0)); expr_isnumber_2 = gnm_expr_new_funcall3 (fd_if, gnm_expr_new_funcall1 (fd_isnumber, gnm_expr_copy (expr_2)), make_int (1), make_int (0)); expr_isnumber = gnm_expr_new_binary (expr_isnumber_1, GNM_EXPR_OP_MULT, expr_isnumber_2); expr = gnm_expr_new_funcall1 (fd_median, gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (gnm_expr_copy (expr_isnumber), GNM_EXPR_OP_EQUAL, make_int (1)), gnm_expr_copy (expr_1), gnm_expr_new_constant (value_new_string("")))); dao_set_cell_array_expr (dao, 1, 1, expr); expr = gnm_expr_new_funcall1 (fd_median, gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (gnm_expr_copy (expr_isnumber), GNM_EXPR_OP_EQUAL, make_int (1)), gnm_expr_copy (expr_2), gnm_expr_new_constant (value_new_string("")))); dao_set_cell_array_expr (dao, 2, 1, expr); expr_diff = gnm_expr_new_binary (gnm_expr_copy (expr_1), GNM_EXPR_OP_SUB, gnm_expr_copy (expr_2)); dao_set_cell_array_expr (dao, 1, 2, gnm_expr_new_funcall1 (fd_median, gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (gnm_expr_copy (expr_isnumber), GNM_EXPR_OP_EQUAL, make_int (1)), gnm_expr_copy (expr_diff), gnm_expr_new_constant (value_new_string(""))))); expr = gnm_expr_new_funcall1 (fd_sum, gnm_expr_new_binary (gnm_expr_copy (expr_isnumber), GNM_EXPR_OP_MULT, gnm_expr_new_funcall2 (fd_iferror, gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (gnm_expr_copy (expr_diff), GNM_EXPR_OP_NOT_EQUAL, make_cellref (0,-2)), make_int (1), make_int (0)), make_int (0)))); dao_set_cell_array_expr (dao, 1, 4, expr); expr_diff_pred = gnm_expr_new_binary (gnm_expr_copy (expr_diff), GNM_EXPR_OP_SUB, make_cellref (0,-2)); expr_abs = gnm_expr_new_funcall1 (fd_abs, gnm_expr_copy (expr_diff_pred)); expr_big = gnm_expr_new_binary (gnm_expr_new_funcall1 (fd_max, gnm_expr_copy (expr_abs)), GNM_EXPR_OP_ADD, make_int (1)); expr = gnm_expr_new_funcall3 (fd_if, gnm_expr_new_funcall1 (fd_isnumber, expr_1), gnm_expr_new_funcall3 (fd_if, gnm_expr_new_funcall1 (fd_isnumber, expr_2), gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (gnm_expr_copy (expr_diff), GNM_EXPR_OP_EQUAL, make_cellref (0,-2)), gnm_expr_copy (expr_big), expr_abs), gnm_expr_copy (expr_big)), expr_big); expr = gnm_expr_new_funcall3 (fd_rank, gnm_expr_new_unary (GNM_EXPR_OP_UNARY_NEG, expr_diff_pred), expr, make_int (1)); expr = gnm_expr_new_funcall1 (fd_sum, gnm_expr_new_binary (expr_isnumber, GNM_EXPR_OP_MULT, gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (expr_diff, GNM_EXPR_OP_LT, make_cellref (0,-2)), expr, make_int (0)))); dao_set_cell_array_expr (dao, 1, 5, expr); dao_set_cell_expr (dao, 1, 6, gnm_expr_new_binary (gnm_expr_new_binary (gnm_expr_new_binary (make_cellref (0,-2), GNM_EXPR_OP_MULT, gnm_expr_new_binary (make_cellref (0,-2), GNM_EXPR_OP_ADD, make_int (1))), GNM_EXPR_OP_DIV, make_int (2)), GNM_EXPR_OP_SUB, make_cellref (0,-1))); dao_set_cell_expr (dao, 1, 7, gnm_expr_new_funcall2 (fd_min, make_cellref (0,-1), make_cellref (0,-2))); expr_expect = gnm_expr_new_binary (gnm_expr_new_binary (make_cellref (0,-5), GNM_EXPR_OP_MULT, gnm_expr_new_binary (make_cellref (0,-5), GNM_EXPR_OP_ADD, make_int (1))), GNM_EXPR_OP_DIV, make_int (4)); expr_var = gnm_expr_new_binary (gnm_expr_new_binary (gnm_expr_copy (expr_expect), GNM_EXPR_OP_MULT, gnm_expr_new_binary (gnm_expr_new_binary (make_int (2), GNM_EXPR_OP_MULT, make_cellref (0,-5)), GNM_EXPR_OP_ADD, make_int (1))), GNM_EXPR_OP_DIV, make_int (6)); expr = gnm_expr_new_funcall4 (fd_normdist, gnm_expr_new_binary (make_cellref (0,-2), GNM_EXPR_OP_ADD, make_float (0.5)), expr_expect, gnm_expr_new_funcall1 (fd_sqrt, expr_var), gnm_expr_new_constant (value_new_bool (TRUE))); dao_set_cell_expr (dao, 1, 9, gnm_expr_new_funcall3 (fd_if, gnm_expr_new_binary (make_cellref (0,-5), GNM_EXPR_OP_LT, make_int (12)), gnm_expr_new_constant (value_new_error_NA (NULL)), expr)); dao_set_cell_comment (dao, 1, 9, _("This p-value is calculated by a normal approximation.\n" "It is only valid if the sample size is at least 12.")); expr = gnm_expr_new_binary (make_int (2), GNM_EXPR_OP_MULT, make_cellref (0,-1)); dao_set_cell_array_expr (dao, 1, 10, expr); gnm_func_unref (fd_median); gnm_func_unref (fd_if); gnm_func_unref (fd_min); gnm_func_unref (fd_sum); gnm_func_unref (fd_normdist); gnm_func_unref (fd_isnumber); gnm_func_unref (fd_iferror); gnm_func_unref (fd_rank); gnm_func_unref (fd_abs); gnm_func_unref (fd_sqrt); gnm_func_unref (fd_max); value_release (val_1); value_release (val_2); dao_redraw_respan (dao); return FALSE; }