Pass/Fail question

[Csinaco]

"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(<WBR>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"

 

[Csinaco]

Ok...
=IF(SUM(H3:K3),SUM(H3:K3),"") this clared the cell but
=IF(OR(H3<60,J3<30),"Fail",VLOOKUP(L3,$P$3:$Q$8,2,1)) is reporting a "Fail" in M3 as well as other cells that are blank, what am I doing wrong?

Excel Workbook
	H	I	J	K	L	M
2	GRAM. POINTS	ESSAY POINTS	TALKING POINTS	READING	FINAL POINTS	RESULTS
3	0	0	0	0		Fail
4	100	10	30	50	190	PASS - VERY GOOD!
5	40	30	39	40	149	Fail
6	80	30	30	40	180	PASS - GOOD!
7	90	40	35	30	195	PASS - VERY GOOD!
8	59	50	29	12	150	Fail
9	60	50	30	50	190	PASS - VERY GOOD!
10	50	50	50	50	200	Fail
11	5	5	5	5	20	Fail
12	100	50	50	40	240	PASS - AWESOME!!!
13	60	40	29	50	179	Fail
14	1	2	3	5	11	Fail
15						Fail
16						Fail
17						Fail
18						Fail
Лист1

Excel 2010

Cell Formulas
Range	Formula
M18	=IF(OR(H18<60,J18<30),"Fail",VLOOKUP(L18,$P$3:$Q$8,2,1))

[/B]

 

[jasonb75]

The formula Peter gave you earlier only blanks L3, the formula in M3 is still evaluating the figures in H3 & J3, 0 is less than the pass amount so will result in a fail.

Empty cells with no formula to show a blank are also evaluated as 0, this is why you see Fail from row 15 onwards.

Try =IF(L3="","",IF(OR(H3<60,J3<30),"Fail",VLOOKUP(L3,$P$3:$Q$8,2,1)))

This will show M3 as blank when L3 is blank and show the result when it's not.

 

[Csinaco]

Thanks for helping!

So that I may understand a little more, what is the function of L3,$P$3:$Q$8,2,1? What does $Q$8,2,1 do?

 

[Csinaco]

Works perfectly now, thanks Peter! Thanks Jason!

 

[Peter_SSs]

Ok...
=IF(SUM(H3:K3),SUM(H3:K3),"") this clared the cell but
=IF(OR(H3<60,J3<30),"Fail",VLOOKUP(L3,$P$3:$Q$8,2,1)) is reporting a "Fail" in M3 as well as other cells that are blank, what am I doing wrong?

I see that Jason has got you sorted but we did already address that issue in posts #4 & #5 earlier in the thread.