Format the 3 numbers in a column closest to ZERO
Page 3 of 6 FirstFirst 12345 ... LastLast
Results 21 to 30 of 52

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

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

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

    Cheers.
    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 VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  2. #22
    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 back again ....

    I have the same issue with this formula when I delete an entry.
    It just keeps the remaining higher numbers of ranking because of the deletes. It assumes the celss I delete are part of the ranks.
    The formula works fine except when I delete. Here is that formula ... What do I do to it to correct the ranking of it?

    =SUMPRODUCT((ABS(R6-0)>ABS(R$6:R$20-0))+0)+1

    Thanks so Much Pete!
    Last edited by Ultimate Selector; Jul 17th, 2019 at 10:49 PM.

  3. #23
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,354
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

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

    Quote Originally Posted by Ultimate Selector View Post
    I have the same issue with this formula ...
    1. In words, what is this formula supposed to be doing?
    2. Is it being used in Conditional Formatting like the previous ones?
    3. What cell(s) is the formula applied in?


    Quote Originally Posted by Ultimate Selector View Post
    ... when I delete an entry.
    4. Do you mean deleting a whole row like in the previous problem or just deleting a value out of a cell or something else?
    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 VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #24
    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,

    This formula ranks the closest to zero as well.
    It is being used in conditional formatting.
    The formula is being applied to all entries in the column.

    4) Yes, same exact issue when I delete a row or even a cell.

    I hope this helps ...

    Thanks again,
    U.S.

  5. #25
    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

    This is what happens when I have no data in the cells because I have no entries.
    I may have only 8 entries or maybe just 6 or even 10.
    But this is what happens on the formatting. It wants to include all rows no matter what.

    Here is my example for 15 entries.
    Now when I delete the last 7 because of no data, it will delete l the #1's or the last 7.
    The remaining top 8 will be ranked but not using the lowest numbers for ranking value.
    I think the 14,6 needs to be input in the statement somehow of the ABS.

    15
    13
    12
    10
    9
    8
    11
    14
    1
    1
    1
    1
    1
    1
    1
    Last edited by Ultimate Selector; Jul 18th, 2019 at 02:44 PM.

  6. #26
    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

    Here is the data I'm ranking closest to zero if you would like to use this for your reply.

    11.4
    9.7
    5.2
    3.4
    -3.0
    -2.0
    4.2
    -10.9

  7. #27
    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

    Again, Thanks sooo much Pete!

  8. #28
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,354
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

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

    Quote Originally Posted by Ultimate Selector View Post
    This formula ranks the closest to zero as well.
    It is being used in conditional formatting.
    The formula is being applied to all entries in the column.

    4) Yes, same exact issue when I delete a row or even a cell.
    So, why not use the CF formulas from post #11 ?

    CF Near Zero (3)

    A
    1Numbers
    211.4
    39.7
    45.2
    53.4
    6-3
    7-2
    84.2
    9-10.9
    10

    Conditional formatting
    CellNr.: / ConditionFormat
    A11. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$993)*(A$1:A$993<>""),COUNT(A$1:A$993)))Abc
    A12. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$993)*(A$1:A$993<>""),COUNT(A$1:A$993)-1))Abc
    A13. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$993)*(A$1:A$993<>""),COUNT(A$1:A$993)-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 VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #29
    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

    Because I want to rank them with a number system of 1-2-3 etc. in a different column.

    Lowest being number 1 and so on.
    Last edited by Ultimate Selector; Jul 19th, 2019 at 01:30 AM.

  10. #30
    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,

    By the way ... wanted to let you know that I am using that formula from post 11 in a column.

    Now what I want to do is rank that column with the numerical (1,2,3) ranking system I stated above.
    I was able to rank them but the deleted rows affect the column of numerical ranks as you can see above.

    Thanks and much appreciated!

    -U.S.
    Last edited by Ultimate Selector; Jul 19th, 2019 at 02:17 AM.

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
  •