View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default Listing data without blank rows

Danny,

the formula JMB suggested had the following skeleton:

=INDEX(data,SMALL(IF(conditions),ROW())

The IF(Conditions) is generating a virtual array, which only only
includes non-blank rows. So, SMALL(..., ROW()) was to find first the
smallest, then the second smallest etc, of this array. If the virtual
array has fewer elements than the current row, ROW() will try to find
an element that does not exist. Also, if your data start in row 10, the
formula would start to find the 10th element, then 11th etc.

So, first modification was to subtract 9 from ROW(). We used ROW($A$9)
in order to show exactly which cell is used to start the data. This
leads us to the formula:

=INDEX(data,SMALL(IF(conditions),ROW()-ROW($A$9))

The second modification, which led to this length, was to see which
part could create the error, and this would be SMALL. This leads us to
the general philosophy of trapping errors:

=IF(ISERROR(formula),"",formula).

This can be simplified if instead of using the entire formula in
ISERROR, we use only the offending part. So your formula became:

=IF(ISERROR(LARGE(...),"",modifed_formula)

which is what you used.

HTH
Kostis