Return non-blank cells

  • Thread starter Thread starter Gene
  • Start date Start date
G

Gene

I got the following formula from a book which was accompanied by a CD. I
downloaded the example which did exactly what I wanted. When I dluplicated
the example on a new workbook, however, it did not work.


{IFERROR(INDEX(Data,SMALL(IF(Data<>"",ROW("1:"&ROWS(Data)))),ROW("1:"&ROWS(Data))))),"")}

Data Result My Result
33 33 33
-10 -10 33
20 33
20 15 33
etc. I think I have the latest updates for 2007 and the book
was of course written some time ago. I evaluated the formulas and the
process was the same on both the download and my worksheet. Only the result
was different. Can someone advise.?
 
First thing that comes to my mind is two things.

Idea 1:
You're row counter is incrementing correctly, perhaps one of the quotation
marks is in wrong spot? Check to make sure both formulas match.

Idea 2:
Since results are only first line of data, your formula does not appear to
be operating as an array. Make sure formula is confirmed using
Ctrl+Shift+Enter, not just enter, and XL puts the curly brackets in itself.

Also, since your formula is returning a result, you must somehow have this
corrected, but the formula you posted is unbalanced in it's parathensis. By
this I mean, you have 2 more )'s than ('.
 
First thing that comes to my mind is two things.

Idea 1:
You're row counter is incrementing correctly, perhaps one of the quotation
marks is in wrong spot? Check to make sure both formulas match.

Idea 2:
Since results are only first line of data, your formula does not appear to
be operating as an array. Make sure formula is confirmed using
Ctrl+Shift+Enter, not just enter, and XL puts the curly brackets in itself.

Also, since your formula is returning a result, you must somehow have this
corrected, but the formula you posted is unbalanced in it's parathensis. By
this I mean, you have 2 more )'s than ('.
 
This expression is incorrect:

ROW("1:"&ROWS(Data))

You would need to wrap that inside the INDIRECT function like this:

ROW(INDIRECT("1:"&ROWS(Data)))

However, that's very inefficient.

Try it like this...

Assume you enter the first formula in cell C1.

Array entered**

=IFERROR(INDEX(Data,SMALL(IF(Data<>"",ROW(Data)),ROWS(C$1:C1))-MIN(ROW(Data))+1),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
This expression is incorrect:

ROW("1:"&ROWS(Data))

You would need to wrap that inside the INDIRECT function like this:

ROW(INDIRECT("1:"&ROWS(Data)))

However, that's very inefficient.

Try it like this...

Assume you enter the first formula in cell C1.

Array entered**

=IFERROR(INDEX(Data,SMALL(IF(Data<>"",ROW(Data)),ROWS(C$1:C1))-MIN(ROW(Data))+1),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
You are right about the INDIRECT. I didn't copy correctly. I did in the
test however. My first thought was the formula format was incorrect.but on
examination I realized the author was using the absence of the FALSE
statement in the IF as a means to skip the row in the result column. I
wondered if that was still available in 2007. The last statement in the
evaluation process in both my test and the downloaded worksheet was
IFERROR(33,""). I would expect 33 to be my result but in the downloaded
worksheet the result was the appropriate return????
 
You are right about the INDIRECT. I didn't copy correctly. I did in the
test however. My first thought was the formula format was incorrect.but on
examination I realized the author was using the absence of the FALSE
statement in the IF as a means to skip the row in the result column. I
wondered if that was still available in 2007. The last statement in the
evaluation process in both my test and the downloaded worksheet was
IFERROR(33,""). I would expect 33 to be my result but in the downloaded
worksheet the result was the appropriate return????
 
I would have to see the full original formula to tell you what's it doing
and how it's doing it.

I recommend not using that ROW(INDIRECT("1:"&ROWS(Data))) method. The basic
formula to extract the data is already fairly calculation intensive so you
want to do everything you can to make it as efficient as possible.
 
I would have to see the full original formula to tell you what's it doing
and how it's doing it.

I recommend not using that ROW(INDIRECT("1:"&ROWS(Data))) method. The basic
formula to extract the data is already fairly calculation intensive so you
want to do everything you can to make it as efficient as possible.
 
I'll try to avoid the clause you identified but for the sake of showing you
I will duplicate it here. Incidentally, can you explain why the downloaded
version worked but I could not duplicate it.?

={IFERROR(INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),Row(INDIRECT("1:"&ROWS(Data))))),"")}

I thought the exclusion of the FALSE portion of the IF statement was an
interesting technique. Can you comment on it?
 
I'll try to avoid the clause you identified but for the sake of showing you
I will duplicate it here. Incidentally, can you explain why the downloaded
version worked but I could not duplicate it.?

={IFERROR(INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),Row(INDIRECT("1:"&ROWS(Data))))),"")}

I thought the exclusion of the FALSE portion of the IF statement was an
interesting technique. Can you comment on it?
 
That formula is designed to be entered as a range array. For example, you
select a range of 10 cells like C1:C10 then you enter the formula in all the
cells at once. Is that how you entered it?
the exclusion of the FALSE portion of the IF statement
was an interesting technique. Can you comment on it?
Click to expand...

You're only interested in the cells that meet the condition of <>"" (not
equal to blank). So, we have the logical test:

IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data))))

Where the condition is TRUE, the row numbers are passed to the SMALL
function. Since the value_if_false argument was omitted the default return
is the logical value FALSE. These are also passed to the SMALL function.We
might have an array like this being passed to the SMALL function:

1;FALSE;FALSE;4;5;FALSE

The SMALL function ignores the logical FALSE and calculates solely on the
numeric values.
 
That formula is designed to be entered as a range array. For example, you
select a range of 10 cells like C1:C10 then you enter the formula in all the
cells at once. Is that how you entered it?
the exclusion of the FALSE portion of the IF statement
was an interesting technique. Can you comment on it?
Click to expand...

You're only interested in the cells that meet the condition of <>"" (not
equal to blank). So, we have the logical test:

IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data))))

Where the condition is TRUE, the row numbers are passed to the SMALL
function. Since the value_if_false argument was omitted the default return
is the logical value FALSE. These are also passed to the SMALL function.We
might have an array like this being passed to the SMALL function:

1;FALSE;FALSE;4;5;FALSE

The SMALL function ignores the logical FALSE and calculates solely on the
numeric values.
 
Back
Top