+ Reply to Thread
Results 1 to 10 of 10

Nested If statement, checking multiple cells for Blank cells

  1. #1
    Registered User
    Join Date
    04-29-2015
    Location
    Tempe, Arizona
    MS-Off Ver
    2013
    Posts
    10

    Nested If statement, checking multiple cells for Blank cells

    TooManyArguments.JPG


    I have tried creating a formula testing to see if three cells are not empty. If they are not empty I want a value of "1" to be returned, if not then I want to test to see if the three cells are all blank. If all three cells are blank then I want a value of "*" returned. And all else should return "0".

    Excel refused my formula saying there were too many arguments. Is there a better way to write the formula or a better set of functions?

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Nested If statement, checking multiple cells for Blank cells

    How about:

    Please Login or Register  to view this content.
    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Nested If statement, checking multiple cells for Blank cells

    Post deleted
    Last edited by protonLeah; 04-29-2015 at 07:47 PM.
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    04-29-2015
    Location
    Tempe, Arizona
    MS-Off Ver
    2013
    Posts
    10

    Re: Nested If statement, checking multiple cells for Blank cells

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-29-2015
    Location
    Tempe, Arizona
    MS-Off Ver
    2013
    Posts
    10

    Re: Nested If statement, checking multiple cells for Blank cells

    The Choose formula above isn't computing what I want when I plug the formula into excel. The formula returns * for all cases.
    If all the cells are not empty I want a 1
    If all the cells are empty I want *
    Else 0

    Interesting idea though, not very familiar with the choose function. Will try to mess with it some more.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Nested If statement, checking multiple cells for Blank cells

    You want the following...

    1. If cells are not empty, enter 1
    2. if cells are blank, enter 8
    3. otherwise enter 1

    How do you define the difference between empty and blank?

    This will shorten your formula...
    =IF(COUNTA(AF4:AH4)=0,"*",1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    04-29-2015
    Location
    Tempe, Arizona
    MS-Off Ver
    2013
    Posts
    10

    Re: Nested If statement, checking multiple cells for Blank cells

    I was using the ISBLANK function to determine if the cell was blank or not, the reason why the formula was long was because I am testing three cells and am using the NOT function, ISBLANK function, and IF
    Last edited by JLeague; 04-30-2015 at 12:31 AM.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Nested If statement, checking multiple cells for Blank cells

    Quote Originally Posted by JLeague View Post
    I was using the ISBLANK function to determine if the cell was blank or not, the reason why the formula was long was because I am testing three cells and am using the NOT function, ISBLANK function, and IF
    That doesnt really answer my question. You are you want a certain answer if the cell is blank, and you are a different answer if the cell is empty. how do YOU define those 2 - what is the difference?

  9. #9
    Registered User
    Join Date
    04-29-2015
    Location
    Tempe, Arizona
    MS-Off Ver
    2013
    Posts
    10

    Re: Nested If statement, checking multiple cells for Blank cells

    IF(AND(NOT(ISBLANK(Raw!$AF4)),NOT(ISBLANK(Raw!$AG4)),NOT(ISBLANK(Raw!$AH4))),"1",IF(AND(ISBLANK(Raw!$AF4),ISBLANK(Raw!AG4),ISBLANK(Raw!$AH4)),"*"),"0")

    Overall 2 tests then a catch all
    I have three cells (AF, AG, and AH)
    I tried Defining the If as if all three are not blank then assign the value of 1
    Then testing If all three are empty assign the value of *
    and then catch all value of 0

    so overall if all three are not empty then give it a 1
    if all three are empty give it a *
    Else give it a 0

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Nested If statement, checking multiple cells for Blank cells

    ok lets try again. Forget about the formula for a minute, you say the cell may be blank, or it may be empty. What is the difference between blank and empty?

    Normally an empty cell is a cell that has absolutely nothing in it, while a blank cell, could contain a formula that returns "nothing" ie ""

    Did you try my initial suggested formula?
    =IF(COUNTA(AF4:AH4)=0,"*",1)

    If those 3 cells contain absolutely nothing, that formula will return *
    If any of those cells contain anything at all (even a formula that returns "") it will return 1

+ 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. IF Statement to leave cell blank if multiple cells are all blank
    By sweeteri in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-14-2014, 12:02 PM
  2. Nested IF statement comparing value1 against multiple cells?
    By MJSlattery in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2012, 06:12 PM
  3. Nested IF checking non blank cells
    By brentandrews in forum Excel General
    Replies: 2
    Last Post: 12-21-2009, 10:18 AM
  4. Checking if multiple cells are blank
    By MichaelWatson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2008, 07:14 AM
  5. Replies: 0
    Last Post: 08-23-2005, 03:43 AM

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