# 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

Cheers.   Reply With Quote

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

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

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

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

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

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

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.

 15 13 12 10 9 8 11 14 1 1 1 1 1 1 1  Reply With Quote

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

Here is the data I'm ranking closest to zero if you would like to use this for your reply.

 11.4 9.7 5.2 3.4 -3 -2 4.2 -10.9  Reply With Quote

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

Again, Thanks sooo much Pete!   Reply With Quote

8. ## Re: Format the 3 numbers in a column closest to ZERO Originally Posted by Ultimate Selector 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 ?

CF Near Zero (3)

 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 10

Conditional formatting
 Cell Nr.: / Condition Format A1 1. / Formula is =ISNUMBER(A1)*(ABS(A1)<=AGGREGATE(14,6,ABS(A\$1:A\$993)*(A\$1:A\$993<>""),COUNT(A\$1:A\$993))) Abc A1 2. / 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 A1 3. / 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

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

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

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

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

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