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.



<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>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hey Pete back again ....

I have the same issue with this formula when I delete an entry.
It just keeps the remaining higher numbers of ranking because of the deletes. It assumes the celss I delete are part of the ranks.
The formula works fine except when I delete. Here is that formula ... What do I do to it to correct the ranking of it?

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

Thanks so Much Pete!
 
Last edited:
Upvote 0
I have the same issue with this formula ...
1. In words, what is this formula supposed to be doing?
2. Is it being used in Conditional Formatting like the previous ones?
3. What cell(s) is the formula applied in?


... when I delete an entry.
4. Do you mean deleting a whole row like in the previous problem or just deleting a value out of a cell or something else?
 
Upvote 0
Hey Pete,

This formula ranks the closest to zero as well.
It is being used in conditional formatting.
The formula is being applied to all entries in the column.

4) Yes, same exact issue when I delete a row or even a cell.

I hope this helps ...

Thanks again,
U.S.
 
Upvote 0
This is what happens when I have no data in the cells because I have no entries.
I may have only 8 entries or maybe just 6 or even 10.
But this is what happens on the formatting. It wants to include all rows no matter what.

Here is my example for 15 entries.
Now when I delete the last 7 because of no data, it will delete l the #1's or the last 7.
The remaining top 8 will be ranked but not using the lowest numbers for ranking value.
I think the 14,6 needs to be input in the statement somehow of the ABS.


<tbody>
[TD="class: xl66"]15[/TD]

[TD="class: xl66"]13[/TD]

[TD="class: xl66"]12[/TD]

[TD="class: xl66"]10[/TD]

[TD="class: xl66"]9[/TD]

[TD="class: xl66"]8[/TD]

[TD="class: xl66"]11[/TD]

[TD="class: xl66"]14[/TD]

[TD="class: xl66"]1[/TD]

[TD="class: xl66"]1[/TD]

[TD="class: xl66"]1[/TD]

[TD="class: xl66"]1[/TD]

[TD="class: xl66"]1[/TD]

[TD="class: xl66"]1[/TD]

[TD="class: xl66"]1[/TD]

</tbody>
 
Last edited:
Upvote 0
Here is the data I'm ranking closest to zero if you would like to use this for your reply.


<colgroup><col style="mso-width-source:userset;mso-width-alt:1582;width:34pt" width="45"> </colgroup><tbody>
[TD="class: xl66, width: 45"]11.4[/TD]

[TD="class: xl66"]9.7[/TD]

[TD="class: xl66"]5.2[/TD]

[TD="class: xl66"]3.4[/TD]

[TD="class: xl66"]-3.0[/TD]

[TD="class: xl66"]-2.0[/TD]

[TD="class: xl66"]4.2[/TD]

[TD="class: xl66"]-10.9[/TD]

</tbody>
 
Upvote 0
This formula ranks the closest to zero as well.
It is being used in conditional formatting.
The formula is being applied to all entries in the column.

4) Yes, same exact issue when I delete a row or even a cell.
So, why not use the CF formulas from post #11 ?

Excel Workbook
A
1Numbers
211.4
39.7
45.2
53.4
6-3
7-2
84.2
9-10.9
10
CF Near Zero (3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$993)*(A$1:A$993<>""),COUNT(A$1:A$993)))Abc
A12. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$993)*(A$1:A$993<>""),COUNT(A$1:A$993)-1))Abc
A13. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A$1:A$993)*(A$1:A$993<>""),COUNT(A$1:A$993)-2))Abc
 
Upvote 0
Because I want to rank them with a number system of 1-2-3 etc. in a different column.

Lowest being number 1 and so on.
 
Last edited:
Upvote 0
Hey Pete,

By the way ... wanted to let you know that I am using that formula from post 11 in a column.

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.

Thanks and much appreciated!

-U.S.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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