Conditional formatting

[Guest]

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)

--

 

[Guest]

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