functions.c 36 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 -*- */
2
/*
Morten Welinder's avatar
Morten Welinder committed
3
 * Range lookup functions
4
 *
5
 * Authors:
6
 *   Michael Meeks <michael@ximian.com>
Morten Welinder's avatar
Morten Welinder committed
7
8
9
 *   Jukka-Pekka Iivonen <iivonen@iki.fi>
 *   JP Rosevear <jpr@arcavia.com>
 *   Morten Welinder (terra@diku.dk)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 *
 * 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.
24
 */
25

26
27
28
29
30
#include <gnumeric-config.h>
#include <gnumeric.h>
#include <func.h>

#include <parse-util.h>
31
#include <dependent.h>
32
33
34
#include <cell.h>
#include <str.h>
#include <sheet.h>
35
#include <value.h>
36
#include <ranges.h>
37
38
#include <expr.h>
#include <expr-impl.h>
Jody Goldberg's avatar
Jody Goldberg committed
39
#include <expr-impl.h>
40
#include <expr-name.h>
41
#include <parse-util.h>
42
#include <gnm-i18n.h>
43

44
#include <goffice/app/go-plugin.h>
Jody Goldberg's avatar
Jody Goldberg committed
45
#include <gnm-plugin.h>
Morten Welinder's avatar
Morten Welinder committed
46

47
48
49
#include <string.h>
#include <stdlib.h>

Jody Goldberg's avatar
Jody Goldberg committed
50
GNM_PLUGIN_MODULE_HEADER;
Morten Welinder's avatar
Morten Welinder committed
51
52


53
static gboolean
Jody Goldberg's avatar
Jody Goldberg committed
54
find_type_valid (GnmValue const *find)
55
56
{
	/* Excel does not lookup errors or blanks */
57
58
59
	if (VALUE_IS_EMPTY (find))
		return FALSE;
	return VALUE_IS_NUMBER (find) || find->type == VALUE_STRING;
60
61
62
}

static gboolean
Jody Goldberg's avatar
Jody Goldberg committed
63
find_compare_type_valid (GnmValue const *find, GnmValue const *val)
64
65
66
67
{
	if (!val) {
		return FALSE;
	}
68
69

	if ((VALUE_IS_NUMBER (find) && VALUE_IS_NUMBER (val)) ||
70
71
72
	    (find->type == val->type)) {
		return TRUE;
	}
73

74
75
76
77
78
79
80
81
82
83
	return FALSE;
}

/**
 * find_bound_walk:
 * @l: lower bound
 * @h: upper bound
 * @start: starting point
 * @up: is first step incrementing
 * @reset: reset static values
84
 *
85
86
87
88
89
90
91
 * This function takes and upper and lower integer bound
 * and then walks that range starting with the given
 * starting point.  The walk is done by incrementing or
 * decrementing the starting point (based on the up value)
 * until the upper or lower bound is reached.  At this
 * point the step is reversed and the values move to the
 * opposite boundary (not repeating any values of course)
92
 *
93
94
95
 * Return value: the next value in the range
 **/
static int
96
find_bound_walk (int l, int h, int start, gboolean up, gboolean reset)
97
98
99
100
101
102
103
104
105
{
	static int low, high, current, orig;
	static gboolean sup, started;

	g_return_val_if_fail (l >= 0, -1);
	g_return_val_if_fail (h >= 0, -1);
	g_return_val_if_fail (h >= l, -1);
	g_return_val_if_fail (start >= l, -1);
	g_return_val_if_fail (start <= h, -1);
106

107
108
109
110
111
112
	if (reset) {
		low = l;
		high = h;
		current = start;
		orig = start;
		sup = up;
113
		started = up;
114
115
		return current;
	}
116

117
118
	if (sup) {
		current++;
119
		if (current > high && sup == started) {
120
121
			current = orig - 1;
			sup = FALSE;
122
		} else if (current > high && sup != started) {
123
124
125
126
			return -1;
		}
	} else {
		current--;
127
		if (current < low && sup == started) {
128
129
			current = orig + 1;
			sup = TRUE;
130
		} else if (current < low && sup != started) {
131
132
133
134
135
136
137
			return -1;
		}
	}
	return current;
}

static int
Jody Goldberg's avatar
Jody Goldberg committed
138
139
find_index_linear (FunctionEvalInfo *ei,
		   GnmValue const *find, GnmValue const *data,
140
		   gint type, gboolean height)
