# Find the position with specific condition in excel 2007

I have a list of student who need to score minimum 40 marks for each subject(five subjects) to get position. For example if a student does not get more than or equal to 40 marks in each subject, he/she will not be ranked and should be skipped from giving position. The following is my current not working solution.

``````=IF(IF(COUNTIF(C2:G2,">=40")=5,TRUE,FALSE), RANK(H2,H\$2:H\$6),"NA")
``````

Instead of `COUNTIF(C2:G2,">=40")=5` I would use the following, for simplicity:

``````IF(min(C2:G2)<40, "FAIL", "PASS")
``````

i.e. if the smallest number is less than 40, FAIL, but if they're all above 40 so the smallest of all the numbers is more than/equal to 40, PASS. In your example you'll replace "PASS" with the rank formula

You don't need two IF() statements if there are only two possible choices. A good explanation of this was given recently.

Quoting that answer, the IF() statement is simply:

``````=IF(Condition, What to do if true, What to do if false).
``````

You have the condition correct: `COUNTIF(C2:G2,">=40")=5`
... and the what to do if true: `RANK(H2,H\$2:H\$6)`
... and the what to do if false: `"NA"`

So all you need to do is combine them in one IF() statement.