Format the 3 numbers in a column closest to ZERO

Ultimate Selector

Board Regular
Joined
Dec 15, 2013
Messages
153
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.


1.4
0.9
0.1
-6.7
-9.3
-7.6
-4.7
-0.8
-1.6




<colgroup><col style="mso-width-source:userset;mso-width-alt:1512;width:33pt" width="43"> </colgroup><tbody>
</tbody>
 
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
1Numbers
21.4
30.9
40.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
CellConditionCell FormatStop If True
A11. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$1000)*(A$1:A$1000<>""),COUNT(A$1:A$1000)))Abc
A12. / 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
A13. / 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
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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. :)
 
Upvote 0
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.:cool:
 
Upvote 0
.. 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!
 
Last edited:
Upvote 0
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))
 
Last edited:
Upvote 0
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
1Numbers
239
351
466
563
645
739
870
963
1060
11
12
CF Near 50
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / 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
A12. / 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
A13. / 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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top