This has me stumped
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 |
This has me stumped
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. -- Biff Microsoft Excel MVP "wild turkey no9" wrote in message ... 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 |
This has me stumped
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. "wild turkey no9" wrote: 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 |
This has me stumped
Great stuff Mike. Much appreciated.
Kevin "Mike H" wrote: 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. "wild turkey no9" wrote: 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 |
This has me stumped
Thanks, Biff
Elegant solution and works like a charm. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "wild turkey no9" wrote in message ... 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 . |
This has me stumped
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "wild turkey no9" wrote in message ... Thanks, Biff Elegant solution and works like a charm. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "wild turkey no9" wrote in message ... 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 . |
All times are GMT +1. The time now is 10:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com