Results 1 to 3 of 3

Thread: First and Last Sales with Exapmle

  1. #1
    Seeker dta.mo's Avatar
    Join Date
    Aug 2014
    Location
    Ballwin MO
    Posts
    5
    Articles
    0

    First and Last Sales with Exapmle



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Looking for a way to identify first and last non zero cells within a cell range. Attachment for demo purposes. Thanks!!
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    You already had an answer to the first non-zero cell from xld in your previos thread:http://www.excelguru.ca/forums/showt...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.

  3. #3
    Seeker dta.mo's Avatar
    Join Date
    Aug 2014
    Location
    Ballwin MO
    Posts
    5
    Articles
    0
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •