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
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.
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.
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.