fn-math.c 92 KB
Newer Older
Arturo Espinosa's avatar
Arturo Espinosa committed
1
/*
2
 * fn-math.c:  Built in mathematical functions and functions registration
Arturo Espinosa's avatar
Arturo Espinosa committed
3
 *
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
4
 * Authors:
Arturo Espinosa's avatar
Arturo Espinosa committed
5
 *  Miguel de Icaza (miguel@gnu.org)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
6
 *  Jukka-Pekka Iivonen (iivonen@iki.fi)
Arturo Espinosa's avatar
Arturo Espinosa committed
7 8 9
 */
#include <config.h>
#include "func.h"
Jody Goldberg's avatar
Jody Goldberg committed
10 11
#include "func-util.h"
#include "parse-util.h"
12
#include "cell.h"
Jody Goldberg's avatar
Jody Goldberg committed
13 14
#include "sheet.h"
#include "str.h"
Morten Welinder's avatar
Morten Welinder committed
15
#include "mathfunc.h"
Morten Welinder's avatar
Morten Welinder committed
16
#include "rangefunc.h"
Morten Welinder's avatar
Morten Welinder committed
17
#include "collect.h"
Morten Welinder's avatar
Morten Welinder committed
18
#include "auto-format.h"
Donnie Barnes's avatar
Donnie Barnes committed
19

Jody Goldberg's avatar
Jody Goldberg committed
20 21 22
#include <math.h>
#include <string.h>

23 24 25 26 27
typedef struct {
        GSList *list;
        int    num;
} math_sums_t;

28
static Value *
29 30 31 32
callback_function_sumxy (Sheet *sheet, int col, int row,
			 Cell *cell, void *user_data)
{
        math_sums_t *mm = user_data;
Morten Welinder's avatar
Fix.  
Morten Welinder committed
33
        gnum_float  x;
34 35 36
	gpointer    p;

	if (cell == NULL || cell->value == NULL)
37
	        return NULL;
38 39

        switch (cell->value->type) {
40
	case VALUE_ERROR:
Morten Welinder's avatar
Fix.  
Morten Welinder committed
41
		return value_terminate ();
42

43
	case VALUE_BOOLEAN:
44
	        x = cell->value->v_bool.val ? 1 : 0;
45
		break;
46
	case VALUE_INTEGER:
47
	        x = cell->value->v_int.val;
48 49
		break;
	case VALUE_FLOAT:
50
	        x = cell->value->v_float.val;
51
		break;
52
	case VALUE_EMPTY:
53
	default:
54
	        return NULL;
55 56
	}

Morten Welinder's avatar
Fix.  
Morten Welinder committed
57
	p = g_new (gnum_float, 1);
Jody Goldberg's avatar
Jody Goldberg committed
58
	*((gnum_float *) p) = x;
Morten Welinder's avatar
Fix.  
Morten Welinder committed
59
	mm->list = g_slist_append (mm->list, p);
60 61
	mm->num++;

62
	return NULL;
63 64 65 66 67 68 69
}

typedef struct {
        GSList              *list;
        criteria_test_fun_t fun;
        Value               *test_value;
        int                 num;
70 71
        int                 total_num;
        gboolean            actual_range;
Morten Welinder's avatar
Fix.  
Morten Welinder committed
72
        gnum_float          sum;
73
        GSList              *current;
74 75
} math_criteria_t;

