Community Forums

Share:

excel formula formatting questoin-settlement check data  


DANEMINCE@YAHOO.COM
Posts: 372
250+ posts
Joined: 9 years ago

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

7 Replies
MathTeacher
Posts: 765
Member
(@mathteacher)
500+ posts
Joined: 6 years ago

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.

 

 

Reply
2 Replies
Dave Karoly
Member
(@dave-karoly)
Joined: 9 years ago

10,000+ posts
Posts: 10362

@mathteacher

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

Reply
MathTeacher
Member
(@mathteacher)
Joined: 6 years ago

500+ posts
Posts: 765

@dave-karoly

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.

Reply
dmyhill
Posts: 1241
Member
(@dmyhill)
1,000+ posts
Joined: 9 years ago

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.

Reply
1 Reply
MathTeacher
Member
(@mathteacher)
Joined: 6 years ago

500+ posts
Posts: 765

@dmyhill

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.

Reply
DANEMINCE@YAHOO.COM
Posts: 372
250+ posts
Joined: 9 years ago

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.    

Reply
1 Reply
MathTeacher
Member
(@mathteacher)
Joined: 6 years ago

500+ posts
Posts: 765

@[email protected]

Nice!

Reply
Default
:)
:d
:wink:
:mrgreen:
:neutral:
:twisted:
:arrow:
:shock:
:???:
:cool:
:evil:
:oops:
:razz:
:roll:
:cry:
:eek:
:lol:
:mad:
:sad:
:!:
:?:
:idea:
:hmm:
:beg:
:whew:
:chuckle:
:silly:
:envy:
:shutmouth: