need help with logical formulas

asiam

New member
Joined
Jul 23, 2014
Messages
2
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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

New member
Joined
Jul 23, 2014
Messages
2
Reaction score
0
Points
0
It's worked brillliantly!! Thanks so much NBVC. :)
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
You are welcome.

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