referencing non-blank cells
 

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

referencing non-blank cells
 

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.

referencing non-blank cells
 

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.

referencing non-blank cells
 

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.

referencing non-blank cells
 

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.

referencing non-blank cells
 

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.

referencing non-blank cells
 

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

referencing non-blank cells
 

Biff's formula works for me!

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=544223

referencing non-blank cells
 

You're right!
I just re-tried it in a new sheet with made up numbers and it worked. Must
be something to do with how my original sheet is set up.

Back to the drawing board...

"Dav" wrote:


Biff's formula works for me!

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=544223

referencing non-blank cells
 

Ok, I've had a chance to look into this some more.

If I put Biff's formula into a column next to a column of made up numbers
and spaces, it works.

When I put it into my sheet it returns 0. Presumably, this is because the
(MATCH)-1 part evaluates to zero.

The formula in the cell to the left (in column I) is:

=IF(A3="End of job",
"end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,""))," ",IF(ISBLANK(C2)=FALSE,C2+H2,"")))

I tried inserting another column with =I1 etc in it - same result.

Hope this helps,

MM

referencing non-blank cells
 

The formula in the cell to the left (in column I) is:

=IF(A3="End of job",
"end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,"")), "",IF(ISBLANK(C2)=FALSE,C2+H2,"")))

OK, that means the cells have formula blanks in them, so, try this:

Still array entered:

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

Biff

"MM" wrote in message
...
Ok, I've had a chance to look into this some more.

If I put Biff's formula into a column next to a column of made up numbers
and spaces, it works.

When I put it into my sheet it returns 0. Presumably, this is because the
(MATCH)-1 part evaluates to zero.

The formula in the cell to the left (in column I) is:

=IF(A3="End of job",
"end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,""))," ",IF(ISBLANK(C2)=FALSE,C2+H2,"")))

I tried inserting another column with =I1 etc in it - same result.

Hope this helps,

MM

referencing non-blank cells
 

Yep, that does the trick! I was going to try something similar this morning
as it happens.

Thank you all for your help.

MM

"Biff" wrote:

The formula in the cell to the left (in column I) is:

=IF(A3="End of job",
"end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,"")), "",IF(ISBLANK(C2)=FALSE,C2+H2,"")))

OK, that means the cells have formula blanks in them, so, try this:

Still array entered:

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

Biff

"MM" wrote in message
...
Ok, I've had a chance to look into this some more.

If I put Biff's formula into a column next to a column of made up numbers
and spaces, it works.

When I put it into my sheet it returns 0. Presumably, this is because the
(MATCH)-1 part evaluates to zero.

The formula in the cell to the left (in column I) is:

=IF(A3="End of job",
"end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,""))," ",IF(ISBLANK(C2)=FALSE,C2+H2,"")))

I tried inserting another column with =I1 etc in it - same result.

Hope this helps,

MM

referencing non-blank cells
 

You're welcome!

Biff

"MM" wrote in message
...
Yep, that does the trick! I was going to try something similar this
morning
as it happens.

Thank you all for your help.

MM

"Biff" wrote:

The formula in the cell to the left (in column I) is:

=IF(A3="End of job",
"end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,"")), "",IF(ISBLANK(C2)=FALSE,C2+H2,"")))

OK, that means the cells have formula blanks in them, so, try this:

Still array entered:

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

Biff

"MM" wrote in message
...
Ok, I've had a chance to look into this some more.

If I put Biff's formula into a column next to a column of made up
numbers
and spaces, it works.

When I put it into my sheet it returns 0. Presumably, this is because
the
(MATCH)-1 part evaluates to zero.

The formula in the cell to the left (in column I) is:

=IF(A3="End of job",
"end",IF(ISERROR(IF(ISBLANK(C2)=FALSE,C2+H2,""))," ",IF(ISBLANK(C2)=FALSE,C2+H2,"")))

I tried inserting another column with =I1 etc in it - same result.

Hope this helps,

MM