+ Reply to Thread
Results 1 to 3 of 3

Searching right to left for a differing value

Hybrid View

  1. #1
    Registered User
    Join Date
    10-22-2007
    Posts
    2

    Searching right to left for a differing value

    Hi everyone.
    Given: A row with either positive real numbers or zero in each cell, i.e,
    0 0 459.11 0 0 0 11 0 0 11198.1 0 0 0
    I need to scan the row right to left, and return one of the following:

    The number of zeros before the first non-zero value appears
    The address of the last zero cell encountered or the first non-zero cell

    The values are not arranged in any sort of order, and cannot be reordered, which killed my attempt at using an OFFSET/MATCH combination.

    I think I could do this by simply parsing a text string, but I haven't figured out a way to automatically concatenate the entire row.

    Oh, and if it's any help, the row will *always* be the same length.

    My next step is VBA, but I've never worked with it, and I'd really like to avoid it if I can.

    Thanks for any and all hints you can offer!

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,698
    Assuming your data is in A1:M1

    To give the number of zeroes before the first non-zero value appears (3, I believe in your example above)

    =COLUMNS(A1:M1)-MATCH(2,1/(A1:M1))

    and the address of the first non-zero value (from the right)

    =CELL("address",INDEX(A1:M1,MATCH(2,1/(A1:M1))))

    both formulas need to be "array entered" i.e. confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

  3. #3
    Registered User
    Join Date
    10-22-2007
    Posts
    2
    Thank you very much for your help. The project got back-burnered for a while, so I am only now getting back to it. Sorry for not acknowledging your help earlier!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1