You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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:
…and you want to produce this list:
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:
…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:
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)
Then click the Format button, the Font tab, and select a white font:
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.
Comments: (5) Collapse
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
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().
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)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.
Or even simpler:
To have the list in accending sequence
SMALL($A$1:$A$14,ROW())
Or descending
=LARGE($A$1:$A$14,ROW())
ianB, the goal is to filter the list in its actual order, not sorted in either ascending or descending order.
Comments: (loading) Collapse