+ Reply to Thread
Results 1 to 5 of 5

Find the closest zero to the left and return its address

  1. #1
    Registered User
    Join Date
    07-01-2013
    Location
    Davis, Ca
    MS-Off Ver
    Excel 2010
    Posts
    5

    Find the closest zero to the left and return its address

    Lets say I have a row with the following numbers. 00110100011111100011

    Each number is in its own cell. I will wish to do some computations on only the zeros. What I need to do is to be able to find the address of the zero to the left that is the closest in this series for each and ever cell.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Find the closest zero to the left and return its address

    Like this..?
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Find the closest zero to the left and return its address

    If you want something like this:
    0 1 1 0 1 1 1 0 0
    $A$2 $D$2 $G$2 $I$2 $J$2

    you can use this formula:
    =IF(A2=0,ADDRESS(ROW(A2),COLUMN(A1)),"") assuming your data is starts in row 2 cell A2

  4. #4
    Registered User
    Join Date
    07-01-2013
    Location
    Davis, Ca
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Find the closest zero to the left and return its address

    Everyone thank you for all the feedback. Both Alkey and ACE_XL are very close. I have posted example of what I am looking for.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-01-2013
    Location
    Davis, Ca
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Find the closest zero to the left and return its address

    The above attached file includes ALkey solution in the third row the second row is what I am going for. Thanks everyone!

+ 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