static GnmExpr const *
xlsx_func_map_in (GnmConventions const *convs,
G_GNUC_UNUSED Workbook *scope,
char const *name, GnmExprList *args)
{
XLSXExprConventions const *xconv = (XLSXExprConventions const *)convs;
GnmExpr const * (*handler) (GnmConventions const *convs, Workbook *scope,
GnmExprList *args);
GnmFunc *f;
char const *new_name;
if (0 == g_ascii_strncasecmp (name, "_xlfn.", 6)) {
if (NULL != xconv->xlfn_map &&
NULL != (new_name = g_hash_table_lookup (xconv->xlfn_map, name + 6)))
name = new_name;
else
name = name + 6;
handler = g_hash_table_lookup (xconv->xlfn_handler_map, name);
if (handler != NULL) {
GnmExpr const * res = handler (convs, scope, args);
if (res != NULL)
return res;
}
} else if (0 == g_ascii_strncasecmp (name, "_xlfnodf.", 9))
/* This should at most happen for ODF functions incorporated */
/* in an xlsx file, we should perform the appropriate translation! */
name = name + 9;
else if (0 == g_ascii_strncasecmp (name, "_xlfngnumeric.", 9))
/* These are Gnumeric's own functions */
name = name + 14;
f = gnm_func_lookup_or_add_placeholder (name);
return gnm_expr_new_funcall (f, args);
}
static GnmExpr const *
xlsx_func_binominv_handler (G_GNUC_UNUSED GnmConventions const *convs, G_GNUC_UNUSED Workbook *scope, GnmExprList *args)
/* BINOM.INV(a,b,c) --> R.QBINOM(c,a,b) */
{
GnmFunc *f = gnm_func_lookup_or_add_placeholder ("r.qbinom");
GnmExprList *arg;
arg = g_slist_nth (args, 2);
args = g_slist_remove_link (args, arg);
args = g_slist_concat (arg, args);
return gnm_expr_new_funcall (f, args);
}
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 GnmExpr const *
xlsx_func_dist_handler (GnmExprList *args, guint n_args, char const *name, char const *name_p, char const *name_d)
{
if (gnm_expr_list_length (args) != n_args) {
GnmFunc *f = gnm_func_lookup_or_add_placeholder (name);
return gnm_expr_new_funcall (f, args);
} else {
GnmFunc *f_if = gnm_func_lookup_or_add_placeholder ("if");
GnmFunc *f_p = gnm_func_lookup_or_add_placeholder (name_p);
GnmFunc *f_d = gnm_func_lookup_or_add_placeholder (name_d);
GnmExprList *arg_cum, *args_c;
GnmExpr const *cum;
GnmValue const *constant;
arg_cum = g_slist_nth (args, n_args - 1);
args = g_slist_remove_link (args, arg_cum);
cum = arg_cum->data;
gnm_expr_list_free (arg_cum);
constant = gnm_expr_get_constant (cum);
if (constant == NULL || !VALUE_IS_NUMBER (constant)) {
args_c = gnm_expr_list_copy (args);
return gnm_expr_new_funcall3
(f_if, cum,
gnm_expr_new_funcall (f_p, args),
gnm_expr_new_funcall (f_d, args_c));
} else if (value_is_zero (constant)) {
gnm_expr_free (cum);
return gnm_expr_new_funcall (f_d, args);
} else {
gnm_expr_free (cum);
return gnm_expr_new_funcall (f_p, args);
}
}
}
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 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_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;
}