+ Reply to Thread
Results 1 to 6 of 6

Check if any cell is blank.

  1. #1
    Registered User
    Join Date
    06-18-2009
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    21

    Check if any cell is blank.

    I have a spreadsheet that records student grades. This is done by putting a tick in a cell in whether the student has received a pass/merit/distinction in four different areas.

    This is then checked with an if statement to see if the student has a Pass, Merit or Distinction and the next cell is completed. However, there is a final column which counts the number of points based on how many passes, merits or distinctions the student has received. At the moment the student can still achieve a pass by acruing enough points in other areas because of the final if statement.

    I want to ensure that all sections A1, A2, A3, A4 have either P, M or D in them and if any of them is blank then to return INCOMPLETE. I have tried using ISBLANK but this does not work as there is a function:
    =IF(R75=$Q$2,"D",IF(Q75=$Q$2,"M",IF(P75=$Q$2,"P"," "))) in the cells to check whether it is a P, M or D.

    I have attached an excel file to make this clear.

    Thanks,

    spqr
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Check if any cell is blank.

    ISBLANK does not see a Null as Blank, COUNTBLANK on the other hand does...

    AH9: =IF(COUNTBLANK($Y9:$AB9),"INCOMPLETE",remainder of formulae)

  3. #3
    Registered User
    Join Date
    06-18-2009
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Check if any cell is blank.

    Tried both ways but still get Pass:

    =IF(COUNTBLANK(Y9:AB9),"INCOMPLETE", IF(AG9<4,"Below Pass",IF(AG9>11,"Distinction",IF(AG9>=8,"Merit","Pass"))))

    =IF(COUNTBLANK(Y9:AB9)>1,"INCOMPLETE", IF(AG9<4,"Below Pass",IF(AG9>11,"Distinction",IF(AG9>=8,"Merit","Pass"))))

  4. #4
    Registered User
    Join Date
    06-18-2009
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Check if any cell is blank.

    Countblank returns 0 if the check is done on this cell range: ($Y9:$AB9)

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Check if any cell is blank.

    On further inspection instead of returning a Null "" in the Y:AB formulae you're actually returning a Space " " the two are different things ... you could either then use

    AH9: =IF(COUNTIF($Y9:$AB9," "),"INCOMPLETE",....)

    or

    change the original formula to return a Null (advised) and use the formula you have already in AH re: COUNTBLANK

  6. #6
    Registered User
    Join Date
    06-18-2009
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Check if any cell is blank.

    Ok, brilliant that works when I change it to null.

    Thanks for your speedy help

+ 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