141
{
Jody Goldberg's avatar
Jody Goldberg committed
142
	GnmValue const *index_val = NULL;
143
	GnmValDiff comp;
144
	int length, lp, index = -1;
145

Jody Goldberg's avatar
Jody Goldberg committed
146
147
148
149
	if (height)
		length = value_area_get_height (data, ei->pos);
	else
		length = value_area_get_width (data, ei->pos);
150

151
	for (lp = 0; lp < length; lp++){
Jody Goldberg's avatar
Jody Goldberg committed
152
		GnmValue const *v;
153

Jody Goldberg's avatar
Jody Goldberg committed
154
155
156
157
		if (height)
			v = value_area_fetch_x_y (data, 0, lp, ei->pos);
		else
			v = value_area_fetch_x_y (data, lp, 0, ei->pos);
158
159
160

		g_return_val_if_fail (v != NULL, -1);

Jody Goldberg's avatar
Jody Goldberg committed
161
		if (!find_compare_type_valid (find, v))
162
			continue;
163

164
		comp = value_compare (find, v, FALSE);
165

166
		if (type >= 1 && comp == IS_GREATER) {
167
			GnmValDiff comp = TYPE_MISMATCH;
168

169
			if (index >= 0) {
170
171
				comp = value_compare (v, index_val, FALSE);
			}
172
173

			if (index < 0 ||
174
175
176
177
178
			    (index >= 0 && comp == IS_GREATER)) {
				index = lp;
				index_val = v;
			}
		} else if (type <= -1 && comp == IS_LESS) {
179
			GnmValDiff comp = TYPE_MISMATCH;
180

181
			if (index >= 0) {
182
183
				comp = value_compare (v, index_val, FALSE);
			}
184
185

			if (index < 0 ||
186
187
188
189
190
191
			    (index >= 0 && comp == IS_LESS)) {
				index = lp;
				index_val = v;
			}
		} else if (comp == IS_EQUAL) {
			return lp;
192
		}
193
194
195
196
197
198
	}

	return index;
}

static int
Jody Goldberg's avatar
Jody Goldberg committed
199
200
find_index_bisection (FunctionEvalInfo *ei,
		      GnmValue const *find, GnmValue const *data,
201
		      gint type, gboolean height)
202
{
203
	GnmValDiff comp = TYPE_MISMATCH;
204
205
	int high, low = 0, prev = -1, mid = -1;

Jody Goldberg's avatar
Jody Goldberg committed
206
207
208
209
	if (height)
		high = value_area_get_height (data, ei->pos);
	else
		high = value_area_get_width (data, ei->pos);
210
	high--;
211

212
213
214
	if (high < low) {
		return -1;
	}
215

216
	while (low <= high) {
Jody Goldberg's avatar
Jody Goldberg committed
217
		GnmValue const *v = NULL;
218
219
220
221
222
		int start;

		if ((type >= 1) != (comp == IS_LESS)) {
			prev = mid;
		}
223
224

		mid = ((low + high) / 2);
225
226
		mid = find_bound_walk (low, high, mid,
				       type >= 0 ? TRUE : FALSE, TRUE);
227

228
229
230
231
232
233
234
235
236
237
		start = mid;

		/*
		 * Excel handles type mismatches by skipping first one
		 * way then the other (if necessary) to find a valid
		 * value.  The initial direction depends on the search
		 * type.
		 */
		while (!find_compare_type_valid (find, v) && mid != -1) {
			gboolean rev = FALSE;
238

Jody Goldberg's avatar
Jody Goldberg committed
239
240
241
242
			if (height)
				v = value_area_get_x_y (data, 0, mid, ei->pos);
			else
				v = value_area_get_x_y (data, mid, 0, ei->pos);
243

Jody Goldberg's avatar
Jody Goldberg committed
244
			if (find_compare_type_valid (find, v))
245
				break;
246

247
248
249
250
251
252
253
254
255
256
			mid = find_bound_walk (0, 0, 0, FALSE, FALSE);

			if (!rev && type >= 0 && mid < start) {
				high = mid;
				rev = TRUE;
			} else if (!rev && type < 0 && mid > start) {
				low = mid;
				rev = TRUE;
			}
		}
257
258

		/*
259
260
261
262
263
264
265
266
267
		 * If we couldn't find another entry in the range
		 * with an appropriate type, return the best previous
		 * value
		 */
		if (mid == -1 && ((type >= 1) != (comp == IS_LESS))) {
			return prev;
		} else if (mid == -1) {
			return -1;
		}
268

269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
		comp = value_compare (find, v, FALSE);

		if (type >= 1 && comp == IS_GREATER) {
			low = mid + 1;
		} else if (type >= 1 && comp == IS_LESS) {
			high = mid - 1;
		} else if (type <= -1 && comp == IS_GREATER) {
			high = mid - 1;
		} else if (type <= -1 && comp == IS_LESS) {
			low = mid + 1;
		} else if (comp == IS_EQUAL) {
			/* This is due to excel, it does a
			 * linear search after the bisection search
			 * to find either the first or last value
			 * that is equal.
			 */
			while ((type <= -1 && mid > low) ||
286
			       (type >= 0 && mid < high)) {
287
288
289
290
291
292
293
				int adj = 0;

				if (type >= 0) {
					adj = mid + 1;
				} else {
					adj = mid - 1;
				}
294

Jody Goldberg's avatar
Jody Goldberg committed
295
296
				if (height)
					v = value_area_fetch_x_y (data, 0, adj, ei->pos);
Jody Goldberg's avatar
Jody Goldberg committed
297
				else
Jody Goldberg's avatar
Jody Goldberg committed
298
					v = value_area_fetch_x_y (data, adj, 0, ei->pos);
299
300
301

				g_return_val_if_fail (v != NULL, -1);

Jody Goldberg's avatar
Jody Goldberg committed
302
				if (!find_compare_type_valid (find, v))
303
304
305
					break;

				comp = value_compare (find, v, FALSE);
Jody Goldberg's avatar
Jody Goldberg committed
306
				if (comp != IS_EQUAL)
307
					break;
308

309
310
311
				mid = adj;
			}
			return mid;
312
		}
313
314
315
316
317
318
319
320
321
322
	}

	/* Try and return a reasonable value */
	if ((type >= 1) != (comp == IS_LESS)) {
		return mid;
	}

	return prev;
}

