First and Last Sales with Exapmle

dta.mo

New member
Joined
Aug 1, 2014
Messages
5
Reaction score
0
Points
0
Location
Ballwin MO
Looking for a way to identify first and last non zero cells within a cell range. Attachment for demo purposes. Thanks!!
 

Attachments

  • dta.mo.excel1.xlsx
    9.1 KB · Views: 19

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
You already had an answer to the first non-zero cell from xld in your previos thread:http://www.excelguru.ca/forums/showthread.php?3326-Identify-Start-and-Stop-Positions
Specifically:
=MATCH(1,IF(C5:N5<>0,1),0)

For the last non-zero cell:
=MAX((C5:N5<>0)*(COLUMN(C5:N5)-COLUMN(C5)+1))

or if you're not going to move the range:
=MAX((C5:N5<>0)*(COLUMN(C5:N5)-2))

all formule to be array-entered with Ctrl+Shift+Enter.
 

dta.mo

New member
Joined
Aug 1, 2014
Messages
5
Reaction score
0
Points
0
Location
Ballwin MO
Thanks! I didn't know the Ctrl+Shift+Enter need for an array formula, thus the repost. I've communicated the same to Bob who answered initially - he followed up also with the array part.

So then, thanks for part 2 answer!

Cheers!
 
Top