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

 

[nemmi69]

try

 

[awoohaw]

I am not sure your original expectations are accurate. Why do you have "Below" as the expected value in the first record.
And I'm not sure you're taking into consideration all possible scenarios. Try this:

Mr excel questions 63.xlsm
	A	B	C	D
1
2	TEST 1	TEST 2	RETURN VALUE
3	5	4	Below	Expected
4	5	5	Expected	Expected
5	6	6	Above	Above
6	7	7	Above	Above
7	8	8	Above	Above
8	9	9	Above	Above
9	4	4	Expected	Expected
10	3	4	Above	Expected
11	3	3	Expected	Expected
12	4	3	Below	Expected
13	1	2		Expected
14	2	1		Expected
15	6	6		Above
16	4	4		Expected
17	5	6		Other
18	6	5		Other
Carla carla

Cell Formulas
Range	Formula
D3:D18	D3	=IF(AND(A3<=5,B3<=5),"Expected",IF(A3<A3,"Below",IF(AND(A3>=6,B3>=6),"Above","Other")))

 

[Rick Rothstein]

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")))

 

[Carla carla]

I am not sure your original expectations are accurate. Why do you have "Below" as the expected value in the first record.
And I'm not sure you're taking into consideration all possible scenarios. Try this:

Mr excel questions 63.xlsm
	A	B	C	D
1
2	TEST 1	TEST 2	RETURN VALUE
3	5	4	Below	Expected
4	5	5	Expected	Expected
5	6	6	Above	Above
6	7	7	Above	Above
7	8	8	Above	Above
8	9	9	Above	Above
9	4	4	Expected	Expected
10	3	4	Above	Expected
11	3	3	Expected	Expected
12	4	3	Below	Expected
13	1	2		Expected
14	2	1		Expected
15	6	6		Above
16	4	4		Expected
17	5	6		Other
18	6	5		Other
Carla carla

Cell Formulas
Range	Formula
D3:D18	D3	=IF(AND(A3<=5,B3<=5),"Expected",IF(A3<A3,"Below",IF(AND(A3>=6,B3>=6),"Above","Other")))

TTest B should be higher than TEst 1

 

[awoohaw]

TTest B should be higher than TEst 1

i'm not sure which of my questions/comments this is referring to?
Is your original column C your expectations or is it what your original calculation came up with?
Or are you saying Test2 (i am assuming B and 2 are the same) is always greater than Test 1 because of your environment/data circumstances?
Are the additional scenarios I added possible?

Do you have any priority of the tests other than what you have listed?

 

[Rick Rothstein]

@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?

 

[awoohaw]

@Rick Rothstein I see how your formula works. You somewhat re-wrote the requirement with the a=b condition.
And I had a typo in my first condition, but I still have differences.

But I still question Row 1. Why does Row 1 have values of 5 & 4 and @Carla carla expects that to result in "Below" when the test for Below is test1<test 2 ===below.
(Your formula figures that out, but I don't see how the progression of conditions in the requirements matches your formula).

 

[Rick Rothstein]

The way I deciphered the OP's instructions coupled with the posted example photos, if the two values are equal and equal to or less than 5, the "Expected", if the two values are equal and above 5, then "Above", otherwise you compare the second value to the first value to the first value and return "Above" or "Below".

 

[awoohaw]

The way I deciphered the OP's instructions coupled with the posted example photos, if the two values are equal and equal to or less than 5, the "Expected", if the two values are equal and above 5, then "Above", otherwise you compare the second value to the first value to the first value and return "Above" or "Below".

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.