Find variance between two columns in two different Excel files

[VictorYan]

.

[Dynamomo · Accepted solution]

@VictorYan 

Can you share the formula with us?  The process looks right.

[VictorYan]

.

[Dynamomo]

@VictorYan

You posted a few things here then deleted them.  I am not entirely sure what you want to achieve, but here are a few comments/suggestions.

The formula you posted earlier had too many ABS functions in it and ultimately, removing the outer one will remove that error.

However, these will also work and get the same true/false values.

IF ([Price]-[Right_Price])/[Right_Price]>=0.1 or ([Price]-[Right_Price])/[Right_Price]<=-0.1 THEN "True" ELSE "FALSE" ENDIF

if you are testing if the variance is <=-0.1 then you can't use ABS because it will put the value into the positive.

This will also work:

IF abs(([Price]-[Right_Price])/[Right_Price])>=0.1 then "true" else "false" endif

If you want to just filter out the records where the variance is greater than 0.1 (meaning >0.1 and <-0.1) then you can put this into teh Filter tool directly and not even use a Formula tool:

abs(([Price]-[Right_Price])/[Right_Price])>=0.1

The values that have a variance above this threshold will go out the True side and those with a lower variance will go out the False side.

Hope this helps.