Check if any cell is blank.

[spqr]

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

[DonkeyOte]

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

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

[spqr]

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"))))

[spqr]

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

[DonkeyOte]

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

[spqr]

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

Thanks for your speedy help