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 :

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]data1[/TH]
[TH]data2[/TH]
[TH]data3[/TH]
[/TR]
[TR]
[TD]-10%[/TD]
[TD]39%[/TD]
[TD]-46%[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Col.A[/TH]
[TH][/TH]
[TH]Col.C[/TH]
[TH][/TH]
[TH]Col.E[/TH]
[/TR]
[TR]
[TD]78%[/TD]
[TD][/TD]
[TD]-12%[/TD]
[TD][/TD]
[TD]15%[/TD]
[/TR]
</tbody>[/TABLE]

 

[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))