Page 4 of 6 FirstFirst ... 23456 LastLast
Results 31 to 40 of 52

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

  1. #31
    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
    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.
    In post #24 you said you were using the formula in Conditional Formatting. Are you now saying that is not the case and this is being used a s a standard formula in its own cell/column?
    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. #32
    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

    Let's use your example. Column one I already have done and completed. No problems there.

    Column two is the column that will rank column one numerically. 1-2-3-4-5-6-7-8-9-10

    I will show you how I want to rank column one with the second example.



    A
    1 Numbers
    2 11.4
    3 9.7
    4 5.2
    5 3.4
    6 -3
    7 -2
    8 4.2
    9 -10.9



    Rank above column
    8
    7
    6
    3
    2
    1
    1
    4
    7


    Last edited by Ultimate Selector; Jul 19th, 2019 at 03:20 AM.

  3. #33
    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

    so I can rank them in table 1 by using this formula. The result is table 2 ....
    Of course change the range to whatever you need to for your own example.

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

    However, whenever I delete a row it doesn't ignore the deleted rows.

    Try using this formula above to rank your own example the try and delete a row or two and you will see what I mean.

    Thanks my Friend!
    Last edited by Ultimate Selector; Jul 19th, 2019 at 03:26 AM.

  4. #34
    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
    I will show you how I want to rank column one with the second example.



    A
    1 Numbers
    2 11.4
    3 9.7
    4 5.2
    5 3.4
    6 -3
    7 -2
    8 4.2
    9 -10.9



    Rank above column
    8
    7
    6
    3
    2
    1
    1
    4
    7


    I don't follow the example.
    - In the top part there are 8 numbers but in the rankings you have 9 numbers.
    - In the rankings you have two '1' values but it doesn't seem that you have two numbers equally closest to zero.
    - Similarly you have two '7' values in the rankings column. Again I can't see that any two of the sample numbers fit that equal ranking.
    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. #35
    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
    I don't follow the example.
    - In the top part there are 8 numbers but in the rankings you have 9 numbers.
    - In the rankings you have two '1' values but it doesn't seem that you have two numbers equally closest to zero.
    - Similarly you have two '7' values in the rankings column. Again I can't see that any two of the sample numbers fit that equal ranking.
    Sorry my bad, it's late and I've been at it all day. I don't know how that happened.

    Here is the correct numerical rankings from your example data.


    11.4 = Rank #8
    9.7 = Rank #6
    5.2 = Rank #5
    3.4 = Rank #3
    -3 = Rank #2
    -2 = Rank #1
    4.2= Rank #4
    -10.9 = Rank #7
    Last edited by Ultimate Selector; Jul 19th, 2019 at 04:14 AM.

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

    Simple thing to do is just rank your example data numbers with the formula I provided.
    Of course change the range to whatever you need to for your own example.

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

    Do this in a completely different column. Then delete a couple of the rows and see what happens.

    Again, what you're doing is simply ranking the order by numerical (1,2,3, etc) rather than color formatting.

    It's column one against column two. Just rank it in the second column and then delete a couple of rows in the 2nd column you just ranked numerically.

    Thanks!
    Last edited by Ultimate Selector; Jul 19th, 2019 at 04:20 AM.

  7. #37
    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
    Here is the correct numerical rankings from your example data.


    11.4 = Rank #8
    9.7 = Rank #6
    5.2 = Rank #5
    3.4 = Rank #3
    -3 = Rank #2
    -2 = Rank #1
    4.2= Rank #4
    -10.9 = Rank #7
    OK, that makes more sense.

    Try something like this

    =IF(R6="","",SUMPRODUCT((ABS(R6)>ABS(R$6:R$1000))+0)+1-COUNTIF(R$6:R$1000,""))
    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. #38
    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

    Perfect!

    Once again you come thru! Excellent work!

    U R Da Man! Thanks Pete!

    Regards,
    U.S.

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

    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 # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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

    Hi Pete!

    I'm having an issue with the highlighting format of using a Negative number in the formula. This number happens to -9

    =ISNUMBER(W8)*(ABS(W8--9)<=AGGREGATE(14,6,ABS(W$8:W$22--9)*(W$8:W$22<>""),COUNT(W$8:W$22)))

    Obviously I'm doin something wrong in the statement. Can you help me fix it so it will only HL -10 and below. When I say below I mean -11, -12 etc..

    Thanks Pete!


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
  •