323
324
/***************************************************************************/

325
326
327
static GnmFuncHelp const help_address[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=ADDRESS\n"
328
329
330
331
	   "@SYNTAX=ADDRESS(row_num,col_num[,abs_num,a1,text])\n"

	   "@DESCRIPTION="
	   "ADDRESS returns a cell address as text for specified row "
Jukka Pekka's avatar
Jukka Pekka committed
332
	   "and column numbers.\n"
333
334
335
336
	   "\n"
	   "@a1 is a logical value that specifies the reference style.  If "
	   "@a1 is TRUE or omitted, ADDRESS returns an A1-style reference, "
	   "i.e. $D$4.  Otherwise ADDRESS returns an R1C1-style reference, "
Jukka Pekka's avatar
Jukka Pekka committed
337
	   "i.e. R4C4.\n"
338
339
	   "\n"
	   "@text specifies the name of the worksheet to be used as the "
Jukka Pekka's avatar
Jukka Pekka committed
340
	   "external reference.\n"
341
	   "\n"
Jukka Pekka's avatar
Jukka Pekka committed
342
343
344
345
346
347
348
349
350
351
	   "* If @abs_num is 1 or omitted, ADDRESS returns absolute "
	   "reference.\n"
	   "* If @abs_num is 2 ADDRESS returns absolute row and relative "
	   "column.\n"
	   "* If @abs_num is 3 ADDRESS returns relative row and "
	   "absolute column.\n"
	   "* If @abs_num is 4 ADDRESS returns relative reference.\n"
	   "* If @abs_num is greater than 4 ADDRESS returns #VALUE! error.\n"
	   "* If @row_num or @col_num is less than one, ADDRESS returns "
	   "#VALUE! error.\n"
352
	   "\n"
353
	   "@EXAMPLES=\n"
354
355
356
	   "ADDRESS(5,4) equals \"$D$5\".\n"
	   "ADDRESS(5,4,4) equals \"D5\".\n"
	   "ADDRESS(5,4,3,FALSE) equals \"R[5]C4\".\n"
357
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
358
	   "@SEEALSO=COLUMNNUMBER")
359
360
	},
	{ GNM_FUNC_HELP_END }
361
362
};

Jody Goldberg's avatar
Jody Goldberg committed
363
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
364
gnumeric_address (FunctionEvalInfo *ei, GnmValue const * const *args)
365
{
366
        int   row, col, abs_num, a1;
367
368
	gchar *sheet_name, *buf;
	char const *sheet_quote;
369

370
371
	row = value_get_as_int (args[0]);
	col = value_get_as_int (args[1]);
372

373
374
	if (row < 1 || SHEET_MAX_ROWS <= row ||
	    col < 1 || SHEET_MAX_COLS <= col)
375
	        return value_new_error_VALUE (ei->pos);
376

377
	abs_num = args[2] ? value_get_as_int (args[2]) : 1;
378

379
	if (args[3] == NULL)
380
381
	        a1 = 1;
	else {
382
		gboolean err;
383
384
	        a1 = value_get_as_bool (args[3], &err);
		if (err)
385
		        return value_new_error_VALUE (ei->pos);
386
387
	}

388
389
390
391
	sheet_name = (args[4] != NULL)
		? sheet_name_quote (value_peek_string (args[4]))
		: g_strdup ("");
	sheet_quote = *sheet_name ? "!" : "";
392

393
	buf = g_new (gchar, strlen (sheet_name) + 1 + 50);
394
	switch (abs_num) {
395
	case 1: case 5:
396
	        if (a1)
397
		        sprintf (buf, "%s%s$%s$%d", sheet_name, sheet_quote, col_name (col - 1),
Jukka Pekka's avatar
Jukka Pekka committed
398
				 row);
399
		else
400
		        sprintf (buf, "%s%sR%dC%d", sheet_name, sheet_quote, row, col);
401
		break;
402
	case 2: case 6:
403
	        if (a1)
404
		        sprintf (buf, "%s%s%s$%d", sheet_name, sheet_quote, col_name (col - 1), row);
405
		else
406
		        sprintf (buf, "%s%sR%dC[%d]", sheet_name, sheet_quote, row, col);
407
		break;
408
	case 3: case 7:
409
	        if (a1)
410
		        sprintf (buf, "%s%s$%s%d", sheet_name, sheet_quote, col_name (col - 1), row);
411
		else
412
		        sprintf (buf, "%s%sR[%d]C%d", sheet_name, sheet_quote, row, col);
413
		break;
414
	case 4: case 8:
415
	        if (a1)
416
		        sprintf (buf, "%s%s%s%d", sheet_name, sheet_quote, col_name (col - 1), row);
417
		else
418
		        sprintf (buf, "%s%sR[%d]C[%d]", sheet_name, sheet_quote, row, col);
419
420
		break;
	default:
421
	        g_free (sheet_name);
Morten Welinder's avatar
Morten Welinder committed
422
	        g_free (buf);
423
		return value_new_error_VALUE (ei->pos);
424
	}
425
	g_free (sheet_name);
426

427
	return value_new_string_nocopy (buf);
428
429
}

