Back
Excel

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

Today’s author is Bob Umlas, an Excel MVP since 1994, and author of This isn’t Excel, it’s Magic, a book that is filled with tips and tricks for getting the most out of Excel.

This tip is a formula which enables you to access a list of values interspersed with zeros or blanks and pick up only the non-zero values in the same sequence they’re listed. It’s better to illustrate. Suppose you have this list in A1:A14:

image01

…and you want to produce this list:

image02

The following formula entered in E1 and filled down to E6 does the trick. It’s an array formula which means that you must press Ctrl+Shift+Enter after entering the formula instead of just Enter:

=INDEX($A$1:$A$14,SMALL(IF($A$1:$A$14<>0,ROW($1:$14),””),ROW(A1)))

Let’s take the formula apart and see how it works. The inner IF-statement, IF($A$1:$A$14<>0,ROW($1:$14),””), checks for non-zeros, and if it is a non-zero, it returns the row number; otherwise it returns blanks. If you select that portion of the formula in E1 and press the F9 key, you’ll see this:

image03

…which means that rows 1,4,7,9,10 and 14 do not contain zeros or blanks. ROW(A1) returns the value 1 and is used instead of simply the number 1 because this formula is being filled down, and in the row below it will become ROW(A2), returning a 2, etc. So, the SMALL function now returns the smallest value from that list, or 1. In the row below, we have ROW(A2) or 2, and the 2nd smallest value is 4. So each formula returns the row number for the non-zero cells. That in turn is passed to the INDEX function, and we effectively have these formulas:

=INDEX(A1:A14,1)
=INDEX(A1:A14,4)
=INDEX(A1:A14,7)
and so on, which returns the values we need for the list of non-zero values.

As an additional tip, because it may not be clear how far down to fill, you can use conditional formatting to hide the potential errors if you were to drag down too far. That is, suppose you initially dragged the formula down to E8:

image04

You can hide these this way. Select all of column E, then click Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter this formula:

=ISERROR(E1)

image05

Then click the Format button, the Font tab, and select a white font:

image06

Then, when you click OK all the way out, the #NUM! errors won’t show.

The reason this formula must be entered as an array-formula is because the IF(A1:A14<>0… portion of the formula requires it. The IF-statement normally takes one value to test for true/false, not an array of values as we did here. If you don’t use Ctrl+Shift+Enter to create an array formula, you will get #VALUE! and #NUM! errors.