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?  Reply With Quote

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  Reply With Quote

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!  Reply With Quote

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
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.   Reply With Quote

5. Re: Format the 3 numbers in a column closest to ZERO Originally Posted by Peter_SSs 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  Reply With Quote

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!  Reply With Quote

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,""))  Reply With Quote

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.  Reply With Quote

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

Cheers   Reply With Quote

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!  Reply With Quote

User Tag List

Tags for this Thread

closest, format, numbers, suggestions, worked  Posting Permissions

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