Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |