IF functions with Two conditions

[Jkeatsooo]

Good morning,

I have a request regarding an IF or IFS function. I want to compare columns of Grades and get a result. It goes like this:

IF a student add a D in 2018 and a C,B,A in 2019, I want the cell to show Above expected.
If a student add a D in 2018 and a D in 2019; I want the cell to show Expected.
If a student add a C in 2018 and B,A, I want the cell to show Above expected.
If a student add a C in 2018 and a C in 2019 I want the cell to show expected.
If a student add a C in 2018 and a D in 2019, I want the cell to show False or anything.

I tried a IFS with AND but it doesn't work all the time... I don't know if I'm very clear!

I attached a file with my tests!

Thank you!

[Pepe Le Mokko]

Please post a sample sheet '(see yellow banner). Thanks

[bebo021999]

In short,
with grade A>B>C>D (excel translate it to be A<B<C<D)
from 2018 to 2019:
If same grade: "Expected"
If promote: (D to A,B,C or C to A,B) "Above expected"
If going down: False

Is it true?

If yes,
With grade of 2018 and 2019 is in B2 and C2, try in D2:

=IF(B2<C2,B2>C2,IF(B2=C2,"Expected","Above expected"))

[Jkeatsooo]

Good morning,

Thank you for your prompt reply. Yes it's almost it except that if B2 and C2=A, I want Above expected to appear. How can I can modify your formula to achieve that?

[Jkeatsooo]

And to add to the complexity(at least for me!), sometimes I don't have anything in the 2018(B) column. I don't want the D cell to show "False" in that case or have to manually remove anything.

[bebo021999]

OK, try again:

Please Login or Register  to view this content.

replace "missing" with anything you want to display.

[Jkeatsooo]

Thank you again. But it gives me "Above Expected" for B in B2 and C2 when I want Expected in that case.

[bebo021999]

It is giving me "Expected"
from this...IF(AND(B2<>"A",B2=C2),"Expected"...
If it still give you "Above", try upload image/ or file again

[Jkeatsooo]

Thank you!

[AliGW]

The syntax is wrong:

=IF(OR(B2="",C2=""),"missing",IF(B2<C2,B2>C2,IF(AND(B2<>"A",B2=C2),"Expected","Above expected")))

[bebo021999]

D2 should be:

=IF(C2="","Nouveau",IF(C2="new","True",IF(C2>=0.75,"A",IF(C2>=0.5,"B",IF(C2>=0.25,"C","D")))))

And E2, you'd replaced "<>A" by "=A", and still using C instead of D.
Use this in E2:

Please Login or Register  to view this content.

If it does not work, try to type in your expected outcome in those cells.

[bebo021999]

The syntax is wrong:
=IF(OR(B2="",C2=""),"missing",IF(B2<C2,B2>C2,IF(AND(B2<>"A",B2=C2),"Expected","Above expected")))

Hi, instead of "FALSE", I am using logical statement to write "FALSE".