fn-math.c 93.4 KB
Newer Older
Jody Goldberg's avatar
Jody Goldberg committed
1
/* vim: set sw=8: -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
Arturo Espinosa's avatar
Arturo Espinosa committed
2
/*
3
 * fn-math.c:  Built in mathematical functions and functions registration
Arturo Espinosa's avatar
Arturo Espinosa committed
4
 *
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
5
 * Authors:
Arturo Espinosa's avatar
Arturo Espinosa committed
6
 *  Miguel de Icaza (miguel@gnu.org)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
7
 *  Jukka-Pekka Iivonen (iivonen@iki.fi)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
8 9 10 11 12 13 14 15 16 17 18 19 20 21
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
Arturo Espinosa's avatar
Arturo Espinosa committed
22
 */
23 24 25 26 27 28 29 30 31 32 33 34
#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>
35 36
#include <value.h>
#include <expr.h>
37
#include <auto-format.h>
Donnie Barnes's avatar
Donnie Barnes committed
38

Jody Goldberg's avatar
Jody Goldberg committed
39 40
#include <math.h>
#include <string.h>
41
#include <libgnome/gnome-i18n.h>
Jody Goldberg's avatar
Jody Goldberg committed
42

43 44 45 46 47
typedef struct {
        GSList *list;
        int    num;
} math_sums_t;

48
static Value *
49 50 51 52
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
53
        gnum_float  x;
54 55 56
	gpointer    p;

	if (cell == NULL || cell->value == NULL)
57
	        return NULL;
58 59

        switch (cell->value->type) {
60
	case VALUE_ERROR:
61
		return VALUE_TERMINATE;
62

63
	case VALUE_BOOLEAN:
64
	        x = cell->value->v_bool.val ? 1 : 0;
65
		break;
66
	case VALUE_INTEGER:
67
	        x = cell->value->v_int.val;
68 69
		break;
	case VALUE_FLOAT:
70
	        x = cell->value->v_float.val;
71
		break;
72
	case VALUE_EMPTY:
73
	default:
74
	        return NULL;
75 76
	}

Morten Welinder's avatar
Fix.  
Morten Welinder committed
77
	p = g_new (gnum_float, 1);
Jody Goldberg's avatar
Jody Goldberg committed
78
	*((gnum_float *) p) = x;
Morten Welinder's avatar
Fix.  
Morten Welinder committed
79
	mm->list = g_slist_append (mm->list, p);
80 81
	mm->num++;

82
	return NULL;
83 84 85 86 87 88 89
}

typedef struct {
        GSList              *list;
        criteria_test_fun_t fun;
        Value               *test_value;
        int                 num;
90 91
        int                 total_num;
        gboolean            actual_range;
Morten Welinder's avatar
Fix.  
Morten Welinder committed
92
        gnum_float          sum;
93
        GSList              *current;
94 95
} math_criteria_t;

96
static Value *
97 98 99 100 101 102
callback_function_criteria (Sheet *sheet, int col, int row,
			    Cell *cell, void *user_data)
{
        math_criteria_t *mm = user_data;
	Value           *v;

103
	mm->total_num++;
104
	if (cell == NULL || cell->value == NULL)
105
	        return NULL;
106 107

        switch (cell->value->type) {
108
	case VALUE_BOOLEAN:
109 110 111
	case VALUE_INTEGER:
	case VALUE_FLOAT:
	case VALUE_STRING:
Morten Welinder's avatar
Morten Welinder committed
112
	        v = value_duplicate (cell->value);
113
		break;
114
	case VALUE_EMPTY:
115
	default:
116
	        return NULL;
117 118
	}

Morten Welinder's avatar
Fix.  
Morten Welinder committed
119
	if (mm->fun (v, mm->test_value)) {
120
	        if (mm->actual_range) {
121 122
		        mm->list = g_slist_append (mm->list,
				GINT_TO_POINTER (mm->total_num));
Morten Welinder's avatar
Fix.  
Morten Welinder committed
123
			value_release (v);
124 125
		} else
		        mm->list = g_slist_append (mm->list, v);
126 127
		mm->num++;
	} else
Morten Welinder's avatar
Fix.  
Morten Welinder committed
128
	        value_release (v);
129

130
	return NULL;
131 132
}

133 134
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
135
static const char *help_gcd = {
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
136
	N_("@FUNCTION=GCD\n"
137
	   "@SYNTAX=GCD(number1,number2,...)\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
138 139

	   "@DESCRIPTION="
140
	   "GCD returns the greatest common divisor of given numbers. "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
141 142 143
	   "\n"
	   "If any of the arguments is less than zero, GCD returns #NUM! "
	   "error. "
144
	   "If any of the arguments is non-integer, it is truncated.\n"
145
	   "This function is Excel compatible. "
146 147 148 149
	   "\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
150 151 152 153
	   "\n"
	   "@SEEALSO=LCM")
};

Morten Welinder's avatar
Morten Welinder committed
154
static int
Jody Goldberg's avatar
Jody Goldberg committed
155
range_gcd (const gnum_float *xs, int n, gnum_float *res)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
156
{
Morten Welinder's avatar
Morten Welinder committed
157 158 159 160 161 162 163 164
	if (n > 0) {
		int i;
		int gcd_so_far = 0;

		for (i = 0; i < n; i++) {
			if (xs[i] <= 0)
				return 1;
			else
165
				gcd_so_far = gcd ((int)(floorgnum (xs[i])),
166
						  gcd_so_far);
167
		}
Morten Welinder's avatar
Morten Welinder committed
168 169 170 171 172
		*res = gcd_so_far;
		return 0;
	} else
		return 1;
}
173

Morten Welinder's avatar
Morten Welinder committed
174
static Value *
175
gnumeric_gcd (FunctionEvalInfo *ei, GnmExprList *nodes)
Morten Welinder's avatar
Morten Welinder committed
176 177 178
{
	return float_range_function (nodes, ei,
				     range_gcd,
179
				     COLLECT_IGNORE_STRINGS |
180 181
				     COLLECT_IGNORE_BOOLS |
				     COLLECT_IGNORE_BLANKS,
182
				     gnumeric_err_NUM);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
183 184
}

185 186
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
187
static const char *help_lcm = {
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
188 189 190 191 192 193 194 195 196
	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! "
197
	   "error.\n"
198
	   "This function is Excel compatible. "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
199
	   "\n"
200
	   "@EXAMPLES=\n"
201
	   "LCM(2,13) equals to 26.\n"
202 203
	   "LCM(4,7,5) equals to 140.\n"
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
204 205 206
	   "@SEEALSO=GCD")
};

