Conditional Formating Problem

  • Thread starter Thread starter Steven T. Robillard
  • Start date Start date
S

Steven T. Robillard

A large shreadsheet that I have compares the sum of individual
payments in one worksheet to the sum of daily payments in another
sheet.

A cell has conditional fomating to turn red if the comparison is false
and green if it is true.

It has worked correctly for over a year and now a strange thing is
happening. Even though the numbers are the same the cell is red.

I have gone back and made sure the formating of the cell being
compared were set to be currency with 2 places after the decimal point
but no help.

The really strange thing is that if I change the payment to a whole
number or a number with a value in the tenth place the validation
turns green but is I use a value in the hundreth place (ie. I add one
penny to the payment in the daily and the individual payment) the cell
turns red. Even though the cells show that the value is the same.

I have run out of ideas of what could be going on.

Can anyone make any suggestions of what else I should look for.

Steve
 
I run into the same problem now and then and it has something to do with the
accuracy of Excel (or something like that). If you are comparing numbers
that are the results of formulas, there may be something floating out
several decimal places from the hundreths you see (when I use accounting
format I sometimes get a 0 in a cell when I subtract a number from itself
rather than a "-"). My solution for when it is critical for the number to
exactly match is to use the ROUND function and round off all formulas to 2
or however many decimal places I need. This has always worked for me.

HTH
Mike.
 
Steven T. Robillard said:
A large shreadsheet that I have compares the sum of individual
payments in one worksheet to the sum of daily payments in another
sheet.

A cell has conditional fomating to turn red if the comparison is false
and green if it is true.

It has worked correctly for over a year and now a strange thing is
happening. Even though the numbers are the same the cell is red.

I have gone back and made sure the formating of the cell being
compared were set to be currency with 2 places after the decimal point
but no help.

The really strange thing is that if I change the payment to a whole
number or a number with a value in the tenth place the validation
turns green but is I use a value in the hundreth place (ie. I add one
penny to the payment in the daily and the individual payment) the cell
turns red. Even though the cells show that the value is the same.

I have run out of ideas of what could be going on.

Can anyone make any suggestions of what else I should look for.

Steve
Click to expand...

The problem may be due to rounding errors. When you format a cell, you only
change what is displayed, NOT the underlying value in the cell. So, for
example, with a format of two decimal places 123.450000000001 will display
as 123.45 but will not equal another cell that actually contains the value
123.45 .

There are several approaches to solving this, amongst which are these two:

1 Change the formula in the cell so that the underlying value is rounded to
two decimal places.
=ROUND(YourFormula,2)

2 Change the conditional format formula so that it doesn't require an exact
match. For example, instead of
=(A1=B1)
use
=(ABS(A1-B1)<0.001)
 
Thank you for the idea. I figured it was a rounding error although I
thought incorrectly that when I set the display to 2 places right of
the decimal point it was also rounding the number.

Steve
 
Thank you Michael. I figured it was a rounding error but I incorectly
thought that it was already rounding the number.

Steve
 
Back
Top