+ Reply to Thread
Results 1 to 8 of 8

Finding blank cells in an array formula not working. Why?

  1. #1
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE PRO 2021
    Posts
    56

    Finding blank cells in an array formula not working. Why?

    On multiple sheets I have Column A (Event date), Column B (Event Name), Column C (Participation). Every sheet is populated with event dates in Column A and the Name in Column B. Column C is blank until the event takes place. If there is participation, a brief description is made and is not a limited answer and is a mix of alphanumeric (never starts with a number).

    On the 1st sheet, my Summary sheet, I have a cell for each sheet to reflect when the last date was that participation took place at an event and another cell for when the next possible event date will be. The formula for finding the last date participation took place works. The formula for finding the next possible day is not working.

    {=MAX(IF(TLSA_S!C$11:C$45<>"",TLSA_S!A$11:A$45))} <------This works
    {=MIN(IF(TLSA_S!C$11:C$45="",TLSA_S!A$11:A$45))} <------Doesn't work

    It appears to be because it cant find the blank cells because if i change it to
    {=MIN(IF(TLSA_S!C$11:C$45="X",TLSA_S!A$11:A$45))}
    and put an X in every cell in TLSA_S!C$11:C$45 then it works. I have tried incorporating ISBLANK and using 0 and nothing works. I wouldn't really care to put an X or . in each of those cells to make it work but I am just concerned about a potential error with the other formula and its use of <>"" for the same range (not sure why that didn't happen when I tested it and I assume the solution to that would be <>"X" etc.).

    What am I doing wrong? Do I need to use different formulas all together?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,406

    Re: Finding blank cells in an array formula not working. Why?

    Maybe the cells are not really blank. What do they contain? A formula?

    This is why attaching a workbook is more helpful, as we can see the problem in context.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE PRO 2021
    Posts
    56

    Re: Finding blank cells in an array formula not working. Why?

    The cells are blank and formatted as general. I didnt attach a workbook because of time it takes to prep it. Thought someone might be able to look at it and see something right away. Here is a sample
    Attached Files Attached Files

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Finding blank cells in an array formula not working. Why?

    Given your version:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    your current calc is returning 0 as your blanks in A (where C is blank) will equate to 0; in the MAX test they're irrelevant as the date values / serials will exceed 0.

    edit: or, perhaps better:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,406

    Re: Finding blank cells in an array formula not working. Why?

    OK - that's clear now.

    It's returning what it finds in A11, as that's the first match (and it sees the blank as a 0).

    Why should it be 18 Feb and not 15 Feb?

  6. #6
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE PRO 2021
    Posts
    56

    Re: Finding blank cells in an array formula not working. Why?

    Quote Originally Posted by XLent View Post
    Given your version:

    your current calc is returning 0 as your blanks in A (where C is blank) will equate to 0; in the MAX test they're irrelevant as the date values / serials will exceed 0.

    confirmed with Enter[/FORMULA]
    A working solution and a clear concise explanation for the failure. Makes sense. Thank you very much!

  7. #7
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE PRO 2021
    Posts
    56

    Re: Finding blank cells in an array formula not working. Why?

    Quote Originally Posted by AliGW View Post

    Why should it be 18 Feb and not 15 Feb?
    That was my mistake while preparing the smaller sample. The initial explanation was correct. Thanks for looking at it though.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,406

    Re: Finding blank cells in an array formula not working. Why?

    No problem - I had a solution, but was beaten to it. Should have gone with my gut instinct!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Ignore Blank Cells in an array formula
    By Brycicle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-26-2018, 02:28 AM
  2. [SOLVED] Formula for finding an average number, excluding blank cells
    By RachA12 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 03-28-2018, 10:39 AM
  3. Finding the smallest difference between a range of cells? Array formula?
    By dnlwstr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-19-2014, 11:10 AM
  4. A Formula to skip blank cells (array?)
    By nemo74 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 12:44 PM
  5. Formula for finding number of consecutive blank cells over a year - See info!
    By oldtauntonian in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-16-2013, 08:31 AM
  6. [SOLVED] Array formula to ignore blank cells
    By trickeyja in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-22-2012, 07:35 PM
  7. Array formula to ignore blank cells
    By bronsonb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-13-2011, 09:14 AM

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