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(AVE RAGE(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]


mitchbragg wrote:

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