+ Reply to Thread
Results 1 to 11 of 11

Determining if a certain number of blanks occur in succession at any point in a row

  1. #1
    Registered User
    Join Date
    03-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Determining if a certain number of blanks occur in succession at any point in a row

    I am trying to determine whether or not 26 blanks occur in succession in a row. Two example rows of data are listed below, where "_" refers to a blank cell:

    2 _ _ 3 _ _ _ _ _ _ _ 4 5 6 3 4 _ _ _ _ _ 4
    _ _ _ _ _ _ 7 8 13 2 3 _ _ _ _4 5 6 11 10 2

    Hopefully you can picture the data from above. Anyway, there are multiple locations where a string of blanks occur. I'm looking for a formula that can test as to whether or not a certain number of blanks occur in succession during any part of the row.

    Any help would be much appreciated! Thanks!

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    This will gives number of blank cells in range, for instance, A1:Z1:
    =COLUMNS(A1:Z1)-COUNTA(A1:Z1)
    Quang PT

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    hi ohreals, welcome to the forum. i'm assuming your data is from A:AC. so change the range in red to whatever applicable to you:
    =OR(SUBTOTAL(2,OFFSET($A1,,ROW(INDIRECT(COLUMNS(A1:A1)&":"&COLUMNS(A1:AC1)-26+1))-1,,26))=0)

    press CTRL + SHIFT + ENTER to confirm. 26 consecutive blanks will indicate a TRUE while the opposite will be a FALSE
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Forum Contributor
    Join Date
    12-23-2003
    Posts
    179

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    Control+shift+enter, not just enter, and copy down for each row:

    =MAX(FREQUENCY(IF(A1:Z1="",COLUMN(A1:Z1)),IF(A1:Z1<>"",COLUMN(A1:Z1))))=26

  5. #5
    Registered User
    Join Date
    03-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    Thanks benishiryo! This worked perfectly, it was really bugging me that I couldn't make it work so thanks a lot for your help.

    One more question to complicate it a little further. Do you know an addition to the equation that I could use to return the address of the cell at the end of the string of blanks, if it is true that a string of 26 blanks exists?

    Thanks again for your assistance!

  6. #6
    Forum Contributor
    Join Date
    12-23-2003
    Posts
    179

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    Quote Originally Posted by ohreals View Post
    ...the address of the cell at the end of the string of blanks...
    Control+shift+enter:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    Sorry I should have specified, that equation would work when looking for exactly strings of 26 blanks. But really I'm looking for any string of 26 blanks in a row or greater, which the equation "=OR(SUBTOTAL(2,OFFSET($A1,,ROW(INDIRECT(COLUMNS(A1:A1)&":"&COLUMNS(A1:AC1)-26+1))-1,,26))=0)" provides nicely.

    But when trying to return the address, I'm trying to return the address of the last blank cell (so the end of the string of blanks). Trying to alter the equation Aladin posted above to try and come up with something. Will post if I have success.

    Thanks!

  8. #8
    Registered User
    Join Date
    03-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    CELL("address",INDEX(A1:AC1,MATCH(EX25,FREQUENCY(IF(A1:AC1="",(COLUMN(A1:AC1))),IF(A1:AC1<>"",(COLUMN(A1:AC1)))),0)+EX25-1))

    I altered Aladin's equation slightly, replacing 26 with numbers from another column that would display the max number of blanks in a row (Cell EX25 in the equation above). But my problem with the equation is that it doesn't give the exact address all the time. An example of the final array from this "FREQUENCY(IF(A1:AC1="",(COLUMN(A1:AC1))),IF(A1:AC1<>"",(COLUMN(A1:AC1))))" portion of the equation looks like this {0,0,0,0,1,0,0,2,0,0,31,0,0,0,0,1,0}, the problem in determining the location by just adding back in EX25-1 at the end is that since there is a 2 in the array before the 31, we are miscounting by 1 blank space. So if there was a string of lets say 7 blank spaces before a string of over 26, the final cell address would be off by 6.

    Anyone have a solution for this issue?

  9. #9
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    an array again:
    Please Login or Register  to view this content.
    for those which does not have 26 blanks, you could do:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    Thanks again benishiryo, that got rid of the multiple zero problem. So at this point I am able to determine the location of the largest string of zeros that occur. But what about if there are multiple strings of 26+ blanks in the data, and I want to determine the location of the last string of 26+ blanks?

    Right now I am finding the max string of blanks by using this formula:
    1.)
    MAX(FREQUENCY(IF(A1:AC1="",COLUMN(A1:AC1)),IF(A1:AC1<>"",COLUMN(A1:AC1))))

    And then finding the location using this formula (referring to the result of the formula above in place of the "26" in the equation below):
    2.) IF(OR(SUBTOTAL(2,OFFSET($A1,,ROW(INDIRECT(COLUMNS(A1:A1)&":"&COLUMNS(A1:AC1)-26+1))-1,,26))=0),MATCH(TRUE,SUBTOTAL(2,OFFSET($A1,,ROW(INDIRECT(COLUMNS(A1:A1)&":"&COLUMNS(A1:AC1)-26+1))-1,,26))=0,0)+25,"")

    The problem is the maximum string of blanks may not be the last string of 26+ blanks (see the example I attached).

    Can anyone think of an approach to solve this issue and find the location of last string of blanks of 26 or more? The example I attached shows why the method I'm using does not work.

    Thanks!
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Determining if a certain number of blanks occur in succession at any point in a row

    MAX(COLUMN(A1:AC1)*(A1:AC1="")*(SUBTOTAL(2,OFFSET(A1:AC1,,COLUMN(A1:AC1),,26))=0))+26-1

    Solved. Thanks everyone.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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