430
431
/***************************************************************************/

432
433
434
static GnmFuncHelp const help_areas[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=AREAS\n"
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
435
	   "@SYNTAX=AREAS(reference)\n"
436
437
438
439
440
441
442

	   "@DESCRIPTION="
	   "AREAS returns the number of areas in @reference. "
	   "\n"
	   "\n"
	   "@EXAMPLES=\n"
	   "AREAS((A1,B2,C3)) equals "
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
443
	   "3.\n"
444
445
	   "\n"
	   "@SEEALSO=ADDRESS,INDEX,INDIRECT,OFFSET")
446
447
	},
	{ GNM_FUNC_HELP_END }
448
449
450
};

/* TODO : we need to rethink EXPR_SET as an operator vs a value type */
Jody Goldberg's avatar
Jody Goldberg committed
451
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
452
gnumeric_areas (FunctionEvalInfo *ei, GnmExprList const *l)
453
454
455
456
457
458
{
	GnmExpr const *expr;
	int res = -1;
	int argc =  gnm_expr_list_length (l);

	if (argc < 1 || l->data == NULL || argc > 1)
459
		return value_new_error_VALUE (ei->pos);
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
	expr = l->data;

restart :
	switch (expr->any.oper) {
	case GNM_EXPR_OP_CONSTANT:
		if (expr->constant.value->type != VALUE_CELLRANGE)
			break;

	case GNM_EXPR_OP_CELLREF:
	case GNM_EXPR_OP_RANGE_CTOR:
	case GNM_EXPR_OP_INTERSECT:
		res = 1;
		break;
	case GNM_EXPR_OP_ANY_BINARY:
	case GNM_EXPR_OP_ANY_UNARY:
	case GNM_EXPR_OP_ARRAY:
		break;

	case GNM_EXPR_OP_FUNCALL: {
Jody Goldberg's avatar
Jody Goldberg committed
479
		GnmValue *v = gnm_expr_eval (expr, ei->pos,
480
481
482
483
484
485
486
487
			GNM_EXPR_EVAL_PERMIT_NON_SCALAR);
		if (expr->constant.value->type == VALUE_CELLRANGE)
			res = 1;
		value_release (v);
		break;
	}

	case GNM_EXPR_OP_NAME:
488
		if (expr->name.name->active) {
489
			expr = expr->name.name->expr;
490
491
492
493
494
495
496
497
498
499
500
501
502
503
			goto restart;
		}
		break;

	case GNM_EXPR_OP_SET:
		res = gnm_expr_list_length (expr->set.set);
		break;

	default:
		g_warning ("unknown expr type.");
	};

	if (res > 0)
		return value_new_int (res);
504
	return value_new_error_VALUE (ei->pos);
505
506
507
508
}

/***************************************************************************/

509
510
511
static GnmFuncHelp const help_choose[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=CHOOSE\n"
Michael Meeks's avatar
Michael Meeks committed
512
513
514
	   "@SYNTAX=CHOOSE(index[,value1][,value2]...)\n"

	   "@DESCRIPTION="
515
	   "CHOOSE returns the value of index @index. "
Jukka Pekka's avatar
Jukka Pekka committed
516
	   "@index is rounded to an integer if it is not.\n"
Michael Meeks's avatar
Michael Meeks committed
517
	   "\n"
Jukka Pekka's avatar
Jukka Pekka committed
518
519
	   "* If @index < 1 or @index > number of values, CHOOSE "
	   "returns #VALUE! error.\n"
Michael Meeks's avatar
Michael Meeks committed
520
	   "\n"
521
	   "@EXAMPLES=\n"
Jukka Pekka's avatar
Jukka Pekka committed
522
523
	   "CHOOSE(3,\"Apple\",\"Orange\",\"Grape\",\"Perry\") equals "
	   "\"Grape\".\n"
524
	   "\n"
Michael Meeks's avatar
Michael Meeks committed
525
	   "@SEEALSO=IF")
