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  Reply With Quote

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.   Reply With Quote

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.  Reply With Quote

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!

Much Appreciated on your input!

Have a Great Day!

- U.S.   Reply With Quote

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)  Reply With Quote

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!  Reply With Quote

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))  Reply With Quote

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!  Reply With Quote

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  Reply With Quote

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!  Reply With Quote

User Tag List

Tags for this Thread

closest, format, numbers, suggestions, worked  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•