76
static Value *
77 78 79 80 81 82
callback_function_criteria (Sheet *sheet, int col, int row,
			    Cell *cell, void *user_data)
{
        math_criteria_t *mm = user_data;
	Value           *v;

83
	mm->total_num++;
84
	if (cell == NULL || cell->value == NULL)
85
	        return NULL;
86 87

        switch (cell->value->type) {
88
	case VALUE_BOOLEAN:
89
	        v = value_new_bool (cell->value->v_bool.val);
90
		break;
91
	case VALUE_INTEGER:
92
	        v = value_new_int (cell->value->v_int.val);
93 94
		break;
	case VALUE_FLOAT:
95
	        v = value_new_float (cell->value->v_float.val);
96 97
		break;
	case VALUE_STRING:
98
	        v = value_new_string (cell->value->v_str.val->str);
99
		break;
100
	case VALUE_EMPTY:
101
	default:
102
	        return NULL;
103 104
	}

Morten Welinder's avatar
Fix.  
Morten Welinder committed
105
	if (mm->fun (v, mm->test_value)) {
106
	        if (mm->actual_range) {
107 108
		        mm->list = g_slist_append (mm->list,
				GINT_TO_POINTER (mm->total_num));
Morten Welinder's avatar
Fix.  
Morten Welinder committed
109
			value_release (v);
110 111
		} else
		        mm->list = g_slist_append (mm->list, v);
112 113
		mm->num++;
	} else
Morten Welinder's avatar
Fix.  
Morten Welinder committed
114
	        value_release (v);
115

116
	return NULL;
117 118
}

119 120
/***************************************************************************/

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
121 122
static char *help_gcd = {
	N_("@FUNCTION=GCD\n"
123
	   "@SYNTAX=GCD(number1,number2,...)\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
124 125

	   "@DESCRIPTION="
126
	   "GCD returns the greatest common divisor of given numbers. "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
127 128 129
	   "\n"
	   "If any of the arguments is less than zero, GCD returns #NUM! "
	   "error. "
130
	   "If any of the arguments is non-integer, it is truncated. "
131
	   "This function is Excel compatible. "
132 133 134 135
	   "\n"
	   "@EXAMPLES=\n"
	   "GCD(470,770) equals to 10.\n"
	   "GCD(470,770,1495) equals to 5.\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
136 137 138 139
	   "\n"
	   "@SEEALSO=LCM")
};

Morten Welinder's avatar
Morten Welinder committed
140
static int
Jody Goldberg's avatar
Jody Goldberg committed
141
range_gcd (const gnum_float *xs, int n, gnum_float *res)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
142
{
Morten Welinder's avatar
Morten Welinder committed
143 144 145 146 147 148 149 150
	if (n > 0) {
		int i;
		int gcd_so_far = 0;

		for (i = 0; i < n; i++) {
			if (xs[i] <= 0)
				return 1;
			else
151 152
				gcd_so_far = gcd ((int)(floor (xs[i])),
						  gcd_so_far);
153
		}
Morten Welinder's avatar
Morten Welinder committed
154 155 156 157 158
		*res = gcd_so_far;
		return 0;
	} else
		return 1;
}
159

Morten Welinder's avatar
Morten Welinder committed
160 161 162 163 164
static Value *
gnumeric_gcd (FunctionEvalInfo *ei, GList *nodes)
{
	return float_range_function (nodes, ei,
				     range_gcd,
165
				     COLLECT_IGNORE_STRINGS |
166 167
				     COLLECT_IGNORE_BOOLS |
				     COLLECT_IGNORE_BLANKS,
168
				     gnumeric_err_NUM);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
169 170
}

171 172
/***************************************************************************/

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
173 174 175 176 177 178 179 180 181 182 183
static char *help_lcm = {
	N_("@FUNCTION=LCM\n"
	   "@SYNTAX=LCM(number1,number2,...)\n"

	   "@DESCRIPTION="
	   "LCM returns the least common multiple of integers.  The least "
	   "common multiple is the smallest positive number that is a "
	   "multiple of all integer arguments given. "
	   "\n"
	   "If any of the arguments is less than one, LCM returns #NUM! "
	   "error. "
184
	   "This function is Excel compatible. "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
185
	   "\n"
186 187 188 189
	   "@EXAMPLES=\n"
	   "LCM(2,13) equlas to 26.\n"
	   "LCM(4,7,5) equals to 140.\n"
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
190 191 192
	   "@SEEALSO=GCD")
};

