Oct 27 2020 10:56 AM
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
Oct 27 2020 11:10 AM - edited Oct 27 2020 11:12 AM
Hello @tmartinez884,
Here is one solution...
For column C:
=IF(MEDIAN(ABS(A1),ABS(B1),0)=ABS(A1),"Column A", "Column B")
Oct 27 2020 11:40 AM
@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 A | Column B | |
0.20 | -33.63 | Column A |
0.20 | -33.63 | Column A |
339.52 | 325.74 | Column A |
0.20 | -33.63 | Column A |
-0.48 | -14.26 | Column A |
0.20 | -33.63 | Column A |
215.52 | 201.74 | Column A |
-0.48 | -14.26 | Column A |
-137.48 | -151.26 | Column A |
0.20 | -33.63 | Column A |
0.20 | -33.63 | Column A |
89.52 | 75.74 | Column A |
0.20 | -33.63 | Column A |
239.52 | 225.74 | Column A |
339.52 | 325.74 | Column A |
339.52 | 325.74 | Column A |
Oct 27 2020 11:50 AM
That's interesting... the formula is working correctly for me.
Are you using relative or absolute references? (Relative - A1, Absolute - $A$1)
Oct 27 2020 12:38 PM
SolutionOct 27 2020 01:38 PM
You are welcome. Formula suggested by @PReagan shall return exactly the same result
Oct 27 2020 12:38 PM
SolutionAs variant
with
=XLOOKUP(0,ABS(A1:B1),{"Column A","Column B"},,1)