returning a specific cell value

G

Guest

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($E110:$AX110))))
=INDEX($104:$104,MAX(IF($E110:$AX110<>"",COLUMN($E110:$AX110))))


Many Thanks
Allan
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top