Morten Welinder's avatar
Fix.  
Morten Welinder committed
193 194
static int
range_lcm (const gnum_float *xs, int n, gnum_float *res)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
195
{
Morten Welinder's avatar
Fix.  
Morten Welinder committed
196 197 198
	if (n > 0) {
		int i;
		int lcm_so_far = 1;
Morten Welinder's avatar
Morten Welinder committed
199

Morten Welinder's avatar
Fix.  
Morten Welinder committed
200 201 202 203 204 205 206 207 208 209 210 211 212 213
		for (i = 0; i < n; i++) {
			gnum_float x = xs[i];
			if (x <= 0)
				return 1;
			else {
				int xi = (int) floor (x);
				lcm_so_far /= gcd (lcm_so_far, xi);
				lcm_so_far *= xi;
			}
		}
		*res = lcm_so_far;
		return 0;
	} else
		return 1;
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
214 215 216
}

static Value *
217
gnumeric_lcm (FunctionEvalInfo *ei, GList *nodes)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
218
{
Morten Welinder's avatar
Fix.  
Morten Welinder committed
219 220 221 222 223 224
	return float_range_function (nodes, ei,
				     range_lcm,
				     COLLECT_IGNORE_STRINGS |
				     COLLECT_IGNORE_BOOLS |
				     COLLECT_IGNORE_BLANKS,
				     gnumeric_err_NUM);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
225 226 227

}

228 229
/***************************************************************************/

230 231 232
static char *help_abs = {
	N_("@FUNCTION=ABS\n"
	   "@SYNTAX=ABS(b1)\n"
233

234
	   "@DESCRIPTION="
235
	   "ABS implements the Absolute Value function:  the result is "
236
	   "to drop the negative sign (if present).  This can be done for "
237
	   "integers and floating point numbers. "
238
	   "This function is Excel compatible. "
239
	   "\n"
240 241 242
	   "@EXAMPLES=\n"
	   "ABS(7) equals 7.\n"
	   "ABS(-3.14) equals 3.14.\n"
243
	   "\n"
244
	   "@SEEALSO=CEIL, FLOOR")
245 246
};

247
static Value *
248
gnumeric_abs (FunctionEvalInfo *ei, Value **args)
249
{
250
	return value_new_float (fabs (value_get_as_float (args [0])));
251
}
252

253 254
/***************************************************************************/

255
static char *help_acos = {
256
	N_("@FUNCTION=ACOS\n"
257
	   "@SYNTAX=ACOS(x)\n"
258

259
	   "@DESCRIPTION="
260
	   "ACOS function calculates the arc cosine of @x; that "
261 262 263
	   "is the value whose cosine is @x.  If @x  falls  outside  the "
	   "range -1 to 1, ACOS fails and returns the NUM! error. "
	   "The value it returns is in radians. "
264
	   "This function is Excel compatible. "
265
	   "\n"
266 267 268
	   "@EXAMPLES=\n"
	   "ACOS(0.1) equals 1.470629.\n"
	   "ACOS(-0.1) equals 1.670964.\n"
269 270
	   "\n"
	   "@SEEALSO=COS, SIN, DEGREES, RADIANS")
271 272
};

273
static Value *
274
gnumeric_acos (FunctionEvalInfo *ei, Value **args)
275
{
Jody Goldberg's avatar
Jody Goldberg committed
276
	gnum_float t;
Donnie Barnes's avatar
Donnie Barnes committed
277

278 279
	t = value_get_as_float (args [0]);
	if ((t < -1.0) || (t > 1.0))
280
		return value_new_error (ei->pos, gnumeric_err_NUM);
281

Michael Meeks's avatar
Michael Meeks committed
282
	return value_new_float (acos (t));
283 284
}

285 286
/***************************************************************************/

