# Thread: Format the 3 numbers in a column closest to ZERO Thanks: 0 Likes:  1 Post #5305891 (1)

1. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Ultimate Selector
However when I delete columns say 3 of the 10 ...
Originally Posted by Ultimate Selector
.. 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.

Originally Posted by Ultimate Selector
... 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.

CF Near Zero (2)

 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

Conditional formatting
 Cell Nr.: / Condition Format 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

Excel tables to the web >> Excel Jeanie HTML 4

2. ## Re: Format the 3 numbers in a column closest to ZERO

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.

3. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Ultimate Selector
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.

4. ## Re: Format the 3 numbers in a column closest to ZERO

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!

Have a Great Day!

- U.S.

5. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Aryatect
.. but 4 is the next argument which means "Ignore Nothing" as don't ignore error.
Originally Posted by Ultimate Selector
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)

6. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Peter_SSs
.. 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!

7. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Ultimate Selector
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))

8. ## Re: Format the 3 numbers in a column closest to ZERO

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

Another idea or thought?

Thanks!

9. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Ultimate Selector
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 ...

CF Near 50

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

Conditional formatting
 Cell Nr.: / Condition Format 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

Excel tables to the web >> Excel Jeanie HTML 4

10. ## Re: Format the 3 numbers in a column closest to ZERO

Thanks Pete! Once again you're spot on.

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

Much Appreciated!