Compare Students Grade

Copper Contributor

How to compare this

2019-20 (table 1)2020-21 (table 2) 



If 2nd table less than 1st then weak

if 2nd table greater than 1st then very good

if 2nd table equal to 1st table then average



i need formula to calculate this

6 Replies


=IF(B3>A3,"very good",IF(B3<A3,"weak",IF(B3=A3,"average")))

You can use a simple nested IF formula. If you work with Office365 or 2021 you can apply IFS as well.





Presumably, though, there are names associated with the grades too...and you want to make sure you're comparing John Doe's grades from the two years....


Is it possible for you, @shahid20 ,to give us the full structure of the two tables? That formula from @OliverScheurich is great for the data you've shared, but I find it hard to believe it's going to be that simple, unless you've already taken care of making sure each row in each table is for the same student.

A*          A     Below expected

A           A      Expected 



Above Expected

I need help for formula to show the result (Expected, Above Expected and Below Expected) based on two grades compare


@mathetes @OliverScheurich 





Cross-posted at Compare two letter grades 



Hi there


You may try the following steps.


1) Create a Table listing all the possible students' Grades and assign them a value

2) Give the table a Named Range eg. "GradesTable"


Screenshot 2022-10-16 110018.jpg



3) You may use any of the two formulas for the Comparison column


Comparison1 =IF(VLOOKUP(B2,GradesTable,2,0)=VLOOKUP(C2,GradesTable,2,0),"Expected",IF(VLOOKUP(B2,GradesTable,2,0)<VLOOKUP(C2,GradesTable,2,0),"Above Expected","Below Expected"))



=CHOOSE(SIGN(VLOOKUP(C2,GradesTable,2,0)-VLOOKUP(B2,GradesTable,2,0))+2,"Below Expected","Expected","Above Expected")


Screenshot 2022-10-16 110520.jpg



I hope this helps you and gives a solution to your problem


Do let me know if you need more help