This has me stumped

  • Thread starter Thread starter wild turkey no9
  • Start date Start date
W

wild turkey no9

Excel 2003 user here. How do i return the cell reference of the first and
last occurences of a 1 in row?

Thanks in anticipation

Kevin
 
I assume you want the cell address?

Assuming that 1 does exist...

Range of interest is A2:E2

For the first cell address, array entered** :

=ADDRESS(ROW(A2:E2),MIN(IF(A2:E2=1,COLUMN(A2:E2))),4)

For the last cell address, array entered** :

=ADDRESS(ROW(A2:E2),MAX(IF(A2:E2=1,COLUMN(A2:E2))),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Hi,

Address of first 1 in range a1:A8

=ADDRESS(MATCH(1,A1:A8),1)

Last 1 in a1:a8 is an array formula

=ADDRESS((MAX((A1:A8=1)*ROW(A1:A8)))-ROW(A1:A8)+1,1)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Great stuff Mike. Much appreciated.

Kevin

Mike H said:
Hi,

Address of first 1 in range a1:A8

=ADDRESS(MATCH(1,A1:A8),1)

Last 1 in a1:a8 is an array formula

=ADDRESS((MAX((A1:A8=1)*ROW(A1:A8)))-ROW(A1:A8)+1,1)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
Click to expand...
 
Back
Top