Morten Welinder's avatar
Fix.  
Morten Welinder committed
207 208
static int
range_lcm (const gnum_float *xs, int n, gnum_float *res)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
209
{
Morten Welinder's avatar
Fix.  
Morten Welinder committed
210 211 212
	if (n > 0) {
		int i;
		int lcm_so_far = 1;
Morten Welinder's avatar
Morten Welinder committed
213

Morten Welinder's avatar
Fix.  
Morten Welinder committed
214 215 216 217 218
		for (i = 0; i < n; i++) {
			gnum_float x = xs[i];
			if (x <= 0)
				return 1;
			else {
219
				int xi = (int) floorgnum (x);
Morten Welinder's avatar
Fix.  
Morten Welinder committed
220 221 222 223 224 225 226 227
				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
228 229 230
}

static Value *
231
gnumeric_lcm (FunctionEvalInfo *ei, GnmExprList *nodes)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
232
{
Morten Welinder's avatar
Fix.  
Morten Welinder committed
233 234 235 236 237 238
	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
239 240 241

}

242 243
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
244
static const char *help_abs = {
245 246
	N_("@FUNCTION=ABS\n"
	   "@SYNTAX=ABS(b1)\n"
247

248
	   "@DESCRIPTION="
249
	   "ABS implements the Absolute Value function:  the result is "
250
	   "to drop the negative sign (if present).  This can be done for "
251 252
	   "integers and floating point numbers.\n"
	   "This function is Excel compatible."
253
	   "\n"
254 255 256
	   "@EXAMPLES=\n"
	   "ABS(7) equals 7.\n"
	   "ABS(-3.14) equals 3.14.\n"
257
	   "\n"
258
	   "@SEEALSO=CEIL, FLOOR")
259 260
};

261
static Value *
262
gnumeric_abs (FunctionEvalInfo *ei, Value **args)
263
{
Morten Welinder's avatar
Morten Welinder committed
264
	return value_new_float (gnumabs (value_get_as_float (args [0])));
265
}
266

267 268
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
269
static const char *help_acos = {
270
	N_("@FUNCTION=ACOS\n"
271
	   "@SYNTAX=ACOS(x)\n"
272

273
	   "@DESCRIPTION="
274
	   "ACOS function calculates the arc cosine of @x; that "
275
	   "is the value whose cosine is @x.  If @x  falls  outside  the "
276
	   "range -1 to 1, ACOS fails and returns the #NUM! error. "
277 278
	   "The value it returns is in radians.\n"
	   "This function is Excel compatible."
279
	   "\n"
280 281 282
	   "@EXAMPLES=\n"
	   "ACOS(0.1) equals 1.470629.\n"
	   "ACOS(-0.1) equals 1.670964.\n"
283 284
	   "\n"
	   "@SEEALSO=COS, SIN, DEGREES, RADIANS")
285 286
};

287
static Value *
288
gnumeric_acos (FunctionEvalInfo *ei, Value **args)
289
{
Jody Goldberg's avatar
Jody Goldberg committed
290
	gnum_float t;
Donnie Barnes's avatar
Donnie Barnes committed
291

292 293
	t = value_get_as_float (args [0]);
	if ((t < -1.0) || (t > 1.0))
294
		return value_new_error (ei->pos, gnumeric_err_NUM);
295

296
	return value_new_float (acosgnum (t));
297 298
}

299 300
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
301
static const char *help_acosh = {
302
	N_("@FUNCTION=ACOSH\n"
303
	   "@SYNTAX=ACOSH(x)\n"
304 305

	   "@DESCRIPTION="
306
	   "ACOSH  function  calculates  the inverse hyperbolic "
307
	   "cosine of @x; that is the value whose hyperbolic cosine is "
308
	   "@x. If @x is less than 1.0, ACOSH() returns the #NUM! error.\n"
309
	   "This function is Excel compatible. "
Arturo Espinosa's avatar
Arturo Espinosa committed
310
	   "\n"
311 312 313
	   "@EXAMPLES=\n"
	   "ACOSH(2) equals 1.31696.\n"
	   "ACOSH(5.3) equals 2.35183.\n"
314
	   "\n"
315
	   "@SEEALSO=ACOS, ASINH, DEGREES, RADIANS ")
Donnie Barnes's avatar
Donnie Barnes committed
316 317
};

318
static Value *
319
gnumeric_acosh (FunctionEvalInfo *ei, Value **args)
320
{
Jody Goldberg's avatar
Jody Goldberg committed
321
	gnum_float t;
322

323 324
	t = value_get_as_float (args [0]);
	if (t < 1.0)
325
		return value_new_error (ei->pos, gnumeric_err_NUM);
326

327
	return value_new_float (acoshgnum (t));
328 329
}

330 331
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
332
static const char *help_asin = {
333
	N_("@FUNCTION=ASIN\n"
334
	   "@SYNTAX=ASIN(x)\n"
335 336

	   "@DESCRIPTION="
337
	   "ASIN function calculates the arc sine of @x; that is "
338
	   "the value whose sine is @x. If @x falls outside  the  range "
339
	   "-1 to 1, ASIN fails and returns the #NUM! error.\n"
340
	   "This function is Excel compatible. "
341
	   "\n"
342 343 344
	   "@EXAMPLES=\n"
	   "ASIN(0.5) equals 0.523599.\n"
	   "ASIN(1) equals 1.570797.\n"
345 346 347 348
	   "\n"
	   "@SEEALSO=SIN, COS, ASINH, DEGREES, RADIANS")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
349
static Value *
350
gnumeric_asin (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
351
{
Jody Goldberg's avatar
Jody Goldberg committed
352
	gnum_float t;
Arturo Espinosa's avatar
Arturo Espinosa committed
353

354 355
	t = value_get_as_float (args [0]);
	if ((t < -1.0) || (t > 1.0))
356
		return value_new_error (ei->pos, gnumeric_err_NUM);
357

358
	return value_new_float (asingnum (t));
Arturo Espinosa's avatar
Arturo Espinosa committed
359 360
}

361 362
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
363
static const char *help_asinh = {
364
	N_("@FUNCTION=ASINH\n"
365
	   "@SYNTAX=ASINH(x)\n"
366 367

	   "@DESCRIPTION="
368
	   "ASINH function calculates the inverse hyperbolic sine of @x; "
369
	   "that is the value whose hyperbolic sine is @x.\n"
370
	   "This function is Excel compatible. "
371
	   "\n"
372
	   "@EXAMPLES=\n"
373 374
	   "ASINH(0.5) equals 0.481212.\n"
	   "ASINH(1.0) equals 0.881374.\n"
375
	   "\n"
376
	   "@SEEALSO=ASIN, ACOSH, SIN, COS, DEGREES, RADIANS")
377 378
};

Arturo Espinosa's avatar
Arturo Espinosa committed
379
static Value *
380
gnumeric_asinh (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
381
{
382
	return value_new_float (asinhgnum (value_get_as_float (args [0])));
383
}
Arturo Espinosa's avatar
Arturo Espinosa committed
384

385 386
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
387
static const char *help_atan = {
388
	N_("@FUNCTION=ATAN\n"
389
	   "@SYNTAX=ATAN(x)\n"
Arturo Espinosa's avatar
Arturo Espinosa committed
390

391
	   "@DESCRIPTION="
392
	   "ATAN function calculates the arc tangent of @x; that "
393
	   "is the value whose tangent is @x. "
394
	   "Return value is in radians.\n"
395
	   "This function is Excel compatible. "
396
	   "\n"
397
	   "@EXAMPLES=\n"
398
	   "ATAN(0.5) equals 0,463648.\n"
399
	   "ATAN(1) equals 0,785398.\n"
400 401 402
	   "\n"
	   "@SEEALSO=TAN, COS, SIN, DEGREES, RADIANS")
};
Arturo Espinosa's avatar
Arturo Espinosa committed
403 404

static Value *
405
gnumeric_atan (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
406
{
407
	return value_new_float (atangnum (value_get_as_float (args [0])));
Arturo Espinosa's avatar
Arturo Espinosa committed
408 409
}

410 411
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
412
static const char *help_atanh = {
413
	N_("@FUNCTION=ATANH\n"
414
	   "@SYNTAX=ATANH(x)\n"
415 416

	   "@DESCRIPTION="
417 418 419
	   "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 "
420
	   "returns #NUM! error.\n"
421
	   "This function is Excel compatible."
422
	   "\n"
423
	   "@EXAMPLES=\n"
424 425
	   "ATANH(0.5) equals 0.549306.\n "
	   "ATANH(0.8) equals 1.098612.\n"
426 427 428 429
	   "\n"
	   "@SEEALSO=ATAN, TAN, SIN, COS, DEGREES, RADIANS")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
430
static Value *
431
gnumeric_atanh (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
432
{
Jody Goldberg's avatar
Jody Goldberg committed
433
	gnum_float t;
Arturo Espinosa's avatar
Arturo Espinosa committed
434

435 436
	t = value_get_as_float (args [0]);
	if ((t <= -1.0) || (t >= 1.0))
437
		return value_new_error (ei->pos, gnumeric_err_NUM);
438

439
	return value_new_float (atanhgnum (value_get_as_float (args [0])));
Arturo Espinosa's avatar
Arturo Espinosa committed
440 441
}

442 443
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
444
static const char *help_atan2 = {
445
	N_("@FUNCTION=ATAN2\n"
446 447 448
	   "@SYNTAX=ATAN2(b1,b2)\n"

	   "@DESCRIPTION="
449 450 451
	   "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 "
452
	   "are used to determine the quadrant of the result. "
453
	   "The result is in radians.\n"
454
	   "This function is Excel compatible. "
455
	   "\n"
456
	   "@EXAMPLES=\n"
457 458
	   "ATAN2(0.5,1.0) equals 1.107149.\n"
	   "ATAN2(-0.5,2.0) equals 1.815775.\n"
459 460 461 462
	   "\n"
	   "@SEEALSO=ATAN, ATANH, COS, SIN, DEGREES, RADIANS")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
463
static Value *
464
gnumeric_atan2 (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
465
{
466 467
	return value_new_float (atan2gnum (value_get_as_float (args [1]),
					   value_get_as_float (args [0])));
Arturo Espinosa's avatar
Arturo Espinosa committed
468 469
}

470 471
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
472
static const char *help_ceil = {
473
	N_("@FUNCTION=CEIL\n"
474
	   "@SYNTAX=CEIL(x)\n"
475

476 477 478
	   "@DESCRIPTION="
	   "CEIL function rounds @x up to the next nearest integer.\n"
	   "This function is Excel compatible. "
479
	   "\n"
480
	   "@EXAMPLES=\n"
481 482 483
	   "CEIL(0.4) equals 1.\n"
	   "CEIL(-1.1) equals -1.\n"
	   "CEIL(-2.9) equals -2.\n"
484
	   "\n"
485 486 487
	   "@SEEALSO=ABS, FLOOR, INT")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
488
static Value *
489
gnumeric_ceil (FunctionEvalInfo *ei, Value **args)
Arturo Espinosa's avatar
Arturo Espinosa committed
490
{
491
	return value_new_float (gnumeric_fake_ceil (value_get_as_float (args [0])));
492 493
}

494 495
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
496
static const char *help_countif = {
497 498 499 500
	N_("@FUNCTION=COUNTIF\n"
	   "@SYNTAX=COUNTIF(range,criteria)\n"

	   "@DESCRIPTION="
501
	   "COUNTIF function counts the number of cells in the given @range "
502 503
	   "that meet the given @criteria.\n"
	   "This function is Excel compatible."
504
	   "\n"
505
	   "@EXAMPLES=\n"
506 507 508 509 510 511
	   "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"
512
	   "\n"
513 514 515 516
	   "@SEEALSO=COUNT,SUMIF")
};

static Value *
517
gnumeric_countif (FunctionEvalInfo *ei, Value **argv)
518 519
{
        Value           *range = argv[0];
520
	Value           *tmpval = NULL;
521
	Sheet           *sheet;
522

523 524
	math_criteria_t  items;
	Value           *ret;
525 526 527
	GSList          *list;

	items.num  = 0;
528
	items.total_num = 0;
529
	items.list = NULL;
530
	items.actual_range = FALSE;
531

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

Morten Welinder's avatar
Fix.  
Morten Welinder committed
536
	if (VALUE_IS_NUMBER (argv[1])) {
537 538
	        items.fun = (criteria_test_fun_t) criteria_test_equal;
		items.test_value = argv[1];
Morten Welinder's avatar
Morten Welinder committed
539
	} else {
Morten Welinder's avatar
Morten Welinder committed
540
	        parse_criteria (value_peek_string (argv[1]),
Morten Welinder's avatar
Fix.  
Morten Welinder committed
541
				&items.fun, &items.test_value);
542
		tmpval = items.test_value;
Morten Welinder's avatar
Morten Welinder committed
543
	}
544

545
	sheet = eval_sheet (range->v_range.cell.a.sheet, ei->pos->sheet);
546
	ret = sheet_foreach_cell_in_range (sheet,
547
		TRUE, /* Ignore empty cells */
548 549 550 551
		range->v_range.cell.a.col,
		range->v_range.cell.a.row,
		range->v_range.cell.b.col,
		range->v_range.cell.b.row,
552 553
		callback_function_criteria,
		&items);
Morten Welinder's avatar
Morten Welinder committed
554

555 556
	if (tmpval)
		value_release (tmpval);
Morten Welinder's avatar
Morten Welinder committed
557

558
	if (ret != NULL)
559
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
560 561 562 563

        list = items.list;

	while (list != NULL) {
Morten Welinder's avatar
Morten Welinder committed
564
		value_release (list->data);
565 566
		list = list->next;
	}
Morten Welinder's avatar
Fix.  
Morten Welinder committed
567
	g_slist_free (items.list);
568

Michael Meeks's avatar
Michael Meeks committed
569
	return value_new_int (items.num);
570 571
}

572 573
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
574
static const char *help_sumif = {
575
	N_("@FUNCTION=SUMIF\n"
576
	   "@SYNTAX=SUMIF(range,criteria[,actual_range])\n"
577 578

	   "@DESCRIPTION="
579 580
	   "SUMIF function sums the values in the given @range that meet "
	   "the given @criteria.  If @actual_range is given, SUMIF sums "
581
	   "the values in the @actual_range whose corresponding components "
582
	   "in @range meet the given @criteria.\n"
583
	   "This function is Excel compatible. "
584
	   "\n"
585
	   "@EXAMPLES=\n"
586 587 588 589 590 591 592
	   "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"
593
	   "\n"
594
	   "@SEEALSO=COUNTIF, SUM")
595 596
};

597
static Value *
598 599 600 601
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
602
	gnum_float       v = 0.;
603

604
	/* If we have finished the list there is no need to bother */
605
	if (mm->current == NULL)
606
	        return NULL;
607

608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625
	/* 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:
626
			return VALUE_TERMINATE;
627 628 629
		}
	mm->sum += v;
	mm->current = mm->current->next;
630

631
	return NULL;
632 633
}

634
static Value *
635
gnumeric_sumif (FunctionEvalInfo *ei, Value **argv)
636
{
637 638 639
        Value          *range = argv[0];
	Value          *actual_range = argv[2];
	Value          *tmpval = NULL;
640

641
	math_criteria_t items;
642
	Value          *ret;
Morten Welinder's avatar
Fix.  
Morten Welinder committed
643
	gnum_float      sum;
644
	GSList         *list;
645 646

	items.num  = 0;
647
	items.total_num = 0;
648 649
	items.list = NULL;

650 651
	if (range->type != VALUE_CELLRANGE ||
	    !(VALUE_IS_NUMBER (argv[1]) || argv[1]->type == VALUE_STRING))
652
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
653

654 655 656
	/* 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
657
	if (VALUE_IS_NUMBER (argv[1])) {
658 659
	        items.fun = (criteria_test_fun_t) criteria_test_equal;
		items.test_value = argv[1];
Morten Welinder's avatar
Morten Welinder committed
660
	} else {
Morten Welinder's avatar
Morten Welinder committed
661
	        parse_criteria (value_peek_string (argv[1]),
Morten Welinder's avatar
Fix.  
Morten Welinder committed
662
				&items.fun, &items.test_value);
663
		tmpval = items.test_value;
Morten Welinder's avatar
Morten Welinder committed
664
	}
665

666
	items.actual_range = (actual_range != NULL);
667

668
	ret = sheet_foreach_cell_in_range (
669
		eval_sheet (range->v_range.cell.a.sheet, ei->pos->sheet),
670
		/*
671
		 * Do not ignore empty cells if there is a
672 673 674
		 * target range.  We need the orders of the source values to
		 * line up with the values of the target range.
		 */
675 676
		actual_range == NULL,

677 678 679 680
		range->v_range.cell.a.col,
		range->v_range.cell.a.row,
		range->v_range.cell.b.col,
		range->v_range.cell.b.row,
681 682
		callback_function_criteria,
		&items);
Morten Welinder's avatar
Morten Welinder committed
683

684 685
	if (tmpval)
		value_release (tmpval);
Morten Welinder's avatar
Morten Welinder committed
686

687
	if (ret != NULL)
688
	        return value_new_error (ei->pos, gnumeric_err_VALUE);
689

690 691 692
	if (actual_range == NULL) {
	        list = items.list;
		sum = 0;
693

694 695
		while (list != NULL) {
		        Value *v = list->data;
696

697 698 699 700 701 702 703 704
			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;
705
 	      ret = sheet_foreach_cell_in_range (
706 707
			eval_sheet (actual_range->v_range.cell.a.sheet,
				    ei->pos->sheet),
708 709 710
			/* Empty cells too.  Criteria and results must align */
			FALSE,

711 712 713 714
			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,
715 716
			callback_function_sumif,
			&items);
717
	      sum = items.sum;
718 719
	}

Morten Welinder's avatar
Fix.  
Morten Welinder committed
720
	g_slist_free (items.list);
721

Michael Meeks's avatar
Michael Meeks committed
722
	return value_new_float (sum);
723 724
}

725 726
/***************************************************************************/

Morten Welinder's avatar
Morten Welinder committed
727
static const char *help_ceiling = {