526
527
	},
	{ GNM_FUNC_HELP_END }
Michael Meeks's avatar
Michael Meeks committed
528
529
};

Jody Goldberg's avatar
Jody Goldberg committed
530
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
531
gnumeric_choose (FunctionEvalInfo *ei, GnmExprList const *l)
Michael Meeks's avatar
Michael Meeks committed
532
533
534
{
	int     index;
	int     argc;
Jody Goldberg's avatar
Jody Goldberg committed
535
	GnmValue  *v;
Michael Meeks's avatar
Michael Meeks committed
536

537
	argc =  gnm_expr_list_length (l);
538

539
	if (argc < 1 || !l->data)
540
		return value_new_error_VALUE (ei->pos);
541

Jody Goldberg's avatar
Jody Goldberg committed
542
	v = gnm_expr_eval (l->data, ei->pos, GNM_EXPR_EVAL_SCALAR_NON_EMPTY);
543
544
545
	if (!v)
		return NULL;

546
	if ((v->type != VALUE_INTEGER) && (v->type != VALUE_FLOAT)) {
Morten Welinder's avatar
Morten Welinder committed
547
		value_release (v);
548
		return value_new_error_VALUE (ei->pos);
Michael Meeks's avatar
Michael Meeks committed
549
	}
550

Jukka Pekka's avatar
Jukka Pekka committed
551
	index = value_get_as_int (v);
Michael Meeks's avatar
Michael Meeks committed
552
	value_release (v);
553
	for (l = l->next; l != NULL ; l = l->next) {
Michael Meeks's avatar
Michael Meeks committed
554
555
		index--;
		if (!index)
Jukka Pekka's avatar
Jukka Pekka committed
556
557
			return gnm_expr_eval (l->data, ei->pos,
					      GNM_EXPR_EVAL_PERMIT_NON_SCALAR);
Michael Meeks's avatar
Michael Meeks committed
558
	}
559
	return value_new_error_VALUE (ei->pos);
Michael Meeks's avatar
Michael Meeks committed
560
561
}

562
563
/***************************************************************************/

564
565
566
static GnmFuncHelp const help_vlookup[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=VLOOKUP\n"
Jody Goldberg's avatar
Jody Goldberg committed
567
	   "@SYNTAX=VLOOKUP(value,range,column[,approximate,as_index])\n"
568
569

	   "@DESCRIPTION="
570
	   "VLOOKUP function finds the row in range that has a first "
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
571
	   "column similar to @value.  If @approximate is not true it finds "
Jody Goldberg's avatar
Jody Goldberg committed
572
	   "the row with an exact equivalence.  If @approximate is true, "
573
574
	   "then the values must be sorted in order of ascending value for "
	   "correct function; in this case it finds the row with value less "
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
575
	   "than @value.  It returns the value in the row found at a 1-based "
Jody Goldberg's avatar
Jody Goldberg committed
576
	   "offset in @column columns into the @range.  @as_index returns the "
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
577
	   "0-based offset that matched rather than the value.\n"
578
	   "\n"
Jukka Pekka's avatar
Jukka Pekka committed
579
580
	   "* VLOOKUP returns #NUM! if @column < 0.\n"
	   "* VLOOKUP returns #REF! if @column falls outside @range.\n"
581
	   "\n"
582
583
	   "@EXAMPLES=\n"
	   "\n"
584
	   "@SEEALSO=HLOOKUP")
585
586
	},
	{ GNM_FUNC_HELP_END }
587
588
};

Jody Goldberg's avatar
Jody Goldberg committed
589
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
590
gnumeric_vlookup (FunctionEvalInfo *ei, GnmValue const * const *args)
591
{
Jukka Pekka's avatar
Jukka Pekka committed
592
	int      col_idx, index = -1;
593
	gboolean approx;
594

595
	col_idx = value_get_as_int (args[2]);
596

597
	if (!find_type_valid (args[0]))
598
		return value_new_error_NA (ei->pos);
599
	if (col_idx <= 0)
600
		return value_new_error_VALUE (ei->pos);
Jody Goldberg's avatar
Jody Goldberg committed
601
	if (col_idx > value_area_get_width (args [1], ei->pos))
602
		return value_new_error_REF (ei->pos);
603

Jody Goldberg's avatar
Jody Goldberg committed
604
605
606
607
608
609
610
	approx = (args[3] != NULL)
		? value_get_as_checked_bool (args [3]) : TRUE;
	index = approx
		? find_index_bisection (ei, args[0], args[1], 1, TRUE)
		: find_index_linear (ei, args[0], args[1], 0, TRUE);
	if (args[4] != NULL && value_get_as_checked_bool (args [4]))
		return value_new_int (index);
611

612
	if (index >= 0) {
Jody Goldberg's avatar
Jody Goldberg committed
613
	        GnmValue const *v;
614

Jody Goldberg's avatar
Jody Goldberg committed
615
		v = value_area_fetch_x_y (args [1], col_idx-1, index, ei->pos);
616
		g_return_val_if_fail (v != NULL, NULL);
617
		return value_dup (v);
618
619
	}

620
	return value_new_error_NA (ei->pos);
621
622
}

623
624
/***************************************************************************/

625
626
627
static GnmFuncHelp const help_hlookup[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=HLOOKUP\n"
Jody Goldberg's avatar
Jody Goldberg committed
628
	   "@SYNTAX=HLOOKUP(value,range,row[,approximate,as_index])\n"
629
630

	   "@DESCRIPTION="
631
	   "HLOOKUP function finds the col in range that has a first "
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
632
	   "row cell similar to @value.  If @approximate is not true it finds "
Jody Goldberg's avatar
Jody Goldberg committed
633
	   "the col with an exact equivalence.  If @approximate is true, "
634
635
	   "then the values must be sorted in order of ascending value for "
	   "correct function; in this case it finds the col with value less "
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
636
637
638
	   "than @value it returns the value in the col found at a 1-based "
	   "offset in @row rows into the @range.  @as_index returns the "
	   "0-based offset "
Jukka Pekka's avatar
Jukka Pekka committed
639
	   "that matched rather than the value.\n"
640
	   "\n"
Jukka Pekka's avatar
Jukka Pekka committed
641
642
	   "* HLOOKUP returns #NUM! if @row < 0.\n"
	   "* HLOOKUP returns #REF! if @row falls outside @range.\n"
643
	   "\n"
644
645
	   "@EXAMPLES=\n"
	   "\n"
646
	   "@SEEALSO=VLOOKUP")
647
648
	},
	{ GNM_FUNC_HELP_END }
649
650
};

Jody Goldberg's avatar
Jody Goldberg committed
651
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
652
gnumeric_hlookup (FunctionEvalInfo *ei, GnmValue const * const *args)
653
{
654
655
	int row_idx, index = -1;
	gboolean approx;
656

657
	row_idx = value_get_as_int (args[2]);
658

659
	if (!find_type_valid (args[0]))
660
		return value_new_error_NA (ei->pos);
661
	if (row_idx <= 0)
662
		return value_new_error_VALUE (ei->pos);
Jody Goldberg's avatar
Jody Goldberg committed
663
	if (row_idx > value_area_get_height (args [1], ei->pos))
664
		return value_new_error_REF (ei->pos);
665

Jody Goldberg's avatar
Jody Goldberg committed
666
667
668
669
670
671
672
	approx = (args[3] != NULL)
		? value_get_as_checked_bool (args [3]) : TRUE;
	index = approx
		? find_index_bisection (ei, args[0], args[1], 1, FALSE)
		: find_index_linear (ei, args[0], args[1], 0, FALSE);
	if (args[4] != NULL && value_get_as_checked_bool (args [4]))
		return value_new_int (index);
673

674
	if (index >= 0) {
Jody Goldberg's avatar
Jody Goldberg committed
675
	        GnmValue const *v;
676

Jody Goldberg's avatar
Jody Goldberg committed
677
		v = value_area_fetch_x_y (args[1], index, row_idx-1, ei->pos);
678
		g_return_val_if_fail (v != NULL, NULL);
679
		return value_dup (v);
680
681
	}

682
	return value_new_error_NA (ei->pos);
683
684
}

685
686
/***************************************************************************/

687
688
689
static GnmFuncHelp const help_lookup[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=LOOKUP\n"
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
690
	   "@SYNTAX=LOOKUP(value,vector1[,vector2])\n"
Michael Meeks's avatar
Michael Meeks committed
691
692

	   "@DESCRIPTION="
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
693
694
695
	   "LOOKUP function finds the row index of @value in @vector1 "
	   "and returns the contents of @vector2 at that row index. "
	   "Alternatively a single array can be used for @vector1. "
696
	   "If the area is longer than it is wide then the sense of the "
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
697
	   "search is rotated. \n"
Michael Meeks's avatar
Michael Meeks committed
698
	   "\n"
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
699
700
	   "* If LOOKUP can't find @value it uses the largest value less "
	   "than @value.\n"
Jukka Pekka's avatar
Jukka Pekka committed
701
702
	   "* The data must be sorted.\n"
	   "* If @value is smaller than the first value it returns #N/A.\n"
Michael Meeks's avatar
Michael Meeks committed
703
	   "\n"
704
705
	   "@EXAMPLES=\n"
	   "\n"
Michael Meeks's avatar
Michael Meeks committed
706
	   "@SEEALSO=VLOOKUP,HLOOKUP")
