SOLVED

Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1824145%22%20slang%3D%22en-US%22%3EFormula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824145%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%20with%20number%20values.%20How%20do%20I%20create%20a%20formula%20to%20show%20which%20column%20is%20closest%20to%200%3F%3C%2FP%3E%3CP%3EColumn%20A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Column%20B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Results%3C%2FP%3E%3CP%3E0.20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-33.63%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Column%20A%3C%2FP%3E%3CP%3E13.52%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-0.26%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Column%20B%3C%2FP%3E%3CP%3E239.52%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20225.74%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Column%20B%3C%2FP%3E%3CP%3E-0.48%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-14.26%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Column%20A%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1824145%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1824185%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824185%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847894%22%20target%3D%22_blank%22%3E%40tmartinez884%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20one%20solution...%3C%2FP%3E%3CP%3EFor%20column%20C%3A%3C%2FP%3E%3CPRE%3E%3DMEDIAN(ABS(A1)%2CABS(B1)%2C0)%3C%2FPRE%3E%3CP%3EFor%20column%20%3CLI-EMOJI%20id%3D%22lia_anguished-face%22%20title%3D%22%3Aanguished_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CPRE%3E%26nbsp%3B%3DIF(ABS(A1)%3DC1%2C%22Column%20A%22%2C%20%22Column%20B%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1824349%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824349%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3Bthanks.%26nbsp%3B%20The%20formula%20is%20not%20working%20100%25%20of%20the%20time.%20It%20appears%20to%20be%20working%20when%20Column%20A%20%26lt%3B0%20but%20not%20when%20the%20number%20is%20%26gt%3B0.%26nbsp%3B%20How%20can%20I%20accommodate%20the%20second%20scenario%3F%3C%2FP%3E%3CTABLE%20width%3D%22248px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3EColumn%20A%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3EColumn%20B%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E0.20%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-33.63%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E0.20%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-33.63%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E339.52%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E325.74%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3EColumn%20A%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E0.20%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-33.63%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E-0.48%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-14.26%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E0.20%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-33.63%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E215.52%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E201.74%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3EColumn%20A%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E-0.48%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-14.26%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E-137.48%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-151.26%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E0.20%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-33.63%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E0.20%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-33.63%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E89.52%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E75.74%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3EColumn%20A%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E0.20%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E-33.63%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3EColumn%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E239.52%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E225.74%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3EColumn%20A%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E339.52%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E325.74%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3EColumn%20A%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286px%22%3E339.52%3C%2FTD%3E%3CTD%20width%3D%2294px%22%3E325.74%3C%2FTD%3E%3CTD%20width%3D%2267px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3EColumn%20A%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1824369%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824369%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847894%22%20target%3D%22_blank%22%3E%40tmartinez884%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20interesting...%20the%20formula%20is%20working%20correctly%20for%20me.%3C%2FP%3E%3CP%3EAre%20you%20using%20relative%20or%20absolute%20references%3F%20(Relative%20-%20A1%2C%20Absolute%20-%20%24A%241)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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