Conditional Formatting Formula Not Working


S

Steve B

I have created a series of conditional formats that turn a cell "Red" if one
group of numbers does not equal (<>) a certain cell value. However, it
appears that even when the condition is "False" the cells are turning Red.

When I check the total from the cells being compared they the same as the
comparitor cell and thus should not turn red.

The cells feeding the range being evaluated are comprised of formulas that
derive numbers. Because they are formulas the decimal calculation are
carried out beyond 5 decimal places. If I type in the result to less than 5
decimal places the conditional formula works correctly.

Does anyone know how to truncate the formulas at 4 or less decimal places?
E.g. simply setting the cell to that number does not truncate it vs. simply
showing that level in the spreadsheet.

Or, can anyone explain how to get around the problem without deleting the
formulas?

The conditional formula looks something like the following:

formula Is: =(SUM($C$8:$F$8)+$AU$8)<>$H$3 with format to turn cell Red if
statement is true.
 
Ad

Advertisements

L

Luke M

This will truncate all but 4 decimals after the arithmatic operations
(5.12345 becomes 5.1234).

=(TRUNC(SUM($C$8:$F$8)+$AU$8,4)<>$H$3
If you prefer to round (5.12345 becomes 5.1235),
=(ROUND(SUM($C$8:$F$8)+$AU$8,4)<>$H$3
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top