Returning smallest/largest change

**Koolaid** · 03-10-2010, 08:08 PM

Hi everyone

This is probably an easy problem, but I've been trying to get this thing to work for a few hours now and I'm getting frustrated.

Suppose you have a list of several percentage changes:
4%
1%
-3%
-5%

What kind of command can you use while in conditional formatting so that it'll highlight 1% as the smallest % change? Min and small commands don't seem to work since they both return -5% as the smallest change (but instead it's actually the biggest).

Any suggestions? Thanks!

**Paul** · 03-10-2010, 08:52 PM

Hi Koolaid,

Let's say your list is in A1:A4. Select that range and then:

To highlight the largest change set conditional formatting formula to:

=OR(A1=MAX(ABS($A$1:$A$4)),-A1=MAX(ABS($A$1:$A$4)))

To highlight the smallest change, use:

=OR(A1=MIN(ABS($A$1:$A$4)),-A1=MIN(ABS($A$1:$A$4)))

**shg** · 03-10-2010, 09:19 PM

Or =ABS(A1)=MIN(ABS($A$1:$A$4))

**Koolaid** · 03-10-2010, 11:24 PM

Awesome! Both works great, thanks Paul and shg!

Can I also tell it to not return 0s as the smallest value? i.e. the next smallest?
I tried using

=ABS(A1)=SMALL(ABS($A$1:$A$4),2)

but that didn't seem to work.