Conditional formatting-Is there a formula for when negative numbers will sometimes be

[sweetsue516]

Trying to compare to numbers, some are negative. The cells contain formulas
so I am using formulas verses cell values for comparision.

The problem I am having is when the numbers are negative. We are comparing
a loss of -4.39 (cell a6) which is better and the one we want the format
applied to, than -4.72 (cell a8) but since these numbers are only sometimes
negative the forumla is =a6>a8 for a formatting result of yellow background.

Is there a formula for when negative numbers will sometimes be involved?

[swatsp0p]

Try squaring the numbers before comparing them. This will eliminate the negative issue, assuming you want the value closest to zero.

=a6^2<a8^2 should meet your needs regardless of positive or negative values.

[David McRitchie]

Verify that you do in fact have numbers.

=ISNUMBER(a1)

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"sweetsue516" <[email protected]> wrote in message news:[email protected]...
> Trying to compare to numbers, some are negative. The cells contain formulas
> so I am using formulas verses cell values for comparision.
>
> The problem I am having is when the numbers are negative. We are comparing
> a loss of -4.39 (cell a6) which is better and the one we want the format
> applied to, than -4.72 (cell a8) but since these numbers are only sometimes
> negative the forumla is =a6>a8 for a formatting result of yellow background.
>
> Is there a formula for when negative numbers will sometimes be involved?

[B. R.Ramachandran]

Comparing squares will fail if the two numbers being compared are equal in
magnitude by opposite in sign. (eg., a6=-4.39, and a8=4.39, or vice versa).

Direct comparison SHOULD work regardless of their sign. Of course, as David
McRitchie remarked, the values should be formatted as numbers, not strings.

B.R.Ramachandran

"swatsp0p" wrote:

>
> Try squaring the numbers before comparing them. This will eliminate the
> negative issue, assuming you want the value closest to zero.
>
> =a6^2<a8^2 should meet your needs regardless of positive or negative
> values.
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=399121
>
>