How do I return this value

[Carla carla]

Hello how should I put this formula.
I want to compare two test.
So the conditions are:

test 1 <=5& test2 <=5 ====Expected
test1<test 2 ===below
test 1>=6&test 2>=6=== above

Thank you

TEST 1	TEST 2	RETURN VALUE
5	4	Below
5	5	Expected
6	6	Above
7	7	Above
8	8	Above
9	9	Above
4	4	Expected
3	4	Above
3	3	Expected
4	3	Below
2	4	Above

 

[Carla carla]

Give this formula a try...

=LET(a,A2:A12,b,B2:B12,IF(a=b,IF(a>5,"Above","Expected"),IF(b>a,"Above","Below")))

This is super! Thanks

 

[Carla carla]

@awoohaw - I think the requirements will make sense if you follow the logic in the formula I posted.

@Carla carla - Did you see the formula I posted yet or did you accidentally skip over i

@awoohaw - I think the requirements will make sense if you follow the logic in the formula I posted.

@Carla carla - Did you see the formula I posted yet or did you accidentally skip over it?

@Rick Rothstein , I just saw now, came back from teaching and it was super. Can I ask for one more solution?

 

[Carla carla]

Yes, you did an extra step of analysis! I read the requirement verbatim and saw that they didn't fit and asked. You figured something out. And although I would not say the requirement is ambiguous it is confusing.

Sorry to confuse you, but big thank you for rescuing me

 

[Carla carla]

Give this formula a try...

=LET(a,A2:A12,b,B2:B12,IF(a=b,IF(a>5,"Above","Expected"),IF(b>a,"Above","Below")))

Hello Rick,

Thank you so much for your help, but could you also coach me on this calculation.
It's pretty much the same idea with the previous one but with the fine grade.
Thank you.
Carla

Fine Grade 21-22	Fine Grade 22-23	RETURN VALUE	Condition for return value
6+	7-	Above	if D is one step higher than C - Expected
6-	7-	Above	if D is one step lower than C- Below
7	6	Below	if D is two steps higher than C -Above
7	7	Above	if D is = 7+ and C=7+ - Above
7-	6-	Below
7-	7	Below
6-	7-	Above
6+	6-	Below
7	7-	Below
7	7	Above
7-	7	Expected
6	7-	Above
6+	7-	Above
6-	7-	Above
7	7-	Above
7-	6	Below

Grading Structure/STEPS
8+
8
8-
7+
7
7-
6+
6
6-
5+
5
5-
4+
4
4-
3+
3
3-
2+
2
2-
1+
1
1-