Formula to Access a List of Values Interspersed with Zeros or Blanks

re: Formula to Access a List of Values Interspersed with Zeros or Blanks

leniel — Wed, 01 Jul 2009 03:46:12 GMT

Hi Bob,

Really good post! :)

One thing that I think should be emphasized with a bold font is this phrase:

It’s an array formula which means that you must press Ctrl+Shift+Enter after entering the formula instead of just Enter.

Best wishes,

Leniel Macaferi

http://leniel.net

re: Formula to Access a List of Values Interspersed with Zeros or Blanks

Lionel Cazarre — Wed, 01 Jul 2009 12:17:29 GMT

Shouldn't the formula be:

{=INDEX($A$1:$A$14,SMALL(IF($A$1:$A$14<>0,ROW($1:$14),""),ROW()))}

The formula did not work with ROW(A1). But it returns the correct result with ROW().

re: Formula to Access a List of Values Interspersed with Zeros or Blanks

Harlan Grove — Fri, 03 Jul 2009 06:04:05 GMT

Several improvements.

First, if you want only POSITIVE numbers, then make the criterion >0, NOT <>0. If either positive or negative numbers are acceptable, then zero had also better be acceptable. IOW, it's almost always a domain error to exclude only zero rather than exclude nonpositives or nonnegatives.

Second, why not return "" rather than an error and use formatting to hide the errors? If you need to perform subsequent calculations on these INDEX formula results, you'd need to use IF calls and array formulas to skip error values. Much better in the long run to return "" in the first place.

Third, using ROW(A1) works in cell E1 (yes it does, Lionel Cazarre), but it becomes awkward when you want the topmost filtered result in, say, G7 but you don't want problems if rows are inserted or deleted. Better to use a literal 1 in cell G7 and a COUNT(G$7:G7)+1 in G8, then fill G8 down as needed.

Fourth, your formula would return "" for cells containing zero-length strings and "0" for cells evaluating to the text string "0". Unlikely users who'd find this useful would want that functionality. More likely they'd want to filter out just the positive numbers.

So if the topmost result should be in cell G7, better to use

G7:

=IF(COUNTIF(A1:A14,">0"),INDEX(A1:A14,

MATCH(1,ISNUMBER(A1:A14)*(A1:A14>0),0)),

"")

G8:

=IF(COUNT(G$7:G7)<COUNTIF(A$1:A$14,">0"),INDEX(A$1:A$14,

SMALL(IF(ISNUMBER(A$1:A$14)*A$1:A$14>0),ROW(A$1:A$14)),

COUNT(G$7:G7)+1)),"")

Fill G8 down as far as needed.

re: Formula to Access a List of Values Interspersed with Zeros or Blanks

ianB — Mon, 06 Jul 2009 13:53:21 GMT

Or even simpler:

To have the list in accending sequence

SMALL($A$1:$A$14,ROW())

Or descending

=LARGE($A$1:$A$14,ROW())

re: Formula to Access a List of Values Interspersed with Zeros or Blanks

Harlan Grove — Thu, 09 Jul 2009 07:36:51 GMT

ianB, the goal is to filter the list in its actual order, not sorted in either ascending or descending order.