# Thread: Format the 3 numbers in a column closest to ZERO Thanks: 0 Likes:  1 Post #5305891 (1)

1. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Ultimate Selector
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?

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

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

4. ## Re: Format the 3 numbers in a column closest to ZERO

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

5. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Peter_SSs
- 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

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

7. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Ultimate Selector
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,""))

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

Cheers

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