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]

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

This is where the problem lies, an empty cell is equal to zero so is the closest to the criteria.

Trying to think of ways to make this work, will AM25 always be 0 or can it vary? Can any of the other cells be 0 as well?

 

[Peter_SSs]

See if this does what you want.

20 08 31.xlsm
	AM	AN			AQ			AT			AW			AZ			BC			BF			BI			BL
25	0	-27			19			-17			-27			3			-35			-45			-24			-39
CF near value

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

 

[alexfooty]

Peter - works perfectly - thank you again for your help.

jasonb75 - thank you

 

[Peter_SSs]

Cheers. Glad it helped.

 

[alexfooty]

Peter - how would this formula change if I also wanted to highlight the number furthest from zero?

 

[Peter_SSs]

Peter - works perfectly

Just checking - are you sure?
I just realised did not post the CF formula that I meant to.

Further to jasonb75's question about whether the AM value is always 0 or not.

If it is not always zero then my previous formula will not always work. Something like this instead

20 08 31.xlsm
	AM	AN			AQ			AT			AW			AZ			BC			BF			BI			BL
25	11	-27			19			-17			-27			3			-35			-45			-24			-39
CF near value

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

If the AM value is always zero then you only need

20 08 31.xlsm
	AM	AN			AQ			AT			AW			AZ			BC			BF			BI			BL
25	0	-27			19			-17			-27			3			-35			-45			-24			-39
CF near value (2)

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

 

[Peter_SSs]

Peter - how would this formula change if I also wanted to highlight the number furthest from zero?

Furthest from zero or furthest from whatever is in column AM? Comes back to the same question about whether that value ever changes?

 

[alexfooty]

AM25 is always zero. Your original formula worked fine. I changed the numbers in some of the cells and the correct cell was highlighted each time.
However, I've changed the formula to the 2nd one you posted, on the off chance that AM25 value may change in the future.

 

[alexfooty]

I used your formula "=ABS(AN25-$AM25)=AGGREGATE(15,6,ABS($AN25:$BL25-$AM25)/($AN25:$BL25<>""),1)" to correctly highlight the cell value nearest to zero in cells AN25:BL25 using conditional formatting.
Is there a formula I can use in cells AN26:BL26 to rank these values so that the cell nearest to zero is ranked 1 and the cell furthest from zero is ranked 9?