On Sun, Nov 27, 2005 at 10:59:36PM -0500, Jody Goldberg wrote: > > I can also prepare an XL compat test sheet for INDEX() if that's helpful > Please.
Such a sheet is attached, as is an updated patch which fixes two mistakes found when writing the tests. * INDEX(foo) should be #REF! * INDEX(foo,0,0) should be the entire range foo > Additionally this seems like a good time to move the docs to the new > format. Indeed some of your changes may strain the proposed system. > Please try to use the new layout and see how it works. I'm afraid I struggled pretty quickly with the new layout. INDEX has essentially two parallel but distinct functions. In the paper docs I have for Excel it's listed twice, once for each syntax. Is there any documentation on how to write function docs with the new format? Compared to what I've seen so far it needs at least: * Tables - for tiny spreadsheet examples (Excel uses these all over) and for lists in functions like CELL or ERROR.TYPE * A way to declare some parameters more than once, to describe e.g. INDEX(array,row[,column]) and INDEX(reference,row[,column,index]) Nick.
index.xls
Description: MS-Excel spreadsheet
--- functions.c.orig 2005-09-21 06:10:08.000000000 +0100
+++ functions.c 2005-11-29 00:54:39.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 }
};
@@ -903,6 +913,9 @@
value_release (v);
}
+ if (i == 0) /* too few parameters */
+ return value_new_error_REF (ei->pos);
+
if (source->any.oper == GNM_EXPR_OP_SET) {
source = gnm_expr_list_nth (source->set.set, elem[2]);
if (elem[2] < 0 || source == NULL)
@@ -910,17 +923,82 @@
} else if (elem[2] != 0)
return value_new_error_REF (ei->pos);
+
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;
+ }
+
+ if (i == 1) { /* lone 2nd argument */
+ if (a_row == b_row) { /* special case in Excel */
+ elem[1] = elem[0];
+ elem[0] = 0;
+ } else if (a_col != b_col) { /* not enough */
+ value_release (v);
+ return value_new_error_REF (ei->pos);
+ }
+ }
+
+ /* 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 => #VALUE! see Excel */
+ } else if (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 +1370,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
