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>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Like this?

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
CF Near Zero
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =ABS(A2)<=AGGREGATE(15,4,ABS(A$2:A$10),3)Abc
 
Upvote 0
Booo Yaaa!!! Thanks sooo much Pete! This was exactly what I needed. I have been searchin for this forever!

U Da Man! Have a spectacular Morning!

Regards,
U.S. :cool:
 
Upvote 0
Cheers, glad it was what you wanted. Thanks for letting us know. :)

I think this would also have done as your CF formula:
=ABS(A2)<=SMALL(ABS(A$2:A$10),3)
 
Upvote 0
Hey Pete ...

It works fine if yur in a static mode and don't delete any columns. So if I start with 10 and don't delete any it's ok on both formulas.
However when I delete columns say 3 of the 10 then it does NOT work with either formula.
I need it to be dynamic as this is what my work entails.

Any ideas would be great. We are Very close to getting this done!

Thanks so much Pete!
 
Upvote 0
I don't understand what you are saying regarding multiple columns. Your original sample data in post #1 appears to be in a single column so that's what I worked with. If you have multiple columns then you will need to explain more about that.
What do you have and where is it?
What are you trying to highlight and where?
etc

I need to be able to know what you have so I can mock up a sample sheet and test.
 
Upvote 0
It is a single column just as I posted. However, it can go up to and including 14 entries.
The one I posted has 10 entries. Now use the formula and then go delete a row or two say A2 and A9 in your example.
The highlighting will also go away when you do this. Try it out and see if you get that as well.
These entries can start out with 14 or even maybe just 10. The formula seems to always cover 14. But if you start out with less say 10 then my preset template will lose the highlighting on this formula!

Let me know what you come up with cuz I have tried everything.
Thanks!
 
Upvote 0
By the way Pete,

Jut wanted to let you know I am highlighting the closest thee to zero by using a different color for each by changing the last number in the formula from 3 then to 2 then 1 each as a new rule.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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