Hi, and thanks in advance for any help!

I am trying to automate some the functions of my student's English progress, based on individual test scores.

There are 5 data and 1 results columns. 2 of the tests have a fail/pass component that even if the cumulative score would be enough to pass, failure on these 2 tests would result in a failing grade.

Pass/Fail conditions - A perfect score of 250 pts (Col-H, 100 grammar pts; Col-I, 50 essay pts; Col-J, 50 speech pts; Col-K, 50 comprehension pts). The summation of student scores are found in column L, and minimum of 150 pts is required to pass the test, provided these 2 “special” conditions are met;

1. The GRAMMAR part of the test has a maximum score of 100 points, but less than 60 would result in test failure.
and
2. The SPEECH part of test has a maximum score of 50 points, but less than 30 would result in test failure.

Failure in either test would negate any passing grade.

I have concocted this IF formula, but I am having trouble incorporating the 2 above conditions into this formula;

=IF(L12>=230,"PASS - AWESOME!!!",IF(L12>=200,"PASS - EXCELLENT!!",IF(L12>=185,"PASS - VERY GOOD!",IF(L12>=175,"PASS - GOOD",IF(L12>=150,"PASS",IF(L12<=149,"FAIL"))))))

Also, in column L, where the individual scores are compiled, =SUM(H12:K12), when there are no scores, it records a 0, I would like it to be blank if columns H thru K are not populated.

Thanks again!
Marco

---

Richard Replies:

It's kind of hard to see what's going on without reconstructing your sheet, but if you have two separate conditions and you need to check if BOTH of them are PASS in order to give a passing grade, you could use the AND function:

=IF(AND(A1="PASS",B1="PASS"),"PASS","FAIL")

Something like that? Just put together your PASS/FAIL grades for each section (grammar, speech) into two cells, and then you can check both of those.

Does this help?