707
708
	},
	{ GNM_FUNC_HELP_END }
Michael Meeks's avatar
Michael Meeks committed
709
710
};

Jody Goldberg's avatar
Jody Goldberg committed
711
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
712
gnumeric_lookup (FunctionEvalInfo *ei, GnmValue const * const *args)
Michael Meeks's avatar
Michael Meeks committed
713
{
714
	int index = -1;
Jody Goldberg's avatar
Jody Goldberg committed
715
	GnmValue const *result = args[2];
Jody Goldberg's avatar
Jody Goldberg committed
716
717
	int width = value_area_get_width (args[1], ei->pos);
	int height = value_area_get_height (args[1], ei->pos);
718

Jody Goldberg's avatar
Jody Goldberg committed
719
	if (!find_type_valid (args[0]))
720
		return value_new_error_NA (ei->pos);
721

722
	if (result) {
Jody Goldberg's avatar
Jody Goldberg committed
723
724
		int width = value_area_get_width (result, ei->pos);
		int height = value_area_get_height (result, ei->pos);
725

726
		if (width > 1 && height > 1) {
727
			return value_new_error_NA (ei->pos);
728
729
730
731
		}
	} else {
		result = args[1];
	}
732

733
	index = find_index_bisection (ei, args[0], args[1], 1,
734
				      width > height ? FALSE : TRUE);
735

736
	if (index >= 0) {
Jody Goldberg's avatar
Jody Goldberg committed
737
	        GnmValue const *v = NULL;
Jody Goldberg's avatar
Jody Goldberg committed
738
739
740
741
742
743
744
		int width = value_area_get_width (result, ei->pos);
		int height = value_area_get_height (result, ei->pos);

		if (width > height)
			v = value_area_fetch_x_y (result, index, height - 1, ei->pos);
		else
			v = value_area_fetch_x_y (result, width - 1, index, ei->pos);
745
		return value_dup (v);
Michael Meeks's avatar
Michael Meeks committed
746
	}
747

748
	return value_new_error_NA (ei->pos);
Michael Meeks's avatar
Michael Meeks committed
749
750
}

751
752
/***************************************************************************/

753
754
755
static GnmFuncHelp const help_match[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=MATCH\n"
Morten Welinder's avatar
Morten Welinder committed
756
	   "@SYNTAX=MATCH(seek,vector[,type])\n"
Michael Meeks's avatar
Michael Meeks committed
757
758

	   "@DESCRIPTION="
759
	   "MATCH function finds the row index of @seek in @vector "
Jukka Pekka's avatar
Jukka Pekka committed
760
761
	   "and returns it.\n"
	   "\n"
Michael Meeks's avatar
Michael Meeks committed
762
	   "If the area is longer than it is wide then the sense of the "
Jukka Pekka's avatar
Jukka Pekka committed
763
	   "search is rotated. Alternatively a single array can be used.\n"
Michael Meeks's avatar
Michael Meeks committed
764
	   "\n"
Jukka Pekka's avatar
Jukka Pekka committed
765
766
767
768
	   "* The @type parameter, which defaults to +1, controls the search:\n"
	   "* If @type = 1, MATCH finds largest value <= @seek.\n"
	   "* If @type = 0, MATCH finds first value == @seek.\n"
	   "* If @type = -1, MATCH finds smallest value >= @seek.\n"
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
769
770
771
772
	   "* For @type = 0, the data can be in any order.  "
	   "* For @type = -1 and @type = +1, "
	   "the data must be sorted.  (And in these cases, MATCH uses "
	   "a binary search to locate the index.)\n"
Jukka Pekka's avatar
Jukka Pekka committed
773
	   "* If @seek could not be found, #N/A is returned.\n"
Michael Meeks's avatar
Michael Meeks committed
774
775
776
777
	   "\n"
	   "@EXAMPLES=\n"
	   "\n"
	   "@SEEALSO=LOOKUP")
778
779
	},
	{ GNM_FUNC_HELP_END }
Michael Meeks's avatar
Michael Meeks committed
780
781
};