287 288
static char *help_acosh = {
	N_("@FUNCTION=ACOSH\n"
289
	   "@SYNTAX=ACOSH(x)\n"
290 291

	   "@DESCRIPTION="
292
	   "ACOSH  function  calculates  the inverse hyperbolic "
293
	   "cosine of @x; that is the value whose hyperbolic cosine is "
Morten Welinder's avatar
Fix.  
Morten Welinder committed
294
	   "@x. If @x is less than 1.0, ACOSH() returns the NUM! error. "
295
	   "This function is Excel compatible. "
Arturo Espinosa's avatar
Arturo Espinosa committed
296
	   "\n"
297 298 299
	   "@EXAMPLES=\n"
	   "ACOSH(2) equals 1.31696.\n"
	   "ACOSH(5.3) equals 2.35183.\n"
300
	   "\n"
301
	   "@SEEALSO=ACOS, ASINH, DEGREES, RADIANS ")
Donnie Barnes's avatar
Donnie Barnes committed
302 303
};

304
static Value *
305
gnumeric_acosh (FunctionEvalInfo *ei, Value **args)
306
{
Jody Goldberg's avatar
Jody Goldberg committed
307
	gnum_float t;
308

309 310
	t = value_get_as_float (args [0]);
	if (t < 1.0)
311
		return value_new_error (ei->pos, gnumeric_err_NUM);
312

Michael Meeks's avatar
Michael Meeks committed
313
	return value_new_float (acosh (t));
314 315
}

316 317
/***************************************************************************/

