Page 1 of 6 123 ... LastLast
Results 1 to 10 of 52

Thread: Format the 3 numbers in a column closest to ZERO

  1. #1
    Board Regular
    Join Date
    Dec 2013
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Format the 3 numbers in a column closest to ZERO

    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



  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,964
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    Like this?

    CF Near Zero

    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

    Conditional formatting
    CellNr.: / ConditionFormat
    A21. / Formula is =ABS(A2)<=AGGREGATE(15,4,ABS(A$2:A$10),3)Abc


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Dec 2013
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,964
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    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)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Dec 2013
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,964
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    Dec 2013
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  8. #8
    Board Regular
    Join Date
    Dec 2013
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    What do these two numers represent in the formula?
    The 15,4

    =ABS(A2)<=AGGREGATE(15,4,ABS(A$2:A$10),3)

  9. #9
    Board Regular
    Join Date
    Dec 2013
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by Ultimate Selector; Jul 8th, 2019 at 02:47 AM.

  10. #10
    Board Regular
    Join Date
    Dec 2013
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Ultimate Selector View Post
    What do these two numers represent in the formula?
    The 15,4

    =ABS(A2)<=AGGREGATE(15,4,ABS(A$2:A$10),3)
    I now understand that this means small and max.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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