PDA

View Full Version : need help with logical formulas



asiam
2014-07-23, 02:42 PM
Hello everyone,
I'm new here and a fairly basic user of Excel so would appreciate patience and 'slow talking' :)
i need to find a formula that calculates a grade based on several variables as follows:

if A1>=80 and A2>50 and A3>50 and A4>50 and A5>50, then the grade is A; if A1>76 but <80 and any of A2 to A5<50, then the grade is B; if A1=<66, then the grade is C.

I hope this make sense. I've been trying to use the IF formulas but but keep getting the message it's incorrect, or too many variables, etc.

is there a way to calculate the outcome that would be an overall grade for a subject?

A1 is average attendance at subjects A2, A3, A4 and A5 combined. So to get an A the student needs to have over 80 in overall attendance and 50 in each element. to get B he needs overall attendance between 67 and 79 and only one of the A2, A3, A4 and A5 below 50. to get C we only look at overall attendance below 66.

I would be grateful for any help you could offer.

many thanks.

NBVC
2014-07-23, 03:02 PM
Try:

=IF(AND(A1>=80,COUNTIF(A2:A5,">50")=4),"A",IF(AND(A1>76,A1<80,COUNTIF(A2:A5,"<50")>0),"B",IF(A1<=66,"C","")))

asiam
2014-07-23, 03:33 PM
It's worked brillliantly!! Thanks so much NBVC. :)

NBVC
2014-07-23, 03:47 PM
You are welcome.

Looking at it again, I guess you wanted the last part, for "C" to be <=76...