Hello,
I am hoping that some kind person may be able to help with this problem.
I have a spreadsheet that can contain a value in column A to column AA or
even greater.
A simple example is this
A B C D E F G H I J K L
1 4 5 6 7 8 9 10 11 12 13 14 15
2 x x x x x x x x x x x x
3 x x x x x
4 x x x x x
I am using the below formulas (thanks to bob) to return the corresponding
value in row 1 for the first and last ocurrence of x in rows 2,3 and so on.
These formulas work fine however I would like to restrict the indexing to a
maximum of 4 columns. For example,in the above I would like row 2 to return
the values 4 and 7 for the first block of 4 x's, then 8 and 11 for the second
block,12 and 15 for the third block.
Row 2 would return 6 and 9 for the first and 10 and 10 for the second block.
I hope that this explains what I am after.
The current formulas return 1 and 15 for row 1, 6 and 10 fro row 2 and 10
and 14 for row 3.


=INDEX($104:$104,MIN(IF($E110:$AX110<"",COLUMN($E 110:$AX110))))
=INDEX($104:$104,MAX(IF($E110:$AX110<"",COLUMN($E 110:$AX110))))


Many Thanks
Allan

Allan,

Is this what you mean?

=INDEX($1:$1,MAX(IF($A2:INDEX(2:2,MIN(IF($A2:$AX2< "",COLUMN($A2:$AX2)))+4)<
"",COLUMN($A2:INDEX(2:2,MIN(IF($A2:$AX2<"",COLUM N($A2:$AX2)))+4)))))

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Allan from Melbourne" wrote
in message ...
Hello,
I am hoping that some kind person may be able to help with this problem.
I have a spreadsheet that can contain a value in column A to column AA or
even greater.
A simple example is this
A B C D E F G H I J K L
1 4 5 6 7 8 9 10 11 12 13 14 15
2 x x x x x x x x x x x x
3 x x x x x
4 x x x x x
I am using the below formulas (thanks to bob) to return the corresponding
value in row 1 for the first and last ocurrence of x in rows 2,3 and so
on.
These formulas work fine however I would like to restrict the indexing to
a
maximum of 4 columns. For example,in the above I would like row 2 to
return
the values 4 and 7 for the first block of 4 x's, then 8 and 11 for the
second
block,12 and 15 for the third block.
Row 2 would return 6 and 9 for the first and 10 and 10 for the second
block.
I hope that this explains what I am after.
The current formulas return 1 and 15 for row 1, 6 and 10 fro row 2 and 10
and 14 for row 3.


=INDEX($104:$104,MIN(IF($E110:$AX110<"",COLUMN($E 110:$AX110))))
=INDEX($104:$104,MAX(IF($E110:$AX110<"",COLUMN($E 110:$AX110))))


Many Thanks
Allan

Bob,
Thanks, it's on the right track but not quite there. Placing your 1st
formula =INDEX($1:$1,MIN(IF($E2:$AX2<"",COLUMN($E2:$AX2)) )) in A2 returns
the value 4 (E1). Then I place your new formula in B2 which returns the value
8 (I1). This is fine, what I now require is a formula for C2 which will
return the value 9 (J1) (this ignores what was accounted for by the first two
formulas A2 B2) and then another max formula in D2 which will then return the
value of 11 (L1). I then repeat these formulas in the other rows. I hope
that this makes sence.
Thanks
Allan
A B C D E F G H I J K L M N O P
1 4 5 6 7 8 9 10 11 12 13 14 15
2 x x x x x x x x
3 x x x x x
4 x x x x x


"Bob Phillips" wrote:

Allan,

Is this what you mean?

=INDEX($1:$1,MAX(IF($A2:INDEX(2:2,MIN(IF($A2:$AX2< "",COLUMN($A2:$AX2)))+4)<
"",COLUMN($A2:INDEX(2:2,MIN(IF($A2:$AX2<"",COLUM N($A2:$AX2)))+4)))))

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Allan from Melbourne" wrote
in message ...
Hello,
I am hoping that some kind person may be able to help with this problem.
I have a spreadsheet that can contain a value in column A to column AA or
even greater.
A simple example is this
A B C D E F G H I J K L
1 4 5 6 7 8 9 10 11 12 13 14 15
2 x x x x x x x x x x x x
3 x x x x x
4 x x x x x
I am using the below formulas (thanks to bob) to return the corresponding
value in row 1 for the first and last ocurrence of x in rows 2,3 and so
on.
These formulas work fine however I would like to restrict the indexing to
a
maximum of 4 columns. For example,in the above I would like row 2 to
return
the values 4 and 7 for the first block of 4 x's, then 8 and 11 for the
second
block,12 and 15 for the third block.
Row 2 would return 6 and 9 for the first and 10 and 10 for the second
block.
I hope that this explains what I am after.
The current formulas return 1 and 15 for row 1, 6 and 10 fro row 2 and 10
and 14 for row 3.


=INDEX($104:$104,MIN(IF($E110:$AX110<"",COLUMN($E 110:$AX110))))
=INDEX($104:$104,MAX(IF($E110:$AX110<"",COLUMN($E 110:$AX110))))


Many Thanks
Allan