318 319
static char *help_asin = {
	N_("@FUNCTION=ASIN\n"
320
	   "@SYNTAX=ASIN(x)\n"
321 322

	   "@DESCRIPTION="
323
	   "ASIN function calculates the arc sine of @x; that is "
324
	   "the value whose sine is @x. If @x falls outside  the  range "
325
	   "-1 to 1, ASIN fails and returns the NUM! error. "
326
	   "This function is Excel compatible. "
327
	   "\n"
328 329 330
	   "@EXAMPLES=\n"
	   "ASIN(0.5) equals 0.523599.\n"
	   "ASIN(1) equals 1.570797.\n"
331 332 333 334
	   "\n"
	   "@SEEALSO=SIN, COS, ASINH, DEGREES, RADIANS")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
335
static Value *
336
gnumeric_asin (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
337
{
Jody Goldberg's avatar
Jody Goldberg committed
338
	gnum_float t;
Arturo Espinosa's avatar
Arturo Espinosa committed
339

340 341
	t = value_get_as_float (args [0]);
	if ((t < -1.0) || (t > 1.0))
342
		return value_new_error (ei->pos, gnumeric_err_NUM);
343

Michael Meeks's avatar
Michael Meeks committed
344
	return value_new_float (asin (t));
Arturo Espinosa's avatar
Arturo Espinosa committed
345 346
}

347 348
/***************************************************************************/

349 350
static char *help_asinh = {
	N_("@FUNCTION=ASINH\n"
351
	   "@SYNTAX=ASINH(x)\n"
352 353

	   "@DESCRIPTION="
354 355
	   "ASINH function calculates the inverse hyperbolic sine of @x; "
	   "that is the value whose hyperbolic sine is @x. "
356
	   "This function is Excel compatible. "
357
	   "\n"
358
	   "@EXAMPLES=\n"
359 360
	   "ASINH(0.5) equals 0.481212.\n"
	   "ASINH(1.0) equals 0.881374.\n"
361
	   "\n"
362
	   "@SEEALSO=ASIN, ACOSH, SIN, COS, DEGREES, RADIANS")
363 364
};

Arturo Espinosa's avatar
Arturo Espinosa committed
365
static Value *
366
gnumeric_asinh (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
367
{
368
	return value_new_float (asinh (value_get_as_float (args [0])));
369
}
Arturo Espinosa's avatar
Arturo Espinosa committed
370

371 372
/***************************************************************************/

373 374
static char *help_atan = {
	N_("@FUNCTION=ATAN\n"
375
	   "@SYNTAX=ATAN(x)\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
376

377
	   "@DESCRIPTION="
378
	   "ATAN function calculates the arc tangent of @x; that "
379 380
	   "is the value whose tangent is @x. "
	   "Return value is in radians. "
381
	   "This function is Excel compatible. "
382
	   "\n"
383
	   "@EXAMPLES=\n"
384
	   "ATAN(0.5) equals 0,463648.\n"
385
	   "ATAN(1) equals 0,785398.\n"
386 387 388
	   "\n"
	   "@SEEALSO=TAN, COS, SIN, DEGREES, RADIANS")
};
Arturo Espinosa's avatar
Arturo Espinosa committed
389 390

static Value *
391
gnumeric_atan (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
392
{
393
	return value_new_float (atan (value_get_as_float (args [0])));
Arturo Espinosa's avatar
Arturo Espinosa committed
394 395
}

396 397
/***************************************************************************/

398 399
static char *help_atanh = {
	N_("@FUNCTION=ATANH\n"
400
	   "@SYNTAX=ATANH(x)\n"
401 402

	   "@DESCRIPTION="
403 404 405 406
	   "ATANH function calculates the inverse hyperbolic tangent "
	   "of @x; that is the value whose hyperbolic tangent is @x. "
	   "If the absolute value of @x is greater than 1.0, ATANH "
	   "returns NUM! error. "
407
	   "This function is Excel compatible. "
408
	   "\n"
409
	   "@EXAMPLES=\n"
410 411
	   "ATANH(0.5) equals 0.549306.\n "
	   "ATANH(0.8) equals 1.098612.\n"
412 413 414 415
	   "\n"
	   "@SEEALSO=ATAN, TAN, SIN, COS, DEGREES, RADIANS")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
416
static Value *
417
gnumeric_atanh (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
418
{
Jody Goldberg's avatar
Jody Goldberg committed
419
	gnum_float t;
Arturo Espinosa's avatar
Arturo Espinosa committed
420

421 422
	t = value_get_as_float (args [0]);
	if ((t <= -1.0) || (t >= 1.0))
423
		return value_new_error (ei->pos, gnumeric_err_NUM);
424 425

	return value_new_float (atanh (value_get_as_float (args [0])));
Arturo Espinosa's avatar
Arturo Espinosa committed
426 427
}

428 429
/***************************************************************************/

430
static char *help_atan2 = {
431
	N_("@FUNCTION=ATAN2\n"
432 433 434
	   "@SYNTAX=ATAN2(b1,b2)\n"

	   "@DESCRIPTION="
435 436 437
	   "ATAN2 function calculates the arc tangent of the two "
	   "variables @b1 and @b2.  It is similar to calculating the arc "
	   "tangent of @b2 / @b1, except that the signs of both arguments "
438
	   "are used to determine the quadrant of the result. "
439
	   "The result is in radians. "
440
	   "This function is Excel compatible. "
441
	   "\n"
442
	   "@EXAMPLES=\n"
443 444
	   "ATAN2(0.5,1.0) equals 1.107149.\n"
	   "ATAN2(-0.5,2.0) equals 1.815775.\n"
445 446 447 448
	   "\n"
	   "@SEEALSO=ATAN, ATANH, COS, SIN, DEGREES, RADIANS")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
449
static Value *
450
gnumeric_atan2 (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
451
{
Morten Welinder's avatar
Morten Welinder committed
452 453
	return value_new_float (atan2 (value_get_as_float (args [1]),
				       value_get_as_float (args [0])));
Arturo Espinosa's avatar
Arturo Espinosa committed
454 455
}

456 457
/***************************************************************************/

458 459
static char *help_ceil = {
	N_("@FUNCTION=CEIL\n"
460
	   "@SYNTAX=CEIL(x)\n"
461

462 463 464
	   "@DESCRIPTION="
	   "CEIL function rounds @x up to the next nearest integer.\n"
	   "This function is Excel compatible. "
465
	   "\n"
466
	   "@EXAMPLES=\n"
467 468 469
	   "CEIL(0.4) equals 1.\n"
	   "CEIL(-1.1) equals -1.\n"
	   "CEIL(-2.9) equals -2.\n"
470
	   "\n"
471 472 473
	   "@SEEALSO=ABS, FLOOR, INT")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
474
static Value *
475
gnumeric_ceil (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
476
{
477
	return value_new_float (gnumeric_fake_ceil (value_get_as_float (args [0])));
478 479
}

480 481
/***************************************************************************/

482 483 484 485 486
static char *help_countif = {
	N_("@FUNCTION=COUNTIF\n"
	   "@SYNTAX=COUNTIF(range,criteria)\n"

	   "@DESCRIPTION="
487 488
	   "COUNTIF function counts the number of cells in the given @range "
	   "that meet the given @criteria. "
489
	   "This function is Excel compatible. "
490
	   "\n"
491
	   "@EXAMPLES=\n"
492 493 494 495 496 497
	   "Let us assume that the cells A1, A2, ..., A5 contain numbers "
	   "23, 27, 28, 33, and 39.  Then\n"
	   "COUNTIF(A1:A5,\"<=28\") equals 3.\n"
	   "COUNTIF(A1:A5,\"<28\") equals 2.\n"
	   "COUNTIF(A1:A5,\"28\") equals 1.\n"
	   "COUNTIF(A1:A5,\">28\") equals 2.\n"
498
	   "\n"
499 500 501 502
	   "@SEEALSO=COUNT,SUMIF")
};

static Value *
503
gnumeric_countif (FunctionEvalInfo *ei, Value **argv)
504 505
{
        Value           *range = argv[0];
506
	Value           *tmpval = NULL;
507
	Sheet           *sheet;
508

509 510
	math_criteria_t  items;
	Value           *ret;
511 512 513
	GSList          *list;

	items.num  = 0;
514
	items.total_num = 0;
515
	items.list = NULL;
516
	items.actual_range = FALSE;
517

Morten Welinder's avatar
Fix.  
Morten Welinder committed
518
	if ((!VALUE_IS_NUMBER (argv[1]) && argv[1]->type != VALUE_STRING)
519
	    || (range->type != VALUE_CELLRANGE))
520
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
521

Morten Welinder's avatar
Fix.  
Morten Welinder committed
522
	if (VALUE_IS_NUMBER (argv[1])) {
523 524
	        items.fun = (criteria_test_fun_t) criteria_test_equal;
		items.test_value = argv[1];
Morten Welinder's avatar
Morten Welinder committed
525
	} else {
Morten Welinder's avatar
Fix.  
Morten Welinder committed
526 527
	        parse_criteria (argv[1]->v_str.val->str,
				&items.fun, &items.test_value);
528
		tmpval = items.test_value;
Morten Welinder's avatar
Morten Welinder committed
529
	}
530

531
	sheet = eval_sheet (range->v_range.cell.a.sheet, ei->pos->sheet);
532
	ret = sheet_foreach_cell_in_range (sheet,
533
		TRUE, /* Ignore empty cells */
534 535 536 537
		range->v_range.cell.a.col,
		range->v_range.cell.a.row,
		range->v_range.cell.b.col,
		range->v_range.cell.b.row,
538 539
		callback_function_criteria,
		&items);
Morten Welinder's avatar
Morten Welinder committed
540

541 542
	if (tmpval)
		value_release (tmpval);
Morten Welinder's avatar
Morten Welinder committed
543

544
	if (ret != NULL)
545
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
546 547 548 549

        list = items.list;

	while (list != NULL) {
Morten Welinder's avatar
Morten Welinder committed
550
		value_release (list->data);
551 552
		list = list->next;
	}
Morten Welinder's avatar
Fix.  
Morten Welinder committed
553
	g_slist_free (items.list);
554

Michael Meeks's avatar
Michael Meeks committed
555
	return value_new_int (items.num);
556 557
}

558 559
/***************************************************************************/

560 561
static char *help_sumif = {
	N_("@FUNCTION=SUMIF\n"
562
	   "@SYNTAX=SUMIF(range,criteria[,actual_range])\n"
563 564

	   "@DESCRIPTION="
565 566
	   "SUMIF function sums the values in the given @range that meet "
	   "the given @criteria.  If @actual_range is given, SUMIF sums "
567
	   "the values in the @actual_range whose corresponding components "
568
	   "in @range meet the given @criteria. "
569
	   "This function is Excel compatible. "
570
	   "\n"
571
	   "@EXAMPLES=\n"
572 573 574 575 576 577 578
	   "Let us assume that the cells A1, A2, ..., A5 contain numbers "
	   "23, 27, 28, 33, and 39.  Then\n"
	   "SUMIF(A1:A5,\"<=28\") equals 78.\n"
	   "SUMIF(A1:A5,\"<28\") equals 50.\n"
	   "In addition, if the cells B1, B2, ..., B5 hold numbers "
	   "5, 3, 2, 6, and 7 then:\n"
	   "SUMIF(A1:A5,\"<=27\",B1:B5) equals 8.\n"
579
	   "\n"
580
	   "@SEEALSO=COUNTIF, SUM")
581 582
};

583
static Value *
584 585 586 587
callback_function_sumif (Sheet *sheet, int col, int row,
			 Cell *cell, void *user_data)
{
        math_criteria_t *mm = user_data;
Morten Welinder's avatar
Fix.  
Morten Welinder committed
588
	gnum_float       v = 0.;
589

590
	/* If we have finished the list there is no need to bother */
591
	if (mm->current == NULL)
592
	        return NULL;
593

594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611
	/* We have not reached the next selected element yet.
	 * This implies that summing a range containing an error
	 * where the criteria does not select the error is OK.
	 */
	if (++(mm->total_num) != GPOINTER_TO_INT (mm->current->data))
		return NULL;

	if (cell != NULL && cell->value != NULL)
		switch (cell->value->type) {
		case VALUE_BOOLEAN:	v = cell->value->v_bool.val ? 1 : 0; break;
		case VALUE_INTEGER:	v = cell->value->v_int.val; break;
		case VALUE_FLOAT:	v = cell->value->v_float.val; break;

		case VALUE_STRING:
		case VALUE_EMPTY:
			break;

		default:
Morten Welinder's avatar
Fix.  
Morten Welinder committed
612
			return value_terminate ();
613 614 615
		}
	mm->sum += v;
	mm->current = mm->current->next;
616

617
	return NULL;
618 619
}

620
static Value *
621
gnumeric_sumif (FunctionEvalInfo *ei, Value **argv)
622
{
623 624 625
        Value          *range = argv[0];
	Value          *actual_range = argv[2];
	Value          *tmpval = NULL;
626

627
	math_criteria_t items;
628
	Value          *ret;
Morten Welinder's avatar
Fix.  
Morten Welinder committed
629
	gnum_float      sum;
630
	GSList         *list;
631 632

	items.num  = 0;
633
	items.total_num = 0;
634 635
	items.list = NULL;

636 637
	if (range->type != VALUE_CELLRANGE ||
	    !(VALUE_IS_NUMBER (argv[1]) || argv[1]->type == VALUE_STRING))
638
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
639

640 641 642
	/* If the criteria is a number test for equality else the parser
	 * will evaluate the condition as a string
	 */
Morten Welinder's avatar
Fix.  
Morten Welinder committed
643
	if (VALUE_IS_NUMBER (argv[1])) {
644 645
	        items.fun = (criteria_test_fun_t) criteria_test_equal;
		items.test_value = argv[1];
Morten Welinder's avatar
Morten Welinder committed
646
	} else {
Morten Welinder's avatar
Fix.  
Morten Welinder committed
647 648
	        parse_criteria (argv[1]->v_str.val->str,
				&items.fun, &items.test_value);
649
		tmpval = items.test_value;
Morten Welinder's avatar
Morten Welinder committed
650
	}
651

652
	items.actual_range = (actual_range != NULL);
653

654
	ret = sheet_foreach_cell_in_range (
655
		eval_sheet (range->v_range.cell.a.sheet, ei->pos->sheet),
656
		/*
657
		 * Do not ignore empty cells if there is a
658 659 660
		 * target range.  We need the orders of the source values to
		 * line up with the values of the target range.
		 */
661 662
		actual_range == NULL,

663 664 665 666
		range->v_range.cell.a.col,
		range->v_range.cell.a.row,
		range->v_range.cell.b.col,
		range->v_range.cell.b.row,
667 668
		callback_function_criteria,
		&items);
Morten Welinder's avatar
Morten Welinder committed
669

670 671
	if (tmpval)
		value_release (tmpval);
Morten Welinder's avatar
Morten Welinder committed
672

673
	if (ret != NULL)
674
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
675

676 677 678
	if (actual_range == NULL) {
	        list = items.list;
		sum = 0;
679

680 681
		while (list != NULL) {
		        Value *v = list->data;
682

683 684 685 686 687 688 689 690
			if (v != NULL)
			        sum += value_get_as_float (v);
			value_release (v);
			list = list->next;
		}
	} else {
	      items.current = items.list;
	      items.sum = items.total_num = 0;
691
 	      ret = sheet_foreach_cell_in_range (
692 693
			eval_sheet (actual_range->v_range.cell.a.sheet,
				    ei->pos->sheet),
694 695 696
			/* Empty cells too.  Criteria and results must align */
			FALSE,

697 698 699 700
			actual_range->v_range.cell.a.col,
			actual_range->v_range.cell.a.row,
			actual_range->v_range.cell.b.col,
			actual_range->v_range.cell.b.row,
701 702
			callback_function_sumif,
			&items);
703
	      sum = items.sum;
704 705
	}

Morten Welinder's avatar
Fix.  
Morten Welinder committed
706
	g_slist_free (items.list);
707

Michael Meeks's avatar
Michael Meeks committed
708
	return value_new_float (sum);
709 710
}

711 712
/***************************************************************************/

713 714 715 716
static char *help_ceiling = {
	N_("@FUNCTION=CEILING\n"
	   "@SYNTAX=CEILING(x,significance)\n"

717 718
	   "@DESCRIPTION="
	   "CEILING function rounds @x up to the nearest multiple of "
719
	   "@significance. "
720
	   "\n"
721 722 723 724
	   "If @x or @significance is non-numeric CEILING returns "
	   "#VALUE! error. "
	   "If @x and @significance have different signs CEILING returns "
	   "#NUM! error. "
725
	   "This function is Excel compatible. "
726
	   "\n"
727
	   "@EXAMPLES=\n"
728 729
	   "CEILING(2.43,1) equals 3.\n"
	   "CEILING(123.123,3) equals 126.\n"
730
	   "\n"
731 732 733 734
	   "@SEEALSO=CEIL")
};

static Value *
735
gnumeric_ceiling (FunctionEvalInfo *ei, Value **argv)
736
{
Jody Goldberg's avatar
Jody Goldberg committed
737
        gnum_float number, s;
738

Morten Welinder's avatar
Morten Welinder committed
739 740 741 742 743
	number = value_get_as_float (argv[0]);
	if (argv[1] == NULL)
	        s = (number >= 0) ? 1.0 : -1.0;
	else {
	        s = value_get_as_float (argv[1]);
744
	}
Morten Welinder's avatar
Morten Welinder committed
745

746
	if (s == 0 || number / s < 0)
747
		return value_new_error (ei->pos, gnumeric_err_NUM);
748

749
	return value_new_float (gnumeric_fake_ceil (number / s) * s);
750 751
}

752 753
/***************************************************************************/

754 755
static char *help_cos = {
	N_("@FUNCTION=COS\n"
756
	   "@SYNTAX=COS(x)\n"
757 758

	   "@DESCRIPTION="
759 760
	   "COS function returns the cosine of @x, where @x is given "
           "in radians. "
761
	   "This function is Excel compatible. "
762 763
	   "\n"
	   "@EXAMPLES=\n"