IF function

[Guest]

I have been given a task in Excel and am struggling to come up with a
formula. Can anyone help?

Calculate the average grades for the students.
Using the average grade create an IF statement to generate a target grade
based on the criteria below.

Note: this will require the use of AND and OR

A - Average 60+; English AND Maths 70+.
B - Average 60+; English, Maths OR Science 70+
C - Average 60+
D - Average between 40-60; English, Maths AND Science 50+
E - other

Use conditional formatting to highlight those grades below C

 

[Lori]

Suppose the layout starting with column A is:

Name English Maths Science Grade

In cell E2 enter:

=IF(AVERAGE(B2,C2,D2)>=60,IF(AND(B2>=70,C2>=70),"A",IF(OR(B2>=70,C2>=70,D2>=70),"B","C")),IF(AND(AVERAGE(B2,C2,D2)>=40,B2>=50,C2>=50,D2>=50),"D","E"))

Then highlight the grade column and choose conditional formatting with
condition:

[Cell Value] is [Greater than or Equal] to [C]

 

[Lori]

Correction: Conditional formatting should be "less than or equal to".
Also AVERAGE(B2,C2,D2)>=40 can be removed as all grades have to be
larger than 50.

 

[Guest]

Thank you very much, it worked. Oh and for the conditional formatting, I
used "greater than" as it was any grade below C, not including the C grade
itself. Also the students needed to get a score higher than 60 or 70, so I
just removed the equals signs and left the greater than signs on their own.

 

[Lori]

Glad you got it to work... Lori

Thank you very much, it worked. Oh and for the conditional formatting, I
used "greater than" as it was any grade below C, not including the C grade
itself. Also the students needed to get a score higher than 60 or 70, so I
just removed the equals signs and left the greater than signs on their own.