If/ifs formula help

[Sophiepope]

I have the below data.
I have the current formula in which works for all the data apart from row 3 - returns working above but in fact is working below.

=ifs(a1=b1,”working at”,a1>b1,”working above”,a1<b1,”working below”)

Does anyone know how I can amend the formula. I cannot ask it to ignore all instances of the + symbol as that would create incorrect data in other cells e.g row 5.

A B C
4+ 4+ working at
4+ 3 Working above
4+ 6 Working above
4 4+ working below
4+ 4 Working above

 

[Alex Blakenburg]

Would something like this work for you ?

20240406 Alpa Numeric Result comparison Sophiepope.xlsx
	A	B	C
1	L2P	L2P	Working At
2	L1P	L2P	Working Above
3	L3P	L2P	Working Below
4	L2D	L2P	Working Below
5	L2M	L2P	Working Below
6	L2P	L2D	Working Above
7
8	L2D	L2M	Working Below
Test

Cell Formulas
Range	Formula
C1:C8	C1	=IFERROR( CHOOSE( SIGN( (MID(B1,2,1)+SWITCH(RIGHT(B1,1),"P",0.1,"M",".2","D",0.3)) -(MID(A1,2,1)+SWITCH(RIGHT(A1,1),"P",0.1,"M",".2","D",0.3))) +2, "Working Below","Working At", "Working Above"),"")

 

[Peter_SSs]

If Alex has it right and the final letters are fixed at P, M and D then you could also try this modification.

24 04 06.xlsm
	A	B	C
1	L2P	L2P	Working At
2	L1P	L2P	Working Above
3	L3P	L2P	Working Below
4	L2D	L2P	Working Below
5	L2M	L2P	Working Below
6	L2P	L2D	Working Above
7
8	L2D	L2M	Working Below
At Above Below

Cell Formulas
Range	Formula
C1:C8	C1	=IFERROR("Working "&CHOOSE(SIGN((MID(B1,2,1)&90-CODE(RIGHT(B1)))-(MID(A1,2,1)&90-CODE(RIGHT(A1))))+2,"Below","At","Above"),"")

 

[Sophiepope]

thank you both, this has fixed my issues - I really appreciate your help!

 

[Alex Blakenburg]

You’re welcome. Glad we could help.