Conditional Formatting - Nearest to Zero

[alexfooty]

In the cells below (AN25:BL25) I want to use conditional formatting to highlight the cell whose value is nearest to cell AM25 (Zero)

I have tried the formula =ABS(AN25-$AM$25)=MIN(ABS($AN$25:$BL$25-$AM$25)) which doesn't seem to work.

Also note that between each of the cells there are two hidden columns which do NOT contain any data or formulas.

Any help would be greatly appreciated.

 

[jasonb75]

Using the same basic method as Peter's suggestion which you are already using for the conditional formatting, try entering this one in AN26 and dragging right.

Must be array confirmed with Crtl Shift Enter.

=SUM(IFERROR(--(ABS(AN25-$AM25)>=(ABS($AN25:$BL25-$AM25)/($AN25:$BL25<>""))),""))

Results might not be as expected with duplicate values in row 25.

 

[alexfooty]

jasonb75 - thank you so much, works brilliantly.

 

[Peter_SSs]

Results might not be as expected with duplicate values in row 25.

.. duplicate absolute values too as shown in row 26..

An alternative in row 27 that allows for ties. I'm assuming always zero in column AM.

20 08 31.xlsm
	AM	AN			AQ			AT			AW			AZ			BC			BF			BI			BL
25	0	-27			-3			-17			-27			3			7			-27			24			-39
26		8			2			4			8			2			3			8			5			9
27		6			1			4			6			1			3			6			5			9
CF near value (2)

Cell Formulas
Range	Formula
AN26,AQ26,AT26,AW26,AZ26,BC26,BF26,BI26,BL26	AN26	=SUM(IFERROR(--(ABS(AN25-$AM25)>=(ABS($AN25:$BL25-$AM25)/($AN25:$BL25<>""))),""))
AN27,AQ27,AT27,AW27,AZ27,BC27,BF27,BI27,BL27	AN27	=SUMPRODUCT(--(ABS($AN25:$BL25)<ABS(AN25)),--($AN25:$BL25<>""))+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AN25:BL25	Expression	=ABS(AN25)=AGGREGATE(15,6,ABS($AN25:$BL25)/($AN25:$BL25<>""),1)	text	NO

 

[alexfooty]

You are correct. I made 2 values the same and they were both ranked as 2 instead of 1. Changed the formula and all is well. Many thanks to both of you for your imput.