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

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

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.

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

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" wrote:

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.

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

Glad you got it to work... Lori

mitchbragg wrote:

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" wrote:

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.

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