find number closest to 0 (positive or negative)

[rickvinke]

i have a row of digits of positive and negative numbers.
in this row i want to highlight the number closest to 0.

we have made guesses of multiple outcomes and for each outcome i would like to highlight the closest number so we could see in an instand who's guessis the closest per line.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[MrShorty]

I would probably add a helper row =ABS(A1) (assuming your values are in row 1) in an adjacent row. Then =MIN(helper row) to find the value closes to 0. Then a lookup function (probably MATCH()) to find the position of the minimum value, then whatever is needed to associate position with the person who made the guess.

Step 1: helper row =ABS(A1)
Step 2: get minimum =MIN(helper row)
Step 3: find the position of the minimum =MATCH(result of 2,helper row,0)

Will that work?

[Phuocam]

Try this:

=INDEX($B2:$G2,MATCH(AGGREGATE(15,6,ABS($B2:$G2)/($B2:$G2<>0),1),ABS($B2:$G2),0))

[Gregb11]

If your numbers are in cells A1 through J1, in Conditional formatting, try:

=MIN(ABS($A$1:$J$1))=ABS(A1)