Hi all,

Say I have a column with cell values 4, 0, 0, 5, 0 , 0, 0, 2, 0, 3

What I am trying to do is have in the next column a formula which returns
the number adjacent to it multiplied by the number of blanks between it and
the next non-blank cell.

So, for the example above, the output would be 8, 0, 0, 15, 0, 0, 0, 2 etc

Thanks in advance,
MM

Assuming the blank cells do not contain zeros,
(but may contain a formula that results in blanks),
and the input column starts at A1 and is named set1 with
Insert Name Define
set1 Refers To: =A1:A100
seq1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,ROWS(set1)))
Into B1 enter this array formula with Cntrl+Shift+Enter
=IF(set1 1:1="","",(MATCH(1,--((set1<"")*seq1ROW()),0)-ROW()-1)
*set1 1:1)
and copy down.

Thanks very much, Herbert, I'll give that a shot.

Cheers, MM

"Herbert Seidenberg" wrote:

Assuming the blank cells do not contain zeros,
(but may contain a formula that results in blanks),
and the input column starts at A1 and is named set1 with
Insert Name Define
set1 Refers To: =A1:A100
seq1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,ROWS(set1)))
Into B1 enter this array formula with Cntrl+Shift+Enter
=IF(set1 1:1="","",(MATCH(1,--((set1<"")*seq1ROW()),0)-ROW()-1)
*set1 1:1)
and copy down.

It doesn't quite work, I'm afraid.

What I have (in row I) is : 12
3 spaces
11
7 spaces
11
7 spaces
12
3 spaces

Your formula gives the correct value (36) for the first occupied row but
then gives 33 instead of 77 for the second and #N/A for the last two. My
guess is that this is due to MATCH requiring the array to be sorted (which it
can't be), but I've never used arrays before, so I'm only guessing.

I'll keep plugging away - at the very least you've opened a new avenue of
exploration for me!

Cheers, MM

"Herbert Seidenberg" wrote:

Assuming the blank cells do not contain zeros,
(but may contain a formula that results in blanks),
and the input column starts at A1 and is named set1 with
Insert Name Define
set1 Refers To: =A1:A100
seq1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,ROWS(set1)))
Into B1 enter this array formula with Cntrl+Shift+Enter
=IF(set1 1:1="","",(MATCH(1,--((set1<"")*seq1ROW()),0)-ROW()-1)
*set1 1:1)
and copy down.

The formula will not work if the data is arranged in a row.
It has to be in a column, starting at row 1 (one).
To make it work at any other starting row, you have to
bring seq into a displayed column and refer to seq instead of row().
Seq is a simple counting sequence 1,2,3....n as shown:
1 12 36
2
3
4
5 11 77
6
7
8
9
10
11
12
13 11 77
14
15
16
17
18
19
20
21 12 36
22
23
24
25 999 #N/A

The last number (999) will give a #N/A since it only serves
to define how many spaces are below the number 12.
If your data is arranged in a horizontal row, you can make
it work by substituting row refs with column refs.
Using R1C1 ref style might help.

Hi!

Try this:

Assume your data:

What I have (in row I) is : 12
3 spaces
11
7 spaces
11
7 spaces
12
3 spaces

Starts in cell A1. Based on the above that would make the range A1:A24. This
formula works but it needs a value in the last cell of the range. Since your
data range ends in A24 you could put (end) in cell A25. This is a fairly
common practice where ranges have empty cells at their end. So, now you
would have this data:

12
3 spaces
11
7 spaces
11
7 spaces
12
3 spaces
(End)

Then enter this formula in B1 as an array using the key combination of
CTRL,SHIFT,ENTER:

=IF(A1<"",(MATCH(TRUE,A2:A$250,0)-1)*A1,"")

Copy down to A24. A25 is not considered part of the data range, it's just a
"marker".

Biff

"MM" wrote in message
...
It doesn't quite work, I'm afraid.

What I have (in row I) is : 12
3 spaces
11
7 spaces
11
7 spaces
12
3 spaces

Your formula gives the correct value (36) for the first occupied row but
then gives 33 instead of 77 for the second and #N/A for the last two. My
guess is that this is due to MATCH requiring the array to be sorted (which
it
can't be), but I've never used arrays before, so I'm only guessing.

I'll keep plugging away - at the very least you've opened a new avenue of
exploration for me!

Cheers, MM

"Herbert Seidenberg" wrote:

Assuming the blank cells do not contain zeros,
(but may contain a formula that results in blanks),
and the input column starts at A1 and is named set1 with
Insert Name Define
set1 Refers To: =A1:A100
seq1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,ROWS(set1)))
Into B1 enter this array formula with Cntrl+Shift+Enter
=IF(set1 1:1="","",(MATCH(1,--((set1<"")*seq1ROW()),0)-ROW()-1)
*set1 1:1)
and copy down.

Herbert,

Sorry, my bad - the data is in a column not a row.

Biff, your formula just returns zeros against the non-blanks!

Thanks for the input,

MM