referencing non-blank cells

[Guest]

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

 

[Herbert Seidenberg]

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<>"")*seq1>ROW()),0)-ROW()-1)
*set1 1:1)
and copy down.

 

[Guest]

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

Cheers, MM

 

[Guest]

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]

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.

 

[Biff]

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$25>0,0)-1)*A1,"")

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

Biff

 

[Guest]

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

 

[Dav]

Biff's formula works for me!

Regards

Da

 

[Guest]

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...

 

[Guest]

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

 

[Biff]

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

 

[Guest]

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]

You're welcome!

Biff

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

Thank you all for your help.

MM