Format the 3 numbers in a column closest to ZERO

Ultimate Selector

Board Regular
Joined
Dec 15, 2013
Messages
153
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.


1.4
0.9
0.1
-6.7
-9.3
-7.6
-4.7
-0.8
-1.6




<colgroup><col style="mso-width-source:userset;mso-width-alt:1512;width:33pt" width="43"> </colgroup><tbody>
</tbody>
 
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?
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
1Numbers
211.4
39.7
45.2
53.4
6-3
7-2
84.2
9-10.9

<tbody>
</tbody>



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



<tbody>
</tbody>
 
Last edited:
Upvote 0
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:
Upvote 0
I will show you how I want to rank column one with the second example.



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

<tbody>
</tbody>



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



<tbody>
</tbody>
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.
:confused:
 
Upvote 0
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.
:confused:

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:
Upvote 0
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:
Upvote 0
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,""))
 
Upvote 0
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!

 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top