Identify Start and Stop Positions

dta.mo

New member
Joined
Aug 1, 2014
Messages
5
Reaction score
0
Points
0
Location
Ballwin MO
Greetings, all, and thanks!

I need to evaluate a range of cells, say A1:AB1, and identify the first cell that is not 0 value. Cells A1:Z1 are all value zero and AA1 is value 10, I need the formula to indicate that the 27th cell is the first non zero. This is to identify the date of first sales for a company.

Likewise, I need to identify the date / cell of last sales for a company in the same cell range.

Thanks!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
This array formula will do it

=MATCH(1,IF(A1:AB1>0,1),0)
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
It's an array formula, you need to enter with Ctrl-Shift-Enter, Excel will then wrap the formula in braces, like so {=MATCH(1,IF(A1:AB1>0,1),0)}
 

dta.mo

New member
Joined
Aug 1, 2014
Messages
5
Reaction score
0
Points
0
Location
Ballwin MO
OK thanks. I didn't know the Ctrl+Shift+Enter part of the array formula entry. Works perfectly now.
 
Top