multiple excel if statements to produce value 1,2 or 3

Question

I am trying to find out the right if statement to use for this task:

if B5 is higher than C5 = 1
if B5 is within 2% higher than C5 = 2
if B5 is lower than C5 = 3

I have the formula for the second statement

=IF(AND(B5>(C5-(C5*2/100)),B5<(C5+(C5*2/100))),"yes","no")

Im just not sure how to put it into one statement to get one of the 3 numbers to come out at the end. main thing im struggling with is that there are only two answers in an excel if statement it can return

Answer 1

Just to make sure I understand correctly, when B5 is within 2% of C5 (either greater than, equal to, or less than), should the formula return "2"? If so, you can use the following formula:

=IF(ABS(B5-C5)<=C5*0.02,2,IF(B5>C5,1,3))

This formula returns "2" when B5 is exactly 2% less than or exactly 2% greater than C5. If you want it to return "3" and "1" in these cases respectively, you can use the following instead:

=IF(ABS(B5-C5)<C5*0.02,2,IF(B5>C5,1,3))