Jody Goldberg's avatar
Jody Goldberg committed
782
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
783
gnumeric_match (FunctionEvalInfo *ei, GnmValue const * const *args)
Michael Meeks's avatar
Michael Meeks committed
784
{
785
	int type, index = -1;
Jody Goldberg's avatar
Jody Goldberg committed
786
787
	int width = value_area_get_width (args[1], ei->pos);
	int height = value_area_get_height (args[1], ei->pos);
788

789
	if (!find_type_valid (args[0]))
790
		return value_new_error_NA (ei->pos);
Michael Meeks's avatar
Michael Meeks committed
791

792
	if (width > 1 && height > 1)
793
		return value_new_error_NA (ei->pos);
794

795
	type = VALUE_IS_EMPTY (args[2]) ? 1 : value_get_as_int (args[2]);
Michael Meeks's avatar
Michael Meeks committed
796

797
	if (type == 0)
798
		index = find_index_linear (ei, args[0], args[1], type,
799
					   width > 1 ? FALSE : TRUE);
800
	else
801
802
		index = find_index_bisection (ei, args[0], args[1], type,
					      width > 1 ? FALSE : TRUE);
803

804
	if (index >= 0)
805
	        return value_new_int (index+1);
806
	return value_new_error_NA (ei->pos);
Michael Meeks's avatar
Michael Meeks committed
807
808
}

809
810
/***************************************************************************/

811
812
813
static GnmFuncHelp const help_indirect[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=INDIRECT\n"
814
	   "@SYNTAX=INDIRECT(ref_text[,format])\n"
Michael Meeks's avatar
Michael Meeks committed
815

816
	   "@DESCRIPTION="
817
	   "INDIRECT function returns the contents of the cell pointed to "
Jody Goldberg's avatar
Jody Goldberg committed
818
	   "by the @ref_text string. The string specifies a single cell "
819
	   "reference the format of which is either A1 or R1C1 style. The "
Andreas J. Guelzow's avatar
Andreas J. Guelzow committed
820
821
	   "style is set by the @format boolean, which defaults to the A1 "
	   "style.\n"
822
	   "\n"
Jukka Pekka's avatar
Jukka Pekka committed
823
	   "* If @ref_text is not a valid reference returns #REF! "
824
	   "\n"
825
	   "@EXAMPLES=\n"
826
827
	   "If A1 contains 3.14 and A2 contains A1, then\n"
	   "INDIRECT(A2) equals 3.14.\n"
828
	   "\n"
829
	   "@SEEALSO=AREAS,INDEX,CELL")
830
831
	},
	{ GNM_FUNC_HELP_END }
832
833
};

Jody Goldberg's avatar
Jody Goldberg committed
834
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
835
gnumeric_indirect (FunctionEvalInfo *ei, GnmValue const * const *args)
836
{
Morten Welinder's avatar
Morten Welinder committed
837
	GnmParsePos  pp;
Jody Goldberg's avatar
Jody Goldberg committed
838
	GnmValue *res = NULL;
Jody Goldberg's avatar
Jody Goldberg committed
839
840
841
842
	GnmExpr const *expr;
	char const *text = value_peek_string (args[0]);
	GnmExprConventions const *convs = gnm_expr_conventions_default;

843
	if (args[1] && !value_get_as_checked_bool (args[1]))
Jody Goldberg's avatar
Jody Goldberg committed
844
845
846
847
848
		convs = gnm_expr_conventions_r1c1;

	expr = gnm_expr_parse_str (text,
		parse_pos_init_evalpos (&pp, ei->pos),
		GNM_EXPR_PARSE_DEFAULT, convs, NULL);
Michael Meeks's avatar
Michael Meeks committed
849

Jody Goldberg's avatar
Jody Goldberg committed
850
	if (expr != NULL) {
Jody Goldberg's avatar
Jody Goldberg committed
851
		res = gnm_expr_get_range (expr);
852
		gnm_expr_unref (expr);
Jody Goldberg's avatar
Jody Goldberg committed
853
	}
854
	return (res != NULL) ? res : value_new_error_REF (ei->pos);
855
}
Michael Meeks's avatar
Michael Meeks committed
856

Jody Goldberg's avatar
Jody Goldberg committed
857
858
/*****************************************************************************/

859
860
861
static GnmFuncHelp const help_index[] = {
	{ GNM_FUNC_HELP_OLD,
	F_(
862
	"@FUNCTION=INDEX\n"
863
	"@SYNTAX=INDEX(array[,row, col, area])\n"
864
865
	"@DESCRIPTION="
	"INDEX gives a reference to a cell in the given @array."
Jukka Pekka's avatar
Jukka Pekka committed
866
867
868
	"The cell is pointed out by @row and @col, which count the rows and "
	"columns in the array.\n"
	"\n"
J.H.M. Dassen (Ray)'s avatar
J.H.M. Dassen (Ray) committed
869
	"* If @row and @col are omitted the are assumed to be 1.\n"
Jukka Pekka's avatar
Jukka Pekka committed
870
871
	"* If the reference falls outside the range of the @array, INDEX "
	"returns a #REF! error.\n"
872
873
	"\n"
	"@EXAMPLES="
Jukka Pekka's avatar
Jukka Pekka committed
874
	"Let us assume that the cells A1, A2, ..., A5 contain numbers 11.4, "