Format the 3 numbers in a column closest to ZERO

[Ultimate Selector]

Ok so here is what I'm trying to do. Here are my numbers ..
I want to highlight the three lowse numbers closest to zero. Which would be numbers
0.1 , -.08 and 0.9 ...
I have tried several formulas but nothing has worked.
Any suggestions or the right conditional format formula would be greatly Appreciated!

Thanks in Advance!
U.S.

<colgroup><col style="mso-width-source:userset;mso-width-alt:1512;width:33pt" width="43"> </colgroup><tbody>
[TD="class: xl64, width: 43"]1.4[/TD]

[TD="class: xl65"]0.9
[/TD]

[TD="class: xl65"] 0.1
[/TD]

[TD="class: xl65"]-6.7[/TD]

[TD="class: xl65"]-9.3[/TD]

[TD="class: xl65"]-7.6[/TD]

[TD="class: xl65"]-4.7[/TD]

[TD="class: xl65"]-0.8[/TD]

[TD="class: xl65"]-1.6



[/TD]

</tbody>

 

[Peter_SSs]

However when I delete columns say 3 of the 10 ...

.. delete a row or two say A2 and A9 in your example.

OK, so I didn't understand because you talked about deleting columns when it now appears you meant deleting rows.

... I am highlighting the closest thee to zero by using a different color for each ..

So try removing the previous CF from the column then apply this to say A1:A1000. When you delete rows, you also delete the CF that is in those rows so if you end up deleting enough rows you will eventually end up with insufficient cells with the CF.
You will need to be careful with the order and/or the 'Stop if true' condition for these three rules.

Excel Workbook
	A
1	Numbers
2	1.4
3	0.9
4	0.1
5	-6.7
6	-9.3
7	-7.6
8	-4.7
9	-0.8
10	-1.6
11
12
CF Near Zero (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$1000)*(A$1:A$1000<>""),COUNT(A$1:A$1000)))	Abc
A1	2. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$1000)*(A$1:A$1000<>""),COUNT(A$1:A$1000)-1))	Abc
A1	3. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$1000)*(A$1:A$1000<>""),COUNT(A$1:A$1000)-2))	Abc

 

[Ultimate Selector]

Most excellent Pete! Absolutely Fantastic!

My bad when I said deleting columns when indeed I meant rows!
This has been kickin my Azz for over a year now!

Works like a charm! You're a genius!

Again, so many Thanks and have a stupendous week Pete!

Regards,
U.S.

 

[Aryatect]

I now understand that this means small and max.

Hi US,

you are right 15 = SMALL, but 4 is the next argument which means "Ignore Nothing" as don't ignore error.

 

[Ultimate Selector]

Hello Aryatect,

Thanks for correcting my statement. I did use the 4 in the same function which was max.
I didn't move to the next argument. My bad!

Much Appreciated on your input!

Have a Great Day!

- U.S.

 

[Peter_SSs]

.. but 4 is the next argument which means "Ignore Nothing" as don't ignore error.

I did use the 4 in the same function which was max.
I didn't move to the next argument.

.. but note that my latest suggestion uses the second argument of '6' (DO ignore error values)

 

[Ultimate Selector]

.. but note that my latest suggestion uses the second argument of '6' (DO ignore error values)

I see that Pete Thanks my friend.

I have one other question for you.

If I wanted to change the number from closest to zero and lets say make it closest to 50 instead where in the statement will I make that change?

Thanks!

 

[Peter_SSs]

If I wanted to change the number from closest to zero and lets say make it closest to 50 instead where in the statement will I make that change?

Try something like
=ISNUMBER(A1)*(ABS(A1-50)<=AGGREGATE(14,6,ABS(A$1:A$1000-50)*(A$1:A$1000<>""),COUNT(A$1:A$1000)-2))

 

[Ultimate Selector]

Sorry Pete but I did it several times and I get the "error message" popping up!

Another idea or thought?

Thanks!

 

[Peter_SSs]

Sorry Pete but I did it several times and I get the "error message" popping up!

Another idea or thought?

Thanks!

It is working for me as is ...

Excel Workbook
	A
1	Numbers
2	39
3	51
4	66
5	63
6	45
7	39
8	70
9	63
10	60
11
12
CF Near 50

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =ISNUMBER(A1)*(ABS(A1-50)<=AGGREGATE(14,6,ABS(A$1:A$1000-50)*(A$1:A$1000<>""),COUNT(A$1:A$1000)))	Abc
A1	2. / Formula is =ISNUMBER(A1)*(ABS(A1-50)<=AGGREGATE(14,6,ABS(A$1:A$1000-50)*(A$1:A$1000<>""),COUNT(A$1:A$1000)-1))	Abc
A1	3. / Formula is =ISNUMBER(A1)*(ABS(A1-50)<=AGGREGATE(14,6,ABS(A$1:A$1000-50)*(A$1:A$1000<>""),COUNT(A$1:A$1000)-2))	Abc

 

[Ultimate Selector]

Thanks Pete! Once again you're spot on.

I don't know what I did earlier but it works great now!

Much Appreciated!