CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Rajsha
Rajsha

Reputation: 15

How to compare 2 integers in terms of how many times 1st Integer is greater than second

I am looking to find a formula in excel by which I can calculate how many times 1st integer is greater than second.

For Example:

A = 5
B = 1
Ans: (A/B) i.e 5/1 = 5
A is 5 times bigger than B. Simple Enough!!

Problem occurs when one integer is negative, Example:

A = 10
B = -5

How do I calculate in excel, how much time A is bigger or smaller than B.

Condition: Any Integer could be negative (A or B)

Thank You!!

What I tried:

A = 10
B = -5
C = A / B 
C = 10 / -5 
C = -2 which is incorrect :(**

Upvotes: 0

Views: 113

Answers (2)

Rajsha
Rajsha

Reputation: 15

Thank You@ JvdV and Notus_panda: This works perfectly!!

=(MAX(B1:B2)-MIN(B1:B2))/MIN(ABS(B1:B2))+(OR(MIN(B1:B2)>0,MAX(B1:B2)<0))

="A is " & (MAX(B1:B2)-MIN(B1:B2))/MIN(ABS(B1:B2))+(OR(MIN(B1:B2)>0,MAX(B1:B2)<0)) & " times " & IF(B1<B2,"smaller","bigger") & " than B"

enter image description here

Upvotes: 0

JvdV
JvdV

Reputation: 75960

Maybe try:

enter image description here

Formula in A4:

=(MAX(A1:A2)-MIN(A1:A2))/MIN(ABS(A1:A2))+(OR(MIN(A1:A2)>0,MAX(A1:A2)<0))

Upvotes: 2

Related Questions