fn-math.c 92.6 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 10 11 12 13 14 15 16 17 18 19 20
#include <gnumeric-config.h>
#include <gnumeric.h>
#include <func.h>

#include <func-util.h>
#include <parse-util.h>
#include <cell.h>
#include <sheet.h>
#include <str.h>
#include <mathfunc.h>
#include <rangefunc.h>
#include <collect.h>
#include <auto-format.h>
Donnie Barnes's avatar
Donnie Barnes committed
21

Jody Goldberg's avatar
Jody Goldberg committed
22 23 24
#include <math.h>
#include <string.h>

25 26 27 28 29
typedef struct {
        GSList *list;
        int    num;
} math_sums_t;

30
static Value *
31 32 33 34
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
35
        gnum_float  x;
36 37 38
	gpointer    p;

	if (cell == NULL || cell->value == NULL)
39
	        return NULL;
40 41

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

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

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

64
	return NULL;
65 66 67 68 69 70 71
}

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

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

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

        switch (cell->value->type) {
90
	case VALUE_BOOLEAN:
91 92 93
	case VALUE_INTEGER:
	case VALUE_FLOAT:
	case VALUE_STRING:
Morten Welinder's avatar
Morten Welinder committed
94
	        v = value_duplicate (cell->value);
95
		break;
96
	case VALUE_EMPTY:
97
	default:
98
	        return NULL;
99 100
	}

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

112
	return NULL;
113 114
}

115 116
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
117
static const char *help_gcd = {
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
118
	N_("@FUNCTION=GCD\n"
119
	   "@SYNTAX=GCD(number1,number2,...)\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
120 121

	   "@DESCRIPTION="
122
	   "GCD returns the greatest common divisor of given numbers. "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
123 124 125
	   "\n"
	   "If any of the arguments is less than zero, GCD returns #NUM! "
	   "error. "
126
	   "If any of the arguments is non-integer, it is truncated.\n"
127
	   "This function is Excel compatible. "
128 129 130 131
	   "\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
132 133 134 135
	   "\n"
	   "@SEEALSO=LCM")
};

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

		for (i = 0; i < n; i++) {
			if (xs[i] <= 0)
				return 1;
			else
147
				gcd_so_far = gcd ((int)(floorgnum (xs[i])),
148
						  gcd_so_far);
149
		}
Morten Welinder's avatar
Morten Welinder committed
150 151 152 153 154
		*res = gcd_so_far;
		return 0;
	} else
		return 1;
}
155

Morten Welinder's avatar
Morten Welinder committed
156
static Value *
157
gnumeric_gcd (FunctionEvalInfo *ei, ExprList *nodes)
Morten Welinder's avatar
Morten Welinder committed
158 159 160
{
	return float_range_function (nodes, ei,
				     range_gcd,
161
				     COLLECT_IGNORE_STRINGS |
162 163
				     COLLECT_IGNORE_BOOLS |
				     COLLECT_IGNORE_BLANKS,
164
				     gnumeric_err_NUM);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
165 166
}

167 168
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
169
static const char *help_lcm = {
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
170 171 172 173 174 175 176 177 178
	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! "
179
	   "error.\n"
180
	   "This function is Excel compatible. "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
181
	   "\n"
182
	   "@EXAMPLES=\n"
183
	   "LCM(2,13) equals to 26.\n"
184 185
	   "LCM(4,7,5) equals to 140.\n"
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
186 187 188
	   "@SEEALSO=GCD")
};

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

Morten Welinder's avatar
Fix.  
Morten Welinder committed
196 197 198 199 200
		for (i = 0; i < n; i++) {
			gnum_float x = xs[i];
			if (x <= 0)
				return 1;
			else {
201
				int xi = (int) floorgnum (x);
Morten Welinder's avatar
Fix.  
Morten Welinder committed
202 203 204 205 206 207 208 209
				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
210 211 212
}

static Value *
213
gnumeric_lcm (FunctionEvalInfo *ei, ExprList *nodes)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
214
{
Morten Welinder's avatar
Fix.  
Morten Welinder committed
215 216 217 218 219 220
	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
221 222 223

}

224 225
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
226
static const char *help_abs = {
227 228
	N_("@FUNCTION=ABS\n"
	   "@SYNTAX=ABS(b1)\n"
229

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

243
static Value *
244
gnumeric_abs (FunctionEvalInfo *ei, Value **args)
245
{
Morten Welinder's avatar
Morten Welinder committed
246
	return value_new_float (gnumabs (value_get_as_float (args [0])));
247
}
248

249 250
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
251
static const char *help_acos = {
252
	N_("@FUNCTION=ACOS\n"
253
	   "@SYNTAX=ACOS(x)\n"
254

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

269
static Value *
270
gnumeric_acos (FunctionEvalInfo *ei, Value **args)
271
{
Jody Goldberg's avatar
Jody Goldberg committed
272
	gnum_float t;
Donnie Barnes's avatar
Donnie Barnes committed
273

274 275
	t = value_get_as_float (args [0]);
	if ((t < -1.0) || (t > 1.0))
276
		return value_new_error (ei->pos, gnumeric_err_NUM);
277

278
	return value_new_float (acosgnum (t));
279 280
}

281 282
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
283
static const char *help_acosh = {
284
	N_("@FUNCTION=ACOSH\n"
285
	   "@SYNTAX=ACOSH(x)\n"
286 287

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

300
static Value *
301
gnumeric_acosh (FunctionEvalInfo *ei, Value **args)
302
{
Jody Goldberg's avatar
Jody Goldberg committed
303
	gnum_float t;
304

305 306
	t = value_get_as_float (args [0]);
	if (t < 1.0)
307
		return value_new_error (ei->pos, gnumeric_err_NUM);
308

309
	return value_new_float (acoshgnum (t));
310 311
}

312 313
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
314
static const char *help_asin = {
315
	N_("@FUNCTION=ASIN\n"
316
	   "@SYNTAX=ASIN(x)\n"
317 318

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

Arturo Espinosa's avatar
Arturo Espinosa committed
331
static Value *
332
gnumeric_asin (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
333
{
Jody Goldberg's avatar
Jody Goldberg committed
334
	gnum_float t;
Arturo Espinosa's avatar
Arturo Espinosa committed
335

336 337
	t = value_get_as_float (args [0]);
	if ((t < -1.0) || (t > 1.0))
338
		return value_new_error (ei->pos, gnumeric_err_NUM);
339

340
	return value_new_float (asingnum (t));
Arturo Espinosa's avatar
Arturo Espinosa committed
341 342
}

343 344
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
345
static const char *help_asinh = {
346
	N_("@FUNCTION=ASINH\n"
347
	   "@SYNTAX=ASINH(x)\n"
348 349

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

Arturo Espinosa's avatar
Arturo Espinosa committed
361
static Value *
362
gnumeric_asinh (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
363
{
364
	return value_new_float (asinhgnum (value_get_as_float (args [0])));
365
}
Arturo Espinosa's avatar
Arturo Espinosa committed
366

367 368
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
369
static const char *help_atan = {
370
	N_("@FUNCTION=ATAN\n"
371
	   "@SYNTAX=ATAN(x)\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
372

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

static Value *
387
gnumeric_atan (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
388
{
389
	return value_new_float (atangnum (value_get_as_float (args [0])));
Arturo Espinosa's avatar
Arturo Espinosa committed
390 391
}

392 393
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
394
static const char *help_atanh = {
395
	N_("@FUNCTION=ATANH\n"
396
	   "@SYNTAX=ATANH(x)\n"
397 398

	   "@DESCRIPTION="
399 400 401
	   "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 "
402
	   "returns #NUM! error.\n"
403
	   "This function is Excel compatible."
404
	   "\n"
405
	   "@EXAMPLES=\n"
406 407
	   "ATANH(0.5) equals 0.549306.\n "
	   "ATANH(0.8) equals 1.098612.\n"
408 409 410 411
	   "\n"
	   "@SEEALSO=ATAN, TAN, SIN, COS, DEGREES, RADIANS")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
412
static Value *
413
gnumeric_atanh (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
414
{
Jody Goldberg's avatar
Jody Goldberg committed
415
	gnum_float t;
Arturo Espinosa's avatar
Arturo Espinosa committed
416

417 418
	t = value_get_as_float (args [0]);
	if ((t <= -1.0) || (t >= 1.0))
419
		return value_new_error (ei->pos, gnumeric_err_NUM);
420

421
	return value_new_float (atanhgnum (value_get_as_float (args [0])));
Arturo Espinosa's avatar
Arturo Espinosa committed
422 423
}

424 425
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
426
static const char *help_atan2 = {
427
	N_("@FUNCTION=ATAN2\n"
428 429 430
	   "@SYNTAX=ATAN2(b1,b2)\n"

	   "@DESCRIPTION="
431 432 433
	   "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 "
434
	   "are used to determine the quadrant of the result. "
435
	   "The result is in radians.\n"
436
	   "This function is Excel compatible. "
437
	   "\n"
438
	   "@EXAMPLES=\n"
439 440
	   "ATAN2(0.5,1.0) equals 1.107149.\n"
	   "ATAN2(-0.5,2.0) equals 1.815775.\n"
441 442 443 444
	   "\n"
	   "@SEEALSO=ATAN, ATANH, COS, SIN, DEGREES, RADIANS")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
445
static Value *
446
gnumeric_atan2 (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
447
{
448 449
	return value_new_float (atan2gnum (value_get_as_float (args [1]),
					   value_get_as_float (args [0])));
Arturo Espinosa's avatar
Arturo Espinosa committed
450 451
}

452 453
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
454
static const char *help_ceil = {
455
	N_("@FUNCTION=CEIL\n"
456
	   "@SYNTAX=CEIL(x)\n"
457

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

Arturo Espinosa's avatar
Arturo Espinosa committed
470
static Value *
471
gnumeric_ceil (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
472
{
473
	return value_new_float (gnumeric_fake_ceil (value_get_as_float (args [0])));
474 475
}

476 477
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
478
static const char *help_countif = {
479 480 481 482
	N_("@FUNCTION=COUNTIF\n"
	   "@SYNTAX=COUNTIF(range,criteria)\n"

	   "@DESCRIPTION="
483
	   "COUNTIF function counts the number of cells in the given @range "
484 485
	   "that meet the given @criteria.\n"
	   "This function is Excel compatible."
486
	   "\n"
487
	   "@EXAMPLES=\n"
488 489 490 491 492 493
	   "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"
494
	   "\n"
495 496 497 498
	   "@SEEALSO=COUNT,SUMIF")
};

static Value *
499
gnumeric_countif (FunctionEvalInfo *ei, Value **argv)
500 501
{
        Value           *range = argv[0];
502
	Value           *tmpval = NULL;
503
	Sheet           *sheet;
504

505 506
	math_criteria_t  items;
	Value           *ret;
507 508 509
	GSList          *list;

	items.num  = 0;
510
	items.total_num = 0;
511
	items.list = NULL;
512
	items.actual_range = FALSE;
513

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

Morten Welinder's avatar
Fix.  
Morten Welinder committed
518
	if (VALUE_IS_NUMBER (argv[1])) {
519 520
	        items.fun = (criteria_test_fun_t) criteria_test_equal;
		items.test_value = argv[1];
Morten Welinder's avatar
Morten Welinder committed
521
	} else {
Morten Welinder's avatar
Morten Welinder committed
522
	        parse_criteria (value_peek_string (argv[1]),
Morten Welinder's avatar
Fix.  
Morten Welinder committed
523
				&items.fun, &items.test_value);
524
		tmpval = items.test_value;
Morten Welinder's avatar
Morten Welinder committed
525
	}
526

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

537 538
	if (tmpval)
		value_release (tmpval);
Morten Welinder's avatar
Morten Welinder committed
539

540
	if (ret != NULL)
541
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
542 543 544 545

        list = items.list;

	while (list != NULL) {
Morten Welinder's avatar
Morten Welinder committed
546
		value_release (list->data);
547 548
		list = list->next;
	}
Morten Welinder's avatar
Fix.  
Morten Welinder committed
549
	g_slist_free (items.list);
550

Michael Meeks's avatar
Michael Meeks committed
551
	return value_new_int (items.num);
552 553
}

554 555
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
556
static const char *help_sumif = {
557
	N_("@FUNCTION=SUMIF\n"
558
	   "@SYNTAX=SUMIF(range,criteria[,actual_range])\n"
559 560

	   "@DESCRIPTION="
561 562
	   "SUMIF function sums the values in the given @range that meet "
	   "the given @criteria.  If @actual_range is given, SUMIF sums "
563
	   "the values in the @actual_range whose corresponding components "
564
	   "in @range meet the given @criteria.\n"
565
	   "This function is Excel compatible. "
566
	   "\n"
567
	   "@EXAMPLES=\n"
568 569 570 571 572 573 574
	   "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"
575
	   "\n"
576
	   "@SEEALSO=COUNTIF, SUM")
577 578
};

579
static Value *
580 581 582 583
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
584
	gnum_float       v = 0.;
585

586
	/* If we have finished the list there is no need to bother */
587
	if (mm->current == NULL)
588
	        return NULL;
589

590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607
	/* 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
608
			return value_terminate ();
609 610 611
		}
	mm->sum += v;
	mm->current = mm->current->next;
612

613
	return NULL;
614 615
}

616
static Value *
617
gnumeric_sumif (FunctionEvalInfo *ei, Value **argv)
618
{
619 620 621
        Value          *range = argv[0];
	Value          *actual_range = argv[2];
	Value          *tmpval = NULL;
622

623
	math_criteria_t items;
624
	Value          *ret;
Morten Welinder's avatar
Fix.  
Morten Welinder committed
625
	gnum_float      sum;
626
	GSList         *list;
627 628

	items.num  = 0;
629
	items.total_num = 0;
630 631
	items.list = NULL;

632 633
	if (range->type != VALUE_CELLRANGE ||
	    !(VALUE_IS_NUMBER (argv[1]) || argv[1]->type == VALUE_STRING))
634
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
635

636 637 638
	/* 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
639
	if (VALUE_IS_NUMBER (argv[1])) {
640 641
	        items.fun = (criteria_test_fun_t) criteria_test_equal;
		items.test_value = argv[1];
Morten Welinder's avatar
Morten Welinder committed
642
	} else {
Morten Welinder's avatar
Morten Welinder committed
643
	        parse_criteria (value_peek_string (argv[1]),
Morten Welinder's avatar
Fix.  
Morten Welinder committed
644
				&items.fun, &items.test_value);
645
		tmpval = items.test_value;
Morten Welinder's avatar
Morten Welinder committed
646
	}
647

648
	items.actual_range = (actual_range != NULL);
649

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

659 660 661 662
		range->v_range.cell.a.col,
		range->v_range.cell.a.row,
		range->v_range.cell.b.col,
		range->v_range.cell.b.row,
663 664
		callback_function_criteria,
		&items);
Morten Welinder's avatar
Morten Welinder committed
665

666 667
	if (tmpval)
		value_release (tmpval);
Morten Welinder's avatar
Morten Welinder committed
668

669
	if (ret != NULL)
670
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
671

672 673 674
	if (actual_range == NULL) {
	        list = items.list;
		sum = 0;
675

676 677
		while (list != NULL) {
		        Value *v = list->data;
678

679 680 681 682 683 684 685 686
			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;
687
 	      ret = sheet_foreach_cell_in_range (
688 689
			eval_sheet (actual_range->v_range.cell.a.sheet,
				    ei->pos->sheet),
690 691 692
			/* Empty cells too.  Criteria and results must align */
			FALSE,

693 694 695 696
			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,
697 698
			callback_function_sumif,
			&items);
699
	      sum = items.sum;
700 701
	}

Morten Welinder's avatar
Fix.  
Morten Welinder committed
702
	g_slist_free (items.list);
703

Michael Meeks's avatar
Michael Meeks committed
704
	return value_new_float (sum);
705 706
}

707 708
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
709
static const char *help_ceiling = {
710 711 712
	N_("@FUNCTION=CEILING\n"
	   "@SYNTAX=CEILING(x,significance)\n"

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

static Value *
731
gnumeric_ceiling (FunctionEvalInfo *ei, Value **argv)
732
{
Jody Goldberg's avatar
Jody Goldberg committed
733
        gnum_float number, s;
734

Morten Welinder's avatar
Morten Welinder committed
735 736 737 738 739
	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]);
740
	}
Morten Welinder's avatar
Morten Welinder committed
741

742
	if (s == 0 || number / s < 0)
743
		return value_new_error (ei->pos, gnumeric_err_NUM);
744

745
	return value_new_float (gnumeric_fake_ceil (number / s) * s);
746 747
}

748 749
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
750
static const char *help_cos = {
751
	N_("@FUNCTION=COS\n"
752
	   "@SYNTAX=COS(x)\n"
753 754

	   "@DESCRIPTION="
755
	   "COS function returns the cosine of @x, where @x is given "
756 757
           "in radians.\n"
	   "This function is Excel compatible."
758 759
	   "\n"
	   "@EXAMPLES=\n"
760 761
	   "COS(0.5) equals 0.877583.\n"
	   "COS(1) equals 0.540302.\n"
762 763 764 765
	   "\n"
	   "@SEEALSO=COSH, SIN, SINH, TAN, TANH, RADIANS, DEGREES")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
766
static Value *
767
gnumeric_cos (FunctionEvalInfo *ei, Value **argv)
Arturo Espinosa's avatar
Arturo Espinosa committed
768
{
769
	return value_new_float (cosgnum (value_get_as_float (argv [0])));