What is the shortest way to determine if one number is greater than, less than, or equal to another?

What is the shortest Excel worksheet formula to compare two numbers and determine whether one is greater than, less than, or equal two the other number?

I know how to do a simple comparison with the <,>,=, etc. operators and I know I can nest IF statements, but I've been trying to figure out if there is a shorter way, such as FUNCTION(X,Y) ==> 1 if <, 2 if >, 3 if =

Any ideas?

2 Answers

=sign(a1-b1)

result is -1,0, or 1

1

My approach is little different, I've logically tested data in both cells to get value, to justify the test.

• Formula in Cell `C107:

  =(A107>B107)*(A107*10)+(A107<=B107)*(A107*5)

N.B. :

• Cell A107 will be multiplied by 5 if & when A107 is either Equals to Or Less than B107.

• Otherwise, if cell A107 is Greater than B107, then multiplied by 10.

Adjust cell references in the formula as needed.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy