Results 1 to 5 of 5

Thread: Identify Start and Stop Positions

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

    Identify Start and Stop Positions



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

    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!

  2. #2
    This array formula will do it

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

  3. #3
    Seeker dta.mo's Avatar
    Join Date
    Aug 2014
    Location
    Ballwin MO
    Posts
    5
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    This array formula will do it

    =MATCH(1,IF(A1:AB1>0,1),0)
    Thanks, Bob, but I am getting an error #value - can you recheck? I get the feeling I am overlooking something simple. Thanks for the response.

  4. #4
    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)}

  5. #5
    Seeker dta.mo's Avatar
    Join Date
    Aug 2014
    Location
    Ballwin MO
    Posts
    5
    Articles
    0
    OK thanks. I didn't know the Ctrl+Shift+Enter part of the array formula entry. Works perfectly now.

Posting Permissions

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