Format the 3 numbers in a column closest to ZERO

[Ultimate Selector]

Ok so here is what I'm trying to do. Here are my numbers ..
I want to highlight the three lowse numbers closest to zero. Which would be numbers
0.1 , -.08 and 0.9 ...
I have tried several formulas but nothing has worked.
Any suggestions or the right conditional format formula would be greatly Appreciated!

Thanks in Advance!
U.S.

<colgroup><col style="mso-width-source:userset;mso-width-alt:1512;width:33pt" width="43"> </colgroup><tbody>
[TD="class: xl64, width: 43"]1.4[/TD]

[TD="class: xl65"]0.9
[/TD]

[TD="class: xl65"] 0.1
[/TD]

[TD="class: xl65"]-6.7[/TD]

[TD="class: xl65"]-9.3[/TD]

[TD="class: xl65"]-7.6[/TD]

[TD="class: xl65"]-4.7[/TD]

[TD="class: xl65"]-0.8[/TD]

[TD="class: xl65"]-1.6



[/TD]

</tbody>

 

[Peter_SSs]

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?

 

[Ultimate Selector]

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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Numbers[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]11.4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]9.7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5.2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3.4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]-3[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]-2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4.2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]-10.9[/TD]

</tbody>

[TABLE="width: 45"]
<tbody>[TR]
[TD]
Rank above column [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]7


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ultimate Selector]

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!

 

[Peter_SSs]

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

	A

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Numbers[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]11.4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]9.7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5.2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3.4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]-3[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]-2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4.2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]-10.9[/TD]

</tbody>

[TABLE="width: 45"]
<tbody>[TR]
[TD]
Rank above column [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]7


[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Ultimate Selector]

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

 

[Ultimate Selector]

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!

 

[Peter_SSs]

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,""))

 

[Ultimate Selector]

Perfect!

Once again you come thru! Excellent work!

U R Da Man! Thanks Pete!

Regards,
U.S.

 

[Peter_SSs]

Cheers

 

[Ultimate Selector]

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!