# Community Forums

# excel formula formatting questoin-settlement check data

I want to show the change in XYZ from visit to the next. There are slight differences in the data from one visit to the next. i have a formatting problem in that I would like the cell to return NO CHANGE with they differences in the values is 0.00 . For some reason, some other value is always returned ,i.e. North , South, East,West , Up, Down. Any suggestion on how to fix this?nAny suggestions on conditional formmating? A sample formula is below

=IF('BASIS VISIT'!B3='VISIT 1'!B3,"NOCHANGE",IF('BASIS VISIT'!B3>'VISIT 1'!B3,"SOUTH",IF('BASIS VISIT'!B3<'VISIT 1'!B3,"NORTH")))

thanks for your help

Dane Ince LS 8142, CFEDS 1099

201 Harrison St. Ste.828

San Francisco, California 94105

415-321-9300

[email][email protected][/email]

It's possible that you haven't encountered a true equal test yet if the data is the result of a calculation and the numbers are floating point. For example, one might be 0.5 and the other 0.4999999999999. Both might display or print as 0.5 but they would test unequal. One way to fix this is: =if(abs(cell1 - cell2)<=1E-14,"NO CHANGE",...). The 1E-14 says that if they are within 10^-14 of each other, they are equal. You can pick a different level of precision for two values to be equal by changing the negative exponent.

You don't need the last if statement. Just "NORTH" is sufficient because what follows the last comma is what you want to happen if both of the first two tests return false.

As far as the laws of mathematics refer to reality, they are not certain, and as far as they are certain, they do not refer to reality. *Albert Einstein*

Or simply round the answer to 2 places...=(round(a1,2))

O death, where is thy sting? O grave, where is thy victory? -1 Corinthians 15:55

Yep, not quite as tight for a given number of decimal places, but easier. If you want 3.4554 and 3.4649 to be equal, round to 2 decimal places. If you want them to be different, round to 3 decimal places.

Either way, you have to define "equal" for your purposes and then trick the machine into getting the right answer for you. I wish I had back some of the hours I spent chasing down bugs caused by equality tests on floating point numbers.

As far as the laws of mathematics refer to reality, they are not certain, and as far as they are certain, they do not refer to reality. *Albert Einstein*

I used to do this in spreadsheets, now if I want a flag I just use conditional formatting. It is pretty straightforward to set up rules that highlight cells with different colors.

The problem is in determining equality. For example, if A1 contains =sqrt(18) and A2 contains =3*sqrt(2), putting this in A3 will return "no": =if(A1=A2,"yes","no"). The two expressions are the same number, but it is an irrational number that can't be represented exactly as a floating point number. Two different computations of the number result in two slightly different floating point answers.

But the same problem can exist with rational numbers. Consider =1/9 in A3 and =1234/9*1/1234 in A4, which are equal. However, this formula in A5, =if(A3-A4=0,"yes","no") returns "no". But, =if(A3=A4,"yes","no") returns "yes".

I'm not sure that conditional formatting can cope with floating point inequality/equality, but I suspect that surveying data contains a generous share of examples.

As far as the laws of mathematics refer to reality, they are not certain, and as far as they are certain, they do not refer to reality. *Albert Einstein*

okay finally got it to work. The comments about equality were spot on . I googled excel precision and followed the instructions. I was tearing my hair out because I would get return that 0.01 was equal to no change. I changed the precision of all the cells containing data and result formula cells. I used cutsom formatting along with conditional formatting. in the Z results. This allowed me to display an up arrow, down arrow and no change symbol along side text.

Dane Ince LS 8142, CFEDS 1099

201 Harrison St. Ste.828

San Francisco, California 94105

415-321-9300

[email][email protected][/email]

Nice!

*Albert Einstein*

Latest Post: I remember when... Our newest member: bmg07 Recent Posts Unread Posts Tags

Forum Icons: Forum contains no unread posts Forum contains unread posts Mark all read

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed