SOLVED

Formula

Copper Contributor

I have two columns with number values. How do I create a formula to show which column is closest to 0?

Column A      Column B    Results

0.20                    -33.63      Column A

13.52                   -0.26      Column B

239.52              225.74      Column B

-0.48                  -14.26      Column A

7 Replies

Hello @tmartinez884,

 

Here is one solution...

For column C:

=IF(MEDIAN(ABS(A1),ABS(B1),0)=ABS(A1),"Column A", "Column B") 

@PReagan thanks.  The formula is not working 100% of the time. It appears to be working when Column A <0 but not when the number is >0.  How can I accommodate the second scenario?

Column AColumn B 
0.20-33.63Column A
0.20-33.63Column A
339.52325.74Column A
0.20-33.63Column A
-0.48-14.26Column A
0.20-33.63Column A
215.52201.74Column A
-0.48-14.26Column A
-137.48-151.26Column A
0.20-33.63Column A
0.20-33.63Column A
89.5275.74Column A
0.20-33.63Column A
239.52225.74Column A
339.52325.74Column A
339.52325.74Column A

@tmartinez884 

 

That's interesting... the formula is working correctly for me.

Are you using relative or absolute references? (Relative - A1, Absolute - $A$1)

@PReagan I believe relative

best response confirmed by tmartinez884 (Copper Contributor)
Solution

@tmartinez884 

As variant

image.png

with

=XLOOKUP(0,ABS(A1:B1),{"Column A","Column B"},,1)
Thank you, that worked!

@tmartinez884 

You are welcome. Formula suggested by @PReagan shall return exactly the same result

1 best response

Accepted Solutions
best response confirmed by tmartinez884 (Copper Contributor)
Solution

@tmartinez884 

As variant

image.png

with

=XLOOKUP(0,ABS(A1:B1),{"Column A","Column B"},,1)

View solution in original post