Conditional formatting
 

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 =a6a8 for a formatting result of yellow background.

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

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

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" wrote in message ...
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 =a6a8 for a formatting result of yellow background.

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

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