Format the 3 numbers in a column closest to ZERO

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,611
Office Version
365
Platform
Windows
However when I delete columns say 3 of the 10 ...
.. delete a row or two say A2 and A9 in your example.
OK, so I didn't understand because you talked about deleting columns when it now appears you meant deleting rows. ;)

... I am highlighting the closest thee to zero by using a different color for each ..
So try removing the previous CF from the column then apply this to say A1:A1000. When you delete rows, you also delete the CF that is in those rows so if you end up deleting enough rows you will eventually end up with insufficient cells with the CF.
You will need to be careful with the order and/or the 'Stop if true' condition for these three rules.

Excel Workbook
A
1Numbers
21.4
30.9
40.1
5-6.7
6-9.3
7-7.6
8-4.7
9-0.8
10-1.6
11
12
CF Near Zero (2)
#VALUE!
 

Ultimate Selector

Board Regular
Joined
Dec 15, 2013
Messages
125
Most excellent Pete! Absolutely Fantastic!

My bad when I said deleting columns when indeed I meant rows!
This has been kickin my Azz for over a year now!

Works like a charm! You're a genius!

Again, so many Thanks and have a stupendous week Pete!

Regards,
U.S. :)
 

Ultimate Selector

Board Regular
Joined
Dec 15, 2013
Messages
125
Hello Aryatect,

Thanks for correcting my statement. I did use the 4 in the same function which was max.
I didn't move to the next argument. My bad!

Much Appreciated on your input!

Have a Great Day!

- U.S.:cool:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,611
Office Version
365
Platform
Windows
.. but 4 is the next argument which means "Ignore Nothing" as don't ignore error.
I did use the 4 in the same function which was max.
I didn't move to the next argument.
.. but note that my latest suggestion uses the second argument of '6' (DO ignore error values)
 

Ultimate Selector

Board Regular
Joined
Dec 15, 2013
Messages
125
.. but note that my latest suggestion uses the second argument of '6' (DO ignore error values)
I see that Pete Thanks my friend.

I have one other question for you.

If I wanted to change the number from closest to zero and lets say make it closest to 50 instead where in the statement will I make that change?

Thanks!
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,611
Office Version
365
Platform
Windows
If I wanted to change the number from closest to zero and lets say make it closest to 50 instead where in the statement will I make that change?
Try something like
=ISNUMBER(A1)*(ABS(A1-50)<=AGGREGATE(14,6,ABS(A$1:A$1000-50)*(A$1:A$1000<>""),COUNT(A$1:A$1000)-2))
 
Last edited:

Ultimate Selector

Board Regular
Joined
Dec 15, 2013
Messages
125
Sorry Pete but I did it several times and I get the "error message" popping up!

Another idea or thought?

Thanks!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,611
Office Version
365
Platform
Windows
Sorry Pete but I did it several times and I get the "error message" popping up!

Another idea or thought?

Thanks!
It is working for me as is ...

Excel Workbook
A
1Numbers
239
351
466
563
645
739
870
963
1060
11
12
CF Near 50
#VALUE!
 
Last edited:

Ultimate Selector

Board Regular
Joined
Dec 15, 2013
Messages
125
Thanks Pete! Once again you're spot on.

I don't know what I did earlier but it works great now!

Much Appreciated!
 

Forum statistics

Threads
1,085,261
Messages
5,382,633
Members
401,796
Latest member
MaddAddaM

Some videos you may like

This Week's Hot Topics

Top