Excel's INDEX() is much more powerful than Gnumeric's, I've guessed that
this is simply due to lack of time for implementation and so I offer here
a patch that much improves the situation
This patch alters the behaviour of INDEX() and updates the help to match.
* INDEX() now returns a reference when appropriate rather than a simple value
which means you can use it with functions like ROW(), COLUMN() and CELL() to
give useful results.
* INDEX() now returns range references or arrays (as appropriate) for some
inputs which were previously poorly defined or error states. This matches
Excel much more closely than before e.g. INDEX(A1:C4,2,) returns a reference
to A2:C2 which can then be used together with array syntax to fill multiple
cells with results.
* INDEX() now has a special case matching things of the form INDEX(A1:A6,4)
which was previously meaningless in Gnumeric and now returns a reference to
A4. This is purely an Excel compatibility change and can be removed if
considered unsavoury.
I can also prepare an XL compat test sheet for INDEX() if that's helpful
Nick.
--- fn-lookup/functions.c.orig 2005-09-21 06:10:08.000000000 +0100
+++ fn-lookup/functions.c 2005-11-20 02:19:33.000000000 +0000
@@ -860,21 +860,31 @@
{ GNM_FUNC_HELP_OLD,
F_(
"@FUNCTION=INDEX\n"
- "@SYNTAX=INDEX(array[,row, col, area])\n"
+ "@SYNTAX=INDEX(reference, row[,col, area])\nINDEX(array, row [,col])\n"
"@DESCRIPTION="
- "INDEX gives a reference to a cell in the given @array."
- "The cell is pointed out by @row and @col, which count the rows and "
- "columns in the array.\n"
+ "INDEX can be applied either to an @array or a @reference to one or "
+ "more cell ranges.\n"
+ "In the reference form @area selects which cell range is used, "
+ "and @row and @col the row and column within that range. The result "
+ "is a reference.\n"
+ "In the array form @row and @col choose the row and column in the "
+ "array to be returned. The result is an array or single value."
"\n"
- "* If @row and @col are omitted the are assumed to be 1.\n"
- "* If the reference falls outside the range of the @array, INDEX "
+ "* When only one range is specified in @reference, @area may be "
+ "omitted.\n"
+ "* If @row or @col are omitted or zero the entire row, column or "
+ "area is returned as a reference or array as appropriate.\n"
+ "* If @row or @col are negative, INDEX returns a #VALUE! error.\n"
+ "* If @row or @col falls beyond the array or reference, INDEX "
"returns a #REF! error.\n"
"\n"
"@EXAMPLES="
+ "INDEX({7,8,5,3},1,2) equals 8\n"
"Let us assume that the cells A1, A2, ..., A5 contain numbers 11.4, "
- "17.3, 21.3, 25.9, and 40.1. Then INDEX(A1:A5,4,1,1) equals 25.9\n"
+ "17.3, 21.3, 25.9, and 40.1. Then INDEX(A1:A5,4,1,1) returns a "
+ "reference to cell A4, with value 25.9\n"
"\n"
- "@SEEALSO=")
+ "@SEEALSO=ADDRESS,INDIRECT,LOOKUP,MATCH,OFFSET")
},
{ GNM_FUNC_HELP_END }
};
@@ -912,15 +922,76 @@
v = gnm_expr_eval (source, ei->pos, GNM_EXPR_EVAL_PERMIT_NON_SCALAR);
- if (elem[1] < 0 ||
- elem[1] >= value_area_get_width (v, ei->pos) ||
- elem[0] < 0 ||
- elem[0] >= value_area_get_height (v, ei->pos)) {
- value_release (v);
- return value_new_error_REF (ei->pos);
+ if (v->type == VALUE_CELLRANGE) {
+ GnmCellRef a = v->v_range.cell.a;
+ GnmCellRef b = v->v_range.cell.b;
+
+ /* get absolute row and column */
+ int a_row = a.row + (a.row_relative ? ei->pos->eval.row : 0);
+ int a_col = a.col + (a.col_relative ? ei->pos->eval.col : 0);
+ int b_row = b.row + (b.row_relative ? ei->pos->eval.row : 0);
+ int b_col = b.col + (b.col_relative ? ei->pos->eval.col : 0);
+
+ /* ensure everything is in the expected order */
+ if (a_row > b_row) {
+ int tmp;
+ tmp = a_row; a_row = b_row; b_row = tmp;
+ }
+
+ if (a_col > b_col) {
+ int tmp;
+ tmp = a_col; a_col = b_col; b_col = tmp;
+ }
+
+ /* XL special case, lone 2nd argument is column for one row */
+ if (a_row == b_row && i == 1) {
+ elem[1] = elem[0];
+ elem[0] = 0;
+ }
+
+ /* too large for specific array bounds => #REF! */
+ if (a_row + elem[0] > b_row || a_col + elem[1] > b_col) {
+ value_release (v);
+ return value_new_error_REF (ei->pos);
+ /* too small or both zero => #VALUE! see Excel */
+ } else if (elem[0] < -1 || elem[1] < -1 ||
+ (elem[0] == -1 && elem[1] == -1)) {
+ value_release (v);
+ return value_new_error_VALUE (ei->pos);
+ }
+
+ if (elem[0] != -1) {
+ b_row = a_row += elem[0];
+ }
+
+ if (elem[1] != -1) {
+ b_col = a_col += elem[1];
+ }
+
+ /* singleton or strip */
+ a.row = a_row;
+ a.row_relative = FALSE;
+ a.col = a_col;
+ a.col_relative = FALSE;
+ b.row = b_row;
+ b.row_relative = FALSE;
+ b.col = b_col;
+ b.col_relative = FALSE;
+
+ /* new reference */
+ res = value_new_cellrange (&a, &b, ei->pos->eval.col,
ei->pos->eval.row);
+ } else {
+ if (elem[1] < 0 ||
+ elem[1] >= value_area_get_width (v, ei->pos) ||
+ elem[0] < 0 ||
+ elem[0] >= value_area_get_height (v, ei->pos)) {
+
+ res = value_new_error_REF (ei->pos);
+ } else {
+ res = value_dup (value_area_fetch_x_y (v, elem[1],
elem[0], ei->pos));
+ }
}
- res = value_dup (value_area_fetch_x_y (v, elem[1], elem[0], ei->pos));
value_release (v);
return res;
}
@@ -1292,7 +1363,7 @@
{ "indirect", "s|b",N_("ref_string,format"),
help_indirect, gnumeric_indirect, NULL, NULL, NULL, NULL,
GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE,
GNM_FUNC_TEST_STATUS_BASIC },
- { "index", "A|fff",N_("reference,row,col,area"),
+ { "index", "Af|ff",N_("reference,row,col,area"),
help_index, NULL, gnumeric_index, NULL, NULL, NULL,
GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE,
GNM_FUNC_TEST_STATUS_BASIC },
{ "lookup", "BA|r", N_("val,range,range"),
_______________________________________________
gnumeric-list mailing list
gnumeric-list@gnome.org
http://mail.gnome.org/mailman/listinfo/gnumeric-list
