returning nonblank cells... procedure

[woodzilla]

In the book, Excel 2000 formulas, by Walkenbach. I've been trying to use the returning nonblank cells from a range formula on page 354. Even when I use the sample file, I have not been able to reproduce the results from the sample. At best, I get the top number from the source column repeated.
I must be missing a procedure somewhere. So If someone that has used this formula, can explain the step by step procedure, many thanks by me.
I've looked in the search for prior posts for info on this, as well as other websites and books. Just too much missing information for me to set this up properly. And I don't understand the complex formula, other than changing the name of the source array.

 

[Aladin Akyurek]

On 2002-10-03 22:34, woodzilla wrote:
In the book, Excel 2000 formulas, by Walkenbach. I've been trying to use the returning nonblank cells from a range formula on page 354. Even when I use the sample file, I have not been able to reproduce the results from the sample. At best, I get the top number from the source column repeated.
I must be missing a procedure somewhere. So If someone that has used this formula, can explain the step by step procedure, many thanks by me.
I've looked in the search for prior posts for info on this, as well as other websites and books. Just too much missing information for me to set this up properly. And I don't understand the complex formula, other than changing the name of the source array.

You probably need just to enter the formula in a cell then select a range from this cell on as big as data range. After having selected the range, you need to hit control+shift+enter at the same time.

 

[woodzilla]

After toying with the book example, its clear its makes a difference on how the formula is copied into the range of cells. To copy the formula in one cell and paste it into a range of cells does not work. To copy a range of cells that already have the formulas in all of them, and paste them into a nearby range of cells does work.
What is the proper way of entering a formula into a range of cells by copy and paste?
I understand one needs to select a range of cells, then enter the formula in the formula bar. But I can't seem to copy a formula into the formula bar. This is a very long formula.

 

[Aladin Akyurek]

On 2002-10-04 16:34, woodzilla wrote:
After toying with the book example, its clear its makes a difference on how the formula is copied into the range of cells. To copy the formula in one cell and paste it into a range of cells does not work. To copy a range of cells that already have the formulas in all of them, and paste them into a nearby range of cells does work.
What is the proper way of entering a formula into a range of cells by copy and paste?
I understand one needs to select a range of cells, then enter the formula in the formula bar. But I can't seem to copy a formula into the formula bar. This is a very long formula.

There is a big difference between an array-formula that returns a scalar/single cell result and one that returns an array-constant.

Enclose that array-formula and enclose it with INDEX...

=INDEX(the-target-array-formula,ROW(1:1))

Array-enter this in a single cell and copy down as you would copy an odinary formula...

You'll see the INDEX function to pull off the lements of the computed array constant one by one...

 

[woodzilla]

After much fooling around, I figured out how to use the RETURNING NONBLANK CELLS FROM A RANGE, from page 354 of the Excel 2000 Formulas book. So for all other readers of this message, here's what I did.

The problem, I have dozens of columns of data, each with 100s of rows of numbers, over half that are blank cells. I wanted to summarize the columns of numbers so there is no blanks, and updates dynamically.

Select each column of source data cells,name each one,(from menu, select insert-name-define).

Copy or type in the original formula into wordpad. Copy that as many times as you have named source data columns. Then you need to go into each formula and replace the word "data" with the name of your own named data column, in 7 different places within the formula.

In Excel, select a new destination column, in my case, 300 rows,(can be on a different sheet) copy and paste the modified formula from wordpad into the formula bar, not a cell. Hit shift+ctrl+enter to send the new formula into the new array.

The new list of numbers without blanks, should pop up in a few seconds.

 

[Aladin Akyurek]

On 2002-10-05 11:38, woodzilla wrote:
After much fooling around, I figured out how to use the RETURNING NONBLANK CELLS FROM A RANGE, from page 354 of the Excel 2000 Formulas book. So for all other readers of this message, here's what I did.

The problem, I have dozens of columns of data, each with 100s of rows of numbers, over half that are blank cells. I wanted to summarize the columns of numbers so there is no blanks, and updates dynamically.

Select each column of source data cells,name each one,(from menu, select insert-name-define).

Copy or type in the original formula into wordpad. Copy that as many times as you have named source data columns. Then you need to go into each formula and replace the word "data" with the name of your own named data column, in 7 different places within the formula.

In Excel, select a new destination column, in my case, 300 rows,(can be on a different sheet) copy and paste the modified formula from wordpad into the formula bar, not a cell. Hit shift+ctrl+enter to send the new formula into the new array.

The new list of numbers without blanks, should pop up in a few seconds.

Wordpad? No need for that... Just put the Walkenbach formula in the first cell of the destination column, edit the formula for ranges in that cell by substituting defined names then select the range as big as the source range from the initial cell on [it seems you find this step difficult to apply] and hit control+shift+enter.

By the way, since you're applying this formula to a dozen columns times ~300 rows, you'll notice a considerable performance degredation.

Here is another suggestion to counteract a bit the performance problems.

Download and install the morefunc.xll add-in from http://longre.free.fr/english/index.html.

The figure below shows the relevant computations...

BigNum is defined as follows:

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum as name in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

The essential array-formula is:

=IF(ISNUMBER(SETV(SMALL(IF(Data<>"",INTVECTOR($E$3,1,0,1)),INTVECTOR($E$3,1,0,1)))),INDEX(Data,GETV()),"")

multi-cell array formulas.xls
	A	B	C	D	E	F	G
1	Returnonlynon-blankcellsfromarange
2
3					20
4	Data		Non-Blanks		Non-Blanks
5	33		33		33
6	-33		-33		-33
7	44		44		44
8			-5		-5
9	-5		0		0
10	0		43		43
11	43		-2		-2
12			-3		-3
13	-2		-33		-33
14	-3		5		5
15	-33		9		9
16			11		11
17	5		12		12
18
19
20
21	9
22
23	11
24	12
Sheet2