Pass/Fail question

Csinaco

New Member
Joined
Jan 9, 2011
Messages
9
"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. :confused:

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"
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Welcome to the MrExcel board!

You could use a formula like I have in M2 (copied down) but probably better to have a lookup table like I have in columns P:Q and use a VLOOKUP formula like in N2.

Excel Workbook
HIJKLMNOPQ
1GrammarEssaySpeechCompTotalResult 1Result 20Fail
2100505050250Pass-AwesomePass-Awesome150Pass
320404540145FailFail175Pass-Good
470404540195Pass-Very GoodPass-Very Good185Pass-Very Good
5200Pass-Excellent
6230Pass-Awesome
7
Grades
 
Last edited:
Upvote 0
One more thing... in column "M" (where the pass/fail results are posted), if there is no data in column "L", i want it not to report anything....

Thanks!
 
Upvote 0
=IF(L2="","",IF(OR(H2<60,J2<30),"Fail",VLOOKUP(L2,$P$2:$Q$6,2,1)))
 
Upvote 0
WOW! Thanks Peter!
I have one more question; in column "L", where the =SUM(H12:K12) totals reside, is there a way to make the cell not report a number if it is ZERO? For example, not all students were present for the test, or for this particular class, there are less students than there are rows and excel populates these cells as ZERO, hence M picks it up and reports as FAIL... How can I avoid this?

Thanks in advance, and I realy appreciate your previous post! With my basic knowledge of excel, I would never figured this one out!

Marco
 
Upvote 0
WOW again as I was writing, you were posting! You must be clairvoyant! Now I'm looking for winning lotto numbers :) LOL!

Thanks again Peter!
 
Upvote 0
I have one more question; in column "L", where the =SUM(H12:K12) totals reside, is there a way to make the cell not report a number if it is ZERO? For example, not all students were present for the test, or for this particular class, there are less students than there are rows and excel populates these cells as ZERO, hence M picks it up and reports as FAIL... How can I avoid this?
Marco
Are you right with this now? Your subsequent post sounded like you may now be satisfied.
 
Upvote 0
No, I am still having the aforementioned problem. This is the last problem I need solved, promise!

Thanks in advance!
 
Last edited:
Upvote 0
Try this in L2:

=IF(SUM(H2:K2),SUM(H2:K2),"")
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top