Пример #1
0
int gnumeric_sheet_set_cell(GnumericSheetPtr sheet, int x, int y,
			    GSheetCellPtr cell) {
  if (!cell->is_url) {
    if (cell->all) {
      gnumeric_sheet_set_cell_as_string(sheet,x,y,cell->all);
      return 0;
    }
    gnumeric_sheet_remove_cell(sheet,x,y);
    return 0;
  }

  if (cell->url&&cell->txt) {
    GnmFunc *func = gnm_func_lookup("hyperlink",((Sheet *)sheet)->workbook);
    if (!func) {
      fprintf(stderr,"hyperlink unknown\n");
      exit(1);
    }
    GnmExpr const *expr = 
      gnm_expr_new_funcall2(func,
			    gnm_expr_new_constant(value_new_string(cell->url)),
			    gnm_expr_new_constant(value_new_string(cell->txt)));
    GnmExprTop const *texpr = gnm_expr_top_new(expr);
    GnmCell *cell2 = sheet_cell_get((Sheet*)sheet,x,y);
    if (cell2==NULL) {
      cell2 = sheet_cell_create((Sheet*)sheet,x,y);
    }
    GnmValue *val = value_new_string(cell->txt);
    //sheet_cell_set_value(cell2,val);
    gnm_cell_set_expr_and_value(cell2,texpr,val,0);
  }

  return 0;
}
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;
}
Пример #3
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;
}
Пример #5
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;
}
Пример #6
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;
}
Пример #8
0
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;
}
Пример #9
0
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;
}