Format the 3 numbers in a column closest to ZERO

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Ultimate Selector

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,120
Office Version
365
Platform
Windows
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?
 

Ultimate Selector

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

Ultimate Selector

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

<tbody>
</tbody>
 
Last edited:

Ultimate Selector

Board Regular
Joined
Dec 15, 2013
Messages
125
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.0
-2.0
4.2
-10.9

<colgroup><col style="mso-width-source:userset;mso-width-alt:1582;width:34pt" width="45"> </colgroup><tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,120
Office Version
365
Platform
Windows
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)
#VALUE!
 

Ultimate Selector

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

Ultimate Selector

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

Watch MrExcel Video

Forum statistics

Threads
1,101,996
Messages
5,484,089
Members
407,430
Latest member
sgoldman

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top