Conditional Formatting Formula To Fill Red If Number Nearest Zero

[muhammad susanto]

hi all...

how to make formula in Conditional Formatting :
to determinate fill cell red color for number that nearest zero (absolut number)
this layout :

data1	data2	data3
-10%	39%	-46%

<tbody>
</tbody>

any help thank in advance...

.sst

 

[JoeMo]

Assuming your values are in A2:C2 (change to suit):
1. Select A2:C2
2. Home>Conditional Formatting> New Rule> Use a formula
3. Formula is: =ABS(A2)=MIN(ABS($A$2:$C$2))
4. Choose format and click OK

EDIT: This is an array formula, but you don't need to add any braces, Excel will handle that.

 

[muhammad susanto]

hi JoeMo.....

thanks you, worked it great!!!

 

[JoeMo]

hi JoeMo.....

thanks you, worked it great!!!

You are welcome - thanks for the reply.

 

[muhammad susanto]

hi Joe...
i have found new problem, how is if the column different :
my data in A2,C2,E2
i have just modify but not working

Col.A		Col.C		Col.E
78%		-12%		15%

<tbody>
</tbody>

 

[JoeMo]

I tested this in row 6 so change the row number throughout to suit. New CF formula is:

=AND(ISNUMBER(A6),ABS(A6)=SMALL(ABS($A$6:$E$6),COUNTBLANK($A$6:$E$6)+1))