+ Reply to Thread
Results 1 to 5 of 5

Array formula failing to return a blank when it should

  1. #1
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Array formula failing to return a blank when it should

    This array formula is failing to return a blank cell when the IF / OR is true. Any help to correct it would be appreciated.

    =IF(OR(K89="",K89+'GPlan'!$H$6>MAX('GPlan'!$N$18:'GPlan'!$N$301)),"",MIN(IF('GPlan'!$N$18:'GPlan'!$N$301>=(K89+'GPlan'!$H$6),'GPlan'!$N$18:'GPlan'!$N$301)))

    Column K contains dates interspersed with some blank rows, Column 'GPlan' N contains dates interspersed with some blank rows and 'GPlan'!$H$6 contains a number.
    So the formula looks in K89 and should return a blank when the if /or statement is true, it doesn't it returns a #Value!

    I should say that if K89 contains a date , the formula works perfectly, returning the correct date or a blank , but if K89 is blank it fails and returns #Value!

    Thanks for your help.
    Last edited by sipa; 01-20-2015 at 08:04 AM.

  2. #2
    Forum Contributor
    Join Date
    10-07-2011
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    212

    Re: Array formula failing to return a blank when it should

    Attache excel sheet please.

    Regards,
    Suhas

  3. #3
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Re: Array formula failing to return a blank when it should

    Ok here is a cut down workbook showing the problem and desired result.

    Hope that will help

    thanks
    Attached Files Attached Files

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Array formula failing to return a blank when it should

    I haven't looked at the attachment but I can see a problem with your formula. When K89 contains a "formula blank" like "" then this part

    K89+'GPlan'!$H$6

    is still evaluated and because it's adding a text value to a number, excel returns # VALUE! error. Try using two IFs rather than OR so that if K89 = "" the K89+'GPlan'!$H$6 part is not executed, i.e.

    =IF(K89="","",IF(K89+'GPlan'!$H$6>MAX('GPlan'!$N$18:$N$301),"",MIN(IF('GPlan'!$N$18:$N$301>=K89+'GPlan'!$H$6,'GPlan'!$N$18:$N$301))))
    Audere est facere

  5. #5
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Re: Array formula failing to return a blank when it should

    Hi Daddylonglegs

    Thanks for helping out, your solution seems to be working perfectly now that I've tried it in the real workbook, Great !!

    Many thanks and regards.
    Last edited by sipa; 01-20-2015 at 11:15 AM.

+ 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. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  2. Array formula to return nothing if cell is blank
    By ExcelRoman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2014, 06:56 PM
  3. Replies: 3
    Last Post: 07-01-2014, 03:11 PM
  4. [SOLVED] VLOOKUP - If table array is blank, need it to return blank
    By bxk006 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2014, 11:36 AM
  5. VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY
    By Scott Lolmaugh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2006, 07:10 PM

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