Page 2 of 6 FirstFirst 1234 ... LastLast
Results 11 to 20 of 52

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

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

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

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

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

    Conditional formatting
    CellNr.: / ConditionFormat
    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


    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

  2. #12
    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

    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. #13
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

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

    Quote Originally Posted by Ultimate Selector View Post
    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.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #14
    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

    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.

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

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

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

  6. #16
    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 Peter_SSs View Post
    .. 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 by Ultimate Selector; Jul 9th, 2019 at 11:52 PM.

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

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

    Quote Originally Posted by Ultimate Selector View Post
    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 by Peter_SSs; Jul 13th, 2019 at 05:44 AM.
    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

  8. #18
    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

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

    Another idea or thought?

    Thanks!

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

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

    Quote Originally Posted by Ultimate Selector View Post
    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
    1Numbers
    239
    351
    466
    563
    645
    739
    870
    963
    1060
    11
    12

    Conditional formatting
    CellNr.: / ConditionFormat
    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


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Jul 13th, 2019 at 10:41 PM.
    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

  10. #20
    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

    Thanks Pete! Once again you're spot on.

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

    Much Appreciated!

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
  •